mysql主从复制 不停主库_MySQL主从复制不停库在线开启/关闭GTID

在MySQL 5.7.6及以后的版本，可以在主从复制环境中不停库在线开启GTID，更早的版本只能通过设置gtid_mode等参数后重启的方式

当gtid_mode=on时，从库不能复制非GTID的事务(anonymous transactions)

当gtid_mode=off时，只能复制anonymous transactions的事务

而修改gtid_mode参数在主从上总有先后顺序，因此不能直接修改该参数为on/off来开启或者关闭gtid

因此为了解决在线开启/关闭GTID的问题，mysql 5.7.6以后的版本开始，gtid_mode参数值除了ON/OFF，增加了两个新的参数值OFF_PERMISSIVE/ON_PERMISSIVE。

当gtid_mode=OFF_PERMISSIVE时，新生成的事务是anonymous transactions，从库replicate允许anonymous transactions以及GTID transactions

当gtid_mode=ON_PERMISSIVE时，新生成的事务是GTID，但是从库replicate允许anonymous transactions以及GTID transactions

所以，5.7.6版本开始，可以在不停库的情况下通过修改gtid_mode参数值在主从环境中开启GTID(同样也可以在线关闭GTID)

在线开启GTID的具体步骤如下：

On each server, execute:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

设置后需要在实例上监控error log有没有收到错误。

On each server, execute:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

On each server, execute:

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

On each server, execute:

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

On each server, wait until the status variable ONGOING_ANONYMOUS_TRANSACTION_COUNT is zero. This can be checked using:

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

Wait for all transactions generated up to step 5 to replicate to all servers. You can do this without

stopping updates: the only important thing is that all anonymous transactions get replicated.

可以通过以下方式检查步骤5中的binlog在从库上全部应用完：

--在master检查当前位点

SHOW MASTER STATUS;

--从库上查看，如果为0，表示已经全部复制

SELECT MASTER_POS_WAIT('binlog.000030', 194);

If you use binary logs for anything other than replication, for example point in time backup and

restore, wait until you do not need the old binary logs having transactions without GTIDs.

On each server, execute:

SET @@GLOBAL.GTID_MODE = ON;

On each server, add gtid_mode=ON and enforce_gtid_consistency=ON to my.cnf.

从库上执行以下命令开启GTID based replication:

STOP SLAVE [FOR CHANNEL 'channel'];

CHANGE MASTER TO MASTER_AUTO_POSITION = 1 [FOR CHANNEL 'channel'];

START SLAVE [FOR CHANNEL 'channel'];

在线关停GTID的具体步骤如下：

1.Execute the following on each replica, and if you using multi-source replication, do it for each channel and include the FOR CHANNEL channel clause:

STOP SLAVE ;

SHOW SLAVE STATUS;找到同步到的binlog位点

CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file, MASTER_LOG_POS = position [FOR CHANNEL

'channel'];

START SLAVE [FOR CHANNEL 'channel'];

2. On each server, execute:

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

3. On each server, execute:

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

4. On each server, wait until the variable @@GLOBAL.GTID_OWNED is equal to the empty string.

This can be checked using:

SELECT @@GLOBAL.GTID_OWNED;

5. Wait for all transactions that currently exist in any binary log to replicate to all replicas.

--在master检查当前位点

SHOW MASTER STATUS;

--从库上查看，如果为0，表示已经全部复制

SELECT MASTER_POS_WAIT('binlog.000030', 194);

6. If you use binary logs for anything else than replication, for example to do point in time backup or

restore: wait until you do not need the old binary logs having GTID transactions.

7. On each server, execute:

SET @@GLOBAL.GTID_MODE = OFF;

8. On each server, set gtid_mode=OFF in my.cnf.