--set-gtid-purged=auto
auto ， on ，off
使用场景:
1.--set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gtid-purged=OFF >/data/backup/full.sql
2.auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gtid-purged=ON >/data/backup/full.sql
--max-allowed-packet=# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql --max-allowed-packet=# The maximum packet length to send to or receive from server.
binlog日志的GTID新特性
什么是GTID？
GTID(Global Transaction ID)，全局事务标识符。是对于一个已提交事务的编号，并且是一个全局唯一 的编号。它是MySQL 5.6加入的一个强大特性，目的在于能够实现主从自动定位和切换，而不像以前需要指定文件和位置。
GTID的格式与存储？
1.单个GTID
GTID与主库上提交的每个事务相关联。此标识符不仅对发起事务的库是唯一的，而且在给定复制拓扑中的所有库中都是唯一的。GTID用冒号分隔的一对坐标表示，例如：
8eed0f5b-6f9b-11e9-94a9-005056a57a4e:23 前一部分是主库的server_uuid，后面一部分是主库上按提交事务的顺序确定的序列号，提交的事务序号从1 开始。 GTID = server_uuid ：transaction_id
2.GTID集
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.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 | | +----------------+------------+------+-----+---------+-------+
mysql.gtid_executed表记录的是服务器上已经执行事务的GTID。三个字段分别表示发起事务的服务器UUID、UUID集的起始和结束事务ID。对于单个GTID，后两个字段的值相同。
如何开启？
vim /etc/my.cnf gtid-mode=on enforce-gtid-consistency=true
重启服务查看：
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.00 sec)
DDL和DML语句查看gtid
# DDL一个语句产生一个gtid mysql> create database db3; 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.000013 | 310 | | | 6181523d-bc2e11ea-a78b-000c29221146: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.01 sec) mysql> show master status; +------------------+----------+--------------+------------------+---------------- --------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+---------------- --------------------------+ | mysql-bin.000013 | 471 | | | 6181523d-bc2e11ea-a78b-000c29221146: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.000013 | 632 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-3 | +------------------+----------+--------------+------------------+---------------- --------------------------+ 1 row in set (0.00 sec) mysql> create table t3 (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.000013 | 793 | | | 6181523d-bc2e11ea-a78b-000c29221146: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.000013 | 1128 | | | 6181523d-bc2e11ea-a78b-000c29221146: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.000013 | 1379 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-6 | +------------------+----------+--------------+------------------+---------------- --------------------------+ 1 row in set (0.00 sec)
基于GTID进行查看binlog
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000013'; +------------------+------+----------------+-----------+-------------+----------- --------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+----------- --------------------------------------------------------+ | mysql-bin.000013 | 4 | Format_desc | 123 | 123 | Server ver: 5.7.14-log, Binlog ver: 4 | | mysql-bin.000013 | 123 | Previous_gtids | 123 | 154 | | | mysql-bin.000013 | 154 | Gtid | 123 | 219 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:1' | | mysql-bin.000013 | 219 | Query | 123 | 310 | create database db3 | | mysql-bin.000013 | 310 | Gtid | 123 | 375 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:2' | | mysql-bin.000013 | 375 | Query | 123 | 471 | use `db3`; create table t1 (id int) | | mysql-bin.000013 | 471 | Gtid | 123 | 536 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:3' | | mysql-bin.000013 | 536 | Query | 123 | 632 | use `db3`; create table t2 (id int) | | mysql-bin.000013 | 632 | Gtid | 123 | 697 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:4' | | mysql-bin.000013 | 697 | Query | 123 | 793 | use `db3`; create table t3 (id int) | | mysql-bin.000013 | 793 | Gtid | 123 | 858 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:5' | | mysql-bin.000013 | 858 | Query | 123 | 929 | BEGIN | | mysql-bin.000013 | 929 | Table_map | 123 | 973 | table_id: 108 (db3.t1) | | mysql-bin.000013 | 973 | Write_rows | 123 | 1013 | table_id: 108 flags: STMT_END_F | | mysql-bin.000013 | 1013 | Table_map | 123 | 1057 | table_id: 108 (db3.t1) | | mysql-bin.000013 | 1057 | Write_rows | 123 | 1097 | table_id: 108 flags: STMT_END_F | | mysql-bin.000013 | 1097 | Xid | 123 | 1128 | COMMIT /* xid=21 */ | | mysql-bin.000013 | 1128 | Gtid | 123 | 1193 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:6' | | mysql-bin.000013 | 1193 | Query | 123 | 1264 | BEGIN | | mysql-bin.000013 | 1264 | Table_map | 123 | 1308 | table_id: 109 (db3.t2) | | mysql-bin.000013 | 1308 | Write_rows | 123 | 1348 | table_id: 109 flags: STMT_END_F | | mysql-bin.000013 | 1348 | Xid | 123 | 1379 | COMMIT /* xid=26 */ | +------------------+------+----------------+-----------+-------------+----------- --------------------------------------------------------+ 22 rows in set (0.00 sec)
具备GTID后,截取查看某些事务日志: --include-gtids --exclude-gtids
示例：演示跨binlog文件截取日志。
1.第一次操作：
mysql> show master status; +------------------+----------+--------------+------------------+---------------- --------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+---------------- --------------------------+ | mysql-bin.000013 | 1379 | | | 6181523d-bc2e11ea-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)
2.第二次操作：
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)
3.第三次操作：
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; Query OK, 3 rows affected (0.01 sec)
使用binlog日志恢复误删除的gitd数据库。
首先要确定gtid的起始和结束。
mysql> show binlog events in 'mysql-bin.000013'; ... | mysql-bin.000013 | 1379 | Gtid | 123 | 1444 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:7' | | mysql-bin.000013 | 1444 | Query | 123 | 1538 | create database gtid ... 开始： 文件：mysql-bin.000013 gtid：'6181523d-bc2e-11ea-a78b-000c2921146:7'
mysql> show master status; +------------------+----------+--------------+------------------+---------------- ---------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+---------------- ---------------------------+ | mysql-bin.000015 | 766 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-14 | +------------------+----------+--------------+------------------+---------------- ---------------------------+ 1 row in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000015'; +------------------+-----+----------------+-----------+-------------+------------ --------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+------------ --------------------------------------------------------+ | mysql-bin.000015 | 4 | Format_desc | 123 | 123 | Server ver: 5.7.14-log, Binlog ver: 4 | | mysql-bin.000015 | 123 | Previous_gtids | 123 | 194 | 6181523dbc2e-11ea-a78b-000c29221146:1-11 | | mysql-bin.000015 | 194 | Gtid | 123 | 259 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:12' | | mysql-bin.000015 | 259 | Query | 123 | 356 | use `gtid`; create table t3(id int) | | mysql-bin.000015 | 356 | Gtid | 123 | 421 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:13' | | mysql-bin.000015 | 421 | Query | 123 | 493 | BEGIN | | mysql-bin.000015 | 493 | Table_map | 123 | 538 | table_id: 112 (gtid.t3) | | mysql-bin.000015 | 538 | Write_rows | 123 | 578 | table_id: 112 flags: STMT_END_F | | mysql-bin.000015 | 578 | Xid | 123 | 609 | COMMIT /* xid=50 */ | | mysql-bin.000015 | 609 | Gtid | 123 | 674 | SET @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:14' | | mysql-bin.000015 | 674 | Query | 123 | 766 | drop database gtid | +------------------+-----+----------------+-----------+-------------+------------ --------------------------------------------------------+ 11 rows in set (0.00 sec) 确定结束： 文件：mysql-bin.000015 gtid：'6181523d-bc2e-11ea-a78b-000c29221146:14'
其次，binlog使用gtid截取日志
确定起始范围：7-13
文件：mysql-bin.000013 mysql-bin.000014 mysql-bin.000015
[root@mysql ~]# cd /var/lib/mysql [root@mysql mysql]# mysqlbinlog --skip-gtids --include-gtids='6181523d-bc2e-11ea-a78b000c29221146:7-13' mysql-bin.000013 mysql-bin.000014 mysql-bin.000015 -r /tmp/gtid1.sql --skip-gtids GTID的幂等性 开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了 接着上面，截取日志时添加--skip-gtids。
最后，使用binlog日志恢复
mysql> set sql_log_bin=0; #设为0后，在Master数据库上执行的语句都不记录binlog Query OK, 0 rows affected (0.00 sec) mysql> source /tmp/gtid1.sql mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec) mysql> show databases like 'gtid'; Empty set (0.00 sec)
