Mysql8 主从复制主从切换(超详细)

1 主从复制

1.1 实施前提

需要2台安装了Mysql的服务器，我这边服务器配置如下：

操作系统类型	IP	mysql版本	主从类型
Centos7.9	192.168.25.91	8.0.34	主
Centos7.9	192.168.25.92	8.0.34	从

进行mysql主从复制前，需要安装Msql，安装Mysql的过程之前文章已经介绍过，具体参考：Centos7安装MYSQL8(无坑版)：https://blog.csdn.net/jianghuchuang/article/details/139117966
这里不再赘叙。

注：我的mysql数据目录是/data/mysql，下文配置中的相关数据目录路径需要结合你们实际数据目录路径(默认是/var/lib/mysql)去进行修改。

1.2 主节点配置(在192.168.25.91操作)

注1：我当前mysql数据目录是/data/mysql，你们需根据自己实际的数据库数据目录去进行修改。

主从节点配置的差异：由于后续需要演示主从切换，所以无论是主从节点，都需要提前开启binlog和relaylog。故而这里主从配置基本一致，具体配置选项差异只有：server_id、read-only选项

编辑/etc/my.conf文件，在[mysqld]下添加以下内容

#==================== 主从同步配置=========================
#节点id编号，各个mysql的server_id需要唯一
server_id=1
#[可选]指定binlog和binglog index的文件名
log_bin=/data/mysql/binlog
log_bin_index=/data/mysql/binlog.index
#[可选]启用中继日志
relay-log=/data/mysql/mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
#binlog_expire_logs_seconds=6000
#[可选]设置不要复制的数据库
#binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要复制的主数据库名字

修改配置后重启数据库:

systemctl restart mysqld

配置文件完整内容如下：

[mysqld]
#数据库数据目录
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
#错误日志路径
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 服务端默认字符集
character-set-server=utf8mb4
# # 连接层默认字符集
collation-server=utf8mb4_unicode_ci
#忽略表名大小写
lower_case_table_names=1#==================== 主从同步配置=========================
#节点id编号，各个mysql的server_id需要唯一
server_id=1
#指定binlog和binglog index的文件名
log_bin=/data/mysql/binlog
log_bin_index=/data/mysql/binlog.index
#[可选]启用中继日志
relay-log=/data/mysql/mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
#binlog_expire_logs_seconds=6000
#[可选]设置不要复制的数据库
#binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要复制的主数据库名字[mysql]
# 数据库默认字符集
default-character-set=utf8mb4
[client]
# 客户端来源数据的默认字符集
default-character-set=utf8mb4

1.3 从节点配置(在192.168.25.92操作)

主从节点配置的差异：由于后续需要演示主从切换，所以无论是主从节点，都需要提前开启binlog和relaylog。故而这里主从配置基本一致，具体配置选项差异只有：server_id、read-only选项

编辑/etc/my.conf文件，在[mysqld]下配置添加以下内容

##节点id编号，各个mysql的server_id需要唯一
server_id=2
#指定binlog和binglog index的文件名
log_bin=/data/mysql/binlog
log_bin_index=/data/mysql/binlog.index
#[可选]启用中继日志
relay-log=/data/mysql/mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=1
# #[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
# #binlog_expire_logs_seconds=6000
# #[可选]设置不要复制的数据库
# #binlog-ignore-db=test
# #[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
# #binlog-do-db=需要复制的主数据库名字

修改配置后重启数据库:

systemctl restart mysqld

配置文件完整内容如下：

[mysqld]
#数据库数据目录
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
#错误日志路径
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 服务端默认字符集
character-set-server=utf8mb4
# # 连接层默认字符集
collation-server=utf8mb4_unicode_ci
#忽略表名大小写
lower_case_table_names=1#==================== 主从同步配置=========================
##节点id编号，各个mysql的server_id需要唯一
server_id=2
#指定binlog和binglog index的文件名
log_bin=/data/mysql/binlog
log_bin_index=/data/mysql/binlog.index
#[可选]启用中继日志
relay-log=/data/mysql/mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=1
# #[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
# #binlog_expire_logs_seconds=6000
# #[可选]设置不要复制的数据库
# #binlog-ignore-db=test
# #[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
# #binlog-do-db=需要复制的主数据库名字[mysql]
# # 数据库默认字符集
default-character-set=utf8mb4
[client]
# 客户端来源数据的默认字符集default-character-set=utf8mb4

