当前位置：首页 > news >正文

MySQL主从复制与读写分离概述

news 2025/7/7 5:50:13

前言：

在数据驱动的现代应用中，数据库面临高并发读写与海量存储的双重挑战。单一数据库实例在性能、可用性及扩展性上逐渐成为瓶颈。MySQL主从复制（Master-Slave Replication）与读写分离（Read/Write Splitting）通过分布式架构，将写操作集中于主库（Master），读操作分发至多个从库（Slave），辅以基于二进制日志（Binlog）的异步/半同步数据同步机制，实现了数据冗余、负载分流与故障快速恢复。这一架构不仅成为企业级数据库高可用的基石，更为业务提供了弹性扩展的底层支撑。

一、概述

二、基础环境设置

网络对时

防火墙与SELinux三、配置主从复制

主服务配置

从服务器配置

配置从服务器的所属主服务器

开启同步

验证

主从复制常见问题

四、maxscale概述

五、读写分离

1、环境说明

2、mysql主从复制配置

3、maxscale安装

4、配置maxscale

1.测试数据同步

(1) 在主服务器上操作：

(2) 在从服务器上验证同步：

2.测试读写分离（通过MaxScale）

3.故障切换测试

(1) 模拟主库故障：

(2) 检查MaxScale状态：

(3) 验证新主库写入：

(4) 恢复旧主库并检查同步：

关键命令总结

总结

一、概述

1、master开启二进制日志记录

2、slave开启IO进程，从master中读取二进制日志并写入slave的中继日志

3、slave开启SQL进程，从中继日志中读取二进制日志并进行重放

4、最终，达到slave与master中数据一致的状态，我们称作为主从复制的过程。

二、基础环境设置

网络对时

主与从主机都需要操作

 [root@ryan ~]# cat /etc/chrony.conf | grep -Ev '^$|#'server ntp.aliyun.com iburst   ###添加或者修改driftfile /var/lib/chrony/driftmakestep 1.0 3rtcsynckeyfile /etc/chrony.keysleapsectz right/UTClogdir /var/log/chrony

###切换到主
[root@master ~]# timedatectl set-timezone Asia/Shanghai
[root@master mysql]# vim /etc/chrony.conf

[root@master mysql]# systemctl restart chronyd.service 
[root@master mysql]# date
2025年 07月 06日 星期日 17:25:50 CST

###切换到从
[root@slave ~]# timedatectl set-timezone Asia/Shanghai
[root@slave ~]# vim /etc/chrony.conf

[root@slave ~]# systemctl restart chronyd.service 
[root@slave ~]# date
2025年 07月 06日 星期日 17:29:59 CST

防火墙与SELinux

主与从主机都需要操作

###主
[root@master ~]# systemctl disable --now firewalld
[root@master ~]# setenforce 0
[root@master ~]# getenforce
Permissive###从
[root@slave ~]# systemctl disable --now firewalld
[root@slave ~]# setenforce 0
[root@slave ~]# getenforce
Permissive

三、配置主从复制

主服务配置

 ##修改配置文件[root@localhost ~]# cat /etc/my.cnf## This group is read both both by the client and the server# use it for options that affect everything#[mysqld]log-bin=mysql-binbinlog_format="statement"server-id=11log-slave-updates=true[client-server]## include all files from the config directory#!includedir /etc/my.cnf.d##启动服务[root@localhost ~]# systemctl enable --now mysqld#验证配置[root@localhost ~]# cd /var/lib/mysql/[root@localhost mysql]# lsauto.cnf        client-cert.pem      ibdata1            mysql-bin.000003   mysql_upgrade_info   server-key.pembinlog.000001   client-key.pem       ibtmp1             mysql-bin.000004   mysqlx.sock          sysbinlog.000002   db1                 '#innodb_redo'      mysql-bin.000005   mysqlx.sock.lock     undo_001binlog.index    db2                 '#innodb_temp'      mysql-bin.index    performance_schema   undo_002c2407          '#ib_16384_0.dblwr'   mysql              mysql.ibd          private_key.pemca-key.pem     '#ib_16384_1.dblwr'   mysql-bin.000001   mysql.sock         public_key.pemca.pem          ib_buffer_pool       mysql-bin.000002   mysql.sock.lock    server-cert.pem##创建从主机可以进行访问的用户mysql> create user slave@'192.168.72.%' identified by '123.com';mysql> grant all on *.* to 'slave'@'192.168.72.%';###密码插件修改ALTER USER 'slave'@'192.168.166.%' IDENTIFIED WITH mysql_native_password BY '123.com';###查看master正在使用的日志文件及日志书写位置[root@localhost mysql]# mysqlmysql> show master status;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000003 |      542 |              |                  |+-------------------+----------+--------------+------------------+1 row in set (0.00 sec)#注意：查看位置完毕后，不要对master做insert、update、delete、create、drop等操作！！！

