mysql基于gtid slave 创建

前言

如果您希望GTID支持，则需要在从属服务器中启用log_slave_updates，并且应该考虑性能影响

详细操作步骤如下

1、授权

在master上添加复制账号

grant replication client,replication slave on *.* to 'apoc2real'@'10.140.0.%' identified by 'apoc2real'

2、导出mysql：

    master > show global variables like 'gtid_executed'; 
    +---------------+-------------------------------------------+
    | Variable_name | Value                                     |
    +---------------+-------------------------------------------+
    | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 |
    +---------------+-------------------------------------------+

    master > show global variables like 'gtid_purged';
    +---------------+------------------------------------------+
    | Variable_name | Value                                    |
    +---------------+------------------------------------------+
    | gtid_purged   | 9a511b7b-7059-11e2-9a24-08002762b8af:1-2 |
    +---------------+------------------------------------------+
    # 导出数据库
    # mysqldump --all-databases --single-transaction --triggers --routines --host=127.0.0.1 --port=18675 --user=msandbox --password=msandbox > dump.sql
    /usr/local/webserver/mysql57/bin/mysqldump -uroot -pSQWAYYMPYUTYII9ETOP4NPCAJJJIJCBC -S /data/mysql/apoc2/data/mysql.sock --single-transaction --default-character-set=utf8 -R -E --triggers apoc > /home/zaki/apoc2slave.sql

    grep PURGED dump.sql
    SET @@GLOBAL.GTID_PURGED='9a511b7b-7059-11e2-9a24-08002762b8af:1-13';

3、数据导出

    slave1 > show global variables like 'gtid_executed';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_executed |       |
    +---------------+-------+

    slave1 > show global variables like 'gtid_purged';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_purged   |       |
    +---------------+-------+

    slave1 > source dump.sql;
    [...]

    slave1 > show global variables like 'gtid_executed';
    +---------------+-------------------------------------------+
    | Variable_name | Value                                     |
    +---------------+-------------------------------------------+
    | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 |
    +---------------+-------------------------------------------+

    slave1 > show global variables like 'gtid_purged';
    +---------------+-------------------------------------------+
    | Variable_name | Value                                     |
    +---------------+-------------------------------------------+
    | gtid_purged   | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 |
    +---------------+-------------------------------------------+

4、配置slave

  # change master 设置
  slave1 > CHANGE MASTER TO MASTER_HOST="127.0.0.1", MASTER_USER="msandbox", MASTER_PASSWORD="msandbox", MASTER_PORT=18675, MASTER_AUTO_POSITION = 1;

如何以坏而快速的方式还原slave?

假设我们的从属服务器已经关闭了好几天，而主服务器的二进制日志已被清除。这是我们将要得到的错误：

    Slave_IO_Running: No
    Slave_SQL_Running: Yes
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

因此，让我们尝试解决它。首先，我们有坏的和快速的方式，也就是点到另一个GTID，主在二进制日志。首先，我们从主服务器获取GTID_EXECUTED：

master > show global variables like 'GTID_EXECUTED';
+---------------+-------------------------------------------+
| Variable_name | Value                                     |
+---------------+-------------------------------------------+
| gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-14 |
+---------------+-------------------------------------------+

然后将其设置在从属服务器上：

    slave> set global GTID_EXECUTED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14"
    ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable

错误！记住，我们从主站获取GTID_EXECUTED，在从站上将其设为GTID_PURGED。

    slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14";
    ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.

再次出错，在手动更改GTID_PURGED之前，GTID_EXECUTED应该为空，但是我们不能使用SET进行更改，因为它是只读变量。改变它的唯一方法是重置主（是的，在从服务器上）

    slave1> reset master;
    slave1 > show global variables like 'GTID_EXECUTED';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | gtid_executed |       |
    +---------------+-------+
    slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14";
    slave1> start slave io_thread;
    slave1> show slave status\G
    [...]
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    [...]

如何以良好而缓慢的方式还原slave，
好的方法是再次使用mysqldump。我们像以前看到的那样从主服务器上进行转储，然后尝试将其还原到从服务器上：

    slave1 [localhost] {msandbox} ((none)) > source dump.sql;
    [...]
    ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.
    [...]

哇！值得一提的是，由于转储的还原将继续，因此这些错误消息可能会在shell缓冲区中消失。要小心。

同样的问题，所以同样的解决方案：

    slave1> reset master;
    slave1> source dump.sql;
    slave1> start slave;
    slave1> show slave statusG
    [...]
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    [...]

mysql 官方文档连接

https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/