mysql主从

王酒懵子

已于 2022-07-04 22:30:43 修改

阅读量128

点赞数

文章标签：数据库 mysql sql

于 2022-07-03 20:13:31 首次发布

本文链接：https://blog.csdn.net/whl0102222/article/details/125588719

版权

mysql主从

文章目录

mysql主从
测试验证
- 4. GTID主从

1. 主从简介

在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。

想几个问题：

用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？
业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？

1.1 主从作用

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

1.2 主从形式

一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的
多主一从—5.7开始支持
联级复制

2. 主从复制原理

主从复制步骤：

主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
从库生成两个线程，一个I/O线程，一个SQL线程
- I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中
- SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

3. 主从复制配置

主从复制配置步骤：

确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件）

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.200.5	centos8/redhat8mysql-5.7	有数据
从数据库	192.168.200.6	centos8/redhat8mysql-5.7	无数据

3.1 mysql安装

分别在主从两台服务器上安装mysql-5.7版本，此处略过安装步骤，若有疑问请参考《mysql基础》与《mysql进阶》两篇文章。

3.2mysql主从配置

确保从数据库与主数据库里的数据一样

//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出

[root@node1 ~]# mysqldump -uroot -p'test123' --all-databases > all-$(date +%Y%m%d%H%M%S).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@node1 ~]# ls
all-20220703193218.sql  anaconda-ks.cfg

//将数据库从主库传到从库
[root@node1 ~]# scp all-20220703193218.sql 192.168.200.6:/root/
The authenticity of host '192.168.200.6 (192.168.200.6)' can't be established.
ECDSA key fingerprint is SHA256:mOMwnT9zILPNfJzswjsbIU0EPLhik6GUW5U09LevHTk.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.200.6' (ECDSA) to the list of known hosts.
root@192.168.200.6's password: 
all-20220703193218.sql                                         100%  858KB  38.3MB/s   00:00
//从库主机
[root@node2 ~]# ls
all-20220703193218.sql  anaconda-ks.cfg

//将数据库恢复
[root@node2 ~]# mysql -uroot -p'test123' < all-20220703193218.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@node2 ~]# mysql -uroot -p'test123' -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+

在主数据库里创建一个同步账号授权给从数据库使用

mysql> create user 'repl'@'192.168.200.6' identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)
//先要将读锁退出
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.200.6';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

3.3配置主数据库

配置主数据库my.cnf文件

[root@node1 ~]# vi /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 5  //此处需要小于从库
log-bin = mysql_bin

[root@node1 ~]# systemctl restart mysqld.service

mysql> show master status; //查看主库的状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.4配置从数据库

配置从数据库my.cnf文件

[root@node2 ~]# vi /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 10
relay-log = mysql-relay-bin  //此处为中继日志

3.5配置并启动主从复制

[root@node2 ~]# systemctl restart mysqld.service
[root@node2 ~]# mysql -uroot -p'test123'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> CHANGE MASTER TO
    -> MASTER_HOST= '192.168.200.5', //主库的ip
    -> MASTER_USER='repl',  //配置授权的用户和密码
    -> MASTER_PASSWORD='repl123',   
    -> MASTER_LOG_FILE='mysql_bin.000004',   //主库show master status查看的内容
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;  //启动主从
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000004
             Slave_IO_Running: Yes  //此处需要为yes才能生效(若此处为Connecting 则需要关闭防火墙)
            Slave_SQL_Running: Yes   //此处需要为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: 154
              Relay_Log_Space: 527
              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: 5
                  Master_UUID: 552726ad-f6ff-11ec-9b1d-000c29a35b7b
             Master_Info_File: /opt/data/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: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

测试验证

在主库创建一个test数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

从库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

主库删除数据，修改数据

mysql> select * from tb_students_info
    -> ;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  2 | Green  |   23 | M    |    158 |         2 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   23 | F    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   50 | F    |    164 |         5 |
| 11 | LiMing |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)
mysql> delete from tb_students_info where id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_students_info
    -> ;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   23 | F    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   50 | F    |    164 |         5 |
| 11 | LiMing |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   23 | F    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   50 | F    |    164 |         5 |
| 11 | LiMing |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
mysql> update tb_students_info set age = 30 where id = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   23 | F    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   30 | F    |    164 |         5 |
| 11 | LiMing |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

从库

