启用binlog
vi my.cnf
log-bin=/var/lib/mysql/mysql-bin.log,如果是这样的话log-bin=mysql-bin.log默认在datadir目录下面
[root@BlackGhost mysql]# ls |grep mysql-bin
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.index
查看mysql-bin.000002这样的文件里面到底是什么东西
[root@BlackGhost mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 > /tmp/add.sql
binlog增量备份和增量还原
1,增量备份
既然我们知道了,mysql里面新增加的数据在mysql-bin这样的文件里面,我们只要把mysql-bin这样的文件进行备份就可以了。
cp /var/lib/mysql/mysql-bin* /data/mysql_newbak/
或
备份命令
mysqlbinlog --read-from-remote-server --raw --host=192.168.244.145 --port=3306 --user=repl --password=repl --stop-never mysql-bin.000001
解释如下:
–read-from-remote-server:用于备份远程服务器的binlog。如果不指定该选项,则会查找本地的binlog。
–raw:binlog日志会以二进制格式存储在磁盘中,如果不指定该选项,则会以文本形式保存。
–user:复制的MySQL用户,只需要授予REPLICATION SLAVE权限。
–stop-never:mysqlbinlog可以只从远程服务器获取指定的几个binlog,也可将不断生成的binlog保存到本地。指定此选项,代表只要远程服务器不关闭或者连接未断开,mysqlbinlog就会不断的复制远程服务器上的binlog。
mysql-bin.000001:代表从哪个binlog开始复制。
除了以上选项外,还有以下几个选项需要注意:
–stop-never-slave-server-id:在备份远程服务器的binlog时,mysqlbinlog本质上就相当于一个从服务器,该选项就是用来指定从服务器的server-id的。默认为-1。
–to-last-log:代表mysqlbinlog不仅能够获取指定的binlog,还能获取其后生成的binlog,获取完了,才终止。如果指定了–stop-never选项则会隐式打开–to-last-log选项。
–result-file:用于设置远程服务器的binlog,保存到本地的前缀。譬如对于mysql-bin.000001,如果指定–result-file=/test/backup-,则保存到本地后的文件名为/test/backup-mysql-bin.000001。注意:如果将–result-file设置为目录,则一定要带上目录分隔符“/”。譬如–result-file=/test/,而不是–result-file=/test,不然保存到本地的文件名为/testmysql-bin.000001。
我们现在要结合Binlog来恢复,但前提要找出误操作前的pos点,
通过事件的位置来恢复(不完全恢复)
[root@localhost ~]# mysqlbinlog -v –base64-output=DECODE-ROWS localhost-bin.000002 |grep -C 10 -i "drop database"
### INSERT INTO `xuanzhi`.`tb1`
### SET
### @1=5
### @2=’ee’
# at 290
#170327 21:10:55 server id 1313306 end_log_pos 321 CRC32 0x825a2f99 Xid = 78
COMMIT/*!*/;
# at 321
#170327 21:19:25 server id 1313306 end_log_pos 422
SET TIMESTAMP=1490620765/*!*/;
drop database xuanzhi
上面的黄色加粗的就是,一个是start-position,一个是stop-position
从上面可以看到,误操作前的pos点是321,那我们现在通过binlog来进行数据恢复:
[root@localhost mysql-5.6]# mysqlbinlog –start-position=329 –stop-position=321 localhost-bin.000001 localhost-bin.000002 |mysql -uroot -p123456 xuanzhi
–start-position是备份后记录下的pos点,
–stop-position是误操前的pos点,
如果多个binlog文件,那么start-position是第一个binlog文件的pos点,stop-position是最后一个binlog的pos点
通过事件的时间来恢复(不完全恢复)
我们可以通过参数–start-datetime 和 –stop-datetime指定恢复binlog日志的起止时间点,时间使用DATETIME格式。
比如在时间点2005-04-20 10:00:00我们删除掉一个库,我们要恢复该时间点前的所有日志
[root@localhost /]# mysqlbinlog –stop-datetime="2005-04-20 9:59:59" /usr/local/mysql/data/binlog.123456 | mysql -u root
我们可能几个小时后才发现该错误,后面又有一系列的增删查改等操作,我们还需要恢复后续的binlog,我们可以指定起始时间
组合
和基于时间点恢复类是,但是更加精确.因为同一时间点可能有多条SQL语句执行;
例:
#mysqlbinlog –start-date="2010-10-31 9:55:00" –stop-date="2010-10-31 10:05:00" /usr/local/mysql/var/mysql-bin.000013 > /tmp/mysql_restore.sql
该命令将在/tmp/目录下创建小的文件,编辑它找到错误语句前后的位置号,例如前后位置号分别是368312 和 368315
(2)恢复了以前的备份文件后,输入
#mysqlbinlog –stop-position="368312" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot -p
#mysqlbinlog –start-position="368315" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot –p
总结:
一、在恢复全备数据之前必须将该binlog文件移出,否则恢复过程中,会继续写入语句到binlog,最终导致增量恢复数据部分变得比较混乱
二、做好数据文件及binlog的备份至关重要,但不是备份完就算了,要定期进行数据恢复测试或演练
三、恢复时建议对外停止更新,即禁止更新数据库