Mysql 备份/还原方案

Mysql 备份方案

Mysql 的四种备份方案参考：
http://www.cnblogs.com/SQL888/p/5751631.html

这里使用的是增量加全备方案
参考：http://blog.csdn.net/hijiankang/article/details/10999501

全备命令：
非压缩备份
mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --events --ignore-table=mysql.events --all-databases --delete-master-logs >/data/mysql/backup/full_bk.sql;

压缩备份
mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --events --ignore-table=mysql.events --all-databases --delete-master-logs |gzip >/data/mysql/backup/db/full_bk_$(date +%Y%m%d%H%M%S).tar.gz;

注：
--flush-logs 切割binlog，这样不用担心如何去区分在备份过程中新进来的数据。
--delete-master-logs 在备份完成后删除已备份的binlog文件

如果是远程备份，请加上以下项：
-e --max_allowed_packet=4194304 --net_buffer_length=16384
max_allowed_packet和net_buffer_length要和服务器上的参数一致，服务器上查看方法：
mysql> show variables like 'max_allowed_packet';
mysql> show variables like 'net_buffer_length';

增量备份：
mysqladmin -uroot -p flush-logs;


建一个backup用户
grant ALL on *.* to `backup`@`localhost` identified by '111111';

脚本

full_bk.sh

#! /bin/bash
/bin/su -s /bin/sh - mysql -c "mysqldump -ubackup -p111111 --single-transaction --flush-logs --master-data=2 --events --ignore-table=mysql.events --all-databases --delete-master-logs |gzip >/data/mysql/backup/db/full_bk_$(date +%Y%m%d%H%M%S).tar.gz" >> log_$(date +%Y-%m-%d_%H:%M:%S).log 2>&1


increase_bk.sh

#! /bin/sh
/bin/su -s /bin/sh - mysql -c "mysqladmin -ubackup -p111111 flush-logs" >> increaseLog_$(date +%Y-%m-%d_%H:%M:%S).log

设置定时任务
crontab -e
#每周六的3点执行全库备份
00 3 * * 6 /home/mysql/full_bk.sh
#每天12点执行增量备份
00 12 * * * /home/mysql/full_bk.sh




数据库恢复

1. service mysql stop


下面两步非常重要
2. 拷贝/data/mysql/data/下日期大于最后一次全库备份文件日期的binlog文件到其它地方

3. 把其它相关DB机器上的slave停掉,以防数据重写

4. 删除/data/mysql/data/下所有文件

5. mysql_install_db

6. 开始还原数据

service mysql start

mysql> SHOW DATABASES;   #查看数据库, 数据丢失!
mysql> SET sql_log_bin=OFF;   #暂时先将二进制日志关闭 

非压缩还原
mysql -uroot -p < full_bk_20170824001553.sql

压缩还原
gunzip < db/full_bk_20170831071221.tar.gz | mysql -uroot -padmin


mysql> SHOW DATABASES;   #数据库恢复

还原日期大于最后一次全库备份文件日期的binlog文件中的数据
恢复binlog-file二进制日志文件：
mysqlbinlog --no-defaults /data/backup/mysql-bin.000007 /data/backup/mysql-bin.000008 /data/backup/mysql-bin.000009 | mysql -uroot -p123456
从某一(367)点开始恢复：
mysqlbinlog --no-defaults --stop-position="367" mysql-bin.000001| mysql -uroot -p123456 test
先查好那一点（用more来查看）
[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | more
然后恢复：


[root@localhost mysql]# /usr/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="794" --stop-position="1055" | /usr/bin/mysql -uroot -p123456 test



mysql> SET sql_log_bin=ON; 开启二进制日志

查看master状态
mysql> show master status;

更新slave机器上的master信息.


遇见问题：

slave启动报这个错误：Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
解决方案:

stop slave;

reset slave;

start slave;