mysql 5.5.32备份数据库_十六 mysql的备份与恢复（二）--mysqldump

1)在全备文件中查找二进制文件的开始位子

[root@vm01 backup]#gunzip full_-11-30.sql.gz

[root@vm01 backup]# vim full_-11-30.sql

。。。。。。-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=754;

。。。。。。

mysql-bin.000001,MASTER_LOG_POS=754为二进制截取的起点位子2)确认结束位子

在binlog日志中确认结束位子

[root@vm01 backup]# mysqlbinlog--base64-output=decode-rows -vvv /data/binlog/mysql-bin.000001。。。。。。

SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:5'/*!*/;

# at1429#01 2:51:04 server id 201 end_log_pos 1500 CRC32 0xd2a8cc70 Query thread_id=23 exec_time=0 error_code=0SET TIMESTAMP=1606762264/*!*/;

BEGIN/*!*/;

# at1500#01 2:51:04 server id 201 end_log_pos 1544 CRC32 0xafbfe599 Table_map: `ywx`.`t1` mapped to number 485# at1544#01 2:51:04 server id 201 end_log_pos 1609 CRC32 0xd8b246ce Delete_rows: table id 485flags: STMT_END_F

### DELETE FROM `ywx`.`t1`

### WHERE

### @1=11 /*INT meta=0 nullable=1 is_null=0*/### DELETE FROM `ywx`.`t1`

### WHERE

### @1=22 /*INT meta=0 nullable=1 is_null=0*/### DELETE FROM `ywx`.`t1`

### WHERE

### @1=33 /*INT meta=0 nullable=1 is_null=0*/### DELETE FROM `ywx`.`t1`

### WHERE

### @1=4 /*INT meta=0 nullable=1 is_null=0*/### DELETE FROM `ywx`.`t1`

### WHERE

### @1=6 /*INT meta=0 nullable=1 is_null=0*/### DELETE FROM `ywx`.`t1`

### WHERE

### @1=7 /*INT meta=0 nullable=1 is_null=0*/# at1609#01 2:51:05 server id 201 end_log_pos 1640 CRC32 0x935922a1 Xid = 3817COMMIT/*!*/;

# at1640#01 2:51:15 server id 201 end_log_pos 1705 CRC32 0xedf7e3c1 GTID last_committed=5 sequence_number=6 rbr_only=yes/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:6'/*!*/;

# at1705#01 2:51:14 server id 201 end_log_pos 1776 CRC32 0x0e0087ba Query thread_id=23 exec_time=0 error_code=0SET TIMESTAMP=1606762274/*!*/;

BEGIN/*!*/;

# at1776#01 2:51:14 server id 201 end_log_pos 1820 CRC32 0x4e44226d Table_map: `ywx`.`t1` mapped to number 485# at1820#01 2:51:14 server id 201 end_log_pos 1880 CRC32 0xd2c8605a Write_rows: table id 485flags: STMT_END_F

### INSERT INTO `ywx`.`t1`

### SET

### @1=111 /*INT meta=0 nullable=1 is_null=0*/### INSERT INTO `ywx`.`t1`

### SET

### @1=222 /*INT meta=0 nullable=1 is_null=0*/### INSERT INTO `ywx`.`t1`

### SET

### @1=333 /*INT meta=0 nullable=1 is_null=0*/### INSERT INTO `ywx`.`t1`

### SET

### @1=444 /*INT meta=0 nullable=1 is_null=0*/### INSERT INTO `ywx`.`t1`

### SET

### @1=555 /*INT meta=0 nullable=1 is_null=0*/# at1880#01 2:51:15 server id 201 end_log_pos 1911 CRC32 0xc1cb6934 Xid = 3819COMMIT/*!*/;

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

DELIMITER ;

# End of logfile

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

[root@vm01 backup]#

在events事件中确认结束位子

[ywx]>show binlog events in 'mysql-bin.000001';+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

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

| mysql-bin.000001 | 4 | Format_desc | 201 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |

| mysql-bin.000001 | 123 | Previous_gtids | 201 | 154 | |

| mysql-bin.000001 | 154 | Gtid | 201 | 219 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:1' |

| mysql-bin.000001 | 219 | Query | 201 | 323 | create database ywx charset=utf8 |

| mysql-bin.000001 | 323 | Gtid | 201 | 388 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:2' |

| mysql-bin.000001 | 388 | Query | 201 | 483 | use `ywx`; create table t1(id int) |

| mysql-bin.000001 | 483 | Gtid | 201 | 548 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:3' |

| mysql-bin.000001 | 548 | Query | 201 | 619 | BEGIN |

