Centos9部署Mysql8.0主从同步/自动协商/双主双从配置(超详细配置)

环境说明

实验采用四台虚拟机，分别为Master1、Master2、Slave1、Slave2

机器名称	IP地址	账号	密码
Master1	172.30.100.100	root	123456
Master2	172.30.100.101	root	123456
Slave1	172.30.100.102	root	123456
Slave2	172.30.100.103	root	123456

0、准备工作

0.1 修改主机名(为了方便)

#Master1
[root@localhost ~]# hostnamectl set-hostname master1
#Master2
[root@localhost ~]# hostnamectl set-hostname master2
#Slave1
[root@localhost ~]# hostnamectl set-hostname slave1
#Slave2
[root@localhost ~]# hostnamectl set-hostname slave2

0.2 关闭防火墙及SELinux(必做操作)

避免因为权限等问题，同步失败

#Master1
[root@master1 ~]# systemctl stop firewalld
[root@master1 ~]# systemctl disable firewalld
#Master2
[root@master2 ~]# systemctl stop firewalld
[root@master2 ~]# systemctl disable firewalld
#Slave1
[root@slave1 ~]# systemctl stop firewalld
[root@slave1 ~]# systemctl disable firewalld
#Slave2
[root@slave2 ~]# systemctl stop firewalld
[root@slave2 ~]# systemctl disable firewalld

0.3 编写hosts文件复制到其他节点(为了方便)

#Master1
[root@master1 ~]# vim /etc/hosts
172.30.100.100 master1
172.30.100.101 master2
172.30.100.102 slave1
172.30.100.103 slave2
[root@master1 ~]# scp /etc/hosts master2:/etc/hosts
[root@master1 ~]# scp /etc/hosts slave1:/etc/hosts
[root@master1 ~]# scp /etc/hosts slave2:/etc/hosts

0.4 安装步骤及开机自启

#Master1
[root@master1 ~]# yum -y install mysql*
[root@master1 ~]# systemctl enable --now mysqld
#Master2
[root@master2 ~]# yum -y install mysql*
[root@master2 ~]# systemctl enable --now mysqld
#Slave1
[root@slave1 ~]# yum -y install mysql*
[root@slave1 ~]# systemctl enable --now mysqld
#Slave2
[root@slave2 ~]# yum -y install mysql*
[root@slave2 ~]# systemctl enable --now mysqld

1、单主单从同步(通过二进制文件)

1.1 Master1步骤

1.1.1 数据库密码设置

安装完成后，因默认Mysql8.0没有密码，直接回车进入数据库即可，此时需要对数据库设置密码，方便后续主从同步

[root@master1 ~]# mysql -uroot -p

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

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

1.1.2 准备测试数据

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

mysql> create table master1db.master1tab(id int(10),name char(50));
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into master1db.master1tab values(1,'A');
Query OK, 1 row affected (0.00 sec)

mysql> insert into master1db.master1tab values(2,'B');
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

1.1.3 修改配置文件

#需要注意，有的朋友配置文件可能是/etc/my.cnf，写这个也可以
[root@master1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
log_bin	#新增
server-id=1	#新增

#重启服务
[root@master1 ~]# systemctl restart mysqld

1.1.4 创建用于复制的用户并授权

[root@master1 ~]# mysql -uroot -p

mysql> create user 'rep'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

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

1.1.5 备份Master1数据库的数据

[root@master1 ~]# mysqldump -p'123456' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql

#发送到Slave1，需要手动同步使用
[root@master1 ~]# scp -r 2024-04-27-mysql-all.sql  master2:/tmp

#观察二进制日志分割点，查看刚才的的备份文件
#25行左右会有如下一行
#或者使用/进行搜索CHANGE MASTER TO MASTER_LOG_FILE也可以
#请记住这个位置，这个位置说明了主从同步，从服务器需要从哪个二进制文件的哪个位置开始同步
CHANGE MASTER TO MASTER_LOG_FILE='master1-bin.000002', MASTER_LOG_POS=157;

1.1.6 再次准备数据

[root@master1 ~]# mysql -uroot -p

mysql> insert into master1db.master1tab values (3,'C');
Query OK, 1 row affected (0.00 sec)

mysql> insert into master1db.master1tab values (4,'D');
Query OK, 1 row affected (0.00 sec)

1.2 Slave1步骤

1.2.1 测试repo用户可用性

#安装过程上方已完成，同上，需要设置数据库密码
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)

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

