mysql主从之基于gtid的主从复制

mysql主从之基于gtid的主从复制

一、环境描述
系统：Centos7.8.2003
主Mysql服务器：server1 192.168.6.101
从Mysql服务器：server2 192.168.6.102
Mysql版本：5.7.31
防火墙、selinux：防火墙关闭或放行数据库端口，selinux设置为disabled

备注：MySQL5.7版本Slave可以不开启binlog了，可以节省这部分的磁盘I/O消耗，而MySQL5.6版本必须开启binlog，因为GTID信息需要在binlog中存储（log_slave_updates），只有开启binlog才能使用GTID的功能。MySQL5.7版本通过GTID系统表来记录GITD信息（mysql.gtid_executed），每个事务提交时，将GTID信息插入到表中
二、安装Mysql
参考上一篇文章：Centos 7 二进制安装配置 Mysql数据库
安装完成登录
server1

[root@server1 src]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

server2

[root@server2 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.31-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

三、配置主从

①、主从数据库都需要修改/etc/mysql.cnf
主Mysql服务器：server1 192.168.6.101
从Mysql服务器：server2 192.168.6.102
修改mysql.cnf配置 添加如下两行

gtid_mode=on
enforce_gtid_consistency=on

修改后重启mysql服务
②、主库创建用于同步的用户账号

mysql> CREATE USER 'testrepl'@'192.168.6.102' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

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

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

③、从库配置复制信息并启动slave

mysql> change master to master_host='192.168.6.101',master_port=3301,master_user='testrepl',master_password='123456',master_auto_position = 1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;

④、主库创建数据库

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000002 |      322 |              |                  | 267b27d0-48b5-11eb-92ec-000c290f457f:1 |
+---------------+----------+--------------+------------------+----------------------------------------+

查看从库状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.101
                  Master_User: testrepl
                  Master_Port: 3301
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 322
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: binlog.000002
             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: 322
              Relay_Log_Space: 738
              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: 11
                  Master_UUID: 267b27d0-48b5-11eb-92ec-000c290f457f
             Master_Info_File: /data/mysql/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: 267b27d0-48b5-11eb-92ec-000c290f457f:1
            Executed_Gtid_Set: 267b27d0-48b5-11eb-92ec-000c290f457f:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

注：如果数据库已有正在使用的库，需要先备份主库恢复到从库，本文演示的是全新安装的Mysql。
四、验证主从复制
背景描述：
主库创建了testabc数据库，并创建了一个tables表，表中就两个字段id,name
①、主库操作

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testabc            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use testabc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_testabc |
+-------------------+
| tables            |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from tables;
Empty set (0.00 sec)

mysql> insert t
tables       tables.id    tables.name  tee          testabc     
mysql> insert tables values(1,abc);
ERROR 1054 (42S22): Unknown column 'abc' in 'field list'
mysql> insert tables values(1,'abc');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tables;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |      866 |              |                  | 267b27d0-48b5-11eb-92ec-000c290f457f:1-3 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

②、从库验证

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testabc            |
+--------------------+
5 rows in set (0.00 sec)

mysql> use testabc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_testabc |
+-------------------+
| tables            |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from tables;
Empty set (0.00 sec)

mysql> select * from tables;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
+----+------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.101
                  Master_User: testrepl
                  Master_Port: 3301
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 866
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 1073
        Relay_Master_Log_File: binlog.000002
             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: 866
              Relay_Log_Space: 1282
              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: 11
                  Master_UUID: 267b27d0-48b5-11eb-92ec-000c290f457f
             Master_Info_File: /data/mysql/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: 267b27d0-48b5-11eb-92ec-000c290f457f:1-3
            Executed_Gtid_Set: 267b27d0-48b5-11eb-92ec-000c290f457f:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)