[root@master ~]# systemctl restart mysqld

从服务器配置

 ##修改配置文件[root@localhost ~]# cat /etc/my.cnfrelay-log-index=slave-bin.indexserver-id=22##启动服务[root@localhost ~]# systemctl enable --now mysqld##配置验证，此时没有与主服务器进行连接，所以没有产生对应的relay log[root@localhost mysql]# lsaria_log.00000001  aria_log_control  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  performance_schema  test##配置从服务器的所属主服务器[root@localhost mysql]# mysqlmysql> change master to master_host='192.168.158.4',master_user='slave',master_password='123.com',master_log_file='master-bin.000001',master_log_pos=619;##启动slave角色，默认没有配置主从时，所有的mysql节点都是mastermysql> start slave;##查看slave状态信息mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.166.230Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 2567Relay_Log_File: slave-bin.000004Relay_Log_Pos: 1679Relay_Master_Log_File: mysql-bin.000005Slave_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: 2567Relay_Log_Space: 2416Until_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: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11Master_UUID: 25105bd5-6fd8-11ef-9dae-000c299fb683Master_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)#查看数据目录[root@localhost mysql]# lsaria_log.00000001  ibdata1      ib_logfile1  mysql       performance_schema  slave-bin.000001  slave-bin.indexaria_log_control   ib_logfile0  master.info  mysql.sock  relay-log.info      slave-bin.000002  test

[root@slave ~]# vim /etc/my.cnf.d/mysql-server.cnf

[root@slave mysql]# systemctl restart mysqld

配置从服务器的所属主服务器

开启同步

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.72.155Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000010Read_Master_Log_Pos: 589Relay_Log_File: slave-relay-bin.000014Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000010Slave_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: 589Relay_Log_Space: 885Until_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: 11Master_UUID: a0e533b0-51d8-11f0-a49f-000c2939f194Master_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 specified

验证

从查看不到任何库

切换到主创建新库

切换到从查看到主新创建的库

从查看二进制日志主创建指令是否同步

[root@slave ~]# cd /var/lib/mysql
[root@slave mysql]# lsauto.cnf        ca.pem              '#innodb_temp'        public_key.pembinlog.000001   client-cert.pem      mysql                server-cert.pembinlog.000002   client-key.pem       mysql.ibd            server-key.pembinlog.000003   gooddays             mysql.sock           slave-bin.indexbinlog.000004  '#ib_16384_0.dblwr'   mysql.sock.lock      slave-relay-bin.000001binlog.000005  '#ib_16384_1.dblwr'   mysql_upgrade_info   slave-relay-bin.000002binlog.000006   ib_buffer_pool       mysqlx.sock          sysbinlog.000007   ibdata1              mysqlx.sock.lock     undo_001binlog.index    ibtmp1               performance_schema   undo_002ca-key.pem     '#innodb_redo'        private_key.pem
[root@slave mysql]# mysqlbinlog slave-relay-bin.000002

主从复制常见问题

异常：

 mysql> show slave status\G;*************************** 1. row ***************************Slave_IO_State: Connecting to sourceMaster_Host: 192.168.166.230Master_User: slaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 2567Relay_Log_File: slave-bin.000004Relay_Log_Pos: 1679Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: ConnectingSlave_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: 2567Relay_Log_Space: 2416Until_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: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 2003Last_IO_Error: Error connecting to source 'slave@192.168.166.230:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Can't connect to MySQL server on '192.168.166.230:3306' (113)Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11Master_UUID: 25105bd5-6fd8-11ef-9dae-000c299fb683Master_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: 240913 04:04:49Last_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)

