MySQL逻辑备份

于 2021-06-28 13:19:15 发布
阅读量141 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_56463403/article/details/118300594
版权

MySQL逻辑备份

1.1 全备+binlog恢复

使用mysqldump备份所有数据库，并恢复。要求保持数据库的一致性的前提下尽量减少数据库的锁,然后用binlog前滚到指定时间点。

周一到周日，每天晚上22:00都备份了一个全备，某一天（周四）早上10:00数据库down了，怎么恢复到最新的时间点。

思路：周三晚上22:00通过全备恢复，周三晚上22:00-周四早上10:00通过binlog恢复

准备两台mysql服务器，不要是主从服务器。

1. 准备数据

mysql> create database cool;

Query OK, 1 row affected (0.00 sec)

mysql> use cool;

Database changed

mysql> create table cool (id int,name varchar(10));

Query OK, 0 rows affected (0.15 sec)

mysql> insert into cool values(1,'a'),(2,'b'),(3,'c');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

2. 备份数据库

假设周三晚上10:00进行了一个全备，如下：

[root@test opt]# mysqldump -uroot -ptest --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/full_$(date +%F).sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@test opt]# ls

full_2021-05-17.sql

3. 新建一个数据库及表

周三晚上十点到周四早上十点模拟新增数据。

mysql> create database t1;

Query OK, 1 row affected (0.00 sec)

mysql> use t1;

Database changed

mysql> create table t1(id int,name varchar(20));

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(id,name) values(1,'php'),(2,'java'),(3,'python');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;

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

| id   | name   |

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

|    1 | php    |

|    2 | java   |

|    3 | python |

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

3 rows in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec

4. 模拟故障删库

周四早上十点模拟故障删库t1

mysql> drop database t1;

Query OK, 1 row affected (0.01 sec)

5. 查找binlog的起点及终点

在故障的mysql服务器上查看周三晚上十点备份的binlog起点。

vi /opt/full_2021-05-17.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='slave-binlog.000002', MASTER_LOG_POS=234;

查看binlog终点

mysql> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                   |

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

| slave-binlog.000002 |      840 |              |                  | 18d11475-b5fd-11eb-9b94-000c29c40ee0:1-14,

79c2fe65-b621-11eb-a65a-000c29d77752:1-7 |

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

1 row in set (0.00 sec)

mysql> show binlog events in 'slave-binlog.000002';

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

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

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

| slave-binlog.000002 |   4 | Format_desc    |         2 |         123 | Server ver: 5.7.32-log, Binlog ver: 4                                               |

| slave-binlog.000002 | 123 | Previous_gtids |         2 |         234 | 18d11475-b5fd-11eb-9b94-000c29c40ee0:1-14,

79c2fe65-b621-11eb-a65a-000c29d77752:1-4 |

| slave-binlog.000002 | 234 | Gtid           |         2 |         299 | SET @@SESSION.GTID_NEXT= '79c2fe65-b621-11eb-a65a-000c29d77752:5'                   |

| slave-binlog.000002 | 299 | Query          |         2 |         387 | create database t1                                                                  |

| slave-binlog.000002 | 387 | Gtid           |         2 |         452 | SET @@SESSION.GTID_NEXT= '79c2fe65-b621-11eb-a65a-000c29d77752:6'                   |

| slave-binlog.000002 | 452 | Query          |         2 |         562 | use `t1`; create table t1(id int,name varchar(20))                                  |

| slave-binlog.000002 | 562 | Gtid           |         2 |         627 | SET @@SESSION.GTID_NEXT= '79c2fe65-b621-11eb-a65a-000c29d77752:7'                   |

| slave-binlog.000002 | 627 | Query          |         2 |         697 | BEGIN                                                                               |

| slave-binlog.000002 | 697 | Table_map      |         2 |         743 | table_id: 154 (t1.t1)                                                               |

| slave-binlog.000002 | 743 | Write_rows     |         2 |         809 | table_id: 154 flags: STMT_END_F                                                     |

| slave-binlog.000002 | 809 | Xid            |         2 |         840 | COMMIT /* xid=656 */                                                                |

| slave-binlog.000002 | 840 | Gtid           |         2 |         905 | SET @@SESSION.GTID_NEXT= '79c2fe65-b621-11eb-a65a-000c29d77752:8'                   |

