基于GTID的binlog应用
基于GTID的binlog应用
1 什么是GTID
从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。
通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。
这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量pos值进行增量同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。借助GTID，在发生主备切换的情况下，MySQL的其它从库可以自动在新主库上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。
另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。
2 什么是事务
事务就是
这三件事、三个动作，是一根绳上的蚂蚱，要么都成功，要么都失败
转账要么到表弟账户、要么就转不出去、回到自己卡里
事务的ACID特性
TRANSACTION 事务
Atomicity原子性
原子性强调转账的三个步骤要么成功、要么失败
在一个事务中的所有SQL语句，要么全部执行成功，要么全部失败，即使成功的SQL语句也会被撤销，回到执行之前的状态。
Consistency一致性
一致性是指数据库从一个状态、变为另一个状态
事务开始前、与结束后，数据库的完整性约束没有被破坏。
例如转账，无论成功、或者失败、这500不会多、也不会少
要么超哥卡里扣了500元、表弟多了500元
要么超哥转账失败500元未动、表弟一毛钱也没拿到
这个总和永远是500元，不多也不少
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>
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
在上面的定义中，每一个GTID均代表一个数据库的事务，等号右边的source_id表示执行事务的源服务器主库的uuid（也就是server_uuid）
而transaction_id是一个从1开始的自增的序列号，表示在这个主库上执行的第n个事务。
只要保证每台数据库的server_uuid全局唯一，以及每台数据库生成的transaction_id自身唯一，就能保证GTID的全局唯一性。
server_uuid是什么
server_id=5
开启uuid
vim /etc/my.cnf 加入
[root@db-51 ~]#cat /etc/my.cnf
[mysqld]
gtid-mode=ON
enforce-gtid-consistency=true
log-slave-updates=ON
server_id=51
log_bin=/www.yuchaoit.cn/mysql_3306/logs/mysql-bin
character_set_server=utf8mb4
port=3306
user=mysql
basedir=/opt/mysql
datadir=/www.yuchaoit.cn/mysql_3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
# 重启
systemctl restart mysqld
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截取日志
# 注意参数的添加，--skip-gtids ，不加mysql会进行gtid记录的幂等性检查，导入sql会报错
# 导出从建库，创建数据，的所有gtid记录，不需要记录pos了
mysqlbinlog --skip-gtids --include-gtids='ae068f82-06bc-11ed-839d-000c29b76f3a:1-3' /www.yuchaoit.cn/mysql_3306/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;
