1. 闲不住的人首页
  2. MySQL

MySQL基于GTID主从复制实战

1. 前提条件

  1. 准备 3 台服务器
  2. 安装 MySQL 并且运行状态正常,MySQL 安装参考 三种方式安装MySQL
  3. 两台服务器配置时间同步
  4. 关闭 SELINUX
  5. 防火墙放行 3306 端口

2. 配置一主一从的 GTID 复制

2.1 环境说明

主机IPOS版本MySQL版本角色(master/slave)数据状态
10.100.4.181CentOS75.7.25master全新实例
10.100.4.182CentOS75.7.25slave全新实例

2.2 修改 master 配置文件

root@opsbj-mysql-master:~ # vim /etc/my.cnf
# mysql GTID
log-bin=/var/lib/mysql/mysql-bin # 必须项
binlog_format=row # 建议项
sync-binlog=1 # 建议项
server-id=101 # 必须项
master_info_repository=table # 建议项
relay_log_info_repository=table # 建议项
enforce_gtid_consistency=on # gtid复制需要加上的必须项
gtid_mode=on # gtid复制需要加上的必须项

修改配置文件后重启 MySQL

root@opsbj-mysql-master:~ # systemctl restart mysqld

2.3 修改 slave 配置文件

root@opsbj-mysql-slave:~ # vim /etc/my.cnf
# mysql GTID
log-bin=/var/lib/mysql/slave-bin # mysql 5.6必须项,mysql 5.7非必须项
binlog_format=row # 建议项
relay-log=/var/lib/mysql/relay-bin # 必须项
sync-binlog=1 # 建议项
server-id=182 # 必须项
master_info_repository=table # 建议项 
relay_log_info_repository=table # 建议项 
enforce_gtid_consistency=on # gtid复制需要加上的必须项
gtid_mode=on # gtid复制需要加上的必须项

修改配置文件后重启 MySQL

root@opsbj-mysql-slave:~ # systemctl restart mysqld

2.4 master 上创建复制用户

在 master 上执行

mysql> CREATE USER 'repl'@'10.100.4.%' IDENTIFIED BY 'P@ssword2019';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.100.4.%';
Query OK, 0 rows affected (0.00 sec)

2.5 slave 启用复制线程

因为全新实例 master 上的 binlog 没有删除过,所以在 slave 上直接change master to 配置连接参数。

在 slave 上执行

mysql> CHANGE MASTER TO MASTER_HOST='10.100.4.181',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected (0.03 sec)

因为是 MySQL 5.7,没有在 change master to 语句中加入 user 和 password 项,而是在 start slave 语句中使用,否则会警告。

现在启动 slave 上的两个复制线程:

mysql> START SLAVE USER='repl' PASSWORD='P@ssword2019';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.6 验证 GTID 复制是否生效

查看 io 线程和 sql 线程是否正常。

mysql> show proceesslist;
查看IO线程和SQL线程是否正常
show processlist

在 master 上新增一些测试数据:

DROP DATABASE IF EXISTS backuptest;
CREATE DATABASE backuptest;
USE backuptest;

# 创建myisam类型的数值辅助表和插入数据的存储过程
CREATE TABLE num_isam (n INT NOT NULL PRIMARY KEY) ENGINE = MYISAM ;

DROP PROCEDURE IF EXISTS proc_num1;
DELIMITER $$
CREATE PROCEDURE proc_num1 (num INT) 
BEGIN
    DECLARE rn INT DEFAULT 1 ;
    TRUNCATE TABLE backuptest.num_isam ;
    INSERT INTO backuptest.num_isam VALUES(1) ;
    dd: WHILE rn * 2 < num DO 
        BEGIN
            INSERT INTO backuptest.num_isam 
            SELECT rn + n FROM backuptest.num_isam;
            SET rn = rn * 2 ;
        END ;
    END WHILE dd;
    INSERT INTO backuptest.num_isam 
    SELECT n + rn 
    FROM backuptest.num_isam 
    WHERE n + rn <= num;
END ;
$$
DELIMITER ;

# 创建innodb类型的数值辅助表和插入数据的存储过程
CREATE TABLE num_innodb (n INT NOT NULL PRIMARY KEY) ENGINE = INNODB ;

DROP PROCEDURE IF EXISTS proc_num2;
DELIMITER $$
CREATE PROCEDURE proc_num2 (num INT) 
BEGIN
    DECLARE rn INT DEFAULT 1 ;
    TRUNCATE TABLE backuptest.num_innodb ;
    INSERT INTO backuptest.num_innodb VALUES(1) ;
    dd: WHILE rn * 2 < num DO 
        BEGIN
            INSERT INTO backuptest.num_innodb 
            SELECT rn + n FROM backuptest.num_innodb;
            SET rn = rn * 2 ;
        END ;
    END WHILE dd;
    INSERT INTO backuptest.num_innodb 
    SELECT n + rn 
    FROM backuptest.num_innodb 
    WHERE n + rn <= num ;