一般情况下，都与网络通信异常有关系。排查防火墙、物理网络连接等。二进制日志文件名或位置错误也会引起IO线程异常。

 reset replica;##用于重置SQL线程对relay log的重放记录！！

四、maxscale概述

MaxScale是maridb开发的一个mysql数据中间件，其配置简单，能够实现读写分离，并且可以根据主从状态实现写库的自动切换。官网：https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-20/

下载地址：https://dlm.mariadb.com/3927179/MaxScale/24.02.3/rhel/9/x86_64/maxscale-24.02.3-1.rhel.9.x86_64.rpm

https://dlm.mariadb.com/3993858/MaxScale/24.02.4/rhel/8/x86_64/maxscale-24.02.4-1.rhel.8.x86_64.rpm

五、读写分离

1、环境说明

数据库角色	IP	应用与系统版本
master	192.168.166.25	rocky linux9.4 mysql-8.0.36
slave	192.168.166.26	rocky linux9.4 mysql-8.0.36
slave2	192.168.166.27	rocky linux9.4 mysql-8.0.36
maxscale	192.168.166.9	rocky linux9.4 maxscale24.02.3-GA

主带从

主服务器 (Master): server1 (192.168.72.155:3366)

从服务器 (Slave): server2 (192.168.72.129:3366)

maxscale 192.168.72.9

2、mysql主从复制配置

分别在主从三台服务器上安装mysql8，并配置主从复制。

 ##由于认证插件问题，需要在master服务器使用下述命令进行更改密码的验证插件。ALTER USER'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password';FLUSH PRIVILEGES;###修改认证插件##my.cnfdefault-authentication-plugin=mysql_native_password

主配置

[root@master mysql]# vim /etc/my.cnf.d/mysql-server.cnf

[root@master mysql]# systemctl restart mysqld

从配置

[root@master slave]# vim /etc/my.cnf.d/mysql-server.cnf

[root@master slave]# systemctl restart mysqld

从查看同步状态是否正常

3、maxscale安装

 #下载yum源[root@maxscale ~]# curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash# [info] Checking for script prerequisites.# [info] MariaDB Server version 11.2 is valid# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo# [info] Adding trusted package signing keys.../etc/pki/rpm-gpg ~~# [info] Successfully added trusted package signing keys# [info] Cleaning package cache...25 files removed[root@maxscale ~]# ls /etc/yum.repos.d/mariadb.repo  rocky-addons.repo  rocky-devel.repo  rocky-extras.repo  rocky.repo#安装maxscale[root@maxscale ~]# yum -y install maxscale

rpm安装

拖入maxscale软件包

直接解决依赖下载

[root@maxscale ~]# rpm -ivh maxscale-24.02.3-1.rhel.9.x86_64.rpm 
警告：maxscale-24.02.3-1.rhel.9.x86_64.rpm: 头V4 RSA/SHA512 Signature, 密钥 ID e3c94f49: NOKEY
错误：依赖检测失败：libmicrohttpd.so.12()(64bit) 被 maxscale-24.02.3-1.rhel.9.x86_64 需要libodbc.so.2()(64bit) 被 maxscale-24.02.3-1.rhel.9.x86_64 需要librdkafka++.so.1()(64bit) 被 maxscale-24.02.3-1.rhel.9.x86_64 需要librdkafka.so.1()(64bit) 被 maxscale-24.02.3-1.rhel.9.x86_64 需要nodejs >= 10.0.0 被 maxscale-24.02.3-1.rhel.9.x86_64 需要
[root@maxscale ~]# ls
公共  视频  文档  音乐  anaconda-ks.cfg                       nohup.out
模板  图片  下载  桌面  maxscale-24.02.3-1.rhel.9.x86_64.rpm
[root@maxscale ~]# yum localinstall -y maxscale-24.02.3-1.rhel.9.x86_64.rpm

4、配置maxscale