1.4 创建用于主从同步的用户

主、从节点都需要进行以下操作：

#创建slave1用户
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
#给slave1用户授予数据同步的权限
GRANT replication slave on *.* to 'slave1'@'%'
#刷新权限
flush privileges;

1.5 开启主从同步

开启主从同步过程中，就不要再去操作数据了，以免出现数据不一致情况。

1、查看主节点binlog执行位置（主节点192.168.25.91来执行以下命令）：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000007 |      722 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2、从节点开启主节点同步操作（从节点192.168.25.92来执行以下命令）：

#从节点设置主节点信息
mysql> CHANGE MASTER toMASTER_HOST='192.168.25.91',MASTER_USER='slave1',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000007',MASTER_LOG_POS=722;
Query OK, 0 rows affected, 8 warnings (0.03 sec)
#从节点开启数据同步
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
#查看主从数据同步情况
mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.25.91Master_User: slave1Master_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000007Read_Master_Log_Pos: 722Relay_Log_File: mysql-relay.000002Relay_Log_Pos: 323Relay_Master_Log_File: binlog.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 722Relay_Log_Space: 529Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 38643166-65de-11ef-b398-000c294562daMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.00 sec)ERROR: 
No query specifiedmysql> 

注1：从节点使用show slave status;命令检查主从同步时，需要关注控制台打印结果中的“Slave_IO_Running”以及“Slave_SQL_Running”两个选项，两个选项都为yes，说明主从同步以及成功启动~
  如果发现其中有存在No的情况，那么检查防火墙是否关闭、主节点的slave1用户是否创建成功(可以在从节点上执行“mysql -h 主节点ip -uslave1 -p123456”看是否能登录到主节点上)

如果发现其中有存在No的情况，进行以下排查：
1、先稍等一下，启动slave后，不一定马上就会变为Yes，可能还需要等一下
2、检查主从节点服务器的防火墙是否关闭
3、主节点的slave1用户是否创建成功(可以在从节点上执行“mysql -h 主节点ip -uslave1 -p123456”看是否能登录到主节点上)
4、如果发现是上面执行"change master to …"命令时参数写错导致的，那么在从节点上，先执行“stop slave”停止主从，接着在主节点上重新执行“show master status”来获取主节点最新binlog日志以及偏移位置，然后在从节点重新执行“change master to …”命令，最后在从节点上执行 “start slave;”

1.5 主从同步验证

在主节点192.168.25.92上建库、建表、插入表数据：

#创建数据库
mysql> create database test_db;
Query OK, 1 row affected (0.03 sec)
#查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.06 sec)
#切换数据库
mysql> use test_db;
Database changed
#创建表
mysql> CREATE TABLE `t_test` (->   `id` int(11) NOT NULL,->   `age` int(11) DEFAULT NULL,->   `score` int(11) DEFAULT NULL,->   PRIMARY KEY (`id`)-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 3 warnings (0.10 sec)#插入表数据
mysql> INSERT INTO `t_test` VALUES (1, 2, 1);
Query OK, 1 row affected (0.01 sec)
#插入表数据
mysql> INSERT INTO `t_test` VALUES (222, 22, 19);
Query OK, 1 row affected (0.01 sec)
#查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
#查看表数据
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
+-----+------+-------+
2 rows in set (0.00 sec)

检查从节点192.168.25.92是否也都同步成功：
如下所示，从节点也都自动完成了主节点上所进行的相关操作~

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)
#切换数据库
mysql> use test_db;
Database changed
#查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
#查询表数据
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
+-----+------+-------+
2 rows in set (0.00 sec)

至此，主从同步就完成~

2 主从切换

2.1 实施前提

要求当前要是主备模式，具体主备搭建见上面章节。
主从切换情况：

操作系统类型	IP	mysql版本	切换前	切换后
Centos7.9	192.168.25.91	8.0.34	主	从
Centos7.9	192.168.25.92	8.0.34	从	主

2.2 主节点设置只读(在192.168.25.91操作)

主节点设置只读模式，避免进行主从切换过程中还有写操作，导致切换后主从数据不一致问题。
注：用命令设置的只读模式是临时的，重启后失效。如果想让mysql重启后也能生效，可以将read_only相关选项配置到my.conf文件里面。