#测试rep用户可以正常登录即可
[root@slave1 ~]# mysql -h master1 -urep -p123456
mysql> 

1.2.2 设置Slave1配置文件

#server-id必须要设置，而且必须不能冲突，从服务器不需要开启二进制日志，因为不会向从请求日志
[root@slave1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=2	#新增

#重启数据库
[root@slave1 ~]# systemctl restart mysqld

1.2.3 手动同步数据

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.01 sec)

mysql> source /tmp/2024-04-27-mysql-all.sql

1.2.4 设置主服务器

#此处需要注意，master_log_file和master_log_pos一定要和我们上面查看的相对应(1.1.5部分)
mysql> change master to
    -> master_host='master1',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='master1-bin.000002',
    -> master_log_pos=157,
    -> get_master_public_key=1;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
#Mysql8.0主从数据库同步需要加上get_master_public_key=1
#避免同步报错
Last_IO_Error: Error connecting to source 'rep@master1:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

#如果第一次没加报错了，按照以下流程重新做即可
#必须要先stop关掉同步，然后reset重置同步配置，重新change master。
#不reset，就直接做change master依旧会报错
mysql> stop slave;
mysql> reset slave;
mysql> change master to
    -> master_host='master1',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='master1-bin.000002',
    -> master_log_pos=157,
    -> get_master_public_key=1;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

1.2.5 启动从设备，查看状态

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

mysql> show slave status\G;
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000005
          Read_Master_Log_Pos: 157
               Relay_Log_File: slave1-relay-bin.000005
                Relay_Log_Pos: 377
        Relay_Master_Log_File: master1-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
             Last_IO_Error_Timestamp: 
             Last_SQL_Error_Timestamp: 
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:
#如果状态正常的话Last_SQL_Error、Last_IO_Error都不会有报错信息
#并且Slave_IO_Running、Slave_SQL_Running都是Yes

1.2.6 测试同步

#Master1插入数据
mysql> insert into master1db.master1tab values (5,'E');
Query OK, 1 row affected (0.01 sec)

mysql> insert into master1db.master1tab values (6,'F');
Query OK, 1 row affected (0.00 sec)

#Slave1查看数据是否同步
mysql> use master1db;
Database changed
mysql> select * from master1tab;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
+------+------+
6 rows in set (0.00 sec)
#同步成功，主从同步完成

2、单主单从同步(通过自动协商)

2.1 Slave1步骤

2.1.1 实验准备

[root@slave1 ~]# systemctl stop mysqld
[root@slave1 ~]# rm -rf /var/lib/mysql/*
[root@slave1 ~]# systemctl start mysqld

#重置了数据库没有密码，还是直接回车，给数据库设置密码
[root@slave1 ~]# mysql -uroot -p
Enter password: 
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

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

2.2 Master1步骤

2.2.1 修改Master1配置文件