登录到主库

 [root@master ~]# mysql -urootmysql: [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 14Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.#创建maxscale用户密码是maxscalemysql> CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale';Query OK, 0 rows affected (0.00 sec)#授权maxscale可以查询所有数据库mysql> GRANT SELECT ON mysql.* TO 'maxscale'@'%';Query OK, 0 rows affected (0.01 sec)#授权可以看所有数据库mysql> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';Query OK, 0 rows affected (0.00 sec)#创建admin用户可以在maxscale上登录mysql> CREATE USER 'admin'@'192.168.166.%' IDENTIFIED BY 'admin';Query OK, 0 rows affected (0.01 sec)

主创建账户

从打开二进制日志查看主创建用户指令

[root@slave ~]# cd -
/var/lib/mysql
[root@slave mysql]# lsauto.cnf        ca.pem               mysql                server-key.pembinlog.000001   client-cert.pem      mysql.ibd            slave-bin.indexbinlog.000002   client-key.pem       mysql.sock           slave-relay-bin.000004binlog.000003   gooddays             mysql.sock.lock      slave-relay-bin.000005binlog.000004  '#ib_16384_0.dblwr'   mysql_upgrade_info   slave-relay-bin.000006binlog.000005  '#ib_16384_1.dblwr'   mysqlx.sock          sysbinlog.000006   ib_buffer_pool       mysqlx.sock.lock     undo_001binlog.000007   ibdata1              performance_schema   undo_002binlog.000008   ibtmp1               private_key.pembinlog.index   '#innodb_redo'        public_key.pemca-key.pem     '#innodb_temp'        server-cert.pem
[root@slave mysql]# mysqlbinlog slave-relay-bin.000006

从打开数据库查看主是否完成数据同步

切换到主给maxcale赋予所有权限

切换到从进行验证权限是否赋予成功

切换到主创建admin账户

切换到从进行验证

在maxscale上安装mysql

 [root@maxscale ~]# yum -y install mysql[root@maxscale ~]# which mysql/usr/bin/mysql#登录到master[root@maxscale ~]# mysql -uadmin -padmin -h192.168.166.25mysql: [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 15Server version: 8.0.36 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.###因为没有权限所以只能看到两个数据库mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || performance_schema |+--------------------+2 rows in set (0.00 sec)mysql> exitBye

回到master主库设置增删改查权限

 mysql> GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON *.* TO 'admin'@'192.168.72.%';Query OK, 0 rows affected (0.00 sec)

在master主库上授权真机有增删改查权限

 mysql> CREATE USER 'admin'@'192.168.166.%' IDENTIFIED BY 'admin';Query OK, 0 rows affected (0.01 sec)mysql> GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON *.* TO 'admin'@'192.168.72.%';Query OK, 0 rows affected (0.01 sec)

此时再切回到maxscale主机上可以看到所有库

在maxscale上修改配置文件

 [root@maxscale ~]# vim /etc/maxscale.cnf#先看有无这个[maxscale]threads=auto#修改后端服务器地址[server1]type=serveraddress=192.168.166.25port=3306protocol=MySQLBackend[server2]type=serveraddress=192.168.166.26port=3306protocol=MySQLBackend[server3]type=serveraddress=192.168.166.27port=3306protocol=MySQLBackend#配置监控[MySQL-Monitor]type=monitormodule=mariadbmonservers=server1,server2,server3user=monitorpassword=monitormonitor_interval=2s#注释掉只读配置#[Read-Only-Service]#type=service#router=readconnroute#servers=server2#user=maxscale#password=maxscale#router_options=slave#修改读写分离服务[Read-Write-Service]type=servicerouter=readwritesplitservers=server1,server2,server3user=maxscalepassword=maxscaleversion_string = 8.0#配置listener#注释掉只读#[Read-Only-Listener]#type=listener#service=Read-Only-Service#protocol=mariadbprotocol#port=4008#修改读写分离[Read-Write-Listener]type=listenerservice=Read-Write-Serviceprotocol=mariadbprotocolport=3306 #(伪装成数据库端口3306)