mysql> use school;
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_school |
+------------------+
| info             |
| tb_course        |
| tb_students_info |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | M    |    160 |         1 |
|  3 | Henry  |   23 | F    |    185 |         1 |
|  4 | Jane   |   23 | F    |    162 |         3 |
|  5 | Jim    |   24 | F    |    175 |         2 |
|  6 | John   |   21 | F    |    172 |         4 |
|  7 | Lily   |   22 | M    |    165 |         4 |
|  8 | Susan  |   23 | M    |    170 |         5 |
|  9 | Thomas |   22 | F    |    178 |         5 |
| 10 | Tom    |   30 | F    |    164 |         5 |
| 11 | LiMing |   22 | M    |    180 |         7 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.01 sec)

4. GTID主从

4.1 GTID概念介绍

GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。mysql主从结构在一主一从情况下对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。使用GTID需要注意: 在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。

GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf)，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一。

了解了GTID的格式，通过UUID可以知道这个事务在哪个实例上提交的。通过GTID可以极方便的进行复制结构上的故障转移，新主设置，这就很好地解决了下面这个图所展现出来的问题。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bMUst1sO-1656943973779)(http://www.itwangqing.net.cn/media/15340760215942/16442192283940.jpg)]

如图, Server1(Master)崩溃，根据从上show slave status获得Master_log_File/Read_Master_Log_Pos的值，Server2(Slave)已经跟上了主，Server3(Slave)没有跟上主。这时要是把Server2提升为主，Server3变成Server2的从。这时在Server3上执行change的时候需要做一些计算。

这个问题在5.6的GTID出现后，就显得非常的简单。由于同一事务的GTID在所有节点上的值一致，那么根据Server3当前停止点的GTID就能定位到Server2上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现，我们都不需要知道GTID的具体值，直接使用CHANGE MASTER TO MASTER_HOST=‘xxx’, MASTER_AUTO_POSITION命令就可以直接完成failover的工作。

====== GTID和Binlog的关系 ======

GTID在binlog中的结构

GTID event 结构

Previous_gtid_log_event
Previous_gtid_log_event 在每个binlog 头部都会有每次binlog rotate的时候存储在binlog头部Previous-GTIDs在binlog中只会存储在这台机器上执行过的所有binlog，不包括手动设置gtid_purged值。换句话说，如果你手动set global gtid_purged=xx；那么xx是不会记录在Previous_gtid_log_event中的。
GTID和Binlog之间的关系是怎么对应的呢? 如何才能找到GTID=? 对应的binlog文件呢？
假设有4个binlog: bin.001,bin.002,bin.003,bin.004
bin.001 : Previous-GTIDs=empty; binlog_event有: 1-40
bin.002 : Previous-GTIDs=1-40; binlog_event有: 41-80
bin.003 : Previous-GTIDs=1-80; binlog_event有: 81-120
bin.004 : Previous-GTIDs=1-120; binlog_event有: 121-160
假设现在我们要找GTID=$A，那么MySQL的扫描顺序为:
从最后一个binlog开始扫描（即: bin.004）
bin.004的Previous-GTIDs=1-120，如果$A=140 > Previous-GTIDs,那么肯定在bin.004中
bin.004的Previous-GTIDs=1-120，如果$A=88 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.003,然后再循环前面2个步骤，直到找到为止.

====== GTID 重要参数的持久化 =======

GTID相关参数

参数	comment
gtid_executed	执行过的所有GTID
gtid_purged	丢弃掉的GTID
gtid_mode	GTID模式
gtid_next	session级别的变量，下一个gtid
gtid_owned	正在运行的GTID
enforce_gtid_consistency	保证GTID安全的参数

====== 开启GTID的必备条件 ======

gtid_mode=on    (必选)
enforce-gtid-consistency=1  （必选）
log_bin=mysql-bin           （可选）    #高可用切换，最好开启该功能
log-slave-updates=1     （可选）       #高可用切换，最好打开该功能

4.2 GTID工作原理

从服务器连接到主服务器之后，把自己执行过的GTID (Executed_Gtid_Set: 即已经执行的事务编码) 、获取到的GTID (Retrieved_Gtid_Set: 即从库已经接收到主库的事务编号) 发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主，先change到同步最成功的那台从服务器，等把GTID全部补全了，就可以把它提升为主了。

GTID是MySQL 5.6的新特性，可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识一个事务。当事务提交时，MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。也就是说，无论是级联情况，还是一主多从情况，都可以通过GTID自动找点儿，而无需像之前那样通过File_name和File_position找点儿了。

简而言之，GTID的工作流程为：

master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
如果有记录，说明该GTID的事务已经执行，slave会忽略。
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

4.3 GTID主从配置

环境说明：

数据库角色	IP	应用与系统版本
主数据库	192.168.200.5	centos8/redhat8mysql-5.7
从数据库	192.168.200.6	centos8/redhat8mysql-5.7

//主库从库数据同步
[root@node1 ~]# mysqldump -uroot -p'test123' --all-databases > all-$(date '+%Y%m%d%H%M%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@node1 ~]# ls
all-20220704214333.sql  anaconda-ks.cfg
[root@node1 ~]# scp all-20220704214333.sql root@192.168.200.6:/root/
The authenticity of host '192.168.200.6 (192.168.200.6)' can't be established.
ECDSA key fingerprint is SHA256:mOMwnT9zILPNfJzswjsbIU0EPLhik6GUW5U09LevHTk.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.200.6' (ECDSA) to the list of known hosts.
root@192.168.200.6's password: 
all-20220704214333.sql                                           100%  858KB  38.1MB/s   00:00 
[root@node2 ~]# ls
all-20220704214333.sql  anaconda-ks.cfg
[root@node2 ~]# mysql -uroot -p'test123' < all-20220704214333.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

主库操作

//将账号repl授权给从库
mysql> create user 'repl'@'192.168.200.6' identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)

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

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

