mysql如何使用延迟复制拯救你的误操作（及sql_slave_skip_counter的含义）

最新推荐文章于 2021-03-23 11:24:04 发布

freedom5112

最新推荐文章于 2021-03-23 11:24:04 发布

阅读量1.8k

点赞数

分类专栏： MYSQL_REPLICATION

本文链接：https://blog.csdn.net/lidan3959/article/details/21457271

版权

MYSQL_REPLICATION 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

说明：本文所述内容都是是基于mysql 5.5.x及mairadb 10.0.x 版本

如果是mysql5.6 及其以上版本可使用：CHANGE MASTER TO MASTER_DELAY = N；N为多少秒，该语句设置从数据库延时N秒后，再与主数据库进行数据同步复制。具体介绍详见：ＭySql 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

http://www.percona.com/doc/percona-toolkit/2.2/