[root@maxscale maxscale]# cat /etc/maxscale.cnf
#####################################################
# MaxScale documentation:                           #
# https://mariadb.com/kb/en/mariadb-maxscale-24-02/ #
############################################################################################################################################################
# Global parameters                                                                                   #
#                                                                                                     #
# Complete list of configuration options:                                                             #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-mariadb-maxscale-configuration-guide/ #
#######################################################################################################
[maxscale]
threads=auto############################################################################
# Server definitions                                                       #
#                                                                          #
# Set the address of the server to the network address of a MariaDB server.#
############################################################################[server1]
type=server
address=192.168.72.155
port=3306
protocol=MySQLBackend[server2]
type=server
address=192.168.72.129
port=3306
protocol=MySQLBackend
##################################################################################
# Uncomment this and add MaxScale's IP to proxy_protocol_networks in MariaDB for #
# easier user management: https://mariadb.com/kb/en/proxy-protocol-support/      #
##################################################################################
# proxy_protocol=true##################################################################################################
# Monitor for the servers                                                                        #
#                                                                                                #
# This will keep MaxScale aware of the state of the servers.                                     #
# MariaDB Monitor documentation:                                                                 #
# https://mariadb.com/kb/en/maxscale-24-02monitors/                                              #
#                                                                                                #
# The GRANTs needed by the monitor user depend on the actual monitor.                            #
# The GRANTs required by the MariaDB Monitor can be found here:                                  #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-mariadb-monitor/#required-grants #
##################################################################################################[MySQL-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=monitor
password=monitor
monitor_interval=2s
##################################################################################################################
# Uncomment these to enable automatic node failover:                                                             #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-mariadb-monitor/#cluster-manipulation-operations #
#                                                                                                                #
# The GRANTs required for automatic node failover can be found here:                                             #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-mariadb-monitor/#cluster-manipulation-grants     #
##################################################################################################################
# auto_failover=true
# auto_rejoin=true
# enforce_simple_topology=true
# replication_user=<username used for replication>
# replication_password=<password used for replication>
#########################################################################################################
# Uncomment this if you use more than one MaxScale with automatic node failover:                        #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-mariadb-monitor/#cooperative-monitoring #
#########################################################################################################
# cooperative_monitoring_locks=majority_of_all#########################################################################################################
# Service definitions                                                                                   #
#                                                                                                       #
# Service Definition for a read-only service and a read/write splitting service.                        #
#                                                                                                       #
# The GRANTs needed by the service user can be found here:                                              #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-authentication-modules/#required-grants #
#########################################################################################################################################################################################
# ReadConnRoute documentation:                                                 #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-readconnroute/ #
#################################################################################[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=service_user
#password=service_pw
#router_options=slave#################################################################################
# ReadWriteSplit documentation:                                                 #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-readwritesplit/ #
#################################################################################
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale
password=maxscale
version_string = 8.0
####################################################################################################
# Uncomment these to enable transparent transaction replay on node failure:                        #
# https://mariadb.com/kb/en/mariadb-maxscale-2402-maxscale-2402-readwritesplit/#transaction_replay #
####################################################################################################
# transaction_replay=true
# transaction_replay_timeout=30s####################################################################
# Listener definitions for the services                            #
#                                                                  #
# These listeners represent the ports the services will listen on. #
#####################################################################[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#port=4008
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=mariadbprotocol
port=3306

切换到maxcale做备份并修改配置文件

[root@maxscale ~]# mv /etc/maxscale.cnf /opt/maxscale.cnf.bak [root@maxscale ~]# ls -l /etc/maxscale.cnf
-rw-r--r--. 1 root root 779  7月  6 19:51 /etc/maxscale.cnf
[root@maxscale ~]# ls -l /etc/maxscale.cnf.d/ -d
drwxr-xr-x. 2 maxscale maxscale 6  9月  7  2024 /etc/maxscale.cnf.d/
[root@maxscale ~]# chown maxscale.maxscale /etc/maxscale.cnf[root@maxscale ~]# vim /etc/maxscale.cnf

切换到主库创建monitor监控用户

 mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';Query OK, 0 rows affected (0.01 sec)#再添加授权mysql> GRANT REPLICATION CLIENT on *.* to 'monitor'@'%';Query OK, 0 rows affected (0.00 sec)mysql> GRANT REPLICATION SLAVE on *.* to 'monitor'@'%';Query OK, 0 rows affected (0.01 sec)mysql> GRANT SUPER,RELOAD on *.* to 'monitor'@'%';Query OK, 0 rows affected, 1 warning (0.01 sec)

启动服务

