前言：
本文講述基於主從複製+MHA+Maxscale搭建MySQL叢集的搭建，實現主從切換，故障轉移，讀寫分離和負載均衡等功能。
一、簡介：
MaxScale是maridb開發的一個MySQL資料中介軟體,配置好MySQL的主從複製架構後，希望實現讀寫分離，把讀操作分散到從伺服器中，並且對多個伺服器實現負載均衡。
二、組成：
Authentication：認證外掛 Protocal ：協議外掛 Routing：路由外掛 ->實現讀寫分離 + 負載均衡 readconnroute ：讀負載均衡 readwritesplit ：讀寫分離 Monitor：監控外掛 Filter&Logging：日誌和過濾外掛
三、使用：
安裝：(建議安裝到單獨的伺服器中)
#1、下載repo安裝包
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash
#2、安裝maxscale依賴
yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 -y
#3、安裝
yum install -y maxscale latest配置：
1、配置maxscale使用的資料庫賬號
#建立監控模組使用賬號
create user scalemon@'192.168.71.%' identified by '123456';
#開放監控許可權
grant replication slave,replication client on *.* to scalemon@'192.168.71.%';
#建立路由模組使用賬號
create user maxscale@'192.168.71.%' identified by '123456';
#開放只讀許可權
grant select on mysql.* to maxscale@'192.168.71.%';
#建立測試賬號
create user maxtest@'%' identified by '123456';
#授權
grant all privileges on *.* to maxtest@'%' identified by '123456' with grant option;
flush privileges;
#密碼加密
maxkeys #生成加密資料夾/var/lib/maxscale/
maxpasswd /var/lib/maxscale/ 123456 #生成加密檔案2、修改maxscale檔案
vim /etc/maxscale.cnf
# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md
# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md
[maxscale]
threads=auto
ms_timestamp=1
# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#
[server1]
type=server
address=192.168.71.244
port=3306
protocol=MySQLBackend
[server2]
type=server
address=192.168.71.220
port=3306
protocol=MySQLBackend
[server3]
type=server
address=192.168.71.223
port=3306
protocol=MySQLBackend
# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md
[MySQL-Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=scalemon
passwd=123456
monitor_interval=1000
#檢查複製延遲
detect_replication_lag=true
#當全部slave都不可用時，select查詢請求會轉發到master。
detect_stale_master=true
# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#
#注：此處配置讀寫，故只讀的配置刪除
# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md
# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=maxscale
passwd=123456
max_slave_connections=100%
max_slave_replication_lag=5
use_sql_variables_in=all
# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md
[MaxAdmin-Service]
type=service
router=cli
# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MySQLClient
port=4006
[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
socket=default3、操作maxscale
systemctl start maxscale #啟動
systemctl stop maxscale #停止
systemctl restart maxscale #重啟
systemctl status maxscale #狀態
systemctl enable maxscale #設定開機啟動
或
maxscale --config=/etc/maxscale.cnf4、檢視啟動
ps -ef | grep maxscale #檢視程式
netstat -ntelp #檢視埠 66035、登入管理介面
maxadmin --user=admin --password=mariadb #預設賬號密碼
或者
maxadmin -S /var/run/maxscale/maxadmin.sock
#檢視伺服器資訊列表
list servers
#檢視更多maxadmin命令
help
6、驗證讀寫分離
#檢視模組db使用者
show service "Read-Write-Service"
#驗證讀寫
#監控伺服器 登入MySQL
mysql -umaxtest -P 4006 -h 192.168.71.16 -p
#檢視hostname(若檢視到hostname為server2，可關閉server2的MySQL服務或stop slave 再次檢視hostname，即可觀察至不同)
mysql> select @@hostname;
+-------------------------+
| @@hostname |
+-------------------------+
| server3 |
+-------------------------+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@hostname;
+-------------------------+
| @@hostname |
+-------------------------+
| server1 |
+-------------------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@hostname;
+-------------------------+
| @@hostname |
+-------------------------+
| server3 |
+-------------------------+
1 row in set (0.00 sec)注：
linux 預設為localhost~，為區分hostname，故需修改主機名：
hostnamectl set-hostname server-master
vim /etc/hosts
在127.0.0.1後加上 server-master
重啟主機即可
本文設定：
master： server1
slave1： server2
slave2： server3
monitor：monitor
7、驗證負載均衡
(1)指令碼驗證
for i in `seq 1 10`; do mysql -P4006 -u[user] -p[passwd] -h192.168.71.16 -e "select @@hostname;" 2>/dev/null & done
[root@server1]# 執行上述命令
[root@server1]# +------------+
| @@hostname |
+------------+
| server2 |
+------------+
+------------+
| @@hostname |
+------------+
| server3 |
+------------+
+------------+
| @@hostname |
+------------+
| server3 |
+------------+
+------------+
| @@hostname |
+------------+
| server2 |
+------------+
+------------+
| @@hostname |
+------------+
| server2 |
+------------+
+------------+
| @@hostname |
+------------+
| server3 |
+------------+
+------------+
| @@hostname |
+------------+
| server2 |
+------------+
+------------+
| @@hostname |
+------------+
| server3 |
+------------+
+------------+
| @@hostname |
+------------+
| server2 |
+------------+
+------------+
| @@hostname |
+------------+
| server3 |
+------------+(2)mysqlslap 進行基準測試
mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=1000 --create-schema=sbtest -uroot -p[password]單點的(1000的併發)
叢集的(1000的併發)
其它：