| slave-binlog.000002 | 905 | Query          |         2 |         991 | drop database t1                                                                    |

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

13 rows in set (0.00 sec)

所以可以知道，binlog的起点是234，终点为905

 

起点808终点1479

6. 截取binlog起点及终点的日志

[root@test data]# mysqlbinlog --skip-gtids --start-position=234 --stop-position=905 /data/mysql/data/slave-binlog.000002 > /opt/binlog.sql

#按时间截取是如下命令：

# mysqlbinlog  slave-binlog.000002 --start-datetime='2021-05-19 22:00:00' --stop-datetime='2021-05-19 10:00:00' -r > /opt/binlog.sql

7. 临时库上恢复数据库到最新时间点

临时库上恢复全备+binlog，将全备文件及截取的binlog文件恢复到临时库上（另外一台临时服务器上）

[root@test data]# scp /opt/full_2021-05-17.sql root@192.168.137.18:/opt

root@192.168.137.18's password:

full_2021-05-17.sql                                                   100%  913KB  10.5MB/s   00:00   

[root@test data]# scp /opt/binlog.sql root@192.168.137.18:/opt

root@192.168.137.18's password:

binlog.sql                                                            100% 1621     1.6MB/s   00:00

[root@www opt]# mysql -uroot -ptest

mysql> source /opt/full_2021-05-17.sql;   #现在只是恢复到周三晚上十点。所以并没有周三晚上十点到周四早上十点的数据。即t1数据库。

mysql> show databases;

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

| Database             |

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

| information_schema    |

| cool                  |

| mysql                |

| performance_schema   |

| sys                   |

| test               |

| wordpress          |

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

7 rows in set (0.00 sec)

mysql> source /opt/binlog.sql;   #恢复周三晚上十点到周四早上十点新增的数据。

mysql> show databases;

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

| Database           |

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

| information_schema |

| cool               |

| mysql              |

| performance_schema |

| sys                |

| t1                 |

| test               |

| wordpress          |

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

8 rows in set (0.00 sec)

mysql> use t1;

Database changed

mysql> select * from t1;

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

| id   | name   |

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

|    1 | php    |

|    2 | java   |

|    3 | python |

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

3 rows in set (0.00 sec)

1.2 binlog跳过某个操作进行恢复数据

a.创建一个数据库ceshi

b.在ceshi下创建一张表t1

c.插入5行任意数据

d.全备

e.插入两行数据，任意修改3行数据，删除一行数据

f.删除所有数据

g.再t1中又插入5行新数据，修改3行数据

需求，跳过f恢复表数据

1. 准备数据

mysql> show databases;

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

| Database           |

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

| information_schema |

| cool               |

| mysql              |

| performance_schema |

| sys                |

| test               |

| wordpress          |

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

7 rows in set (0.00 sec)

mysql> create database ceshi charset utf8mb4;

mysql> use ceshi;

mysql> create table ceshi(id int,name varchar(20));

mysql> insert into ceshi values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

mysql> commit;

2. 备份数据库(全备)

[root@test ~]# mysqldump -uroot -ptest -A --master-data=2 --single-transaction -R -E --triggers > /opt/full_$(date +%F-%T).sql

[root@test ~]# vi /opt/full_2021-05-18-09\:27\:38.sql

SET @@GLOBAL.GTID_PURGED='18d11475-b5fd-11eb-9b94-000c29c40ee0:1-14,

1db8158e-b71d-11eb-917f-000c29d77752:1-14,

79c2fe65-b621-11eb-a65a-000c29d77752:1-4';

-- CHANGE MASTER TO MASTER_LOG_FILE='slave-binlog.000002', MASTER_LOG_POS=3605;

3. 插入及删除数据

e.插入两行数据，任意修改3行数据，删除一行数据

mysql> insert into ceshi values(11,'aa'),(22,'bb');

