mysql5.7gtid_MySQL5.7 GTID 运维实战

GTID 和 START SLAVE

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

| MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos

| RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

| SQL_AFTER_MTS_GAPS }

* 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

START SLAVE UNTIL SQL_AFTER_MTS_GAPS;

SET @@GLOBAL.slave_parallel_workers = 0;

START SLAVE SQL_THREAD;

GTID 和 upgrade

如果 --gtid-mode=ON ，那么在使用upgrade时候，不推荐使用--write-binlog 选项。

因为，mysql_upgrade 会更新Myisam引擎的系统表. 而同时更新transction table 和 non-trasaction table 是gtid所不允许的

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或者mysql shutdown的时候才会写入mysql.gtid_executed

如果master 异常shutdown，gtid还没有写入到mysql.gtid_executed怎么办呢？

这种场景，一般通过mysql recover机制写入到mysql.gtid_executed中

GTID 和 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.

QA: 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;