#查看只读相关配置
show VARIABLES like '%read_only%';
#开启全局只读(包括普通用户、超级管理员root也都不能写)
set global super_read_only='on';
#开启全局只读(普通用户不能写)，理论来说开启了super_read_only后，就无需设置当前参数
set global read_only='on';
#查看只读相关配置
show VARIABLES like '%read_only%';

具体操作如下：

mysql> show VARIABLES like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.00 sec)mysql> set global super_read_only='on';
Query OK, 0 rows affected (0.00 sec)mysql> set global read_only='on';
Query OK, 0 rows affected (0.00 sec)mysql> show VARIABLES like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
+-----------------------+-------+
4 rows in set (0.00 sec)

2.3 检查主从数据是否同步完毕(在192.168.25.92操作)

在从节点上执行"show slave status"命令，查看控制台打印结果，要求参数值要和下面的一致：

1. Slave_IO_Running: Yes
2. Slave_SQL_Running: Yes
3. Seconds_Behind_Master: 0
4. Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates

注：Slave_IO_Running和Slave_SQL_Running都为true代表主、从是正常同步，其次Seconds_Behind_Master为0代表当前主、从节点数据一致。

具体操作如下：

mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.25.91Master_User: slave1Master_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000007Read_Master_Log_Pos: 1865Relay_Log_File: mysql-relay.000002Relay_Log_Pos: 1466Relay_Master_Log_File: binlog.000007Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 1865Relay_Log_Space: 1672Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 38643166-65de-11ef-b398-000c294562daMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

2.4 停止并重置从节点(在192.168.25.92操作)

#停止从节点
stop slave;
#重置掉从节点的相关主从同步信息，同时将relaylog文件进行删除重置
reset slave all;

2.5 关闭原从节点的只读配置(在192.168.25.92操作)

注：用命令设置的只读模式是临时的，重启后失效。如果想让mysql重启后也能生效，可以将read_only相关选项配置到my.conf文件里面或者从my.conf进行删除，以为默认就是只读关闭。

#查看只读相关配置
show VARIABLES like '%read_only%';
#关闭全局只读(让超级管理员root能进行写操作)
set global super_read_only='off';
#关闭全局只读(让普通用户也能写操作)
set global read_only='off';
#查看只读相关配置
show VARIABLES like '%read_only%';

2.6 主从切换

进行主从同步的过程不要任何写操作，避免导致切换后主从数据不一致。

1、查看原从节点的最新日志以及偏移量(在192.168.25.92操作)。

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |     2267 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2、将原主节点 的主节点 设置为原从节点(在192.168.25.91操作)

#设置主节点信息
CHANGE MASTER to MASTER_HOST='192.168.25.92',MASTER_USER='slave1',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=2267;
#开启slave
start slave;
#查看主从同步信息
show slave status\G;

注：当Slave_IO_Running和Slave_SQL_Running都为Yes时，代表切换成功。

具体操作如下：

mysql> CHANGE MASTER to -> MASTER_HOST='192.168.25.92',-> MASTER_USER='slave1',-> MASTER_PASSWORD='123456',-> MASTER_LOG_FILE='binlog.000004',-> MASTER_LOG_POS=2267;
Query OK, 0 rows affected, 8 warnings (0.17 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 eventMaster_Host: 192.168.25.92Master_User: slave1Master_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000004Read_Master_Log_Pos: 2267Relay_Log_File: mysql-relay.000002Relay_Log_Pos: 323Relay_Master_Log_File: binlog.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 2267Relay_Log_Space: 529Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2Master_UUID: 151c4ed2-65e2-11ef-89a3-000c29b00378Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

2.7 验证

1、在新主节点(192.168.25.92)插入表数据

mysql> INSERT INTO `t_test` VALUES (300, 3, 1);
Query OK, 1 row affected (0.00 sec)mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
| 300 |    3 |     1 |
+-----+------+-------+
3 rows in set (0.00 sec)

2、在新从节点(192.168.25.91)查看表数据，发现在新主节点插入的数据已经自动同步到新从节点上了。

mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
| 300 |    3 |     1 |
+-----+------+-------+
3 rows in set (0.00 sec)