MySQL数据库备份&恢复（备份恢复）【通过GTID进行增量恢复】

最新推荐文章于 2024-05-05 03:50:35 发布
最新推荐文章于 2024-05-05 03:50:35 发布
阅读量1.9k 收藏 4
点赞数 4
文章标签： mysql 数据库 服务器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/JohnnyG2000/article/details/124529352
版权

通过GTID进行增量恢复

1、GTID 介绍

1）什么是GTID

GTID(Global Transaction ID)全局事务标识符。给予每一个已提交事务全局唯一的编号。目的在于能够实现主从自动定位和切换，不用像之前那样指定文件和位置。GTID使用冒号分隔的一对坐标表示。从MySQL5.6版本后开启支持。

2）GTID的格式与存储

①单个GTID

GTID与主库上提交的每个事务相关联，例如：

8eed0f5b-6f9b-11e9-94a9-005056a57a4e:23
前一部分是主库的server_uuid，后面一部分是主库上按提交事务的顺序确定的序列号，提交的事务序号从1开始。
GTID = server_uuid ：transaction_id

②GTID集

GTID集是包括一个或多个单个GTID或GTID范围的集合，例如：

8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-321
上面的示例表示源自server_uuid为8eed0f5b-6f9b-11e9-94a9-005056a57a4e服务器的“第1”到“第321”个事务。

源自同一服务器的多个单GTID或GTID范围可以同时包含在由冒号分隔的单个表达式（不连续）中，例如：
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-3:11:47-49

3）开启GTID

#查看GTID开启前的设定
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)

//修改配置文件
[root@localhost ~]# vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true

#查看GTID开启后的一些相关设定
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)
#通过“gtid_next=AUTOMATIC”可知GTID自动递增

4）mysql.gtid_executed表

#mysql.gtid_executed表结构
mysql> desc mysql.gtid_executed;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| source_uuid    | char(36)   | NO   | PRI | NULL    |       |
| interval_start | bigint(20) | NO   | PRI | NULL    |       |
| interval_end   | bigint(20) | NO   |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

5）执行DDL和DML语句观察GTID变化（素材）

DDL一个语句产生一个gtid

mysql> create database db3;
Query OK, 1 row affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 |      310 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> use db3;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      471 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      632 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t3 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |      793 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

DML一个事务产生一个gtid

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

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

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

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |     1128 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

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

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)

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

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |     1379 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

6）基于GTID进行查看binlog

mysql> show binlog events in 'mysql-bin.000004';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000004 |    4 | Format_desc    |       133 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                             |
| mysql-bin.000004 |  123 | Previous_gtids |       133 |         154 |                                                                   |
| mysql-bin.000004 |  154 | Gtid           |       133 |         219 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:1' |
| mysql-bin.000004 |  219 | Query          |       133 |         310 | create database db3                                               |
| mysql-bin.000004 |  310 | Gtid           |       133 |         375 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:2' |
| mysql-bin.000004 |  375 | Query          |       133 |         471 | use `db3`; create table t1 (id int)                               |
| mysql-bin.000004 |  471 | Gtid           |       133 |         536 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:3' |
| mysql-bin.000004 |  536 | Query          |       133 |         632 | use `db3`; create table t2 (id int)                               |
| mysql-bin.000004 |  632 | Gtid           |       133 |         697 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:4' |
| mysql-bin.000004 |  697 | Query          |       133 |         793 | use `db3`; create table t3 (id int)                               |
| mysql-bin.000004 |  793 | Gtid           |       133 |         858 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:5' |
| mysql-bin.000004 |  858 | Query          |       133 |         929 | BEGIN                                                             |
| mysql-bin.000004 |  929 | Table_map      |       133 |         973 | table_id: 108 (db3.t1)                                            |
| mysql-bin.000004 |  973 | Write_rows     |       133 |        1013 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000004 | 1013 | Table_map      |       133 |        1057 | table_id: 108 (db3.t1)                                            |
| mysql-bin.000004 | 1057 | Write_rows     |       133 |        1097 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000004 | 1097 | Xid            |       133 |        1128 | COMMIT /* xid=20 */                                               |
| mysql-bin.000004 | 1128 | Gtid           |       133 |        1193 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:6' |
| mysql-bin.000004 | 1193 | Query          |       133 |        1264 | BEGIN                                                             |
| mysql-bin.000004 | 1264 | Table_map      |       133 |        1308 | table_id: 109 (db3.t2)                                            |
| mysql-bin.000004 | 1308 | Write_rows     |       133 |        1348 | table_id: 109 flags: STMT_END_F                                   |
| mysql-bin.000004 | 1348 | Xid            |       133 |        1379 | COMMIT /* xid=25 */                                               |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
22 rows in set (0.00 sec)