END ;
$$
DELIMITER ;

# 分别向两个数值辅助表中插入100W条数据
CALL proc_num1 (1000000) ;
CALL proc_num2 (1000000) ;

在 slave 上查看 slave 的状态,以下是同步结束后的状态信息:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.100.4.181
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 10058235
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 10058448
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 10058235
              Relay_Log_Space: 10058649
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 181
                  Master_UUID: 7cd0f9f0-5feb-11e9-8fbd-c866003ef9a8
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 7cd0f9f0-5feb-11e9-8fbd-c866003ef9a8:1-54
            Executed_Gtid_Set: 7cd0f9f0-5feb-11e9-8fbd-c866003ef9a8:1-54
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

至此处一主已从基础 GTID 复制就完成了。

3. 添加新的 slave 到 GTID 复制集群中

上面的实验是基于全新的实例,生产环境中往往 master 数据库已经运行了很久,并且 binlog 可能会定期删除掉一部分,所以,为了配置更通用的 gtid 复制环境,这里把刚才的 master 的 binlog 给 purge 掉一部分。模拟实际环境中定期删除了一部分 binlog 的场景。

目前 master 上的 binlog 使用情况如下,不难发现绝大多数操作都集中在mysql-bin.000001 这个 binlog 中。

root@opsbj-mysql-master:~ # ll /var/lib/mysql/*bin*
-rw-r----- 1 mysql mysql 10058235 4月  16 15:40 /var/lib/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql       32 4月  16 15:22 /var/lib/mysql/mysql-bin.index

purge 已有的 binlog:

mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> purge master logs to 'mysql/mysql-bin.000002';
Query OK, 0 rows affected (0.01 sec)

查看当前使用的 binlog

root@opsbj-mysql-master:~ # cat /var/lib/mysql/mysql-bin.index
/var/lib/mysql/mysql-bin.000002

3.1 当前环境说明

主机IPOS版本MySQL版本角色(master/slave)数据状态
10.100.4.181CentOS75.7.25master已purge过binlog
10.100.4.182CentOS75.7.25slave1已同步
10.100.4.183CentOS75.7.25slave2全新实例

3.2 修改 slave2 节点配置文件

其中 slave2 节点的配置文件和 slave1 的配置文件完全相同:

root@opsbj-mysql-slave-02:~ # vim /etc/my.cnf
# mysql GTID
log-bin=/var/lib/mysql/slave-bin # mysql 5.6必须项,mysql 5.7非必须项
binlog_format=row # 建议项
relay-log=/var/lib/mysql/relay-bin # 必须项
sync-binlog=1 # 建议项
server-id=183 # 必须项
master_info_repository=table # 建议项 
relay_log_info_repository=table # 建议项 
enforce_gtid_consistency=on # gtid复制需要加上的必须项
gtid_mode=on # gtid复制需要加上的必须项

3.3 备份 master 节点数据

我这里没有安装 xtrabackup 的 innobackupex 工具,所以使用 mysqldump。

在 master 节点执行:

root@opsbj-mysql-master:~ # mysqldump -uroot -p -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --all-databases > all.sql

将数据复制到 slave2 节点

scp all.sql root@10.100.4.183:/tmp/

3.4 将备份恢复到 slave2

在 slave 2上执行:

root@opsbj-mysql-slave-02:~ # mysql -uroot -pP@ssword2019 < /tmp/all.sql

3.5 在 master 查看已经执行过的事务

查看已经执行过的事务,这部分是不需要再 slave2 上执行了,因为已经都恢复数据过去了。

在 master 上直接查看 gtid_executed 的值,注意不是 gtid_purged 的值,master 上的 gtid_purged 表示的是曾经删除掉的 binlog。

mysql> show global variables like '%gtid%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                        |
| enforce_gtid_consistency         | ON                                        |
| gtid_executed                    | 7cd0f9f0-5feb-11e9-8fbd-c866003ef9a8:1-54 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      | 7cd0f9f0-5feb-11e9-8fbd-c866003ef9a8:1-54 |
| session_track_gtids              | OFF                                       |
+----------------------------------+-------------------------------------------+
8 rows in set (0.01 sec)

3.6 启用 slave2 上的复制线程

