用 gtid 二进制日志还原数据

一、gtid的配置

1、查看gtid是否开启：

mysql> show variables like '%gtid%';

2、修改配置：

[root@mysql data]# vim /etc/my.cnf
gtid_mode=on  #开启 gtid
enforce_gtid_consistency=true  #强制gtid一致性
log_slave_updates=1  #从复制中从库记录 binlog，并统一gtid信息

 3、重启数据库：

[root@mysql data]# systemctl restart mysqld

二、gtid日志截取

查看二进制日志的事件信息：

mysql> show master status;

 

mysql> show binlog events in 'mysql-bin.000004';

 

三、gtid截取日志实例

1、准备环境：

mysql> create database gtid charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;

mysql> use gtid;
Database changed

mysql> create table t1(id int) engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(11),(12),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;

 

2、删除数据：

mysql> drop database gtid;

mysql> show databases;

 

3、找起点和终点：

mysql> show master status;

 

mysql> show binlog events in 'mysql-bin.000004';

 

4、截取日志： 

[root@mysql bin_log]# mysqlbinlog  --skip-gtids --include-gtids='a223c211-61da-11ee-9d2b-000c29732e7b:2-5' mysql-bin.000004 > gtid.sql

5、恢复数据：

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /data/3306/data/bin_log/gtid.sql;

mysql> set sql_log_bin=1;

mysql> show databases;

mysql> use gtid;

mysql> select * from t1;

数据成功还原