标签：binlog 事务 set GTID mysql gtid

一、什么是GTID

从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。

通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。

这种方式强化了数据库的主备一致性，故障恢复以及容错能力。

在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量pos值进行增量同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。

借助GTID，在发生主备切换的情况下，MySQL的其它从库可以自动在新主库上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。

另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

二、什么是事务

用转账举例：
1.超哥发起转账，转给表弟
2.超哥卡里少了500元
3.表弟卡里多了500元
事务就是这三件事、三个动作，是一根绳上的蚂蚱，要么都成功，要么都失败

事务的ACID特性

1.Atomicity原子性

原子性强调转账的三个步骤要么成功、要么失败

在一个事务中的所有SQL语句，要么全部执行成功，要么全部失败，即使成功的SQL语句也会被撤销，回到执行之前的状态。

Consistency一致性

一致性是指数据库从一个状态、变为另一个状态

事务开始前、与结束后，数据库的完整性约束没有被破坏

Isolation隔离性

隔离性指的是每个读写事务对其他的事务操作，都是相互隔离不受影响的。
例如同是工商银行
超哥转账操作不会影响到小猪佩奇的转账操作

Durability持久性

事务一旦提交后，结果就是永久性生效。

正确事务执行

# 1.创建表
create table linux0224.bank(
    name varchar(20),
    money decimal(20, 2)
)charset=utf8;

# 2.插入数据
use linux0224;
insert into bank values("于超", 20000),("张飞", 6000);

# 3.执行事务，如存取钱的更新，update
begin;
update bank set money=money-3000 where name="于超";
update bank set money=money+3000 where name="张飞";
commit;

# 4.查询价格
mysql> select * from bank;
+--------+----------+
| name   | money    |
+--------+----------+
| 于超   | 17000.00 |
| 张飞   |  9000.00 |
+--------+----------+
2 rows in set (0.00 sec)

错误SQL事务执行

# 故意的SQL写错，查看在一个事务下的SQL执行状态

begin;
update bank set money=money-3000 where name="于超";
update bank ssssssssset money=money+3000 where name="张飞";
rollback;


# 事务特性，实现了数据正确性，一致性
mysql> select * from bank;
+--------+----------+
| name   | money    |
+--------+----------+
| 于超   | 17000.00 |
| 张飞   |  9000.00 |
+--------+----------+
2 rows in set (0.00 sec)

mysql默认的事务规则

在MySQL数据库中，事务默认是会自动提交的，也就是说，如果没有用 begin ... commit 来显式提交事务的话，MySQL 会认为每一条SQL语句都是一个事务，也就是每一条SQL语句都会自动提交。

可以基于mysqlbinlog去分析日志，发现每一个语句都是事务操作。

语法规则

在 MYSQL 中事务处理主要有两种方法：

1）用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

mysql> show variables like '%commit%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| autocommit                              | ON    |   #
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
| binlog_order_commits                    | ON    |
| innodb_api_bk_commit_interval           | 5     |
| innodb_commit_concurrency               | 0     |
| innodb_flush_log_at_trx_commit          | 1     |
| slave_preserve_commit_order             | OFF   |
+-----------------------------------------+-------+
8 rows in set (0.00 sec)

三、GTID长啥样

GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。

GTID 实际上 是由 UUID+TID 组成的。

其中 UUID 是一个 MySQL 实例的唯一标识。

TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

下面是一个GTID的具体形式：
GTID = source_id ：transaction_id

具体结果
2E11FA47-61CA-11E1-9E33-C70AA9429562:28

据库的事务，等号右边的source_id表示执行事务的源服务器主库的uuid（也就是server_uuid）

而transaction_id是一个从1开始的自增的序列号，表示在这个主库上执行的第n个事务。

只要保证每台数据库的server_uuid全局唯一，以及每台数据库生成的transaction_id自身唯一，就能保证GTID的全局唯一性。

四、开启uuid

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.00 sec)

开启GTID功能

# 在/etc/my.cnf文件的[mysqld]属性下加入
gtid-mode=ON
enforce-gtid-consistency=true
log-slave-updates=ON

# 重启
systemctl restart mysqld

建议mysql5.7以后的版本，默认都开启GTID功能，用处很广。

五、GTID实践

# 创建库以及查看GTID
mysql> create database gtid_db charset utf8mb4;
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 |      338 |              |                  | ae068f82-06bc-11ed-839d-000c29b76f3a:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

# 创建表，再看gtid
mysql> use gtid_db;
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.000001 |      506 |              |                  | ae068f82-06bc-11ed-839d-000c29b76f3a:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)


# 插入数据再看gtid状态，主动开启个事务看看
# 这个事务下，有3个数据插入动作

begin;
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
commit;

# 可以看出，3个insert 作为了一个事务记录

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 |      941 |              |                  | ae068f82-06bc-11ed-839d-000c29b76f3a:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

# 查看binlog的事件记录
show binlog events in 'mysql-bin.000008';

基于GTID截取日志

有了gtid之后，再也不用关心日志的开始pos，结束pos了，一个gtid记录，记录一个事务。

# 注意参数的添加，--skip-gtids ，不加mysql会进行gtid记录的幂等性检查，导入sql会报错
# 导出建库，创建数据的所有gtid记录，不需要记录pos了

mysqlbinlog  --skip-gtids  --include-gtids='ae068f82-06bc-11ed-839d-000c29b76f3a:1-3' /usr/local/mysql/logs/mysql-bin.000001  > /tmp/gtid_t1.sql

基于gtid记录的数据恢复

1.删除数据库试试
mysql> drop database gtid_db;
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 |     1107 |              |                  | ae068f82-06bc-11ed-839d-000c29b76f3a:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

# 查看binlog的事件
mysql> show binlog events in 'mysql-bin.000001';

2.恢复数据，注意关闭二进制日志的记录
set sql_log_bin=0;
source  /tmp/gtid_t1.sql;
set sql_log_bin=1;

重置gtid，清理binlog日志

RESET MASTER可以用来清除GTID的执行历史，全部清空。危险命令。。

删除某个日志
清除二进制日志需要BINLOG_ADMIN特权。
如果未使用--log-bin选项启动服务器以启用二进制日志记录，则此语句无效。

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

binglog刷新

mysql> flush logs;

mysqladmin flush-logs
mysql -e "flush logs"
mysqldump -F

# 自动刷新，max_binlog_size 超过binlog的容量后，自动刷新新日志 默认1G ，单位bytes
# https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html`

mysql> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
|        1073741824 |
+-------------------+
1 row in set (0.00 sec)

日志自动删除

# 常见做法是备份7天
mysql> show variables like '%expire%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| disconnect_on_expired_password | ON    |
| expire_logs_days               | 0     |
+--------------------------------+-------+
2 rows in set (0.00 sec)

# 设置
set GLOBAL expire_logs_days=7;

标签：binlog,事务,set,GTID,mysql,gtid
From： https://www.cnblogs.com/chunjeh/p/17792242.html

mysql-基于GTID的binlog日志