MySQL延迟复制

MySQL延迟复制--percona-toolkit和MASTER TO MASTER_DELAY

一、MASTER TO MASTER_DELAY延迟复制

便于数据恢复，设置从库延迟主库1个小时

方案优点：没有万一，都能快速恢复数据

潜在不足：资源利用率有点低，为了保证数据的安全性，多了2台延时从，降低了从库利用率

【提高从库效率】

1小时延时从也不是完全没有用，对于一些“允许延时”的业务，可以使用1小时延时从，例如：

（1）运营后台，产品后台

（2）BI进行数据同步

（3）研发进行数据抽样，调研

但需要注意的是，毕竟这是从库，只能够提供“只读”服务哟。

【总结】

保证数据的安全性是DBA第一要务，需要进行：

（1）全量备份+增量备份，并定期进行恢复演练，但该方案恢复时间较久，对系统可用性影响大

（2）1小时延时从，双份1小时延时从能极大加速数据库恢复时间

（3）个人建议1小时延时从足够，后台只读服务可以连1小时延时从，提高资源利用率

1、延时从库主从复制解决逻辑损坏

主从复制功能解决了物理损坏(比如把1台服务器砸了)没事,

那么逻辑损坏怎么办呢?

什么是延时,比如,主库删除文件1小时后同步到从库,

慢点传送日志,或者从库sql线程延时,执行语句

SQL线程进行延时设置

延时从库前提,设置主从复制

以多实例为例

3307为主库

3308,3309从库

从库 关闭binlog

mysql> set sql_log_bin=0;

 

1、主库全备设置主从用户

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

[root@db01 3307]#mysqldump -S /data/3307/mysql.sock -A -R --triggers --master-data=2 --single-transaction > /backup/full3307.sql

3307主库查看pos起点和binlog

[root@db01 ~]#sed -n '22p' /backup/full3307.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=120;

 

2、3308,3309设置从库

change master to

master_host='10.0.0.51',

MASTER_PORT=3307,

master_user='repl',

master_log_file='mysql-bin.000006',

master_password='123',

MASTER_LOG_POS=4,

master_connect_retry=10;

 

3、开启从库

start slave;

4、从库设置延时

18000  5小时
1800   30分钟

mysql> stop slave;                    #停止从库

mysql> change master to master_delay = 60;    #延时69秒

mysql> change master to master_delay = 0;    #关闭延时

mysql> start slave;

5、从库查看状态

mysql> show slave status\G

SQL_Delay: 60

6、结论

此时主库有任何插入,删除等操作都是60秒后同步到从库

 

二、利用延时从库恢复数据

1、企业案例

主库删除了db库,从库会在3600秒后,执行删库操作,我要阻止这一切,怎么用延时从库恢复数据呢?

2、处理思路:

停止SQL线程,如果不停止,总有一个时候会同步到从库,

为了严谨,如果后续还有数据从主库复制过来,之关闭sql线程就可以,不让sql线程执行任何操作

3、关闭SQL线程

mysql> stop slave sql_thread;

4、起始点分析

sql线程上次的结束点保存在relay-log.info里,所以relay-log.info就是恢复点

5、结束点分析

我们手动模仿sql线程工作,阻止sql线程执行到drop.因此 结束点就是到drop之前的操作,我要让从库数据状态达到删除之前的状态,还缺什么呢?

对于从库来讲是这后半个小时之内的日志,从库是基于relay.log恢复的,

从库截取relay.log 到误删除之前的点,relay-log里是从库从主库里收过来的事件,所以,结束点就在relay-log里

6、延时从库恢复数据

主库3307

有以下数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| aaaa |

| binlog |

| mysql |

| performance_schema |

| test |

| world |

+--------------------+

6.1、主库插入数据

mysql> use aaaa;

mysql> create table t1 select * from mysql.user;

mysql> create table t2 select * from mysql.user;

6.2、从库3308,3309 3309开启延时从库

mysql> stop slave;

mysql> change master to master_delay=180;

mysql> start slave;

6.3、主库再插入数据

mysql> create table t3 select * from mysql.user;

mysql> create table t4 select * from mysql.user;

6.4、3307主库删除aaa库

mysql> drop database aaaa;

6.5、查看aaa库删库了

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| binlog |

| mysql |

| performance_schema |

| test |

| world |

+--------------------+

6.6、3309从库关闭sql线程

mysql> stop slave sql_thread;

6.7、此时3307和3308库的aaa表都已经删除了.3309的aaa库还在

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| binlog |

| mysql |

| performance_schema |

| test |

| world |
 

 7、  利用3309的延时从库,把3309的aaa库恢复到主库
       7.1 截取起点