[root@maxscale ~]# systemctl start maxscale[root@maxscale maxscale]# systemctl status maxscale
● maxscale.service - MariaDB MaxScale Database ProxyLoaded: loaded (/usr/lib/systemd/system/maxscale.service; enabled; preset: >Active: active (running) since Sun 2025-07-06 20:15:20 CST; 5min agoProcess: 18138 ExecStartPre=/usr/bin/install -d /var/cache/maxscale -o maxsc>Process: 18139 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)Main PID: 18141 (maxscale)Tasks: 12 (limit: 22799)Memory: 8.0MCPU: 287msCGroup: /system.slice/maxscale.service└─18141 /usr/bin/maxscale7月 06 20:15:17 maxscale systemd[1]: Starting MariaDB MaxScale Database Proxy...
7月 06 20:15:17 maxscale maxscale[18141]: Module 'mariadbmon' loaded from '/usr/>
7月 06 20:15:17 maxscale maxscale[18141]: Module 'readwritesplit' loaded from '/>
lines 1-15...skipping...
● maxscale.service - MariaDB MaxScale Database ProxyLoaded: loaded (/usr/lib/systemd/system/maxscale.service; enabled; preset: disabled)Active: active (running) since Sun 2025-07-06 20:15:20 CST; 5min agoProcess: 18138 ExecStartPre=/usr/bin/install -d /var/cache/maxscale -o maxscale -g maxscale (code=exited, status=0/SUCCESS)Process: 18139 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)Main PID: 18141 (maxscale)Tasks: 12 (limit: 22799)Memory: 8.0MCPU: 287msCGroup: /system.slice/maxscale.service└─18141 /usr/bin/maxscale7月 06 20:15:17 maxscale systemd[1]: Starting MariaDB MaxScale Database Proxy...
7月 06 20:15:17 maxscale maxscale[18141]: Module 'mariadbmon' loaded from '/usr/lib64/maxscale/libmariadbmon.so'.
7月 06 20:15:17 maxscale maxscale[18141]: Module 'readwritesplit' loaded from '/usr/lib64/maxscale/libreadwritesplit.so'.
7月 06 20:15:17 maxscale maxscale[18141]: Using up to 543.91MiB of memory for query classifier cache
7月 06 20:15:20 maxscale systemd[1]: Started MariaDB MaxScale Database Proxy.

查看端口

 [root@maxscale ~]# ss -antlState     Recv-Q    Send-Q       Local Address:Port       Peer Address:Port   Process    LISTEN    0         4096             127.0.0.1:8989            0.0.0.0:*                 LISTEN    0         128                0.0.0.0:22              0.0.0.0:*                 LISTEN    0         4096                     *:3306                  *:*                 LISTEN    0         128                   [::]:22                 [::]:*                 [root@maxscale ~]# ss -antlpState      Recv-Q     Send-Q          Local Address:Port           Peer Address:Port     Process                                                                                  LISTEN     0          4096                127.0.0.1:8989                0.0.0.0:*         users:(("maxscale",pid=15450,fd=19))                                                    LISTEN     0          128                   0.0.0.0:22                  0.0.0.0:*         users:(("sshd",pid=792,fd=3))                                                           LISTEN     0          4096                        *:3306                      *:*         users:(("maxscale",pid=15450,fd=27))                                                    LISTEN     0          128                      [::]:22                     [::]:*         users:(("sshd",pid=792,fd=4))

查看有哪些服务

 [root@maxscale ~]# maxctrl list services┌────────────────────┬────────────────┬─────────────┬───────────────────┬───────────────────────────┐│ Service            │ Router         │ Connections │ Total Connections │ Targets                   │├────────────────────┼────────────────┼─────────────┼───────────────────┼───────────────────────────┤│ Read-Write-Service │ readwritesplit │ 0           │ 0                 │ server1, server2, server3 │└────────────────────┴────────────────┴─────────────┴───────────────────┴───────────────────────────┘

验证

检查路由服务状态

[root@maxscale maxscale]# maxctrl list services
[root@maxscale maxscale]# maxctrl list monitors