7）GTID的幂等性

开启GTID后，MySQL恢复Binlog时，重复的GTID事务不会再执行，意思是假如多个GTID中，存在记录相同的语句，则不会执行，这就有可能导致恢复数据时恢复失败（原因：语句缺失）。

这就需要在进行binlog备份时，在语句中添加**–skip-gtids**参数，表示跳过GTID幂等性。

2、恢复示例

1）正常恢复

#此时将db3库删除
mysql> drop database db3;
Query OK, 3 rows affected (0.01 sec)

mysql> show binlog events in 'mysql-bin.000004';		#查看GTID变化
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000004 |    4 | Format_desc    |       133 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                             |
| mysql-bin.000004 |  123 | Previous_gtids |       133 |         154 |                                                                   |
| mysql-bin.000004 |  154 | Gtid           |       133 |         219 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:1' |
| mysql-bin.000004 |  219 | Query          |       133 |         310 | create database db3                                               |
| mysql-bin.000004 |  310 | Gtid           |       133 |         375 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:2' |
| mysql-bin.000004 |  375 | Query          |       133 |         471 | use `db3`; create table t1 (id int)                               |
| mysql-bin.000004 |  471 | Gtid           |       133 |         536 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:3' |
| mysql-bin.000004 |  536 | Query          |       133 |         632 | use `db3`; create table t2 (id int)                               |
| mysql-bin.000004 |  632 | Gtid           |       133 |         697 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:4' |
| mysql-bin.000004 |  697 | Query          |       133 |         793 | use `db3`; create table t3 (id int)                               |
| mysql-bin.000004 |  793 | Gtid           |       133 |         858 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:5' |
| mysql-bin.000004 |  858 | Query          |       133 |         929 | BEGIN                                                             |
| mysql-bin.000004 |  929 | Table_map      |       133 |         973 | table_id: 108 (db3.t1)                                            |
| mysql-bin.000004 |  973 | Write_rows     |       133 |        1013 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000004 | 1013 | Table_map      |       133 |        1057 | table_id: 108 (db3.t1)                                            |
| mysql-bin.000004 | 1057 | Write_rows     |       133 |        1097 | table_id: 108 flags: STMT_END_F                                   |
| mysql-bin.000004 | 1097 | Xid            |       133 |        1128 | COMMIT /* xid=20 */                                               |
| mysql-bin.000004 | 1128 | Gtid           |       133 |        1193 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:6' |
| mysql-bin.000004 | 1193 | Query          |       133 |        1264 | BEGIN                                                             |
| mysql-bin.000004 | 1264 | Table_map      |       133 |        1308 | table_id: 109 (db3.t2)                                            |
| mysql-bin.000004 | 1308 | Write_rows     |       133 |        1348 | table_id: 109 flags: STMT_END_F                                   |
| mysql-bin.000004 | 1348 | Xid            |       133 |        1379 | COMMIT /* xid=25 */                                               |
| mysql-bin.000004 | 1379 | Gtid           |       133 |        1444 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:7' |
| mysql-bin.000004 | 1444 | Query          |       133 |        1533 | drop database db3                                                 |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+
24 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000004 |     1533 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#可以看到最新的删除语句是c4d1a502-b360-11ec-afa4-000c2967ad99:7

//按照GTID进行binlog备份