relay-log.info里保存了 从库的上次sql线程执行的位置,那么这个点就是起点,

[root@db01 ~]#cat /data/3309/data/relay-log.info

7

./db01-relay-bin.000002

283

mysql-bin.000004

1355285

180

0

1
2、截取终点

终点在relaylog里drop aaaa之前

show relaylog events in 'db01-relay-bin.000002'

或者

[root@db01 ~]#mysqlbinlog --base64-output=decode-rows -vvv /data/3309/data/db01-relay-bin.000002

### @42='' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

### @43=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

# at 7284

#190312 23:23:50 server id 3307 end_log_pos 1362317 CRC32 0x2caf9cff     Xid = 5808

COMMIT/*!*/;

# at 7315

#190312 23:24:00 server id 3307 end_log_pos 1362409 CRC32 0xe14bd3b2     Query    thread_id=8    exec_time=0    error_code=0

SET TIMESTAMP=1552404240/*!*/;

drop database aaaa

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    截取relaylog

[root@db01 ~]#mysqlbinlog --start-position=283 --stop-position=7284 /data/3309/data/db01-relay-bin.000002 > /tmp/aaaarelaylog.sql

    恢复relaylog.sql

取消3307从库身份

mysql> stop slave;

mysql> reset salve all;

    3307主库恢复数据

mysql> source /tmp/aaaarelaylog.sql;

    报错,是因为截取的relaylog里没有创建aaaa表的语句,relaylog到时间自动删除了一些东西

ERROR 1049 (42000): Unknown database 'aaaa'

Query OK, 0 rows affected (0.00 sec)

ERROR 1046 (3D000): No database selected

ERROR 1146 (42S02): Table 'aaaa.t3' doesn't exist

    添加aaaa库

mysql> create database aaaa;

mysql> source /tmp/aaaarelaylog.sql;

    数据都回来了

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| aaaa |

| binlog |

| mysql |

| performance_schema |

| test |

| world |

+--------------------+

7 rows in set (0.00 sec)

mysql> use aaaa;

Database changed

mysql> show tables;

+----------------+

| Tables_in_aaaa |

+----------------+

| t3 |

| t4 |

+----------------+

    延时从库600秒, 删库10分钟之前有人插入数据,任务是恢复10分钟之前插入的数据
    模拟有人插入数据

mysql> use test;

mysql> create table t1111 select * from mysql.user;

mysql> create table t2222 select * from mysql.user;

    删库

mysql> drop database test;

test库没了

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| aaaa |

| binlog |

| mysql |

| performance_schema |

| world |

+--------------------+

 

由于3309开启了延时从库600秒,此时对于3309这个库来说,test库之前就存在, 只是个空库,别人写的数据,数据还没写到库里,只存到了relaylog里,因此我们要从relaylog里截取10分钟之前写入的数据

    从库截取relaylog起点

[root@db01 ~]#cat /data/3309/data/relay-log.info

7

./db01-relay-bin.000002

283

mysql-bin.000004

1376814

600

0

1

    截取relaylog结束点

[root@db01 ~]#mysqlbinlog --base64-output=decode-rows /data/3309/data/db01-relay-bin.000002

# at 6382

#190313 0:01:31 server id 3307 end_log_pos 1383082 CRC32 0x506ff15a     Table_map: `test`.`t2222` mapped to number 129

# at 6551

#190313 0:01:31 server id 3307 end_log_pos 1383827 CRC32 0x66414a9d     Write_rows: table id 129 flags: STMT_END_F

# at 7296

#190313 0:01:31 server id 3307 end_log_pos 1383858 CRC32 0xd1274362     Xid = 5948

COMMIT/*!*/;

# at 7327

#190313 0:02:12 server id 3307 end_log_pos 1383950 CRC32 0x599faa44     Query    thread_id=11    exec_time=0    error_code=0

SET TIMESTAMP=1552406532/*!*/;

drop database test

    导出 relaylog 10分钟之前别人写入的数据

[root@db01 ~]#mysqlbinlog --start-position=283 --stop-position=7296 /data/3309/data/db01-relay-bin.000002 > /tmp/incrtestelaylog.sql

    备份3309从库里的 test库,此时是个空库

[root@db01 ~]#mysqldump -S /data/3309/mysql.sock -B test > /tmp/test.sql

 

    3307主库导入test库

mysql> source /tmp/test.sql;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| aaaa |

| binlog |

| mysql |

| performance_schema |

| test |

| world |

    导入数据,数据都回来了

mysql> source /tmp/incrtestelaylog.sql

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| t1111 |

| t2222 |

+----------------+