Query OK, 2 rows affected (0.07 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from ceshi;

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

| id   | name |

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

|    1 | a    |

|    2 | b    |

|    3 | c    |

|    4 | d    |

|    5 | e    |

|   11 | aa   |

|   22 | bb   |

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

7 rows in set (0.00 sec)

mysql> update ceshi set id=10 where id>4;

Query OK, 3 rows affected (0.09 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from ceshi;

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

| id   | name |

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

|    1 | a    |

|    2 | b    |

|    3 | c    |

|    4 | d    |

|   10 | e    |

|   10 | aa   |

|   10 | bb   |

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

7 rows in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from ceshi where id=4;

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from ceshi;

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

| id   | name |

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

|    1 | a    |

|    2 | b    |

|    3 | c    |

|   10 | e    |

|   10 | aa   |

|   10 | bb   |

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

6 rows in set (0.00 sec)

f.删除所有数据

mysql> delete from ceshi;

Query OK, 6 rows affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from ceshi;

Empty set (0.00 sec)

g.再ceshi表中又插入5行新数据，修改3行数据

mysql> insert into ceshi values(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

Query OK, 5 rows affected (0.01 sec)

Records: 5  Duplicates: 0  Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> update ceshi set id=10 where id>2;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> commit;

mysql> select * from ceshi;

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

| id   | name |

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

|    1 | a    |

|    2 | b    |

|   10 | c    |

|   10 | d    |

|   10 | e    |

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

5 rows in set (0.00 sec)

4. gtid起点与终点

mysql> show master status;

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

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                                                              |

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

| slave-binlog.000002 |     5333 |              |                  | 18d11475-b5fd-11eb-9b94-000c29c40ee0:1-14,

1db8158e-b71d-11eb-917f-000c29d77752:1-20,

79c2fe65-b621-11eb-a65a-000c29d77752:1-4 |

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

1 row in set (0.00 sec)

mysql> show binlog events in 'slave-binlog.000002';

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

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

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

| slave-binlog.000002 |    4 | Format_desc    |         2 |         123 | Server ver: 5.7.32-log, Binlog ver: 4                              |

| slave-binlog.000002 |  123 | Previous_gtids |         2 |         154 |                                                                    |

| slave-binlog.000002 |  154 | Gtid           |         2 |         219 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:1'  |

| slave-binlog.000002 |  219 | Query          |         2 |         332 | create database ceshi charset utf8mb4                              |

| slave-binlog.000002 |  332 | Gtid           |         2 |         397 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:2'  |

| slave-binlog.000002 |  397 | Query          |         2 |         516 | use `ceshi`; create table ceshi(id int,name varchar(20))           |

| slave-binlog.000002 |  516 | Gtid           |         2 |         581 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:3'  |

| slave-binlog.000002 |  581 | Query          |         2 |         654 | BEGIN                                                              |

| slave-binlog.000002 |  654 | Table_map      |         2 |         706 | table_id: 108 (ceshi.ceshi)                                        |

| slave-binlog.000002 |  706 | Write_rows     |         2 |         776 | table_id: 108 flags: STMT_END_F                                    |

| slave-binlog.000002 |  776 | Xid            |         2 |         807 | COMMIT /* xid=8 */                                                 |

| slave-binlog.000002 |  807 | Gtid           |         2 |         872 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:4'  |

| slave-binlog.000002 |  872 | Query          |         2 |         945 | BEGIN                                                              |

| slave-binlog.000002 |  945 | Table_map      |         2 |         997 | table_id: 251 (ceshi.ceshi)                                        |

| slave-binlog.000002 |  997 | Write_rows     |         2 |        1048 | table_id: 251 flags: STMT_END_F                                    |

| slave-binlog.000002 | 1048 | Xid            |         2 |        1079 | COMMIT /* xid=2783 */                                              |

| slave-binlog.000002 | 1079 | Gtid           |         2 |        1144 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:5'  |

| slave-binlog.000002 | 1144 | Query          |         2 |        1217 | BEGIN                                                              |

| slave-binlog.000002 | 1217 | Table_map      |         2 |        1269 | table_id: 251 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 1269 | Update_rows    |         2 |        1351 | table_id: 251 flags: STMT_END_F                                    |

| slave-binlog.000002 | 1351 | Xid            |         2 |        1382 | COMMIT /* xid=2786 */                                              |

| slave-binlog.000002 | 1382 | Gtid           |         2 |        1447 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:6'  |

| slave-binlog.000002 | 1447 | Query          |         2 |        1520 | BEGIN                                                              |

| slave-binlog.000002 | 1520 | Table_map      |         2 |        1572 | table_id: 251 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 1572 | Delete_rows    |         2 |        1614 | table_id: 251 flags: STMT_END_F                                    |

| slave-binlog.000002 | 1614 | Xid            |         2 |        1645 | COMMIT /* xid=2789 */                                              |

| slave-binlog.000002 | 1645 | Gtid           |         2 |        1710 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:7'  |

| slave-binlog.000002 | 1710 | Query          |         2 |        1783 | BEGIN                                                              |

| slave-binlog.000002 | 1783 | Table_map      |         2 |        1835 | table_id: 251 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 1835 | Delete_rows    |         2 |        1914 | table_id: 251 flags: STMT_END_F                                    |

| slave-binlog.000002 | 1914 | Xid            |         2 |        1945 | COMMIT /* xid=2792 */                                              |

| slave-binlog.000002 | 1945 | Gtid           |         2 |        2010 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:8'  |

| slave-binlog.000002 | 2010 | Query          |         2 |        2083 | BEGIN                                                              |

| slave-binlog.000002 | 2083 | Table_map      |         2 |        2135 | table_id: 251 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 2135 | Write_rows     |         2 |        2205 | table_id: 251 flags: STMT_END_F                                    |

| slave-binlog.000002 | 2205 | Xid            |         2 |        2236 | COMMIT /* xid=2795 */                                              |

| slave-binlog.000002 | 2236 | Gtid           |         2 |        2301 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:9'  |

| slave-binlog.000002 | 2301 | Query          |         2 |        2374 | BEGIN                                                              |

| slave-binlog.000002 | 2374 | Table_map      |         2 |        2426 | table_id: 251 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 2426 | Update_rows    |         2 |        2476 | table_id: 251 flags: STMT_END_F                                    |

| slave-binlog.000002 | 2476 | Xid            |         2 |        2507 | COMMIT /* xid=2797 */                                              |

| slave-binlog.000002 | 2507 | Gtid           |         2 |        2572 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:10' |

| slave-binlog.000002 | 2572 | Query          |         2 |        2645 | BEGIN                                                              |

| slave-binlog.000002 | 2645 | Table_map      |         2 |        2697 | table_id: 251 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 2697 | Update_rows    |         2 |        2761 | table_id: 251 flags: STMT_END_F                                    |

| slave-binlog.000002 | 2761 | Xid            |         2 |        2792 | COMMIT /* xid=2798 */                                              |

| slave-binlog.000002 | 2792 | Gtid           |         2 |        2857 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:11' |

| slave-binlog.000002 | 2857 | Query          |         2 |        2952 | drop database ceshi                                                |

| slave-binlog.000002 | 2952 | Gtid           |         2 |        3017 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:12' |

| slave-binlog.000002 | 3017 | Query          |         2 |        3130 | create database ceshi charset utf8mb4                              |

| slave-binlog.000002 | 3130 | Gtid           |         2 |        3195 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:13' |

| slave-binlog.000002 | 3195 | Query          |         2 |        3314 | use `ceshi`; create table ceshi(id int,name varchar(20))           |

| slave-binlog.000002 | 3314 | Gtid           |         2 |        3379 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:14' |

| slave-binlog.000002 | 3379 | Query          |         2 |        3452 | BEGIN                                                              |

| slave-binlog.000002 | 3452 | Table_map      |         2 |        3504 | table_id: 297 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 3504 | Write_rows     |         2 |        3574 | table_id: 297 flags: STMT_END_F                                    |

| slave-binlog.000002 | 3574 | Xid            |         2 |        3605 | COMMIT /* xid=2810 */                                              |

| slave-binlog.000002 | 3605 | Gtid           |         2 |        3670 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:15' |

| slave-binlog.000002 | 3670 | Query          |         2 |        3743 | BEGIN                                                              |

| slave-binlog.000002 | 3743 | Table_map      |         2 |        3795 | table_id: 299 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 3795 | Write_rows     |         2 |        3846 | table_id: 299 flags: STMT_END_F                                    |

| slave-binlog.000002 | 3846 | Xid            |         2 |        3877 | COMMIT /* xid=3514 */                                              |

| slave-binlog.000002 | 3877 | Gtid           |         2 |        3942 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:16' |

| slave-binlog.000002 | 3942 | Query          |         2 |        4015 | BEGIN                                                              |

| slave-binlog.000002 | 4015 | Table_map      |         2 |        4067 | table_id: 299 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 4067 | Update_rows    |         2 |        4149 | table_id: 299 flags: STMT_END_F                                    |

| slave-binlog.000002 | 4149 | Xid            |         2 |        4180 | COMMIT /* xid=3517 */                                              |

| slave-binlog.000002 | 4180 | Gtid           |         2 |        4245 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:17' |

| slave-binlog.000002 | 4245 | Query          |         2 |        4318 | BEGIN                                                              |

| slave-binlog.000002 | 4318 | Table_map      |         2 |        4370 | table_id: 299 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 4370 | Delete_rows    |         2 |        4412 | table_id: 299 flags: STMT_END_F                                    |

| slave-binlog.000002 | 4412 | Xid            |         2 |        4443 | COMMIT /* xid=3520 */                                              |

| slave-binlog.000002 | 4443 | Gtid           |         2 |        4508 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:18' |

| slave-binlog.000002 | 4508 | Query          |         2 |        4581 | BEGIN                                                              |

| slave-binlog.000002 | 4581 | Table_map      |         2 |        4633 | table_id: 299 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 4633 | Delete_rows    |         2 |        4712 | table_id: 299 flags: STMT_END_F                                    |

| slave-binlog.000002 | 4712 | Xid            |         2 |        4743 | COMMIT /* xid=3523 */                                              |

| slave-binlog.000002 | 4743 | Gtid           |         2 |        4808 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:19' |

| slave-binlog.000002 | 4808 | Query          |         2 |        4881 | BEGIN                                                              |

| slave-binlog.000002 | 4881 | Table_map      |         2 |        4933 | table_id: 299 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 4933 | Write_rows     |         2 |        5003 | table_id: 299 flags: STMT_END_F                                    |

| slave-binlog.000002 | 5003 | Xid            |         2 |        5034 | COMMIT /* xid=3526 */                                              |

| slave-binlog.000002 | 5034 | Gtid           |         2 |        5099 | SET @@SESSION.GTID_NEXT= '1db8158e-b71d-11eb-917f-000c29d77752:20' |

1db8158e-b71d-11eb-917f-000c29d77752:20

| slave-binlog.000002 | 5099 | Query          |         2 |        5172 | BEGIN                                                              |

| slave-binlog.000002 | 5172 | Table_map      |         2 |        5224 | table_id: 299 (ceshi.ceshi)                                        |

| slave-binlog.000002 | 5224 | Update_rows    |         2 |        5302 | table_id: 299 flags: STMT_END_F                                    |

| slave-binlog.000002 | 5302 | Xid            |         2 |        5333 | COMMIT /* xid=3528 */                                              |

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

87 rows in set (0.00 sec)

263可以看出起点是1db8158e-b71d-11eb-917f-000c29d77752:15

269终点是 1db8158e-b71d-11eb-917f-000c29d77752:20

15 ->插入两行数据，16->任意修改3行数据，17->删除一行数据

f.18->删除所有数据

g.19->再t1中又插入5行新数据，20->修改3行数据

由上看出需要跳过f即gtid为18的进行恢复263-267

5. 截取gtid

[root@test ~]# mysqlbinlog --skip-gtids --include-gtids='1db8158e-b71d-11eb-917f-000c29d77752:15-20' --exclude-gtids='1db8158e-b71d-11eb-917f-000c29d77752:18' /data/mysql/data/slave-binlog.000002>/opt/binlog20210519.sql

6. 原库恢复

mysql> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

mysql> source /opt/full_2021-05-19-01:13:20.sql

mysql> source /opt/binlog20210519.sql;

mysql> use ceshi;

Database changed

mysql> show tables;

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

| Tables_in_ceshi |

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

| ceshi           |

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

1 row in set (0.00 sec)

mysql> select * from ceshi;

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

| id   | name |

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

|    1 | a    |

|    2 | b    |

|   10 | c    |

|   10 | e    |

|   10 | aa   |

|   10 | bb   |

|    1 | a    |

|    2 | b    |

|    3 | c    |

|   10 | d    |

|   10 | e    |

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

11 rows in set (0.00 sec)

cc
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
面向猴子编程 错删表数据的修复方案
qj4865的博客
05-06 515
引文 人生在世，难免有手滑的时候。没有悄悄咪咪删过表数据的程序员的人生，一定是不完美的。特别是连续加班后，带着昏沉沉的大脑，难免会一个不留神，写下了罪恶的foreach remove语句。满怀信心的一看数据库。啪，快乐没有了！表数据因为不合适的查询条件，进行了全表数据删除的操作！ 此时的你一定万分慌张，甚至已经收拾好行李，准备下一秒就提着桶跑路了！ 为什么要跑路呢？因为你不知道接下来该如何处理，只能依靠工具猴最原始的自我保护机制，驱使自己前进。 除了跑路，我们还能做些什么呢？！别慌，下面就来讲讲遇到这种情况
mysql table id_再说MySQL中的 table_id
weixin_36231529的博客
01-19 1082
最近线上一个实例出现了主从数据不一致的情况,也即从库丢失数据的情况。根本原因:由于table_list-gt;table_id为uint,而m_table【背景】最近线上一个实例出现了主从数据不一致的情况,也即从库丢失数据的情况。根本原因:"由于table_list->table_id为uint,而m_table_id为ulong，主库上assign的table map　id 总是一直递增的...
17，MySQL 8.0参考手册4.6.8.2 mysqlbinlog行事件显示
dandan520520的博客
03-26 699
以下示例说明了mysqlbinlog如何 显示指定数据修改的行事件。这些对应于与事件 WRITE_ROWS_EVENT， UPDATE_ROWS_EVENT和 DELETE_ROWS_EVENT类型代码。该 --base64-output=DECODE-ROWS 和--verbose选项可用于影响排事件输出。假设服务器正在使用基于行的二进制日志记录并执行以下语句序列：CREATE TABLE t ...
binlog解读2
mengjiangui的博客
12-14 931
一、binlog文件里面写入的是啥玩意？ 答：1、binlog是由一个一个event组成，event是binlog的最小组成单元。 2、binlog文件头部固定以4个字节开头，这四个字节称为BINLOG_MAGIC(fe 62 69 6e)魔数，当使用mysqldump命令解析binlog文件时，来识别该文件属于binlog文件。 3、每个binlog文件以一个FORMAT_DESCRIPTION_EVENT类型的event开始。以一个Rotate类型的event结束（但也有特殊情况，当数据库出现宕机
高性能MySQL分析
陈晨_软件五千言
05-19 763
文章目录Schema与数据类型优化选择优化的数据类型Scheme设计中的陷阱创建高性能索引高性能的索引策略查询性能优化慢查询基础：优化数据访问是否请求了不需要的数据是否在扫描额外的记录重构查询的方式一个复杂查询还是多个简单查询切分查询分解关联查询执行查询的基础Mysql客户端/服务器通信协议查询状态查询缓存查询优化处理语法解析器和预处理查询优化器MySQL执行关联查询关联查询优化器排序优化查询执行引擎查询优化的局限性优化特定类型的查询优化count查询优化关联查询优化子查询优化group by和distin
Mysql逻辑备份恢复
04-29
Mysql逻辑备份恢复实践，70页详述
08-MySQL逻辑备份工具mysqldump&mysqlpump&mydumper1
08-03
MySQL逻辑备份工具mysqldump、mysqlpump和mydumper mysqldump是MySQL经典的逻辑备份工具，用于将数据库中的数据导出到一个SQL文件中，以便于数据备份和恢复。mysqldump提供了多种参数来控制备份过程，以下是一些...
Windows环境MySQL逻辑备份操作指南.doc
04-30
Windows环境MySQL逻辑备份操作指南.doc
Mysql物理备份以及逻辑备份，还有LVM备份
04-08
Mysql物理备份以及逻辑备份，还有LVM备份案例，还有相关的备份例子。
基于mysqldump工具逻辑备份Mysql8数据自动化工具
12-28
原文链接：https://blog.csdn.net/m0_37814112/article/details/128383961 功能说明： 1、支持单库或多库一键备份 2、支持单库或多库一键恢复
MySQL中Stmt 预处理提高效率问题的小研究
09-11
在oracle数据库中，有一个变量绑定的用法，很多人都比较熟悉，可以调高数据库效率，应对高并发等，好吧，这其中并不包括我，当同事问我MySQL中有没有类似的写法时，我是很茫然的，于是就上网查，找到了如下一种写法
mysql的update怎么恢复_MySQL模拟：线上误update的恢复
weixin_30160357的博客
01-28 822
作为DBA，细心、沉稳是首要的基本素质。不过总有那么一会心烦意乱或者开发同学出现误操作之类的。。。这里模拟一个误update操作，然后恢复。如果开发同学有误操作之后最好先别乱动生产环境，需要记录几个信息给DBA同学来恢复数据。1：binlog_format、autocommit、binlog位置、误操作的时间点mysql> SHOW VARIABLES LIKE '%binlog_forma...
MYSQL同步报错故障处理方法总结
eagle89的专栏
05-01 2221
前言 在发生故障切换后，经常遇到的问题就是同步报错，数据库很小的时候，dump完再导入很简单就处理好了，但线上的数据库都5T，如果用单纯的这种方法，成本太高，故经过一段时间的摸索，总结了几种处理方法。 生产环境架构图 目前现网的架构，保存着两份数据，通过异步复制做的高可用集群，两台机器提供对外服务。在发生故障时，切换到slave上，并将其变成master，坏掉的机器反向同步新的master，...
解决Mysql 主从或主主报1062错误
eagle89的专栏
07-06 6453
1062错误——主键冲突，出现这种情况就是从库出现插入操作，主库又插入相同的数据，iothread没问题，sqlthread出错 处理此种错误一般有两种思路： 1、直接跳过错误执行语句 2、找到错误执行语句，修复主库2数据 语句跳过操作方法如下： 1062 错误提示如下: Last_Errno: 1062 Last_Error: Could not execute Write_rows event on table test.test; Duplicate entry...
Spark-Core中那些你不知道的面试题
Fresh_man888的博客
11-29 242
1.Application 使用SparkSubmit提交的个计算应用,一个Application中可以触发一到多次Action，触发一次Action形成一个DAG，一个DAG对应一个Job,一个Application中可以有一到多个Job 2.Job Driver向Executor提交的作业,触发一次Acition形成一个完整的DAG,一个DAG对应一个Job,一个Job中有一到多个Stage，一个Stage对应一个TaskSet，一个TaskSet中有一到多个Task 3.DAG 概念：有向无
解析MYSQL BINLOG 二进制格式(6)--UPDATE_ROW_EVENT/DELETE_ROW_EVENT
cri5768的博客
02-13 397
原创：转载请说明出处谢谢！ 上接 http://blog.itpub.net/7728585/viewspace-2133188/ 解析MYSQL BINLOG 二进制格式(1)--准备工作 http://blog.it...
mysql deadline_MySQL事务RUNNING状态引发的Transactiontimedout:deadline问题_MySQL
weixin_28737399的博客
02-07 1557
前言：朋友说简单的查询导致Transaction timed out: deadline问题，怀疑是数据库表锁了，1，应用故障描述Deadline问题：--- The error occurred in META-INF/ibatis/ITEM_sqlmap.xml.--- The error occurred while executing query.--- Check the SE...
Mysql数据库的几个经典错误和解决方案（转）
qq_42697271的博客
06-10 1258
目录一、Too many connections二、主从复制报错类型1、Last_SQL_Errno: 10622、Last_IO_Errno: 1593(server-id冲突)3、Last_SQL_Errno: 1032(从库少数据，主库更新的时候，从库报错)三、MySQL安装过程中的报错四、数据库密码忘记的问题五、阿里云 MySQL 的配置文件六、数据库总会出现中文乱码的情况七、Emoji 表情符号录入 MySQL 数据库中报错 一、Too many connections 连接数过多，导致连接不
MySQL Binlog解析
weixin_34167819的博客
11-03 674
概述 MySQL的安装可以参考：Linux(CentOS)中常用软件安装，使用及异常——MySQL, VmTools MySQL关于Binlog的官方文档：The Binary Log 基于Binlog的主备复制：MySQL主备复制原理、实现及异常处理 什么是 Binlog MySQL Server 有四种类型的日志——E...
mysql物理备份和逻辑备份的区别
最新发布
05-17
MySQL的物理备份和逻辑备份有以下区别： 1. 数据备份方式不同。物理备份是基于数据文件进行备份，复制整个数据文件或表空间，包括数据文件、日志文件、索引文件等。逻辑备份则是基于SQL语句进行备份，将数据库中的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值