//通过上方查询GTID可知，“序号7为删除库语句”，因此需要跳过删除语句则需要选取GTID小于7的二进制日志
//最后选取了“6”
mysqlbinlog --include-gtids='c4d1a502-b360-11ec-afa4-000c2967ad99:1-6' /var/lib/mysql/mysql-bin.000004 > /backup/gtid_test1.sql

[root@localhost ~]# ll /backup/gtid_test1.sql		//查看创建备份结果
-rw-r--r-- 1 root root 5050 May  1 22:57 gtid_test1.sql

#进行恢复
mysql> source /backup/gtid_test1.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)

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, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1049 (42000): Unknown database 'db3'
Query OK, 0 rows affected (0.00 sec)

ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1046 (3D000): No database selected
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)

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
#通过观察，没有恢复成功

恢复失败原因
观察GTID可知，序号“2”-“4”均是存在“use db3”语句，恢复失败的原因就在这，可以看到下面图片中，在恢复的时候，出现了不知道db3库的错误，这原因在于GTID的幂等性（上面有提及和解释）。
在这里插入图片描述
在这里插入图片描述
在binlog备份语句中添加–skip-gtids语句

[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='c4d1a502-b360-11ec-afa4-000c2967ad99:1-6' /var/lib/mysql/mysql-bin.000004 > /backup/gtid_test1.sql
//备份文件名相同默认将原本的覆盖

[root@localhost ~]# ll /backup/gtid_test1.sql 		//查看备份结果
-rw-r--r-- 1 root root 3670 May  1 23:07 /backup/gtid_test1.sql

再次恢复

mysql> source /backup/gtid_test1.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.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)

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, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database 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 (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.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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db3                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
#可以看到这次恢复成功了

2）跨binlog文档恢复

①素材

第一次操作

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000013 |     1379 |              |                  | 6181523d-bc2e-11ea-a78b-000c29221146:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> create database gtid;
Query OK, 1 row affected (0.01 sec)

mysql> use gtid;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

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

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000007 |      194 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

第二次操作

mysql> create table t2(id int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)

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

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000008 |      194 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-12 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

第三次操作

mysql> create table t3(id int);
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)

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

mysql> drop database gtid;		//误操作：误删gtid库
Query OK, 3 rows affected (0.01 sec)

②恢复

mysql> show binary logs;		//查看当前所有binlog日志
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |      1556 |
| mysql-bin.000005 |       217 |
| mysql-bin.000006 |       815 |
| mysql-bin.000007 |       656 |
| mysql-bin.000008 |       609 |
+------------------+-----------+
8 rows in set (0.00 sec)

#根据刷新日志次数判断最初创建gtid库的语句在“mysql-bin.000006”中
mysql> show binlog events in 'mysql-bin.000006';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000006 |   4 | Format_desc    |       133 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                              |
| mysql-bin.000006 | 123 | Previous_gtids |       133 |         194 | c4d1a502-b360-11ec-afa4-000c2967ad99:1-7                           |
| mysql-bin.000006 | 194 | Gtid           |       133 |         259 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:8'  |
| mysql-bin.000006 | 259 | Query          |       133 |         353 | create database gtid                                               |
| mysql-bin.000006 | 353 | Gtid           |       133 |         418 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:9'  |
| mysql-bin.000006 | 418 | Query          |       133 |         515 | use `gtid`; create table t1(id int)                                |
| mysql-bin.000006 | 515 | Gtid           |       133 |         580 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:10' |
| mysql-bin.000006 | 580 | Query          |       133 |         652 | BEGIN                                                              |
| mysql-bin.000006 | 652 | Table_map      |       133 |         697 | table_id: 108 (gtid.t1)                                            |
| mysql-bin.000006 | 697 | Write_rows     |       133 |         737 | table_id: 108 flags: STMT_END_F                                    |
| mysql-bin.000006 | 737 | Xid            |       133 |         768 | COMMIT /* xid=13 */                                                |
| mysql-bin.000006 | 768 | Rotate         |       133 |         815 | mysql-bin.000007;pos=4                                             |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
12 rows in set (0.00 sec)
#通过查看可以发现“create database gtid”创建gtid库的语句在“c4d1a502-b360-11ec-afa4-000c2967ad99:8”中

