gtid运维实战
1.gtid与start slave
help start slave START SLAVE [thread_types] [until_option] [connection_options] thread_types: [thread_type [, thread_type] ... ] thread_type: IO_THREAD | SQL_THREAD until_option: UNTIL { {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set # 1.直到指定的gtid位置停下 | MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos # 2.直到指定的binlog位置停下 | RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos # 3.直到指定的relay log位置停下 | SQL_AFTER_MTS_GAPS } 4.直到slave上多个并行线程之前没有延迟差距了就停下,因为多线程复制，不同线程的复制进度不一样，因此有差距 SQL_BEFORE_GTIDS = $gitd_set ： # $gtid_set之前的gtid都会被执行 eg. START SLAVE SQL_THREAD UNTIL SQL_BEFORE_GTIDS='3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56' # 表示，当SQL_thread 执行到3E11FA47-71CA-11E1-9E33-C80AA9429562:10 的时候停止，下一个事务是11 SQL_AFTER_GTIDS = $gitd_set : # $gtid_set之前，以及$gtid_set包含的gtid都会被执行 eg. START SLAVE SQL_THREAD UNTIL SQL_AFTER_GTIDS='3E11FA47-71CA-11E1-9E33-C80AA9429562:11-56' # 表示，当SQL_thread 执行到3E11FA47-71CA-11E1-9E33-C80AA9429562:56 的时候停止，56是最后一个提交的事务。 如何从multi-threaded slave 转化成 single-threaded mode stop slave sql_thread; START SLAVE UNTIL SQL_AFTER_MTS_GAPS; SET @@GLOBAL.slave_parallel_workers = 0; START SLAVE SQL_THREAD;
2.gtid与upgrade
如果 --gtid-mode=ON ，那么在使用upgrade时候，不推荐使用--write-binlog 选项。
因为，mysql_upgrade会更新Myisam引擎的系统表. 而同时更新transction table 和 non-trasaction table 是gtid所不允许的
3.gtid与mysql.gtid_executed
gtid_mode = (ON|ON_PERMISSIVE), bin_log = off gtid 会实时的写入到mysql.gtid_executed表中，且根据executed_gtids_compression_period=N来压缩 gtid_mode = (ON|ON_PERMISSIVE), bin_log = on gtid 不会实时的写入到mysql.gtid_executed，executed_gtids_compression_period会失效。 只有当binlog rotate(flush logs;)或者mysql shutdown的时候才会写入mysql.gtid_executed 如果master异常shutdown，gtid还没有写入到mysql.gtid_executed怎么办呢？这种场景，一般通过mysql recover机制写入到mysql.gtid_executed中 注意： mysql.gtid_executed表的记录可能并不是完整的已执行GTID，而且有不可访问的可能性（例如误删除此表），因此建议始终通过查询@@global.gtid_executed（每次提交后更新）来确认MySQL服务器的GTID状态，而不是查询mysql.gtid_executed表。 # 也就是说，mysql.gtid_executed表的信息不是实时记录的，要想获取实时的gtid集合的信息，需要在全局变量中@@global.gtid_executed来获取 # mysql.gtid_executed表中的gtid是在事务完成之后才写入的，也就是说和事务本身不是原子的，所以一般以@@global.gtid_executed为标准
注意二：
启用二进制日志记录时，mysql.gtid_executed表并不保存所有已执行事务的GTID的完整记录，该信息由gtid_executed全局系统变量的值提供。
如果服务器意外停止，则当前二进制日志文件中的GTID集不会保存在mysql.gtid_executed表中。在MySQL实例恢复期间，这些GTID将从二进制日志文件添加到表中
换句话说就是，开启了二进制，就将gtid_ececuted的值就录到系统变量@@global_gtid_exectued中
没有开启的二进制的话，就记录在系统表中，但是不是原子操作，有可能少数据
4.gtid与gtid_next
gtid_next的取值为： # http://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html#sysvar_gtid_next AUTOMATIC: Use the next automatically-generated global transaction ID. ANONYMOUS: Transactions do not have global identifiers, and are identified by file and position only. A global transaction ID in UUID:NUMBER format. 问题：GTID 0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-50 对应的事务顺序，从小到大，一定是顺序执行的吗？ 答案： 不一定。一般情况下事务是从小到大，顺序执行的。 但是如果再MTS场景，或者是人工设置gtid_next的情况下，就可能不是顺序执行了 dba:(none)> show master status; +--------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------------------------------+ | xx.000009 | 1719 | | | 0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-46 | +--------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec) dba:(none)> set gtid_next='0923e916-3c36-11e6-82a5-ecf4bbf1f518:50'; Query OK, 0 rows affected (0.00 sec) dba:lc> insert into gtid_1 values(5); Query OK, 1 row affected (0.00 sec) dba:lc> set gtid_next=AUTOMATIC; Query OK, 0 rows affected (0.00 sec) dba:lc> flush logs; Query OK, 0 rows affected (0.01 sec) dba:lc> show master status; +--------------------+----------+--------------+------------------+----------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+----------------------------------------------+ | xx.000010 | 210 | | | 0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-46:50 | +--------------------+----------+--------------+------------------+----------------------------------------------+ 1 row in set (0.00 sec) dba:lc> insert into gtid_1 values(6); Query OK, 1 row affected (0.00 sec) dba:lc> insert into gtid_1 values(6); Query OK, 1 row affected (0.00 sec) dba:lc> insert into gtid_1 values(6); Query OK, 1 row affected (0.00 sec) dba:lc> show master status; +--------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +--------------------+----------+--------------+------------------+-------------------------------------------+ | xx.000010 | 1125 | | | 0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-50 | +--------------------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 sec) 在这里面，很明显0923e916-3c36-11e6-82a5-ecf4bbf1f518:1-50 事务执行顺序为： 1-46(最先执行) ， 50（其次执行） ， 47-49（最后执行）
5.gtid与mha
GTID模式下，需要relay-log吗？purge_relay_log设置为on可以吗？ # replication 架构 host_1(host_1:3306) (current master) +--host_2(host_2:3306 candidate master) +--host_3(host_3:3306 no candidate) # 模拟： 1.大量并发的写入，一直持续的往host_1写数据,造成并发写入很大的样子 2.host_2：stop slave，造成host_2 延迟master很多的样子 3.host_1: purge binary logs， 造成master删掉了日志，导致host_2 修复的时候拿不到master的最新binlog 4.host_3: 一直正常同步master，拥有最新的binlog 5.host_3: flush logs; purge_relay_log=on; flush logs；一直循环flush logs，造成host_3已经将最新的relay log删掉了，host_2 是肯定拿不到host_3的relay 来修复自己了 6.好了，一切条件均已经准备完毕，这个时候让master 宕机，这样就能模拟出在relay log没有的情况下，是否可以正常完成mha 切换了 ............... 7.结果完成了正常切换，那mha是怎么再gtid模式下，在没有relay log的情况下，正常切换的恩？ 8.原理：host_2发现自己不是最新的slave，所以就去change master到host_3,通过host_3的binlog来恢复 9.最后，当host_2和host_3都一致的情况下，再让host_3 重新指向host_2，完毕... 结论： gtid模式下，mha恢复切换的原理是不需要relay log的，只需要binlog
6.gtid与备份(物理备份，逻辑备份)
GTID 和 备份（物理备份+逻辑备份） 物理备份：xtrabackup，其他等 逻辑备份：mysqldump，mydumper，mysqlpump等 物理备份 备份的时候，只要在备份的时候记录下Executed_Gtid_Set($gtid_dump)即可，这个可以用于重新change master; reset master; SET @@GLOBAL.GTID_PURGED='$gtid_dump'; change master to master_auto_position=1; # 一般在xtrabackup_binlog_info中，如果加了slave-info参数，可以子啊xtrabackup_slave_info中 逻辑备份 如果是全备： 逻辑备份需要设置--set-gtid-pursed=on,这样从备份时启动开始的事务之前的gtid都不需要了，因为在dump文件里面已经有了 如果是部分备： 需要设置--set-gtid-pursed=off
7.gtid与crash safe slave(从库崩溃)
问题： slave relay log 不完整怎么办？（relay-log-recover=1） relay-log-recover=0 不考虑，因为它会舍弃掉relay log 关于crash safe ， 可以参考官方文档列出的安全配置 http://dev.mysql.com/doc/refman/5.7/en/replication-solutions-unexpected-slave-halt.html 单线程复制 Non-GTID 推荐配置： relay_log_recovery=1，relay_log_info_repository=TABLE，master_info_repository=TABLE GTID 推荐配置： MASTER_AUTO_POSITION=on，relay_log_recovery=0 多线程复制(slave mts) Non-GTID 推荐配置： relay_log_recovery=1, sync_relay_log=1,relay_log_info_repository=TABLE，master_info_repository=TABLE GTID 推荐配置： MASTER_AUTO_POSITION=on, relay_log_recovery=0 关于relay_log_recovery参数： 参数默认是打开的，在数据库启动后立即启动自动relay log恢复，在恢复过程中，创建一个新的relay log文件，将sql线程的位置初始化到新的relay log，并将i/o线程初始化到sql线程位置。 # 从库如何从宕机状态恢复到正确状态，取决与从库是单线程还是多线程、relay_log_recover参数的值，以及master_auto_position的使用方式。 官方解释： 1.非GTID模式下，如何保证slave crash safe 呢？ relay_log_recovery=1，relay_log_info_repository=TABLE，master_info_repository=TABLE,innodb_flush_log_at_trx_commit=1,sync_binlog=1 2.GTID模式下，如何保证slave crash safe呢？ relay_log_recovery=(1|0)，relay_log_info_repository=TABLE，master_info_repository=TABLE,innodb_flush_log_at_trx_commit=1,sync_binlog=1 以上两种情况配置，可以保证crash safe这里看到区别就是relay_log_recovery了，gtid可以是any，这就需要讨论下了。 当relay_log_recovery=1时，当mysql crash的时候，会丢弃掉之前获取的relay，所以这个不会产生一致性问题。 当relay_log_recovery=0时 如果是非GTID模式，因为没办法保证写master_info.log和relay log file之间的原子性，会导致slave有可能多拉取一个事务，这样就有一致性问题。 如果是GTID模式，因为binlog-dump协议变了，master_info.log已经不用，slave会将已经exected_GTID与retrieve_gtid的并集发送给master，以此来获取没有执行过的gtid，所以没问题。 # 这里面的retrieve_gtid就是IO_thread从master获取的gtid，会写入到relay log。 模拟relay log不完整的情况，从上面可以知道，relay log的记录非常重要，那么relay log 不完整，会怎么样呢？ 1) master 创建一张10G的表，然后执行全表更新操作。 2）这时候，slave就在狂写relay log了 3）此时，去slave kill掉mysql进程 4）这时候，relay log就不完整了 WARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set. This might be because the last statement was not fully written to the log, or because you are using a --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output. 总结： relay log不完整，MySQL重启后，Slave SQL thread会根据mysql.slave_relay_log_info表中记录的位置点信息去starting replication。 会重新获取不完整的这个events，sql_thread在回放的时候，如果发现events不完整，会跳过，不会影响到同步。
8.gtid与mts
如果MTS遇到Gap transction怎么办？ # Gap transction间隙事务 1. 先解决问题 START SLAVE UNTIL SQL_AFTER_MTS_GAPS; # 直到slave上多个并行线程之前没有延迟差距了就停下,因为多线程复制，不同线程的复制进度不一样，因此有差距 2. 考虑设置slave_preserve_commit_order=1; # 从库多线程回放的顺序与主库的执行顺序一致
9.gtid在生产中必须要考虑的问题
Migration to GTID replication Non transactionally safe statement will raise errors now MySQL Performance in GTID mysql_upgrade script Errant transactions Filtration on the slave Injecting empty transactions
10.gtid与gtid online升级
1.offline 方式升级 offline 的方式升级最简单。全部关机，然后配置好GTID，重启，change master to MASTER_AUTO_POSITION=1。 2.online方式升级 与gtid_mode参数值有关。 GTID_MODE = OFF : 不产生Normal_GTID，只接受来自master的ANONYMOUS_GTID GTID_MODE = OFF_PERMISSIVE : 不产生Normal_GTID，可以接受来自master的ANONYMOUS_GTID & Normal_GTID GTID_MODE = ON_PERMISSIVE : 产生Normal_GTID，可以接受来自master的ANONYMOUS_GTID & Normal_GTID GTID_MODE = ON : 产生Normal_GTID，只接受来自master的Normal_GTID
3.故障案例
案列一：Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Cannot replicate anonymous transaction when @@GLOBAL.GTID_MODE = ON …’
两种情况： 1）slave的gtid_mode=on时，却还接受着来自master的non-gtid transaction的时候，会报以上错误。 2）事实上，不管slave的gtid_mode是on，还是off，只要master的gtid_mode=on,那么整个replication slave，都必须是gtid的事务 解决方案： 在master上从gtid_mode=ON_PERMISSIVE 设置到gtid_mode=ON，但是如何保证现在所有non-gtid事务都已经在slave执行完毕了？ 第一种方案： 1) 在master上，当设置gtid_mode=ON_PERMISSIVE的时候，其实就已经产生gtid事务了，这个时候show master status；记下这个位置 $pos 2）然后再每个slave上，执行 SELECT MASTER_POS_WAIT(file, position); 第二种更加直接方案： 0）默认情况下，slave的gtid_mode都是off，所以去slave上show master status 都应该是file，position 1) 先在master上，设置gtid_mode=ON_PERMISSIVE 2）然后再每台slave上再次执行show master status，如果发现结果由file，position 变成 GTID_EXECUTED，那么说明slave已经将non-gtid全部执行完毕了
案例二：Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = ON and this server has GTID_MODE = OFF.
slave的gtid_mode=off时，却还接受着来自master的gtid transaction的时候，会报以上错误。
pass
11.GTID 和 mysqlbinlog
* --exclude-gtids : # 排除这些gtid * --include-gtids : # 只打印这些gtid * --skip-gtids : # 所有gtid都不打印 # 可以用--skip-gtids 做传统模式的恢复。但是这个是官方不推荐的。 mysqlbinlog --skip-gtids binlog.000001 > /tmp/dump.sql
12.gtid与重要函数
# gtid_set 用引号扩起来
函数使用
gtid_subset(subset,set) # subset是否是set的子集，是的话返回1，不是返回0 gtid_subtract(set,subset) # 哪些gtids仅仅是set独有的，subset没有的 以上两个函数可以用来干嘛呢？ 通过GTID_SUBSET，master可以知道slave是否是自己的子集，可以很方便的检查数据一致性 通过GTID_SUBTRACT，假设slave是master的子集，那么可以很轻松的将slave没有，master有的gtid发送给slave，以便达到最终一致性 WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(gtid_set[, timeout][,channel]) # 可以用来在master done机的时候，slave进行数据补齐。 # 等到服务器应用了包含在gtid_set中的所有事务。如果指定可选的timeout值（秒数），超时会使函数停止等待而退出。 # timeout 默认为0,表示无限等待slave gtid_set全部执行完毕 # 如果全部执行完毕，会返回执行的gtid的数量。如果没有执行完，会等待timeout秒。如果slave没有起来，或者没有开启gtid，会返回NULL WAIT_FOR_EXECUTED_GTID_SET(gtid_set[, timeout]) # 含义跟WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS一样，唯一一个区别就是：如果slave 的replication 线程没有起来，不会返回NULL。 # 如果都执行了，返回0 ， 跟slave的IO,SQL thread 起没起来无关
#!/bin/env bash master_gtid_executed=`mysql -uroot -proot@123 -P5306 -h172.29.64.41 -N -e "select replace(@@global.gtid_executed,char(10),'')"` # 获取主库的gtid_executed slave_gtid_executed=`mysql -uroot -proot@123 -P5306 -h172.29.64.43 -N -e "select replace(@@global.gtid_executed,char(10),'')"` # 获取从库的gtid_executed sql="select gtid_subset('$master_gtid_executed','$slave_gtid_executed') as number" # 查询主从数据库gtid_executed差异 sql2="select gtid_subtract('$master_gtid_executed','$slave_gtid_executed') as gtid" # 获取主从gtid差异 number=`mysql -uroot -proot@123 -P5306 -h172.29.64.43 -e "$sql"` # 获取是否主从存在差异 ece=`mysql -uroot -proot@123 -P5306 -h172.29.64.43 -e "$sql2"` echo $number echo $ece
参考资料：https://blog.csdn.net/wzy0623/article/details/91982743
pass
13.gtid的缺点和限制
1.同时更新nontransactional和transactional的表，会导致gtid问题 2.CREATE TABLE … SELECT statements 语法对GTID来说是不安全的 3.CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE 对GTID也是不安全的 4.—enforce-gtid-consistency 必须设置on，可以避免以上2，3 不安全的statement 5.sql_slave_skip_counter 不允许执行，可以通过 Injecting empty transactions 来解决 6.GTID 和 mysqldump的问题，mysqldump 中 sql_log_bin 默认是关闭的.会导致导入master后，不会写入gtid到binlog. ( 可以通过 —set-gtid-purged=OFF 避免 ) 7.GTID and mysql_upgrade, 因为部分系统表是myisam引擎的，会有问题。 （可以通过—write-binlog=off来避免 ）
14.参考文档
1.5 Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 5.7 5.5.4 mysqldump — A Database Backup Program 5.6.7 mysqlbinlog — Utility for Processing Binary Log Files 13.17 Functions Used with Global Transaction IDs 14.4.2.1 CHANGE MASTER TO Syntax 14.4.2.6 START SLAVE Syntax 14.7.5.34 SHOW SLAVE STATUS Syntax 18.1.3 Replication with Global Transaction Identifiers 18.1.3.1 GTID Concepts 18.1.3.2 Setting Up Replication Using GTIDs 18.1.3.3 Using GTIDs for Failover and Scaleout 18.1.3.4 Restrictions on Replication with GTIDs 18.1.5.1 Replication Mode Concepts 18.1.5.2 Enabling GTID Transactions Online 18.1.5.3 Disabling GTID Transactions Online 18.1.6.1 Replication and Binary Logging Option and Variable Reference 18.1.6.5 Global Transaction ID Options and Variables 18.3.2 Handling an Unexpected Halt of a Replication Slave 18.4.1.34 Replication and Transaction Inconsistencies 18.4.3 Upgrading a Replication Setup 19.2.1.5 Adding Instances to the Group 24.10.7.1 The events_transactions_current Table 24.10.11.6 The replication_applier_status_by_worker Table 第三方资料 http://www.fromdual.ch/things-you-should-consider-before-using-gtid http://www.fromdual.ch/gtid_in_action http://www.fromdual.ch/replication-troubleshooting-classic-vs-gtid http://www.fromdual.ch/replication-in-a-star http://www.fromdual.com/controlling-worldwide-manufacturing-plants-with-mysql https://www.percona.com/blog/2014/05/19/errant-transactions-major-hurdle-for-gtid-based-failover-in-mysql-5-6/ https://www.percona.com/blog/2016/12/01/database-daily-ops-series-gtid-replication-binary-logs-purge/ https://www.percona.com/blog/2016/11/10/database-daily-ops-series-gtid-replication/ https://www.percona.com/blog/2015/12/02/gtid-failover-with-mysqlslavetrx-fix-errant-transactions/ https://www.percona.com/blog/2014/05/09/gtids-in-mysql-5-6-new-replication-protocol-new-ways-to-break-replication/
15.reset master,reset slave,reset slave all
reset master;
将gtid_purged参数设为空字符
将gtid_executed设为空字符
清空mysql.gtid_executed表
如果DB server开启了binlog，那么reset master还会清除所有binlog文件和binlog index file，然后以初始的自增序列号1开启一个新的binlog
reset slave是各版本Mysql都有的功能，可以让slave忘记自己在master binary log中的复制位置。 reset slave命令主要完成以下工作内容： -删除master.info和relay-log.info文件 -删除所有的relay log（包括还没有应用完的日志） -创建一个新的relay log文件 -将复制延迟选项 master_delay设置为0 reset slave不会修改gtid_executed、gtid_purged的值。 在执行reset slave之前，必须执行stop slave。 在mysql 5.6之前 reset slave不会修改任何复制配置参数，所有的连接信息仍然保留在内存中，包括主库地址、端口、用户、密码等。
也就是说在执行reset slave之后还可以直接运行start slave命令而不必重新输入change master to命令，而运行show slave status也仍和没有运行reset slave一样，有正常的输出。 reset slave之后关闭mysqld就会清除连接参数。 在mysql 5.6.3之后 可以使用reset slave all来清除连接参数，运行show slave status就输出为空了。 mysql 5.6.7之后，reset slave会隐式提交事务。 运行reset slave命令需要reload权限。MHA在做故障切换时，就会在新主上运行命令RESET SLAVE /*!50516 ALL */ ，清除掉它的所有有关主从的信息。
1pass
上帝说要有光，于是便有了光;上帝说要有女人，于是便有了女人！