| mysql-bin.000001 | 619 | Table_map | 201 | 663 | table_id: 453 (ywx.t1) |

| mysql-bin.000001 | 663 | Write_rows | 201 | 723 | table_id: 453 flags: STMT_END_F |

| mysql-bin.000001 | 723 | Xid | 201 | 754 | COMMIT /*xid=3352*/ |

| mysql-bin.000001 | 754 | Gtid | 201 | 819 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:4' |

| mysql-bin.000001 | 819 | Query | 201 | 890 | BEGIN |

| mysql-bin.000001 | 890 | Table_map | 201 | 934 | table_id: 485 (ywx.t1) |

| mysql-bin.000001 | 934 | Update_rows | 201 | 980 | table_id: 485 flags: STMT_END_F |

| mysql-bin.000001 | 980 | Table_map | 201 | 1024 | table_id: 485 (ywx.t1) |

| mysql-bin.000001 | 1024 | Update_rows | 201 | 1070 | table_id: 485 flags: STMT_END_F |

| mysql-bin.000001 | 1070 | Table_map | 201 | 1114 | table_id: 485 (ywx.t1) |

| mysql-bin.000001 | 1114 | Update_rows | 201 | 1160 | table_id: 485 flags: STMT_END_F |

| mysql-bin.000001 | 1160 | Table_map | 201 | 1204 | table_id: 485 (ywx.t1) |

| mysql-bin.000001 | 1204 | Write_rows | 201 | 1249 | table_id: 485 flags: STMT_END_F |

| mysql-bin.000001 | 1249 | Table_map | 201 | 1293 | table_id: 485 (ywx.t1) |

| mysql-bin.000001 | 1293 | Delete_rows | 201 | 1333 | table_id: 485 flags: STMT_END_F |

| mysql-bin.000001 | 1333 | Xid | 201 | 1364 | COMMIT /*xid=3811*/ |

| mysql-bin.000001 | 1364 | Gtid | 201 | 1429 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:5' |

| mysql-bin.000001 | 1429 | Query | 201 | 1500 | BEGIN |

| mysql-bin.000001 | 1500 | Table_map | 201 | 1544 | table_id: 485 (ywx.t1) |

| mysql-bin.000001 | 1544 | Delete_rows | 201 | 1609 | table_id: 485 flags: STMT_END_F |

| mysql-bin.000001 | 1609 | Xid | 201 | 1640 | COMMIT /*xid=3817*/ |

| mysql-bin.000001 | 1640 | Gtid | 201 | 1705 | SET @@SESSION.GTID_NEXT= 'e271e770-310c-11eb-b220-000c29d16f12:6' |

| mysql-bin.000001 | 1705 | Query | 201 | 1776 | BEGIN |

| mysql-bin.000001 | 1776 | Table_map | 201 | 1820 | table_id: 485 (ywx.t1) |

| mysql-bin.000001 | 1820 | Write_rows | 201 | 1880 | table_id: 485 flags: STMT_END_F |

| mysql-bin.000001 | 1880 | Xid | 201 | 1911 | COMMIT /*xid=3819*/ |

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

34 rows in set (0.00sec)

第一段位子：position从是754开始到1544结束，

开始gtid号为754的下一个gtid号：e271e770-310c-11eb-b220-000c29d16f12:4结束gtid号：e271e770-310c-11eb-b220-000c29d16f12:5第二段位子：position从1609开始到1609结束，

gtid为e271e770-310c-11eb-b220-000c29d16f12:6

3)截取二进制文件

从上面可以看到delete from t1的位子为1544

gtid：

第一段：

mysqlbinlog--skip-gtids --include-gtids='e271e770-310c-11eb-b220-000c29d16f12:4' /data/mysql/mysql-bin.000001 >/data/backup/gtid1.sql

第二段：

mysqlbinlog--skip-gtids --include-gtids='e271e770-310c-11eb-b220-000c29d16f12:6' /data/mysql/mysql-bin.000001 >/data/backup/gtid2.sql

合并：

mysqlbinlog--skip-gtids --include-gtids='e271e770-310c-11eb-b220-000c29d16f12:4-6' --exclude-gtids='e271e770-310c-11eb-b220-000c29d16f12:5' /data/mysql/mysql-bin.000001 >/data/backup/gtid3.sql

position:

第一段：

mysqlbinlog--start-position=754 --stop-position=1364 /data/mysql/mysql-bin.000001 >/data/backup/bin1.sql

第二段：

mysqlbinlog--start-position=1609 /data/mysql/mysql-bin.000001 >/data/backup/bin2.sql