查看后台服务器有哪些

 [root@maxscale ~]# maxctrl list servers┌─────────┬─────────────────┬──────┬─────────────┬─────────────────┬──────┬───────────────┐│ Server  │ Address         │ Port │ Connections │ State           │ GTID │ Monitor       │├─────────┼─────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤│ server1 │ 192.168.166.25 │ 3306 │ 0           │ Master, Running │      │ MySQL-Monitor │├─────────┼─────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤│ server2 │ 192.168.166.27 │ 3306 │ 0           │ Slave, Running  │      │ MySQL-Monitor │├─────────┼─────────────────┼──────┼─────────────┼─────────────────┼──────┼───────────────┤│ server3 │ 192.168.166.26 │ 3306 │ 0           │ Slave, Running  │      │ MySQL-Monitor │└─────────┴─────────────────┴──────┴─────────────┴─────────────────┴──────┴───────────────┘

实现一主一从

验证主从同步状态

在 从服务器 上检查复制状态：

 SHOW SLAVE STATUS\G

关键检查项：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0（表示无延迟）
无错误信息（Last_IO_Error/Last_SQL_Error 为空）

1.测试数据同步

(1) 在主服务器上操作：

 -- 创建测试数据库CREATE DATABASE sync_test;USE sync_test;-- 创建测试表CREATE TABLE test_data (id INT AUTO_INCREMENT PRIMARY KEY,message VARCHAR(50),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);-- 插入测试数据INSERT INTO test_data (message) VALUES ('Data from Master');

(2) 在从服务器上验证同步：

 USE sync_test;SELECT * FROM test_data;  -- 应看到主库插入的数据

2.测试读写分离（通过MaxScale）

假设MaxScale的读写分离端口为 4000（需确认实际端口）：

 # 通过MaxScale写入（应路由到主库）mysql -h <MaxScale_IP> -P 4000 -u user -p -e "INSERT INTO sync_test.test_data (message) VALUES ('Via MaxScale')"# 通过MaxScale读取（应路由到从库）mysql -h <MaxScale_IP> -P 4000 -u user -p -e "SELECT * FROM sync_test.test_data"

预期结果：

写入操作在 主服务器 可见
读取结果包含所有数据（包括新插入的 Via MaxScale）

3.故障切换测试

(1) 模拟主库故障：

 # 停止主库MySQL服务（在server1执行）systemctl stop mysql

(2) 检查MaxScale状态：

 maxctrl list servers

预期变化：

server1 状态变为 Down
server2 自动提升为 Master

(3) 验证新主库写入：

 -- 通过MaxScale写入（应路由到新的主库server2）INSERT INTO sync_test.test_data (message) VALUES ('New Master Write');

(4) 恢复旧主库并检查同步：

 # 重启server1的MySQLsystemctl start mysql# 在server1上重新配置为从库CHANGE MASTER TOMASTER_HOST='192.168.72.129',  -- 指向新主库server2MASTER_PORT=3366,MASTER_USER='repl_user',MASTER_PASSWORD='password';START SLAVE;

关键命令总结

操作	命令
检查复制状态	`SHOW SLAVE STATUS\G`
强制切换主库	`maxctrl call command mariadbmon failover MySQL-Monitor`
查看服务器状态	`maxctrl list servers`
验证数据同步	主库写入 → 从库查询

注意：实际测试前请确认：

MaxScale配置文件中定义的读写分离端口（[RW Split Router]）

数据库账号需有复制权限（主从同步）和远程访问权限（MaxScale连接）

测试

用客户机连接maxscale

 username：adminpassword：admin

会发现进行读操作时，是在slave的从数据库上执行；在进行写操作时，是在master主数据库上执行

总结：

MySQL主从复制与读写分离的核心价值可归纳为以下三点：

性能与扩展性提升
- 通过读写分离，将读请求分散到多个从库，显著降低主库压力，支撑高并发查询场景（如电商、社交平台）；
- 横向扩展从库数量可应对业务增长，避免单机硬件瓶颈。
高可用与数据安全
- 主库故障时，从库可快速切换为新主库（Failover），保障服务连续性；
- 从库作为实时数据备份，防止主库数据丢失。
架构灵活性与成本优化
- 支持异步复制（默认）、半同步复制（平衡安全与延迟）及混合复制模式（MIXED），适应不同业务一致性要求；
- 通过中间件（如MyCat、MySQL Router）实现透明路由，减少应用层改造成本。

需规避的风险与优化方向：