mysql主从

目录

• 1. 主从简介
  • 1.1 主从作用
  • 1.2 主从形式
• 2. 主从复制原理
• 3. 主从复制配置
  • 3.1 mysql安装
    • 3.2.2 在主数据库里创建一个同步账号授权给从数据库使用
    • 3.2.3 配置主数据库
    • 3.2.4 配置从数据库
    • 3.2.5 测试验证
• 4. GTID主从
  • 4.1 GTID概念介绍
  • 4.2 GTID工作原理
  • 4.3 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. 主从复制配置

主从复制配置步骤：

• 1. 确保从数据库与主数据库里的数据一样
• 2. 在主数据库里创建一个同步账号授权给从数据库使用
• 3. 配置主数据库（修改配置文件）
• 4. 配置从数据库（修改配置文件）
     需求：
     搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	172.16.12.128	centos8/redhat8
mysql-5.7	有数据
从数据库	172.16.12.129	centos8/redhat8mysql-5.7	无数据

3.1 mysql安装

分别在主从两台服务器上安装mysql-5.7版本

使用二进制分别给两台服务器安装mysql
```bash
[root@mr ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@mr src]# useradd -M -r -s /sbin/nologin mysql 
[root@mr src]# id mysql uid=993(mysql) gid=990(mysql) groups=990(mysql) 
[root@mr src]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ 
[root@mr src]# cd /usr/local/ 
[root@mr local]# ll 
total 0 
drwxr-xr-x. 2 root root 18 Jul 7 10:28 bin 
drwxr-xr-x. 2 root root 6 May 19 2020 etc 
drwxr-xr-x. 2 root root 6 May 19 2020 games 
drwxr-xr-x. 2 root root 6 May 19 2020 include 
drwxr-xr-x. 2 root root 6 May 19 2020 lib 
drwxr-xr-x. 3 root root 17 Jul 1 09:26 lib64 
drwxr-xr-x. 2 root root 6 May 19 2020 libexec 
drwxr-xr-x. 9 root root 129 Jul 27 16:03 mysql-5.7.38-linux-glibc2.12-x86_64 
drwxr-xr-x. 2 root root 6 May 19 2020 sbin 
drwxr-xr-x. 6 root root 64 Jul 7 10:28 share 
drwxr-xr-x. 2 root root 6 May 19 2020 src 
[root@mr local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64 mysql 
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64' 
[root@mr local]# ll 
total 0 
drwxr-xr-x. 2 root root 18 Jul 7 10:28 bin 
drwxr-xr-x. 2 root root 6 May 19 2020 etc
drwxr-xr-x. 2 root root 6 May 19 2020 games
drwxr-xr-x. 2 root root 6 May 19 2020 include 
drwxr-xr-x. 2 root root 6 May 19 2020 lib 
drwxr-xr-x. 3 root root 17 Jul 1 09:26 lib64 
drwxr-xr-x. 2 root root 6 May 19 2020 libexec 
lrwxrwxrwx. 1 root root 35 Jul 27 16:04 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64 
drwxr-xr-x. 9 root root 129 Jul 27 16:03 mysql-5.7.38-linux-glibc2.12-x86_64 
drwxr-xr-x. 2 root root 6 May 19 2020 sbin 
drwxr-xr-x. 6 root root 64 Jul 7 10:28 share 
drwxr-xr-x. 2 root root 6 May 19 2020 src 
[root@mr local]# 
[root@mr local]# chown -R mysql.mysql mysql* 
[root@mr local]# ll 
total 0 
drwxr-xr-x. 2 root root 18 Jul 7 10:28 bin 
drwxr-xr-x. 2 root root 6 May 19 2020 etc 
drwxr-xr-x. 2 root root 6 May 19 2020 games 
drwxr-xr-x. 2 root root 6 May 19 2020 include
drwxr-xr-x. 2 root root 6 May 19 2020 lib 
drwxr-xr-x. 3 root root 17 Jul 1 09:26 lib64 
drwxr-xr-x. 2 root root 6 May 19 2020 libexec 
lrwxrwxrwx. 1 mysql mysql 35 Jul 27 16:04 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Jul 27 16:03 mysql-5.7.38-linux-glibc2.12-x86_64 
drwxr-xr-x. 2 root root 6 May 19 2020 sbin 
drwxr-xr-x. 6 root root 64 Jul 7 10:28 share drwxr-xr-x. 2 root root 6 May 19 2020 src 
[root@mr local]#

[root@mr ~]# cd /usr/local/mysql 
[root@mr mysql]# ls 
bin docs include lib LICENSE man README share support-files 
[root@mr mysql]# cd bin/ 
[root@mr bin]# pwd
/usr/local/mysql/bin 
[root@mr bin]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > 
/etc/profile.d/mysql.sh 
[root@mr bin]# source /etc/profile.d/mysql.sh 
[root@mr bin]# echo $PATH 
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/usr/local/mysql/bin 
[root@mr bin]# which mysql 
/usr/local/mysql/bin/mysql 
[root@mr bin]# 
[root@mr mysql]# ln -sv /usr/local/mysql/include/ /usr/include/mysql 
'/usr/include/mysql' -> '/usr/local/mysql/include/' 
[root@mr mysql]# vim /etc/ld.so.conf.d/mysql.conf 
/usr/local/mysql/lib/ 
[root@mr mysql]# ldconfig 
[root@mr mysql]# vim /etc/man_db.conf 
MANDATORY_MANPATH /usr/man MANDATORY_MANPATH /usr/share/man MANDATORY_MANPATH /usr/local/share/man MANDATORY_MANPATH /usr/local/mysql/man

[root@mr ~]# mkdir /opt/data
[root@mr ~]# ll /opt/data/
total 0
[root@mr ~]# chown -R mysql.mysql /opt/data/
[root@mr ~]# mysqld --initialize --user mysql --datadir /opt/data/
2022-07-27T08:21:49.390169Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-27T08:21:49.520539Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-27T08:21:49.548430Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-27T08:21:49.557890Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 29732c94-0d85-11ed-925a-000c29e8e4aa.
2022-07-27T08:21:49.558494Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-27T08:21:50.271098Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-27T08:21:50.271141Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-27T08:21:50.271798Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-27T08:21:50.425296Z 1 [Note] A temporary password is generated for root@localhost: -ff3uq#Fee%k
[root@mr ~]# 
[root@mr ~]# echo '-ff3uq#Fee%k' > pass
[root@mr ~]# cat pass
-ff3uq#Fee%k

生成配置文件
[root@mr ~]# vim /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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

配置服务启动脚本,启动mysql
[root@mr mysql]# cd support-files/
[root@mr support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@mr support-files]# file mysql.server 
mysql.server: POSIX shell script, ASCII text executable
[root@mr support-files]# 
[root@mr ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/data/mr.err'.
 SUCCESS! 
[root@mr ~]# ss -antl
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port   Process   
LISTEN   0        128              0.0.0.0:111            0.0.0.0:*                
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*                
LISTEN   0        25               0.0.0.0:514            0.0.0.0:*                
LISTEN   0        80                     *:3306                 *:*                
LISTEN   0        128                 [::]:111               [::]:*                
LISTEN   0        128                 [::]:22                [::]:*                
LISTEN   0        25                  [::]:514               [::]:*                
[root@mr ~]# /usr/local/mysql/support-files/mysql.server stop
Shutting down MySQL.. SUCCESS! 
[root@mr ~]# ss -antl
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port   Process   
LISTEN   0        128              0.0.0.0:111            0.0.0.0:*                
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*                
LISTEN   0        25               0.0.0.0:514            0.0.0.0:*                
LISTEN   0        128                 [::]:111               [::]:*                
LISTEN   0        128                 [::]:22                [::]:*                
LISTEN   0        25                  [::]:514               [::]:*    
[root@mr ~]# cp /usr/lib/systemd/system/sshd.service . 
[root@mr ~]# ls sshd.service 
[root@mr ~]# mv sshd.service mysqld.service 
[root@mr ~]# ls mysqld.service
[root@mr ~]#vim mysqld.service 
[Unit] 
Description=mysql server daemon 
After=network.target sshd-keygen.target 

[Service] 
Type=forking 
ExecStart=/usr/local/mysql/support-files/mysql.server start 
ExecStop=/usr/local/mysql/support-files/mysql.server stop 
ExecReload=/bin/kill -HUP $MAINPID 

[Install] 
WantedBy=multi-user.target 
[root@mr ~]# systemctl daemon-reload 
[root@mr ~]# systemctl stop firewalld 
[root@mr ~]# systemctl disable firewalld 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service. 
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@mr ~]# vim /etc/selinux/config 
SELINUX=disabled 
[root@mr ~]# setenforce 0 
[root@mr ~]# systemctl start mysqld
[root@mr ~]# ss -antl 
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:111 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 25 0.0.0.0:514 0.0.0.0:* LISTEN 0 80 *:3306 *:* LISTEN 0 128 [::]:111 [::]:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 25 [::]:514 [::]:* 
[root@mr ~]#dnf install -y ncurses-compat-libs 

[root@mr ~]# mysql -uroot -p'-ff3uq#Fee%k' 
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.38 

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> set password = password('123456'); Query OK, 0 rows affected, 1 warning (0.00 sec) 
mysql> exit Bye 
[root@mr ~]# mysql -uroot -p123456 
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 3 
Server version: 5.7.38 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>

## 3.2 mysql主从配置
### 3.2.1 确保从数据库与主数据库里的数据一样
为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中
```bash
先查看主库有哪些库
[root@master ~]# mysql -uroot -p123456 -e 'show databases;'
查看从库
[root@slave ~]# mysql -uroot -p123456 -e 'show databases;'
//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出

