MYSQL 延时同步-恢复数据

情况一：不做gtid

1、主、备机关闭gtid，,如果主备有一个开启，有一个未开启，做主备同步时候会报错：

The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF.

2、主、备机开启autocommit=0，取消自动提交

临时生效：

set @@autocommit=0 （0为关闭状态，1为开启状态）

永久生效：

修改my.cnf，在[mysqld]添加:     autocommit=0

手动提交：autocommit=0

自动提交：autocommit=1

3、主库创建复制用户

mysql -uroot -p123 -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.78.%' identified by '123'"

4、查看主库的Position号，跟正在使用的binlog

mysql> show master status;

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

| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |

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

| mysql-bin.000006 |      194 |             |                  | 7f805cdd-c0a2-11ea-8fc6-000c294d81a5:1 |

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

5、从库：

mysql> help change master to

CHANGE MASTER TO

  MASTER_HOST='master2.example.com',

  MASTER_USER='replication',

  MASTER_PASSWORD='password',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='master2-bin.001',

  MASTER_LOG_POS=4,

  MASTER_CONNECT_RETRY=10;

修改：

CHANGE MASTER TO

  MASTER_HOST='192.168.78.4',

  MASTER_USER='repl',

  MASTER_PASSWORD='123',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='mysql-bin.000006',

  MASTER_LOG_POS=194,

  MASTER_CONNECT_RETRY=10;

提交后

CHANGE MASTER TO MASTER_DELAY = 300;

mysql>start slave;

mysql> show slave status \G

SQL_Delay: 300

SQL_Remaining_Delay: NULL

测试：删库

1、主库 :

create database delay1 charset utf8mb4;

use delay1;

create table t1 (id int);

insert into t1 values(1),(2),(3);

commit;

后停顿40s

drop database delay;

2、在从库中：

从库发现在到技计时300s后执行，

create database delay charset utf8mb4;

use delay;

create table t1 (id int);

insert into t1 values(1),(2),(3);

commit;

从库停顿40s

会执行drop database delay;

解决方案：

# 1.停止 从库SQL 线程,执行

stop slave sql_thread

获取relay的位置点

mysql> show slave status \G

Relay_Log_File: localhost-relay-bin.000005

Relay_Log_Pos: 367

# 2. 找到relay的截取终点

show relaylog events in 'localhost-relay-bin.000005';

NONYMOUS'   |

| localhost-relay-bin.000005 |  780 | Query                    |         4 |         681 | BEGIN                                  |

| localhost-relay-bin.000005 |  854 | Table_map            |         4 |         728 | table_id: 108 (delay1.t1)              |

| localhost-relay-bin.000005 |  901 | Write_rows           |         4 |         778 | table_id: 108 flags: STMT_END_F        |

| localhost-relay-bin.000005 |  951 | Xid                        |         4 |         809 | COMMIT /* xid=23 */                    |

| localhost-relay-bin.000005 |  982 | Anonymous_Gtid |         4 |         874 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |

| localhost-relay-bin.000005 | 1047 | Query                   |         4 |         969 | drop database delay    

找到drop database delay   

#  3. 截取relay

[root@db01 data]# cd /data/3307/data/

[root@db01 data]# mysqlbinlog --start-position= 367 --stop-position=1047 localhost-relay-bin.000005 >/tmp/relay.sql

#  4. 恢复relay到从库

[root@db01 data]# mysql -uroot -p -S /data/3307/mysql.sock

mysql> set sql_log_bin=0;

mysql> source /tmp/relay.sql

#  5 跳过错误日志

show relaylog events in 'localhost-relay-bin.000005' from 367\G；

从367 到1047 一共13条；

stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 13；

slave start
mysql> show slave status\G；

情况二：

有GTID的延时同步恢复数据

跟上面一样

1、主备开启GTID在my.cnf

gtid-mode=on                               --启用gtid类型，否则就是普通的复制架构

enforce-gtid-consistency=true     --强制GTID的一致性

log-slave-updates=1   --slave       更新是否记入日志

在上述已经搭建的主从上（没有gtid）基础上，直接改my.cnd后，从sql_thread线程no,需要重新搭建主从

 @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON

遇到的问题：

从库：

主库：

从库：

stop slave;

mysq>CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013',MASTER_LOG_POS=194;

https://blog.csdn.net/edwzhang/article/details/17226975

2、做完同步后，测试删除库
stop slave sql_thread;

判断截取日志：

show slave status\G;

 Retrieved_Gtid_Set: 7f805cdd-c0a2-11ea-8fc6-000c294d81a5:1-4   下一次执行
 Executed_Gtid_Set: 7f805cdd-c0a2-11ea-8fc6-000c294d81a5:1-3   已经执行

show relaylog events in 'localhost-relay-bin.000005';

>> 截取1-3号事务:

[root@db01 ~]$ mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-3' /data/binlog/mysql-bin.000009>/data/gtid.sql

--skip-gtids                跳过检查gtid已记录信息

--include-gtids=            导入gtid记录id

--exclude-gtids=            跳过gtid记录id

>> 截取 1-10 gtid事务,跳过6号和8号事务.

[root@db01 ~]$ mysqlbinlog --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10 --exclude-gtids='545fd699-be48-11e9-8f0a-000c2980e248:6,545fd699-be48-11e9-8f0a-000c2980e248:8'    /data/binlog/mysql-bin.000009>/data/gtid.sql

备库：跳过误操作的语句

stop slave;

set gtid_next='7f805cdd-c0a2-11ea-8fc6-000c294d81a5:7';   

begin;commit;

set gtid_next='automatic';

change master to master_delay=0;  为了让后续任务执行完

start slave;

stop slave;

mysql>CHANGE MASTER TO MASTER_DELAY = 300;

start slave;

如何进行多节点删除

stop slave;

set gtid_next='7f805cdd-c0a2-11ea-8fc6-000c294d81a5:7';  

begin;commit;

set gtid_next='automatic';

set gtid_next='7f805cdd-c0a2-11ea-8fc6-000c294d81a5:8';  

begin;commit;

set gtid_next='automatic';

set gtid_next='7f805cdd-c0a2-11ea-8fc6-000c294d81a5:9';  

begin;commit;

set gtid_next='automatic';

change master to master_delay=0;  为了让后续任务执行完

如果不执行begin;commit;

When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '7f805cdd-c0a2-11ea-8fc6-000c294d81a5:9'.

如果不执行 set gtid_next='automatic';

ERROR 1837 (HY000): When @@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a different value after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual page for detailed explanation. Current @@SESSION.GTID_NEXT is '7f805cdd-c0a2-11ea-8fc6-000c294d81a5:10'.