说明:本文所述内容都是是基于mysql 5.5.x及mairadb 10.0.x 版本
如果是mysql5.6 及其以上版本可使用:CHANGE MASTER TO MASTER_DELAY = N;N为多少秒,该语句设置从数据库延时N秒后,再与主数据库进行数据同步复制。具体介绍详见 :MySql 5.6 CHANGE MASTER语法 。
为了数据库安全,作为dba很多时候都绞尽脑汁进行各种控制权限,各种操作限制,但无论如何不管怎么控制,如果是dba自己操作特别是drop table ,truncate,delete等难免会出现失误的时候。也许你说你有备份,但是停机恢复不是什么公司都能承受的而且恢复起来也很不方便。所以,这里简单介绍通过mysql“延迟复制”来为你数据库实现平滑的逻辑恢复。
1,mysql5.5.x 版本如何实现“延迟复制“
这里使用的是percona-toolkit-2.2.5-1 工具,如果你还不清楚它,请点击:percona-toolkits 介绍
用例:
/usr/bin/pt-slave-delay --user=${USER_NAME} --password=${PASS_WORD} --port=${PORT} --daemonize --interval=5m --delay=8h --use-master 123.111.222.110(备库ip)
上述表示每个5分钟检查slave是否需要启动或停止,slave设置比master延迟8小时,binlog点是以master为标准,脚本以守护进程的方式在后台运行
延迟效果如图(1):
图 1
当脚本检查复制延迟操过了设定的时间那么就会启动sql线程进行追赶知道延迟8小时则停止。
注:因为脚本每隔5分钟才检查一次复制情况所以实际延迟其实是8小时5分
追赶主库期间效果如图(2)
2,如何使用”延迟复制“进行恢复
a),主库操作如下(启用自动提交模式):
insert into t values(111);
insert into t values(222);
insert into t values(333);
start transaction; --开启一个事务 假如该事务是误操作 ###
insert into t values(444);
insert into t values(555);
commit; --提交事务
insert into t values(666);
查看binlog日志:
b),备库操作如下
start slave until master_log_file='mysql-bin.0000012' ,MASTER_LOG_POS = 872;
告诉mysql复制执行到master binlog 0000012 中end_log_pos为872的位置然后停止
然后查看:
MariaDB [test]> select * from t;
+------+
| id |
+------+
| 111 |
| 222 |
| 333 |
+------+
发现按照预期的结果复制
现在需要跳过 444,555两条记录 故执行 stop slave ; set global sql_slave_skip_counter=1; start slave;
最后show slave status \G
说明已经执行到了最后的一个end_log_pos 1294。最后查看表里的数据
MariaDB [test]> SELECT * FROM T;
+------+
| id |
+------+
| 111 |
| 222 |
| 333 |
| 666 |
+------+
ok,成功跳过了记录为444,555的插入操作。
PS:
关于这里set global sql_slave_skip_counter=1;需要多解释一下 很多小伙伴都会想当然的认为该语句是跳过一条语句,这里其实是的分情况的: 一般来说(自动提交模式)每次执行一条插入或更新或删除操作,那么执行上述跳过语句你可以认为是一次跳过一条语句,但是如果是使用start transaction(非自动提交模式)那么情况就不大一样了,这个时候的跳过是按照事物进行跳跃的。
参考:
http://dev.mysql.com/doc/refman/5.6/en/change-master-to.html