MySQL主從復制與讀寫分離概述

前言：

????????在數據驅動的現代應用中，數據庫面臨高并發讀寫與海量存儲的雙重挑戰。單一數據庫實例在性能、可用性及擴展性上逐漸成為瓶頸。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 - GPL?Copyright (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 - GPL?Copyright (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）實現透明路由，減少應用層改造成本。

?需規避的風險與優化方向?：