1、查看参数
mysql> show variables like '%GTID%';
2、配置GTID
vim /etc/my.cnf
gtid_mode=ON #开关
enforce_gtid_consistency=ON #强制GTID一致性
log_slave_updates=ON #强制从库更新binlog
3、GTID 说明
是对于一个已提交事务的编号, 并且是一个全局唯一的编号
它的官方定义如下:
GTID = source_id ：transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
5.6 版本新加的特性, 5.7中做了加强
5.6 中不开启, 没有这个功能
5.7 中的GTID, 即使不开也会有自动生成
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
server_uuid
从初始化数据目录, 第一次启动就会生成, 存放 /数据目录/auto.cnf
查询 server_uuid
mysql> select @@server_uuid;
重要参数介绍
mysql> create database gtdb charset utf8mb4;
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 | 329 | | | 2e6ca7e0-45bd-11eb-bba7-000c2909345a:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
mysql> use gtdb;
mysql> create table t1(id int);
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 491 | | | 2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql> begin;
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(3);
mysql> commit;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 914 | | | 2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
4、基于GTID查看 bin_log
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 7 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 7 | 154 | |
| mysql-bin.000001 | 154 | Gtid | 7 | 219 | SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:1' |
| mysql-bin.000001 | 219 | Query | 7 | 329 | create database gtdb charset utf8mb4 |
| mysql-bin.000001 | 329 | Gtid | 7 | 394 | SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:2' |
| mysql-bin.000001 | 394 | Query | 7 | 491 | use `gtdb`; create table t1(id int) |
| mysql-bin.000001 | 491 | Gtid | 7 | 556 | SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:3' |
| mysql-bin.000001 | 556 | Query | 7 | 628 | BEGIN |
| mysql-bin.000001 | 628 | Table_map | 7 | 673 | table_id: 108 (gtdb.t1) |
| mysql-bin.000001 | 673 | Write_rows | 7 | 713 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000001 | 713 | Table_map | 7 | 758 | table_id: 108 (gtdb.t1) |
| mysql-bin.000001 | 758 | Write_rows | 7 | 798 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000001 | 798 | Table_map | 7 | 843 | table_id: 108 (gtdb.t1) |
| mysql-bin.000001 | 843 | Write_rows | 7 | 883 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000001 | 883 | Xid | 7 | 914 | COMMIT /* xid=13 */ |
| mysql-bin.000001 | 914 | Gtid | 7 | 979 | SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:4' |
| mysql-bin.000001 | 979 | Query | 7 | 1071 | drop database gtdb |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
#每个事务操作, 都是提前设置一个server_uuid:N 号
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:1'
create database gtdb charset utf8mb4
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:2'
use `gtdb`; create table t1(id int)
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:3'
#inster 操作
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:4'
drop database gtdb
5、GTID的幂等性
GTID有 "幂等性" 检查, GTID的生成, 通过 SET @@SESSION.GTID_NEXT='xxx:x' 命令实现的
例如:
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:1'
执行 SET 命令时, 自动检查当前系统是否包含这个GTID信息, 如果有就跳过
mysqlbinlog --skip-gtids --include-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-3' /data/3307/binlog/mysql-bin.000001 > /tmp/gtdb.sql
--skip-gtids: 不记录 SET @@SESSION.GTID_NEXT='xxx:x'
#恢复
set sql_log_bin=0;
source /tmp/gtdb.sql
set sql_log_bin=1;
6、通过GTID方式截取 bin_log
6.1、错误的截取
mysqlbinlog --include-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-3' /data/3307/binlog/mysql-bin.000001 > /tmp/gtdb.sql
source /tmp/gtdb.sql
#导出时出现了error
ERROR 1049 (42000): Unknown database 'gtdb'
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
Query OK, 0 rows affected (0.00 sec)
#查询数据库, 没有恢复gtdb库
mysql> show databases;
思考: 为什么会报错
GTID有 "幂等性" 检查, GTID的生成, 通过 SET @@SESSION.GTID_NEXT='xxx:x' 命令实现的
例如:
SET @@SESSION.GTID_NEXT= '2e6ca7e0-45bd-11eb-bba7-000c2909345a:1'
执行 SET 命令时, 自动检查当前系统是否包含这个GTID信息, 如果有就跳过
6.2、正确截取的方式
mysqlbinlog --skip-gtids --include-gtids='2e6ca7e0-45bd-11eb-bba7-000c2909345a:1-3' /data/3307/binlog/mysql-bin.000001 > /tmp/gtdb.sql
--skip-gtids: 不记录 SET @@SESSION.GTID_NEXT='xxx:x'
#恢复
set sql_log_bin=0;
source /tmp/gtdb.sql
set sql_log_bin=1;
6.3、其它场景
开了GTID之后、是否可以用pos方式截取、需不需要加 skip-gtids
可以, 需要加 skip-gtids
6.4、拓展用法
需求: server_uuid:1-10, 跳过第5个
mysqlbinlog --skip-gtids --include-gtids='server_uud:1-10' --exclude-gtids='server_uuid:5' mysql-bin.xxx > /tmp/xxx.sql
需求: 跨多个文件截取
mysql-bin.000001 mysql-bin.000002 mysql-bin.000003
server_id:1-10
mysqlbinlog --skip-gtids --include-gtids='server_uud:1-10'
mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 > /tmp/xxx.sql
7、日志文件清理操作
7.1、bin_log 日志滚动
命令触发
mysql> flush logs;
shell# mysqladmin flush-logs
shell# mysql -e "flush logs"
shell# mysqldump -F
自动触发
1. 日志文件达到设置的最大容量, 默认 1GB
mysql> select @@max_binlog_size;
2. 数据库重启
7.2、bin_log日志删除
7.2.1、配置自动清理
默认: 不自动清理、直至磁盘写满为止
vim /etc/my.cnf
expire_logs_days=15 # 保留15天
#查看binlog保留天数
mysql> select @@expire_logs_days;
#最少设置保留多少天合适?
参考全备周期
例如: 一星期一次全备,可以保留8天,生成环境中一般保留2轮备份周期的日志,15天
7.2.2、手工清理
尽量配置自动清理、不要手动清理
#查看帮助
mysql> help purge binary logs;
Examples:
#删除到某个binlogs为止
PURGE BINARY LOGS TO 'mysql-bin.010';
举例:
有20个binlog
#删除mysql-bin.000001 - 9;
PURGE BINARY LOGS TO 'mysql-bin.0000010';
#删除某个时间位置
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
若有收获，就点个赞吧
0 人点赞