//备份主库并将备份文件传送到从库
[root@master ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-2022.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt/
all-201808191200.sql
[root@localhost ~]# scp /opt/all-2022.sql root@192.169.29.141:/opt/
root@192.169.29.141 's password:
all-201808191200.sql                              100%  786KB  10.6MB/s   00:00 
//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye
//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave ~]# mysql -uroot -p123456 < /opt/all-2022.sql
[root@slave ~]# mysql -uroot -p123456 -e 'show databases;'

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

mysql> create user 'mr'@'192.168.29.141' identified by 'marui';
Query OK, 0 rows affected (0.00 sec)

mysql>  grant replication slave on *.* to 'mr'@'192.168.29.141';
Query OK, 0 rows affected (0.00 sec)

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

mysql> 

3.2.3 配置主数据库

[root@master ~]# vim /etc/my.cnf
添加这两行
log-bin = mysql-bin 
server-id = 10
[root@master ~]# systemctl restart mysqld.service
[root@slave ~]# mysql -uroot -p123456
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.38 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>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      236 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

3.2.4 配置从数据库

[root@slave ~]# vim /etc/my.cnf
//添加下面两条
server-id = 20		
relay-log = mysql-relay-bin		

[root@slave ~]# systemctl restart mysqld.service
[root@slave ~]# ss -anlt
State       Recv-Q       Send-Q             Local Address:Port             Peer Address:Port      Process
LISTEN      0            128                      0.0.0.0:22                    0.0.0.0:*
LISTEN      0            80                             *:3306                        *:*
LISTEN      0            128                         [::]:22    
[root@slave ~]# mysql -uroot -p123456
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.38 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.29.140',
    -> master_user='mr'
    -> master_passwodr='marui',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=236;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.29.140
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 236
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes		
            Slave_SQL_Running: Yes

3.2.5 测试验证

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

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

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

mysql> 

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的具体形式 :

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

如图, 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在binlong的关系
  

• 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相关参数

参数	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	应用与系统版本
主数据库	172.16.12.128	centos8/redhat8mysql-5.7
从数据库	172.16.12.129	centos8/redhat8mysql-5.7

主库配置。vi /etc/my.cnf，添加以下配置，重启mysql。

log-bin=mysql_binserver-id=10gtid_mode=onenforce-gtid-consistency=truelog-slave-updates=on

从库配置。vi /etc/my.cnf, 添加以下配置，重启mysql。

server-id=20relay-log=myrelaygtid_mode=onenforce-gtid-consistency=truelog-slave-updates=onread_only=onmaster-info-repository=TABLErelay-log-info-repository=TABLE

主库授权复制用户。

set global validate_password_policy=0;set global validate_password_length=1;grant replication slave on * . * to 'repl'@'%' identified by 'repl123!';

从库设置要同步的主库信息，并开启同步。

change master to master_host='主库IP',
master_port=3306,master_user='repl',master_password='repl123!',
master_auto_position=1;

start slave;show slave status\G;

配置完之后，通过查看slave的状态，可以看是否配置成功。同时可以在主库进行一些操作，提交一些事务(insert,update)，之后数据就会自动同步到从库。

mysql> create user 'mr'@'192.168.29.141' identified by 'mr';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'mr'@'192.168.29.141' identified by 'marui';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

[root@master ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysqldata
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysqldata/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-bin = mysql_bin
server-id = 10
gtid_mode = on
enforce-gtid-consistency = true
log-slave-updates = on
[root@slave ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysqldata
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysqldata/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=20
relay-log=myrelay
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
[root@slave ~]# systemctl restart mysqld.service
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38-log 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.29.140',
   -> master_user='mr',
   -> master_password='marui',
   -> master_auto_position=1;
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.29.140
                 Master_User: mr
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql_bin.000001
         Read_Master_Log_Pos: 738
              Relay_Log_File: myrelay.000002
               Relay_Log_Pos: 965
       Relay_Master_Log_File: mysql_bin.000001
            Slave_IO_Running: Yes				
           Slave_SQL_Running: Yes
主：
mysql> create database school;
Query OK, 1 row affected (0.01 sec)

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

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

mysql>