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

艾文伯特

已于 2024-09-01 14:10:56 修改

阅读量648

点赞数 7

分类专栏：数据库运维文章标签： mysql linux 运维

于 2024-09-01 14:09:22 首次发布

本文链接：https://blog.csdn.net/jianghuchuang/article/details/141779089

版权

数据库同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

运维

4 篇文章 0 订阅

订阅专栏

文章目录

1 主从复制
2 主从切换

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 event
                  Master_Host: 192.168.25.91
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 722
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000007
             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: 722
              Relay_Log_Space: 529
              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: 1
                  Master_UUID: 38643166-65de-11ef-b398-000c294562da
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql>

注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"命令，查看控制台打印结果，要求参数值要和下面的一致：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
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 event
                  Master_Host: 192.168.25.91
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 1865
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 1466
        Relay_Master_Log_File: binlog.000007
             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: 1865
              Relay_Log_Space: 1672
              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: 1
                  Master_UUID: 38643166-65de-11ef-b398-000c294562da
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_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 event
                  Master_Host: 192.168.25.92
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 2267
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000004
             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: 2267
              Relay_Log_Space: 529
              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: 2
                  Master_UUID: 151c4ed2-65e2-11ef-89a3-000c29b00378
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_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)