#由于进行误删操作后没有进行刷新日志操作，因此删除gtid库的语句肯定位于最后一个binlog中，因此查看“mysql-bin.000008”
mysql> show binlog events in 'mysql-bin.000008';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000008 |   4 | Format_desc    |       133 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                              |
| mysql-bin.000008 | 123 | Previous_gtids |       133 |         194 | c4d1a502-b360-11ec-afa4-000c2967ad99:1-12                          |
| mysql-bin.000008 | 194 | Gtid           |       133 |         259 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:13' |
| mysql-bin.000008 | 259 | Query          |       133 |         356 | use `gtid`; create table t3(id int)                                |
| mysql-bin.000008 | 356 | Gtid           |       133 |         421 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:14' |
| mysql-bin.000008 | 421 | Query          |       133 |         493 | BEGIN                                                              |
| mysql-bin.000008 | 493 | Table_map      |       133 |         538 | table_id: 110 (gtid.t3)                                            |
| mysql-bin.000008 | 538 | Write_rows     |       133 |         578 | table_id: 110 flags: STMT_END_F                                    |
| mysql-bin.000008 | 578 | Xid            |       133 |         609 | COMMIT /* xid=25 */                                                |
| mysql-bin.000008 | 609 | Gtid           |       133 |         674 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:15' |
| mysql-bin.000008 | 674 | Query          |       133 |         766 | drop database gtid                                                 |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
11 rows in set (0.00 sec)
#通过查看可以发现“drop database gtid”删除gtid库的语句在“c4d1a502-b360-11ec-afa4-000c2967ad99:15”中，由于需要忽略删除gtid库操作，因此选取上一个GTID“14”作为结尾

//根据上述两次查看操作的思路，可以推算出GTID范围为“8-14”，binlog文件范围为“6-8”

//进行binlog备份操作
[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='c4d1a502-b360-11ec-afa4-000c2967ad99:8-14' /var/lib/mysql/mysql-bin.000006 /var/lib/mysql/mysql-bin.000007 /var/lib/mysql/mysql-bin.000008 > /backup/gtid_test2.sql

[root@localhost ~]# ll /backup/gtid_test2.sql 		//查看备份结果
-rw-r--r-- 1 root root 4900 May  2 01:28 /backup/gtid_test2.sql

#恢复数据
mysql> source /backup/gtid_test2.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)

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, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
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)

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)
#可以看出恢复没有报错，大概已经成功