[root@master1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
log_bin
server-id=1
gtid_mode=ON	#新增
enforce_gtid_consistency=1	#新增
[root@master1 ~]# systemctl restart mysqld

2.2.2 备份数据

[root@master1 ~]# mysqldump -p123456 --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F-%H`-mysql-all.sql

#发送到Slave1下
[root@master1 ~]# scp 2024-04-27-02-mysql-all.sql slave1:/tmp/

2.2.3 模拟数据增加

mysql> insert into master1db.master1tab values (7,'G');
Query OK, 1 row affected (0.00 sec)

mysql> insert into master1db.master1tab values (8,'H');
Query OK, 1 row affected (0.01 sec)

2.3 Slave1步骤

2.3.1 修改配置文件

#因没有改变实验环境，用户登陆测试略过

[root@slave1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server-id=2
gtid_mode=ON	#新增
enforce_gtid_consistency=1	#新增

#重启数据库服务
[root@slave1 ~]# systemctl restart mysqld

2.3.2 手动回复Master1数据

mysql> set sql_log_bin=0;
mysql> source /tmp/2024-04-27-02-mysql-all.sql
mysql> select * from master1db.master1tab;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
+------+------+
6 rows in set (0.00 sec)
#此时，查看表，发现后新增的两条insert数据没有出现

2.3.3 设置自动协商并启动

mysql> change master to
    -> master_host='master1',
    -> master_user='rep',
    -> master_password='123456',
    -> master_auto_position=1,
    -> get_master_public_key=1;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
#不要忘了get_master_public_key=1参数

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

mysql> show slave status\G;
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master1-bin.000005
          Read_Master_Log_Pos: 157
               Relay_Log_File: slave1-relay-bin.000005
                Relay_Log_Pos: 377
        Relay_Master_Log_File: master1-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
             Last_IO_Error_Timestamp: 
             Last_SQL_Error_Timestamp: 
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:

2.3.4 测试

#查询表已经同步
mysql> select * from master1db.master1tab;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
|    7 | G    |
|    8 | H    |
+------+------+
8 rows in set (0.00 sec)

#Master1再次新增数据
mysql> insert into master1db.master1tab values (9,'I');
Query OK, 1 row affected (0.00 sec)

mysql> insert into master1db.master1tab values (10,'J');
Query OK, 1 row affected (0.01 sec)

#Slave1再次查询
mysql> select * from master1db.master1tab;
+------+------+
| id   | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
|    5 | E    |
|    6 | F    |
|    7 | G    |
|    8 | H    |
|    9 | I    |
|   10 | J    |
+------+------+
10 rows in set (0.00 sec)
#数据同步，单主单从自动协商成功

3、双主双从

做双主双从前，请确保四台机器的数据库状态都是干安好的状态，如果是接着上面做下来的朋友，可以把Master1和Slave1的数据库清空

#以Slave1为例子，Master1也这么来一圈
[root@slave1 ~]# systemctl stop mysqld
[root@slave1 ~]# rm -rf /var/lib/mysql/*
[root@slave1 ~]# systemctl start mysqld

#重置了数据库没有密码，还是直接回车，给数据库设置密码
[root@slave1 ~]# mysql -uroot -p
Enter password: 
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

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

3.1 Master1步骤

#原有基础新增以下配置
[root@master1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
#binlog日志路径
log-bin=/var/lib/mysql/binlog
server-id=1
#不需要复制的数据库有哪些，一个写一行
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#哪些数据库需要复制
binlog-do-db=master2db
binlog_format=statement
# 日志过期天数，过期了自动删除，默认是0，不删除
expire_logs_days=7
# 复制过程遇到1062错误码就跳过
slave_skip_errors=1062
# 如果自己是从库，自己写入操作的时候需要记录到二进制日志文件中
log-slave-updates
# 标识自增长字段每次递增的量，也就是步长
auto-increment-increment=2
# 表示自增长从哪个数开始
auto-increment-offset=1
# 增加mysql的连接数
max_connect_errors=1000

#重启数据库服务
[root@master1 ~]# systemctl restart mysqld

3.2 Master2步骤

#重点！！！配置文件并不是与Master1一样的，一定要改server-id
[root@master2 ~]# vim /etc/my.cnf.d/mysql-server.cnf
#binlog日志路径
log-bin=/var/lib/mysql/binlog
server-id=3
#不需要复制的数据库有哪些，一个写一行
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#哪些数据库需要复制
binlog-do-db=master2db
binlog_format=statement
# 日志过期天数，过期了自动删除，默认是0，不删除
expire_logs_days=7
# 复制过程遇到1062错误码就跳过
slave_skip_errors=1062
# 如果自己是从库，自己写入操作的时候需要记录到二进制日志文件中
log-slave-updates
# 标识自增长字段每次递增的量，也就是步长
auto-increment-increment=2
# 表示自增长从哪个数开始
auto-increment-offset=1
# 增加mysql的连接数
max_connect_errors=1000

#重启数据库服务
[root@master2 ~]# systemctl restart mysqld

3.3 Slave1步骤

#这两条参数是在原有的基础上新增，并不是只留这两个
[root@slave1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
server-id=2
relay-log=mysql-relay
[root@slave1 ~]# systemctl restart mysqld

3.4 Slave2步骤

#重点！！！！改server-id参数
#这两条参数是在原有的基础上新增，并不是只留这两个
[root@slave2 ~]# vim /etc/my.cnf.d/mysql-server.cnf
server-id=4
relay-log=mysql-relay
[root@slave2 ~]# systemctl restart mysqld

3.5 创建同步账号并授权

这个步骤需要在Master1与Master2上都做一遍

mysql> CREATE USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'slave_sync_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
Query OK, 0 rows affected (0.01 sec)

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

3.6 主从同步

3.6.1 Master1—Slave1

#核对好偏移量
#Master1进行操作
mysql> show master status;
+---------------+----------+--------------+--------------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+---------------+----------+--------------+--------------------------+-------------------+
| binlog.000002 |     1215 | master2db    | mysql,information_schema |                   |
+---------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

#Slave1进行操作
mysql> change master to
    -> master_host='master1',
    -> master_user='slave_sync_user',
    -> master_password='123456',
    -> master_log_file='binlog.000002',
    -> master_log_pos=1215,
    -> get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: master1
                  Master_User: slave_sync_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1215
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
#Slave_IO_Running、Slave_SQL_Running都是Yes即可

3.6.2 Master2—Slave2

#Master2进行操作
mysql> show master status;
+---------------+----------+--------------+--------------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+---------------+----------+--------------+--------------------------+-------------------+
| binlog.000002 |     1712 | master2db    | mysql,information_schema |                   |
+---------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

#Slave2进行操作
mysql> change master to
    -> master_host='master2',
    -> master_user='slave_sync_user',
    -> master_password='123456',
    -> master_log_file='binlog.000002',
    -> master_log_pos=1712,
    -> get_master_public_key=1;
    
Query OK, 0 rows affected, 9 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: master2
                  Master_User: slave_sync_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1712
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#Slave_IO_Running、Slave_SQL_Running都是Yes即可

3.6.3 Master1—Master2

#Master1进行操作
mysql> show master status;
+---------------+----------+--------------+--------------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+---------------+----------+--------------+--------------------------+-------------------+
| binlog.000002 |     1215 | master2db    | mysql,information_schema |                   |
+---------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

#Master2进行操作
mysql> change master to
    -> master_host='master1',
    -> master_user='repl_user',
    -> master_password='123456',
    -> master_log_file='binlog.000002',
    -> master_log_pos=1215,
    -> get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: master1
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1215
               Relay_Log_File: master2-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#Slave_IO_Running、Slave_SQL_Running都是Yes即可

3.6.4 Master2—Master1

#Master2进行操作
mysql> show master status;
+---------------+----------+--------------+--------------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+---------------+----------+--------------+--------------------------+-------------------+
| binlog.000002 |     1712 | master2db    | mysql,information_schema |                   |
+---------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

#Master1进行操作
mysql> change master to
    -> master_host='master2',
    -> master_user='repl_user',
    -> master_password='123456',
    -> master_log_file='binlog.000002',
    -> master_log_pos=1712,
    -> get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: master2
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1712
               Relay_Log_File: master1-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
#Slave_IO_Running、Slave_SQL_Running都是Yes即可

3.7 测试

#Master1进行创建库、表、插入数据操作
mysql> create database master2db;
Query OK, 1 row affected (0.00 sec)

mysql> use master2db;
Database changed
mysql> create table master2tab(id int primary key auto_increment, name varchar(50));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into master2tab(name) values('A');
Query OK, 1 row affected (0.02 sec)

mysql> insert into master2tab(name) values('B');
Query OK, 1 row affected (0.01 sec)

mysql> insert into master2tab(name) values('C');
Query OK, 1 row affected (0.00 sec)


#Master2、Slave1、Slave操作
mysql>  select * from master2db.master2tab;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  3 | B    |
|  5 | C    |
+----+------+
3 rows in set (0.00 sec)
#三台数据库的结果应该是皆可同步成功

如果各位朋友实验结果不相同，或者过程中爆了Error可以发到评论区，看到了会帮忙解决