GTID主从配置及一主一从、一主多从、多主一从

一主一从

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

数据库	IP地址
master	192.168.164.131
slave	192.168.164.132

master

/推荐下载MySQL5.7.34的版本
[root@master ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz /usr/src
...

//解压软件至/usr/local目录下，目录的位置可以是其他的地方
[root@master ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@master ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.34-linux-glibc2.12-x86_64  share

//创建用户和组
[root@master ~]# useradd -r -M -s /sbin/nologin mysql

//创建软连接
[root@master ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@master local]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 root root  46 8月  30 14:44 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 8月  30 14:41 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//修改修改目录/usr/local/mysql的属主属组
[root@master ~]# chown -R mysql.mysql /usr/local/mysql*
[root@master ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 mysql mysql  46 8月  30 14:44 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 8月  30 14:41 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//添加环境变量
[root@master ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master ~]# source /etc/profile.d/mysql.sh
[root@master ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//建立数据存放目录
[root@master ~]# mkdir -p /opt/data/
[root@master ~]# chown -R mysql.mysql /opt/data/
[root@master ~]# ll /opt/data
总用量 0

//初始化数据库（不要密码）
[root@master ~]# mysqld --initialize-insecure --datadir=/opt/data/ --user=mysql
2021-08-31T06:33:24.260349Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T06:33:24.493032Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T06:33:24.524102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T06:33:24.529250Z 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: 57d51369-0a25-11ec-8346-000c2931864c.
2021-08-31T06:33:24.530528Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-31T06:33:25.255441Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T06:33:25.287528Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

//安装依赖包
[root@master ~]# yum -y install ncuress-compat-libs
...

//生成配置文件
[root@master data]# vim /etc/my.cnf			#如果这个文件存在，请先备份再修改
[root@master data]# cat /etc/my.cnf 
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysqld.pid
skip-name-serolve	#跳过名称解析

//配置服务启动脚本
[root@master ~]# cp -a /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@master ~]# vim /etc/init.d/mysqld 		#修改文件中的这两个地方
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql服务
[root@master ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@master ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:* 

//设置新密码
[root@master ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

slave：

//在master将mysql-5.7包传输到slave上
[root@master ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  192.168.164.132:/usr/src/
The authenticity of host '192.168.164.132(192.168.164.132)' can't be established.
ECDSA key fingerprint is SHA256:rp+LMeFjAIapu3y+MH6bQa8QZpl/XRlUd1PammVI9E0.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.164.132' (ECDSA) to the list of known hosts.
root@192.168.164.132s password: 
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz               100%  635MB 102.6MB/s   00:06 

//解压软件至/usr/local目录下，目录的位置可以是其他的地方
[root@slave ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@slave ~]# ls /usr/local/
apr       bin  games  include  lib64    mysql-5.7.34-linux-glibc2.12-x86_64  share
apr-util  etc  httpd  lib      libexec  sbin                                 src

//创建用户和组
[root@slave ~]# useradd -r -M -s /sbin/nologin mysql

//创建软连接
[root@slave ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slave ~]# ll -d /usr/local/mysql*
lrwxrwxrwx 1 root root  46 8月  30 14:55 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 root root 129 8月  30 14:52 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//修改修改目录/usr/local/mysql的属主属组
[root@slave ~]# chown -R mysql.mysql /usr/local/mysql*
[root@slave ~]# ll -d /usr/local/mysql*
lrwxrwxrwx 1 mysql mysql  46 8月  30 14:55 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql 129 8月  30 14:52 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//添加环境变量
[root@slave ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@slave ~]# source /etc/profile.d/mysql.sh
[root@slave ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//建立数据存放目录
[root@slave ~]# mkdir -p /opt/data/
[root@slave ~]# chown -R mysql.mysql /opt/data/
[root@slave ~]# ll /opt/data
总用量 0

//初始化数据库（不要密码）
[root@master ~]# mysqld --initialize-insecure --datadir=/opt/data/ --user=mysql
2021-08-31T06:33:24.260349Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T06:33:24.493032Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T06:33:24.524102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T06:33:24.529250Z 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: 57d51369-0a25-11ec-8346-000c2931864c.
2021-08-31T06:33:24.530528Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-31T06:33:25.255441Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T06:33:25.287528Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

//安装依赖包
[root@master ~]# yum -y install ncurses-compat-libs
...

//生成配置文件
[root@slave ~]# vim /etc/my.cnf
[root@slave ~]# cat /etc/my.cnf
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
skip-name-resolve

//配置服务启动脚本
[root@slave ~]# cp -a /usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
[root@slave ~]# vim /etc/init.d/mysqld 
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql服务
[root@slave ~]# service mysqld start
 Starting MySQL. SUCCESS!
[root@slave ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:*       

//设置新密码
[root@master ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

关闭防火墙和selinux（主从都要关闭）

[root@master ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master ~]# setenforce 0

配置主库文件：

//在主数据库里创建一个同步账号授权给从数据库使用
mysql> grant replication slave on *.* to 'wjm'@'192.168.164.132' identified by 'wjm123!';		#无需提前创建用户，执行授权的时候会自动创建用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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

//重启MySQL服务
[root@master ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

//检查gtid模式状态
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)

配置从库文件：

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

//重启MySQL服务
[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//检查gtid模式状态
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.164.131',
    -> master_user='wjm',
    -> master_password='wjm123!',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 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.164.131
                  Master_User: wjm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 ......
 ......

测试：

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

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

mysql> use wjm1;
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 | wjm        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
5 rows in set (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000003 |      870 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

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

mysql> select * from wjm1.student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | wjm        |   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 |      862 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c: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.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | wjm        |   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.000003 |     1172 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

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

mysql> select * from wjm1.student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | wjm        |   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 |     1156 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

一主多从

搭建三台MySQL服务器，一台作为主服务器，一台作为从服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

数据库	IP地址
master	192.168.164.131
slave	192.168.164.132
slave2	192.168.164.133

master：
主库安装方式和上面一样

slave：

[root@slave ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  192.168.164.133:/usr/src/
The authenticity of host '192.168.164.133(192.168.164.133)' can't be established.
ECDSA key fingerprint is SHA256:u29tZ8NW9v2uYAf2bAK7FOZwaxtjsxa0NVjgDTcaNTg.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.164.133' (ECDSA) to the list of known hosts.
root@192.168.164.133's password: 
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz               100%  635MB 134.5MB/s   00:04 

[root@slave2 ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@slave2 ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.34-linux-glibc2.12-x86_64  share

[root@slave2 ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slave2 ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 root root  46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

[root@slave2 ~]# chown -R mysql.mysql /usr/local/mysql*
[root@slave2 ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 mysql mysql  46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

root@slave2 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@slave2 ~]# source /etc/profile.d/mysql.sh
[root@slave2 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

[root@slave2 ~]# mkdir -p /opt/data/
[root@slave2 ~]#  chown -R mysql.mysql /opt/data/
[root@slave2 ~]# ll /opt/data/
total 0

[root@slave2 ~]# mysqld --initialize --datadir=/opt/data/ --user=mysql
2021-08-31T06:15:31.711025Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T06:15:31.866033Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T06:15:31.889223Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T06:15:31.945429Z 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: fdd2fc8c-0a3b-11ec-82ba-000c29209bda.
2021-08-31T06:15:31.947661Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-31T06:15:32.598937Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T06:15:32.652206Z 1 [Note] A temporary password is generated for root@localhost: Qq73AjyWw2%/
[root@slave2 ~]# echo 'Qq73AjyWw2%/' > pass

//安装依赖包
[root@slave2 ~]# yum -y install ncurses-compat-libs

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

//配置服务启动脚本
[root@slave2 ~]# cp -a /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@slave2 ~]# vim /etc/init.d/mysqld 
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql服务
[root@slave2 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@slave2 ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:*   

//设置新密码
[root@slave2 ~]# mysql -uroot -p'Qq73AjyWw2%/'
mysql> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

关闭防火墙和selinux：
所有服务器都需要做

[root@master ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master ~]# setenforce 0

配置主库文件：
和一主一从相同配置

配置从库文件：

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

//重启MySQL服务
[root@slave2 ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//检查gtid模式状态
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.01 sec)

//配置并启动主从复制
mysql> change master to
    -> master_host='192.168.164.131',
    -> master_user='wjm',
    -> master_password='wjm123!',
    -> 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.164.131
                  Master_User: wjm
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 1618
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 1831
        Relay_Master_Log_File: mysql_bin.000003
             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: 1618
              Relay_Log_Space: 2039
              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: 57d51369-0a25-11ec-8346-000c2931864c
             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: 57d51369-0a25-11ec-8346-000c2931864c:1-6
            Executed_Gtid_Set: 57d51369-0a25-11ec-8346-000c2931864c:1-6
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

测试：

//从库查看
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |     1602 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

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

mysql> use wjm1;
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 | wjm        |   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服务器，两台作为主服务器，一台作为从服务器，从服务器进行读操作

数据库	IP地址
master	192.168.164.131
master2	192.168.164.132
slave	192.168.164.133

主数据库1配置：

mysql> grant replication slave on *.* to 'wjm'@'192.168.164.134'identified by 'wjm123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql_bin.000007 |     1300 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-14 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

主数据库2配置：

mysql> grant replication slave on *.* to 'wjm'@'192.168.164.134'identified by 'wjm123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

从数据库配置：

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

mysql> change master to master_auto_position=0;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.164.131',master_use='wjm',master_password='wjm123!', master_log_file='mysql_bin.000007', master_log_pos=1300;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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


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

mysql> change master to master_auto_position=0;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.164.133',master_use='wjm',master_password='wjm123!', master_log_file='mysql_bin.000002', master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

效果：

//主库1插入数据：
mysql> create database testmaster;
Query OK, 1 row affected (0.01 sec)

mysql> use testmaster;
Database changed
mysql> create table tablemaster(id int(11));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tablemaster values(1);
Query OK, 1 row affected (0.00 sec)

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

//主库2插入数据
mysql> create database tablemaster2;

Query OK, 1 row affected (0.00 sec)

mysql> use tablemaster2;
Database changed
mysql> create table tablemaster2(id int(11));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tablemaster2 values(1);
Query OK, 1 row affected (0.01 sec)

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