#查看恢复结果
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db3                |
| gtid               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use gtid;
Database changed
mysql> show tables;
+----------------+
| Tables_in_gtid |
+----------------+
| t1             |
| t2             |
| t3             |
+----------------+
3 rows in set (0.01 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
#可以看出全部数据完好无损全部恢复成功

此时再次查看binlog“mysql-bin.000008”

mysql> show binlog events in 'mysql-bin.000008';
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000008 |    4 | Format_desc    |       133 |         123 | Server ver: 5.7.37-log, Binlog ver: 4                              |
| mysql-bin.000008 |  123 | Previous_gtids |       133 |         194 | c4d1a502-b360-11ec-afa4-000c2967ad99:1-12                          |
| mysql-bin.000008 |  194 | Gtid           |       133 |         259 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:13' |
| mysql-bin.000008 |  259 | Query          |       133 |         356 | use `gtid`; create table t3(id int)                                |
| mysql-bin.000008 |  356 | Gtid           |       133 |         421 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:14' |
| mysql-bin.000008 |  421 | Query          |       133 |         493 | BEGIN                                                              |
| mysql-bin.000008 |  493 | Table_map      |       133 |         538 | table_id: 110 (gtid.t3)                                            |
| mysql-bin.000008 |  538 | Write_rows     |       133 |         578 | table_id: 110 flags: STMT_END_F                                    |
| mysql-bin.000008 |  578 | Xid            |       133 |         609 | COMMIT /* xid=25 */                                                |
| mysql-bin.000008 |  609 | Gtid           |       133 |         674 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:15' |
| mysql-bin.000008 |  674 | Query          |       133 |         766 | drop database gtid                                                 |
| mysql-bin.000008 |  766 | Gtid           |       133 |         831 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:16' |
| mysql-bin.000008 |  831 | Query          |       133 |         925 | create database gtid                                               |
| mysql-bin.000008 |  925 | Gtid           |       133 |         990 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:17' |
| mysql-bin.000008 |  990 | Query          |       133 |        1087 | use `gtid`; create table t1(id int)                                |
| mysql-bin.000008 | 1087 | Gtid           |       133 |        1152 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:18' |
| mysql-bin.000008 | 1152 | Query          |       133 |        1224 | BEGIN                                                              |
| mysql-bin.000008 | 1224 | Table_map      |       133 |        1269 | table_id: 112 (gtid.t1)                                            |
| mysql-bin.000008 | 1269 | Write_rows     |       133 |        1309 | table_id: 112 flags: STMT_END_F                                    |
| mysql-bin.000008 | 1309 | Xid            |       133 |        1340 | COMMIT /* xid=61 */                                                |
| mysql-bin.000008 | 1340 | Gtid           |       133 |        1405 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:19' |
| mysql-bin.000008 | 1405 | Query          |       133 |        1502 | use `gtid`; create table t2(id int)                                |
| mysql-bin.000008 | 1502 | Gtid           |       133 |        1567 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:20' |
| mysql-bin.000008 | 1567 | Query          |       133 |        1639 | BEGIN                                                              |
| mysql-bin.000008 | 1639 | Table_map      |       133 |        1684 | table_id: 113 (gtid.t2)                                            |
| mysql-bin.000008 | 1684 | Write_rows     |       133 |        1724 | table_id: 113 flags: STMT_END_F                                    |
| mysql-bin.000008 | 1724 | Xid            |       133 |        1755 | COMMIT /* xid=70 */                                                |
| mysql-bin.000008 | 1755 | Gtid           |       133 |        1820 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:21' |
| mysql-bin.000008 | 1820 | Query          |       133 |        1917 | use `gtid`; create table t3(id int)                                |
| mysql-bin.000008 | 1917 | Gtid           |       133 |        1982 | SET @@SESSION.GTID_NEXT= 'c4d1a502-b360-11ec-afa4-000c2967ad99:22' |
| mysql-bin.000008 | 1982 | Query          |       133 |        2054 | BEGIN                                                              |
| mysql-bin.000008 | 2054 | Table_map      |       133 |        2099 | table_id: 114 (gtid.t3)                                            |
| mysql-bin.000008 | 2099 | Write_rows     |       133 |        2139 | table_id: 114 flags: STMT_END_F                                    |
| mysql-bin.000008 | 2139 | Xid            |       133 |        2170 | COMMIT /* xid=79 */                                                |
| mysql-bin.000008 | 2170 | Stop           |       133 |        2193 |                                                                    |
+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
35 rows in set (0.00 sec)
#通过观察发现，GTID序号从恢复前的“15”，一直递增到现在的“22”，原因在于GTID将刚刚的恢复语句作为普通的DDL和DML语句一起计算
让GTID忽略恢复语句
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> source /backup/gtid_test2.sql

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
#在恢复前通过“set sql_log_bin=0”将binlog关闭，恢复之后再通过“set sql_log_bin=1”将binlog打开，这样就可以让GTID忽略恢复语句

3）忽略GTID恢复（跳过中间某个GTID）

重置日志（日志太多太复杂了）【生产环境不要乱搞】

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#可以看到binlog已恢复为1

①素材

由于GTID对插入数据语句和删除数据语句进行了加密，为了后续恢复时更好的区分GTID，后面每进行一次DDL或DML语句，就查看一次GTID序号变化

mysql> create database it DEFAULT CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 |      334 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
#创建it库，GTID为“1”

mysql> use it;		//切换it库
Database changed

mysql> CREATE TABLE `Student` (   `Sno` int(10) NOT NULL COMMENT '学号',  `Sname` varchar(16) NOT NULL COMMENT '姓名',   `Ssex` char(2) NOT NULL COMMENT '性别',  `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',   `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别',  PRIMARY KEY (`Sno`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      834 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#创建Student表，GTID为“2”

mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from Student;
+-----+--------+------+------+-----------------+
| Sno | Sname  | Ssex | Sage | Sdept           |
+-----+--------+------+------+-----------------+
|   1 | 陆亚   | 男   |   24 | 计算机网络      |
|   2 | tom    | 男   |   26 | 英语            |
|   3 | 张阳   | 男   |   21 | 物流管理        |
|   4 | alex   | 女   |   22 | 电子商务        |
+-----+--------+------+------+-----------------+
4 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |     1205 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#插入四条语句，GTID为“3”

mysql> INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from Student;
+-----+----------+------+------+-----------------+
| Sno | Sname    | Ssex | Sage | Sdept           |
+-----+----------+------+------+-----------------+
|   1 | 陆亚     | 男   |   24 | 计算机网络      |
|   2 | tom      | 男   |   26 | 英语            |
|   3 | 张阳     | 男   |   21 | 物流管理        |
|   4 | alex     | 女   |   22 | 电子商务        |
|   5 | xumubin  | 男   |   29 | 中文专业        |
|   6 | wangzhao | 男   |   21 | 导弹专业        |
+-----+----------+------+------+-----------------+
6 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      516 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#再次插入两条数据，GTID为“4”

模拟误删除操作，删除“Sno>4”的数据（记住此次操作的GTID）【极其重要】

mysql> delete from Student where Sno > 4;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from Student;
+-----+--------+------+------+-----------------+
| Sno | Sname  | Ssex | Sage | Sdept           |
+-----+--------+------+------+-----------------+
|   1 | 陆亚   | 男   |   24 | 计算机网络      |
|   2 | tom    | 男   |   26 | 英语            |
|   3 | 张阳   | 男   |   21 | 物流管理        |
|   4 | alex   | 女   |   22 | 电子商务        |
+-----+--------+------+------+-----------------+
4 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      838 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-5 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#删除“Sno>4”的两条数据，GTID为“5”

mysql> INSERT INTO Student values(0007,'JohnnyG','男',22,'计算机专业'),(0008,'Fang','女',21,'传媒专业');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from Student;
+-----+---------+------+------+-----------------+
| Sno | Sname   | Ssex | Sage | Sdept           |
+-----+---------+------+------+-----------------+
|   1 | 陆亚    | 男   |   24 | 计算机网络      |
|   2 | tom     | 男   |   26 | 英语            |
|   3 | 张阳    | 男   |   21 | 物流管理        |
|   4 | alex    | 女   |   22 | 电子商务        |
|   7 | JohnnyG | 男   |   22 | 计算机专业      |
|   8 | Fang    | 女   |   21 | 传媒专业        |
+-----+---------+------+------+-----------------+
6 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |     1159 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#删除“Sno>4”的两条数据后，再次插入序号为“7”和“8”的两条数据，GTID为“6”

模拟误删除操作，将it库删除

mysql> drop database it;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |     1310 |              |                  | c4d1a502-b360-11ec-afa4-000c2967ad99:1-7 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#此时模拟误删除操作（删除it库），GTID为“7”

②恢复

//进行binlog备份

//思路一：已知中间删除两条数据的语句的GTID为“5”，因此使用冒号忽略“5”
//思路二：由于最后删除it库的GTID为“7”，因此往前取“6”，忽略“7”
[root@localhost ~]# mysqlbinlog --skip-gtids --include-gtids='c4d1a502-b360-11ec-afa4-000c2967ad99:1-4:6' /var/lib/mysql/mysql-bin.000001 -r /backup/it_test1.sql

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

mysql> source /backup/it_test1.sql

mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
#在恢复前通过“set sql_log_bin=0”将binlog关闭，恢复之后再通过“set sql_log_bin=1”将binlog打开，这样就可以让GTID忽略恢复语句

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db3                |
| gtid               |
| it                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.01 sec)

mysql> use it;
Database changed
mysql> show tables;
+--------------+
| Tables_in_it |
+--------------+
| Student      |
+--------------+
1 row in set (0.00 sec)

mysql> select * from Student;
+-----+----------+------+------+-----------------+
| Sno | Sname    | Ssex | Sage | Sdept           |
+-----+----------+------+------+-----------------+
|   1 | 陆亚     | 男   |   24 | 计算机网络      |
|   2 | tom      | 男   |   26 | 英语            |
|   3 | 张阳     | 男   |   21 | 物流管理        |
|   4 | alex     | 女   |   22 | 电子商务        |
|   5 | xumubin  | 男   |   29 | 中文专业        |
|   6 | wangzhao | 男   |   21 | 导弹专业        |
|   7 | JohnnyG  | 男   |   22 | 计算机专业      |
|   8 | Fang     | 女   |   21 | 传媒专业        |
+-----+----------+------+------+-----------------+
8 rows in set (0.00 sec)
#可以看到最后结果是所有数据都恢复成功（包括中间误删的两条数据）
Johnny.G
关注
  • 4
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
myhoard:MySQL备份和时间点恢复服务
05-03
便清除它们（需要外部系统为备用服务器提供已执行的GTID信息） 创建和还原备份几乎不需要额外的本地磁盘空间容错和监视： 通过重试所有操作来处理临时对象存储连接性问题使用通过statsd进行度量通过Sentry意外报告...
MYSQL数据库GTID实现主从复制实现(超级方便)
01-19
一、添加Maria源 vi /etc/yum.repos.d/MariaDB.repo 粘贴阿里云的最新mariadb镜像： [mariadb] name = MariaDB ...gpgcheck=1 安装新版本的MariaDB ...yum install mariadb mariadb-server ...开启binlog日志
MYSQL使用xtrabackup恢复一个基于GTID的replica
q195136130的专栏
04-30 298
官方提供的基于GTID的恢复从库流程。
mysql使用gtid恢复_mysql主从恢复gtid方式
weixin_31498229的博客
01-19 422
1、全量备份脚本(full_mysql_backup.sh)#!/bin/bashBACK_DIR='/home/mysql_back/xtrabackup'MYSQL_CONF='/etc/my.cnf'MYSQL_SOCKET='/var/lib/mysql/mysql.sock'PASSWORD='Runsdata@2017#7v8'CHECK_DATE=`date +%Y%m%d`DIR_...
MySQL中的gtid数据恢复
oldboy1999的博客
06-10 349
MySQL中的gtid数据恢复
基于gtid的备份_恢复_注意事项_mysqldump_skip-gtids_include-gtids_exclude-gtids
细致-专业-实操
03-28 807
是否使用–skip-gtids=true 参数，要根据情况来定；第一种情况：如果我们是要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例，则使用mysqlbinlog进行导出时候，需要使用该参数。如果不带该参数的话，是无法恢复成功的。因为包含的 GTID 已经在源数据库执行过了，根据 GTID 特性，一个 GTID 信息在一个数据库只能执行一次，所以不会恢复成功。
mysql基于gtid方式恢复数据
u013008898的博客
05-31 336
mysql基于gtid方式恢复数据
开启gtid复制时如何备份恢复
一杯Mojito可以吗
11-03 398
目的 为了在开启gtid复制的数据库中顺利导入导出备份文件 导出 显示地添加--set-gtid-purged=OFF参数 备份文件中将不包括gtid信息。 mysqldump -uroot -p --set-gtid-purged=OFF testdb > testdb.sql 为防止以下警告，也为顺利导入： Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all t
mysql gtid 备份恢复_MySQL 备份恢复 (二)
weixin_34644072的博客
01-27 384
模拟故障案例并恢复(1)每天全备 (2)binlog日志是完整 (3)模拟白天的数据变化 (4)模拟下午两点误删除数据库需求： 利用全备+binlog回复数据库误删除之前。模拟全备mysqldump -uroot -pKlvchen_123 -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF ...
mysqldump+binlog+gtid 实现数据库的增量备份
阿瑾的博客
03-27 1670
GTID(Global Transaction ID)，全局事务标识符。是对于一个已提交事务的编号，并且是一个全局唯一 的编号。它是MySQL 5.6加入的一个强大特性，目的在于能够实现主从自动定位和切换，而不像以前需要指定文件和位置。
mysql gtid 增量备份_Mysql增量备份与恢复
weixin_42561476的博客
01-31 157
Mysql增量备份与恢复实验介绍增量恢复一般适用的场景：1、人为的sql语句破坏了数据库2、在进行下一次完全备份之前发生系统故障导致数据库数据丢失3、在主从架构中，主库数据发生了故障丢失完全备份之后更改的数据的恢复步骤1、首先做一个完全备份，确保生成完全备份的sql文件。mysql> select * from yx; #完全备份前数据库+----------+--------+| na...
MySQL中truncate误操作后的数据恢复案例
12-15
日志格式： mixed 没起用gtid 表结构如下： CREATE TABLE `tb_wubx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREM
MySQL 两种恢复数据的方法
01-21
2.1 利用备份恢复 使用这种方式的前提必须有最近的备份集或者知道出现误操作起始的binlog 位点或者GTID,利用备份集恢复到中间的机器上，然后利用MySQL的slave 特性 START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE...
『MySQL 实战 45 讲』20 - 幻读是什么，幻读有什么问题？
Gavinjou大笨象的博客
05-04 977
3. 当执行 select * from t where d=5 for update 的时候，6 个记录加上了行锁，还同时加了 7 个间隙锁，这样就确保无法再插入新的记录。
32位QT连接64位MySQL
最新发布
weixin_51883798的博客
05-05 201
32位QT连接64位MySQL数据库_数据库_༺饮者༻-腾讯云开发者社区 (csdn.net)QT连接mysql：实现5.9.0的32位QT连接8.0的64位mysql并且解决SSL​“SSL connection error: unknown error number QMYSQL错误_ssl connection error: unknown error number qmysql:-CSDN博客
MySQL —— 表的基本操作
学习C++的小陈同学
04-30 494
本篇整理了和表结构相关的各种基本操作（增删查改等等）
【MySQL】1.安装与配置
2301_79719531的博客
04-28 855
文件的安全性问题。文件不利于数据查询和管理。文件不利于存储海量数据。文件在程序中控制不方便。数据库存储介质：磁盘+内存。为了解决上述问题，专家们设计出更加利于管理数据的软件——数据库，他能更加有效的管理数据。数据库可以提供远程服务，即通过远程连接来使用数据库，因此也称为数据库服务器。
MySQL 高级 - 第四章 | 配置文件与系统变量
xhmico的博客
04-29 1380
MySQLmax_connections：允许同时连入的客户端数量default_storage_engine：表的默认存储引擎用系统变量query_cache_size：查询缓存的大小。
MySQL的数据备份和恢复
本科在读，致力于Linux/C++方向的学习，同时涉及数据结构与算法，操作系统，数据库mysql等等
04-28 681
本博客主要内容涉及到mysql数据库的备份和恢复
mysql gtid主从同步
10-09
MySQL GTID (Global Transaction Identifier) 是一种用于主从复制的标识符。它可以确保在主库和从库之间的数据同步过程中，每个事务都具有唯一的标识符，从而简化了主从配置和管理。 要启用MySQL GTID主从同步，需要进行以下步骤： 1. 确保MySQL版本支持GTID。GTID在MySQL 5.6及更高版本中可用。 2. 在主库和从库的配置文件中启用GTID功能。在主库的配置文件中添加以下参数： ``` gtid_mode=ON enforce_gtid_consistency=true ``` 在从库的配置文件中添加以下参数： ``` gtid_mode=ON ``` 3. 重启MySQL服务以使配置生效。 4. 在主库上创建一个新的复制账号，并授权给从库使用。 5. 在主库上执行一个备份，并将备份恢复到从库。 6. 在从库上启动复制进程，并指定要连接的主库信息。 完成上述步骤后，主从同步就会使用GTID进行数据同步和冲突解决。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值