配置主数据库my.cnf文件

[root@node1 ~]# vi /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 10  //主库的server_id必须小于从库
log-bin = mysql_bin //开启gtid
gtid-mode = on      //强制gtid一致性
enforce-gtid-consistency = on    //开启二进制
binlog-format = row  //默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates = 1   //从库binlog才会记录主库同步的操作日志
skip-slave-start = 1     //跳过slave复制线程

[root@node1 ~]# systemctl restart mysqld //重启MySQL服务
mysql> show variables like '%gtid%';  //检查gtid模式状态
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

配置从库文件：

[root@node2 ~]# vi /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 20  //从库的server_id必须大于主库
log-bin = mysql_bin
gtid-mode = on
enforce-gtid-consistency = on
binlog-format = row
log-slave-updates = 1
skip-slave-start = 1
[root@node2 ~]# systemctl restart mysqld
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

//配置并启动主从复制
mysql> change master to
    -> master_host='192.168.200.5',
    -> master_user='repl',
    -> master_password='repl123',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: node2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000005
             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: 154
              Relay_Log_Space: 574
              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: 10
                  Master_UUID: 552726ad-f6ff-11ec-9b1d-000c29a35b7b
             Master_Info_File: /opt/data/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: 
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

测试：

//主库数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
//从库数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//主服务器创建数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> create table student (id int not null primary key auto_increment,name varchar(50) not null,age tinyint);
Query OK, 0 rows affected (0.01 sec)

mysql> insert student (name,age) values('tom',20),('jerry',20),('wnagermazi',15),('zhangsan',19),('lisi',25);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000005 |      877 |              |                  | 552726ad-f6ff-11ec-9b1d-000c29a35b7b:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

//查看从库中的数据是否同步成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use test;
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 student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
5 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |      868 |              |                  | 552726ad-f6ff-11ec-9b1d-000c29a35b7b:1-3 |
+------------------+----------+--------------+------------------+----------------------------
--------------+
1 row in set (0.00 sec)

停止：

//从库停止服务
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

//主库
mysql> insert student (name,age) values('qq',20),('ww',20),('ee',15),('rr',19),('lisi',25); 
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
|  6 | qq         |   20 |
|  7 | ww         |   20 |
|  8 | ee         |   15 |
|  9 | rr         |   19 |
| 10 | lisi       |   25 |
+----+------------+------+
10 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000005 |     1181 |              |                  | 552726ad-f6ff-11ec-9b1d-000c29a35b7b:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

//从库开启服务
mysql>  start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
|  6 | qq         |   20 |
|  7 | ww         |   20 |
|  8 | ee         |   15 |
|  9 | rr         |   19 |
| 10 | lisi       |   25 |
+----+------------+------+
10 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |     1163 |              |                  | 552726ad-f6ff-11ec-9b1d-000c29a35b7b:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)