mysql gtid binlog_mysql利用有gtid模式的binlog日志文件恢复数据

最新推荐文章于 2024-02-26 19:48:21 发布

大章鱼啊没秘密

最新推荐文章于 2024-02-26 19:48:21 发布

阅读量340

点赞数

文章标签： mysql gtid binlog

本文链接：https://blog.csdn.net/weixin_34374031/article/details/113271000

版权

一、新建数据库test_gtid，并在下面新建一个表test_gtid.并插入几条测试数据

mysql>create database test_gtid CHARSET utf8;

Query OK, 1 row affected (0.01 sec)

mysql> create table test_gtid( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL) ENGINE=INNODB CHARSET=utf8;

Query OK, 0 rows affected (0.11 sec)

mysql> insert into test_gtid(id,name)values(1,'andy');

Query OK, 1 row affected (0.02sec)

mysql> insert into test_gtid(id,name)values(2,'kevin');

Query OK, 1 row affected (0.03sec)

mysql> insert into test_gtid(id,name)values(3,'peter');

Query OK, 1 row affected (0.03sec)

mysql>

二、删除数据库test_gtid

mysql>drop database test_gtid;

Query OK, 1 row affected (0.06sec)

mysql>

mysql>show databases;

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

| Database |

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

| information_schema |

| hl_central_sms_deduction |

| mysql |

| performance_schema |

| sys |

| test |

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

6 rows in set (0.00sec)

mysql>

三、查看binlog日志文件过滤"create database",并显示上下文5行

[root@orderer run]# mysqlbinlog --base64-output=decode-rows -d test_gtid /home/mysql-5.7.26/data/master.000002|grep 'create database' -C 5#200407 11:38:10 server id 69 end_log_pos 483 CRC32 0x122f6de5 GTID last_committed=1 sequence_number=2 rbr_only=no

SET @@SESSION.GTID_NEXT= '143c2270-3b22-11ea-9dea-14feb5dc2c77:2'/*!*/;

# at 483#200407 11:38:10 server id 69 end_log_pos 605 CRC32 0x4de1053c Query thread_id=5 exec_time=0 error_code=0SET TIMESTAMP=1586230690/*!*/;

create database test_gtid CHARSET utf8

/*!*/;

# at 605#200407 11:41:33 server id 69 end_log_pos 670 CRC32 0x99dd5b88 GTID last_committed=2 sequence_number=3 rbr_only=no

SET @@SESSION.GTID_NEXT= '143c2270-3b22-11ea-9dea-14feb5dc2c77:3'/*!*/;

# at 670[root@orderer run]#

可以看到create database test_gtid CHARSET utf8命令的gtid为143c2270-3b22-11ea-9dea-14feb5dc2c77:2

四、查看binlog日志文件过滤"drop database",并显示上下文5行

[root@orderer run]# mysqlbinlog --base64-output=decode-rows -d test_gtid /home/mysql-5.7.26/data/master.000002 |grep "drop database" -C 5WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.

#200407 11:44:33 server id 69 end_log_pos 2231 CRC32 0xa862ff44 GTID last_committed=8 sequence_number=9 rbr_only=no

SET @@SESSION.GTID_NEXT= '143c2270-3b22-11ea-9dea-14feb5dc2c77:9'/*!*/;

# at 2231#200407 11:44:33 server id 69 end_log_pos 2338 CRC32 0xd4f9ce4b Query thread_id=5 exec_time=0 error_code=0SET TIMESTAMP=1586231073/*!*/;

drop database test_gtid

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

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

[root@orderer run]#

可以看到drop database语句的gtid为143c2270-3b22-11ea-9dea-14feb5dc2c77:9，那么利用gtid参数来截取恢复数据库的日志文件内容

五、截取指定gtid区间为:"143c2270-3b22-11ea-9dea-14feb5dc2c77:2到143c2270-3b22-11ea-9dea-14feb5dc2c77:8"的binlog日志文件

[root@orderer run]# mysqlbinlog --skip-gtids --include-gtids='143c2270-3b22-11ea-9dea-14feb5dc2c77:2-8' -d test_gtid /home/mysql-5.7.26/data/master.000002 > /home/binlog_test_gtid.sql;

六、进入mysql,利用source 导入sql

mysql> source /home/binlog_test_gtid.sql;