最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

使用MYSQLBINLOG工具恢复数据GTID范围

XAMPP相关 admin 23浏览 0评论

MYSQLBIINLOG 工具有STAR POSITION和START_TIME 这样的范围。不过POSTION只是在单个BINLOG文件内有效,
多个BINLOG文件它就失效了,START_TIME很多时候不精确。如今都用GTID,可看了帮助没有START_GTID,STOP_GTID的参数。
只有3个GTID参数
–include-gtids :包含GTID
–exclude-gtids :排除GITD
–skip-gtids :开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了

接下来我们测试下是否能达到我们想要的范围


mysql> show master logs;
+-----------------------+-----------+
| Log_name | File_size |
+-----------------------+-----------+
| mydebug_binlog.000001 | 154 |
+-----------------------+-----------+
1 row in set (0.00 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mydebug_binlog.000001 | 154 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| logs |
| mysql |
| performance_schema |
| sys |
| tpcc1000 |
+--------------------+
6 rows in set (0.00 sec)
/*我们创建数据库,表,插入数据,清空表操作*/
mysql> create database books;
Query OK, 1 row affected (0.01 sec)

mysql> show binlog events in 'mydebug_binlog.000001';
+-----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mydebug_binlog.000001 | 4 | Format_desc | 2500 | 123 | Server ver: 5.7.35-debug-log, Binlog ver: 4 |
| mydebug_binlog.000001 | 123 | Previous_gtids | 2500 | 154 | |
| mydebug_binlog.000001 | 154 | Gtid | 2500 | 219 | SET @@SESSION.GTID_NEXT= 'caa5c861-181e-11ec-9d4b-08002709cb25:1' |
| mydebug_binlog.000001 | 219 | Query | 2500 | 316 | create database books |
+-----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> use books;
Database changed
mysql> CREATE TABLE `dic_table_filed_value` (
-> `ID` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
-> `TABLE_NAME` VARCHAR(45) NOT NULL COMMENT '表名',
-> `FILED_NAME` VARCHAR(45) NOT NULL COMMENT '字段',
-> `VALUE_INT` SMALLINT(11) UNSIGNED NULL COMMENT '整形值',
-> `VALUE_CHAR` VARCHAR(5) NULL COMMENT '字符型值',
-> `CREATE_TIME` DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立时间',
-> `UPDATE_TIME` DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
-> `OPREATE_NAME` VARCHAR(15) NULL COMMENT '操作人名',
-> `MEMO` VARCHAR(100) NULL COMMENT '字段值说明',
-> PRIMARY KEY (`ID`),
-> UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))
-> COMMENT = '表字段取值说明表';
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+------------------------------------------+
| mydebug_binlog.000001 | 1244 | | | caa5c861-181e-11ec-9d4b-08002709cb25:1-2 |
+-----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'mydebug_binlog.000001'\G;
*************************** 1. row ***************************
Log_name: mydebug_binlog.000001
Pos: 4
Event_type: Format_desc
Server_id: 2500
End_log_pos: 123
Info: Server ver: 5.7.35-debug-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mydebug_binlog.000001
Pos: 123
Event_type: Previous_gtids
Server_id: 2500
End_log_pos: 154
Info:
*************************** 3. row ***************************
Log_name: mydebug_binlog.000001
Pos: 154
Event_type: Gtid
Server_id: 2500
End_log_pos: 219
Info: SET @@SESSION.GTID_NEXT= 'caa5c861-181e-11ec-9d4b-08002709cb25:1'
*************************** 4. row ***************************
Log_name: mydebug_binlog.000001
Pos: 219
Event_type: Query
Server_id: 2500
End_log_pos: 316
Info: create database books
*************************** 5. row ***************************
Log_name: mydebug_binlog.000001
Pos: 316
Event_type: Gtid
Server_id: 2500
End_log_pos: 381
Info: SET @@SESSION.GTID_NEXT= 'caa5c861-181e-11ec-9d4b-08002709cb25:2'
*************************** 6. row ***************************
Log_name: mydebug_binlog.000001
Pos: 381
Event_type: Query
Server_id: 2500
End_log_pos: 1244
Info: use `books`; CREATE TABLE `dic_table_filed_value` (
`ID` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
`TABLE_NAME` VARCHAR(45) NOT NULL COMMENT '表名',
`FILED_NAME` VARCHAR(45) NOT NULL COMMENT '字段',
`VALUE_INT` SMALLINT(11) UNSIGNED NULL COMMENT '整形值',
`VALUE_CHAR` VARCHAR(5) NULL COMMENT '字符型值',
`CREATE_TIME` DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '建立时间',
`UPDATE_TIME` DATETIME(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间',
`OPREATE_NAME` VARCHAR(15) NULL COMMENT '操作人名',
`MEMO` VARCHAR(100) NULL COMMENT '字段值说明',
PRIMARY KEY (`ID`),
UNIQUE INDEX `ID_UNIQUE` (`ID` ASC))
COMMENT = '表字段取值说明表'
6 rows in set (0.00 sec)

ERROR:
No query specified

/*生成多个BINLOG文件*/
mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;
+-----------------------+-----------+
| Log_name | File_size |
+-----------------------+-----------+
| mydebug_binlog.000001 | 1296 |
| mydebug_binlog.000002 | 194 |
+-----------------------+-----------+
2 rows in set (0.00 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+------------------------------------------+
| mydebug_binlog.000002 | 194 | | | caa5c861-181e-11ec-9d4b-08002709cb25:1-2 |
+-----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO dic_table_filed_value(TABLE_NAME,FILED_NAME,VALUE_INT,VALUE_CHAR,MEMO,OPREATE_NAME)
-> VALUES('DK_ONLINE','STATUS',0,NULL,'申请','DBA');
Query OK, 1 row affected (0.01 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+------------------------------------------+
| mydebug_binlog.000002 | 554 | | | caa5c861-181e-11ec-9d4b-08002709cb25:1-3 |
+-----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO dic_table_filed_value(TABLE_NAME,FILED_NAME,VALUE_INT,VALUE_CHAR,MEMO,OPREATE_NAME)
-> VALUES('DK_ONLINE','STATUS',1,NULL,'审核','DBA');

mysql> INSERT INTO dic_table_filed_value(TABLE_NAME,FILED_NAME,VALUE_INT,VALUE_CHAR,MEMO,OPREATE_NAME)
-> VALUES('DK_ONLINE','STATUS',2,NULL,'审核失败','DBA');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO dic_table_filed_value(TABLE_NAME,FILED_NAME,VALUE_INT,VALUE_CHAR,MEMO,OPREATE_NAME)
-> VALUES('DK_ONLINE','STATUS',3,NULL,'申请成功','DBA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO dic_table_filed_value(TABLE_NAME,FILED_NAME,VALUE_INT,VALUE_CHAR,MEMO,OPREATE_NAME)
-> VALUES('DK_ONLINE','STATUS',4,NULL,'申请失败','DBA');
Query OK, 1 row affected (0.00 sec)

/*我们的GTID值一致在增加*/
mysql> show master status;
+-----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+------------------------------------------+
| mydebug_binlog.000002 | 2012 | | | caa5c861-181e-11ec-9d4b-08002709cb25:1-7 |
+-----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> truncate table dic_table_filed_value;
Query OK, 0 rows affected (0.03 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+------------------------------------------+
| mydebug_binlog.000002 | 2181 | | | caa5c861-181e-11ec-9d4b-08002709cb25:1-8 |
+-----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+-----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+------------------------------------------+
| mydebug_binlog.000003 | 194 | | | caa5c861-181e-11ec-9d4b-08002709cb25:1-8 |
+-----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
/*你的可爱有多金的开发工程师把表给TURNCATE了,私下跑来求你帮忙*/
mysql> select count(*) from dic_table_filed_value;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

还好我们的日志还在,嗯 公司有DBA就是保姆!


[root@CENTOS7GUI logs]# ll
总用量 28
-rw-r----- 1 root root 1296 12月 11 18:35 mydebug_binlog.000001
-rw-r----- 1 root root 2233 12月 11 18:42 mydebug_binlog.000002
-rw-r----- 1 root root 194 12月 11 18:42 mydebug_binlog.000003
-rw-r----- 1 root root 156 12月 11 18:42 mydebug_binlog.index
-rw-r----- 1 root root 4128 12月 11 18:43 mydebug_general.log
-rw-r----- 1 root root 564 12月 11 18:42 mydebug_slow.log

mysqlbinlog --skip-gtids --exclude-gtids='caa5c861-181e-11ec-9d4b-08002709cb25:1-3' --include-gtids='caa5c861-181e-11ec-9d4b-08002709cb25:1-7' mydebug_binlog.000001 mydebug_binlog.000002 mydebug_binlog.000003 > INSERT_DATA.SQL

经检查 INSERT_DATA.SQL 缺少一条语句。EXCLUDE-GITDS是排除,我们的1-3里面是有语句的。所以我们要从1-2开始,
1-2<我们要的GTID范围<=1-7

[root@CENTOS7GUI logs]# /DB/debug/mysql5735/soft/bin/mysqlbinlog -vv --skip-gtids=true --exclude-gtids='caa5c861-181e-11ec-9d4b-08002709cb25:1-2' --include-gtids='caa5c861-181e-11ec-9d4b-08002709cb25:1-7' mydebug_binlog.000001 mydebug_binlog.000002 mydebug_binlog.000003 > 3INSERT_DATA.SQL

[root@CENTOS7GUI logs]# ll
总用量 36
-rw-r--r-- 1 root root 5616 12月 11 18:50 INSERT_DATA.SQL
-rw-r----- 1 root root 1296 12月 11 18:35 mydebug_binlog.000001
-rw-r----- 1 root root 2233 12月 11 18:42 mydebug_binlog.000002
-rw-r----- 1 root root 194 12月 11 18:42 mydebug_binlog.000003
-rw-r----- 1 root root 156 12月 11 18:42 mydebug_binlog.index
-rw-r----- 1 root root 4370 12月 11 18:48 mydebug_general.log
-rw-r----- 1 root root 564 12月 11 18:42 mydebug_slow.log

[root@CENTOS7GUI logs]# /DB/debug/mysql5735/soft/bin/mysqlbinlog -vv --skip-gtids=true --exclude-gtids='caa5c861-181e-11ec-9d4b-08002709cb25:1-2' --include-gtids='caa5c861-181e-11ec-9d4b-08002709cb25:1-7' mydebug_binlog.000001 mydebug_binlog.000002 mydebug_binlog.000003 > 3INSERT_DATA.SQL

/*!*/;
44 # at 344
45 #211211 18:40:29 server id 2500 end_log_pos 430 CRC32 0xae5e9cd8 Table_map: `books`.`dic_table_filed_value` mapped to number 109
46 # at 430
47 #211211 18:40:29 server id 2500 end_log_pos 523 CRC32 0x3e4d3dd3 Write_rows: table id 109 flags: STMT_END_F
48
49 BINLOG '
50 HYC0YRPECQAAVgAAAK4BAAAAAG0AAAAAAAEABWJvb2tzABVkaWNfdGFibGVfZmlsZWRfdmFsdWUA
51 CQgPDwIPEhIPDwy0ALQAFAAGBjwAkAH4AdicXq4=
52 HYC0YR7ECQAAXQAAAAsCAAAAAG0AAAAAAAEAAgAJ//8Q/gEAAAAAAAAACURLX09OTElORQZTVEFU
53 VVMAAJmrVyodDwn0matXKh0PCfQDREJBBgDnlLPor7fTPU0+
54 '/*!*/;
55 ### INSERT INTO `books`.`dic_table_filed_value`
56 ### SET
57 ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
58 ### @2='DK_ONLINE' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */
59 ### @3='STATUS' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */
60 ### @4=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
61 ### @5=NULL /* VARSTRING(20) meta=20 nullable=1 is_null=1 */
62 ### @6='2021-12-11 18:40:29.985588' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
63 ### @7='2021-12-11 18:40:29.985588' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
64 ### @8='DBA' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
65 ### @9='申请' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
66 # at 523
67 #211211 18:40:29 server id 2500 end_log_pos 554 CRC32 0x1e30d253 Xid = 29
68 COMMIT/*!*/;

BEGIN
43 /*!*/;
44 # at 344
45 #211211 18:40:29 server id 2500 end_log_pos 430 CRC32 0xae5e9cd8 Table_map: `books`.`dic_table_filed_value` mapped to number 109
46 # at 430
47 #211211 18:40:29 server id 2500 end_log_pos 523 CRC32 0x3e4d3dd3 Write_rows: table id 109 flags: STMT_END_F
48
49 BINLOG '
50 HYC0YRPECQAAVgAAAK4BAAAAAG0AAAAAAAEABWJvb2tzABVkaWNfdGFibGVfZmlsZWRfdmFsdWUA
51 CQgPDwIPEhIPDwy0ALQAFAAGBjwAkAH4AdicXq4=
52 HYC0YR7ECQAAXQAAAAsCAAAAAG0AAAAAAAEAAgAJ//8Q/gEAAAAAAAAACURLX09OTElORQZTVEFU
53 VVMAAJmrVyodDwn0matXKh0PCfQDREJBBgDnlLPor7fTPU0+
54 '/*!*/;
55 ### INSERT INTO `books`.`dic_table_filed_value`
56 ### SET
57 ### @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
58 ### @2='DK_ONLINE' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */
59 ### @3='STATUS' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */
60 ### @4=0 /* SHORTINT meta=0 nullable=1 is_null=0 */
61 ### @5=NULL /* VARSTRING(20) meta=20 nullable=1 is_null=1 */
62 ### @6='2021-12-11 18:40:29.985588' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
63 ### @7='2021-12-11 18:40:29.985588' /* DATETIME(6) meta=6 nullable=1 is_null=0 */
64 ### @8='DBA' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
65 ### @9='申请' /* VARSTRING(400) meta=400 nullable=1 is_null=0 */
66 # at 523
67 #211211 18:40:29 server id 2500 end_log_pos 554 CRC32 0x1e30d253 Xid = 29
68 COMMIT/*!*/;
69 # at 554
70 # at 619
71 #211211 18:40:41 server id 2500 end_log_pos 704 CRC32 0x242c9bd9 Query thread_id=2 exec_time=0 error_code=0
72 SET TIMESTAMP=1639219241.797911/*!*/;

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

mysql> source /DB/debug/mysql5735/data/logs/3INSERT_DATA.SQL;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from books.dic_table_filed_value;
+----+------------+------------+-----------+------------+----------------------------+----------------------------+--------------+--------------+
| ID | TABLE_NAME | FILED_NAME | VALUE_INT | VALUE_CHAR | CREATE_TIME | UPDATE_TIME | OPREATE_NAME | MEMO |
+----+------------+------------+-----------+------------+----------------------------+----------------------------+--------------+--------------+
| 1 | DK_ONLINE | STATUS | 0 | NULL | 2021-12-11 18:40:29.985588 | 2021-12-11 18:40:29.985588 | DBA | 申请 |
| 2 | DK_ONLINE | STATUS | 1 | NULL | 2021-12-11 18:40:41.797911 | 2021-12-11 18:40:41.797911 | DBA | 审核 |
| 3 | DK_ONLINE | STATUS | 2 | NULL | 2021-12-11 18:40:41.803928 | 2021-12-11 18:40:41.803928 | DBA | 审核失败 |
| 4 | DK_ONLINE | STATUS | 3 | NULL | 2021-12-11 18:40:41.824873 | 2021-12-11 18:40:41.824873 | DBA | 申请成功 |
| 5 | DK_ONLINE | STATUS | 4 | NULL | 2021-12-11 18:40:42.553054 | 2021-12-11 18:40:42.553054 | DBA | 申请失败 |
+----+------------+------------+-----------+------------+----------------------------+----------------------------+--------------+--------------+
5 rows in set (0.00 sec)

复制

我们也可以不用关闭BINLOG,不过考虑到主从场景

mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql;
mysql> set sql_log_bin=1;

转载请注明:XAMPP中文组官网 » 使用MYSQLBINLOG工具恢复数据GTID范围