可以在启动 slave2 线程之前使用 gtid_purged 变量来指定需要跳过的 gtid 集合。但因为要设置 gtid_purged 必须保证全局变量 gtid_executed为空,所以先在 slave2 上执行 reset master(注意,不是 reset slave),再设置 gtid_purged。

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> set @@global.gtid_purged='7cd0f9f0-5feb-11e9-8fbd-c866003ef9a8:1-54';
Query OK, 0 rows affected (0.00 sec)

设置好 gtid_purged 之后,就可以开启复制线程了。

mysql> CHANGE MASTER TO MASTER_HOST='10.100.4.181',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected (0.03 sec)

mysql> START SLAVE USER='repl' PASSWORD='P@ssword2019';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看 slave2 的状态,看是否正确启动了复制功能。如果没错,再在 master 上修改一部分数据,检查是否同步到 slave1和 slave2。

mysql> show slave status\G

在 master 上创建一个测试数据库 mydb 检查 slave1 和 slave2 节点能否同步。

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

slave1 节点查看

slave1节点验证数据同步
slave1节点验证数据同步

slave2 节点查看

slave2节点验证数据同步
slave2节点验证数据同步

3.7 回到 master,purge 掉已同步的 binlog

当 slave 指定 gtid_purged 并实现了同步之后,为了下次重启 mysqld 实例不用再次设置 gtid_purged(甚至可能会在启动的时候自动开启复制线程),所以应该去 master 上将已经同步的 binlog 给 purged 掉。

在 master 上执行

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> purge master logs to 'mysql-bin.000003';
Query OK, 0 rows affected (0.00 sec)

4. GTID 复制相关的状态信息和变量

4.1 show slave status 中和 gtid 复制相关的状态行

Retrieved_Gtid_Set: 7cd0f9f0-5feb-11e9-8fbd-c866003ef9a8:55
Executed_Gtid_Set: 7cd0f9f0-5feb-11e9-8fbd-c866003ef9a8:1-55
Auto_Position: 1
  • Retrieved_Gtid_Set:在开启了 gtid 复制(即 gtid_mode=on)时,slave 在启动 io 线程的时候会检查自己的 relay log,并从中检索出gtid 集合。也就是说,这代表的是 slave 已经从 master 中复制了哪些事务过来。检索出来的 gtid 不会再请求 master 发送过来。
  • Executed_Gtid_Set:在开启了 gtid 复制(即 gtid_mode=on)时,它表示已经向自己的 binlog 中写入了哪些 gtid 集合。注意,这个值是根据一些状态信息计算出来的,并非 binlog 中能看到的那些。举个特殊一点的例子,可能 slave 的 binlog 还是空的,但这里已经显示一些已执行 gtid 集合了。
  • Auto_Position:开启 gtid 时是否自动获取 binlog 坐标。1 表示开启,这是 gtid 复制的默认值。

4.2 一些重要的变量

  • gtid_mode:是否开启gtid复制模式。只允许on/off类的布尔值,不允许其他类型(如1/0)的布尔值,实际上这个变量是枚举类型的。要设置 gtid_mode=on ,必须同时设置 enforce_gtid_consistency 开。在MySQL 5.6中,还必须开启 log_slave_updates ,即使是master也要开启。
  • enforce_gtid_consistency:强制要求只允许复制事务安全的事务。 gtid_mode=on 时必须显式设置该项,如果不给定值,则默认为 on。应该尽量将该选项放在 gtid_mode 的前面,减少启动 mysqld 时的检查。
    • 不能在事务内部创建和删除临时表。只能在事务外部进行,且 autocommit 需要设置为 1。
    • 不能执行 create table … select 语句。该语句除了创建一张新表并填充一些数据,其他什么事也没干。
    • 不能在事务内既更新事务表又更新非事务表。
  • gtid_executed:已经执行过的 GTID。 reset master 会清空该项的全局变量值。
  • gtid_purged:已经 purge 掉的 gtid。要设置该项,必须先保证gtid_executed 已经为空,这意味着也一定会同时设置该项为空。在 slave 上设置该项时,表示稍后启动 io 线程和 SQL 线程都跳过这些 gtid,slave 上设置时应该让此项的 gtid 集合等于 master上 gtid_executed 的值。
  • gtid_next:表示下一个要执行的gtid事务。

需要注意,master和slave上都有gtid_executed和gtid_purged,它们代表的意义有时候是不同的。

参考资料

骏马金龙

GTID复制官方文档

如果觉得文章不错,不妨给个『打赏』写作不易,各位的支持,能激发和鼓励我更大的写作热情。谢谢

原创文章,作者:恩志,如若转载,请注明出处:https://www.xbzdr.com/484.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注

联系我们

在线咨询:点击这里给我发消息

邮件:510749025@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code