mysql：基于gtid的主从复制

---

实验

server1

1. [root@server1 mysql]# vim /etc/my.cnf
gtid_mode=ON  # 开启gtid的复制
enforce-gtid-consistency=true
2. [root@server1 mysql]# systemctl restart mysqld

server2:
1. [root@server2 mysql]# vim /etc/my.cnf
gtid_mode=ON  # 开启gtid的复制
enforce-gtid-consistency=true
2. [root@server2 mysql]# systemctl restart mysqld

重新指定主库的复制点
mysql> stop slave;
Query OK, 0 rows affected (0.14 sec)

mysql> change master to master_host='172.25.60.253',
    -> master_user='repl', master_password='Westos+001',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.27 sec)

mysql> start slave;
Query OK, 0 rows affected (0.11 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.60.253
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 

测试：
server1

[root@server1 mysql]# mysql -uroot -p
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| linux              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use linux;
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_linux |
+-----------------+
| userinfo        |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

mysql> insert into userinfo values ('user2','456');
Query OK, 1 row affected (0.15 sec)

mysql> insert into userinfo values ('user3','789');
Query OK, 1 row affected (0.17 sec)

mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| user3    | 789      |
+----------+----------+
3 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000003 |      698 |              |                  | a00365ae-6080-11ea-acd9-5254009b8bfd:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

server2

[root@server2 mysql]# mysql -uroot -p
Enter password: 
mysql> show slave status\G
*************************** 1. row ***************************
Retrieved_Gtid_Set: a00365ae-6080-11ea-acd9-5254009b8bfd:1-2
Executed_Gtid_Set: a00365ae-6080-11ea-acd9-5254009b8bfd:1-2

mysql> use linux;
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> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| user3    | 789      |
+----------+----------+
3 rows in set (0.00 sec)
mysql> use mysql;
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> select * from gtid_executed;    # 查看获取的gtid
+--------------------------------------+----------------+--------------+
| source_uuid                          | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| a00365ae-6080-11ea-acd9-5254009b8bfd |              1 |            1 |
| a00365ae-6080-11ea-acd9-5254009b8bfd |              2 |            2 |
+--------------------------------------+----------------+--------------+
2 rows in set (0.00 sec)