【Tony 老師】基於 Maxscale 實現讀寫分離和負載均衡

來杯可樂不加糖發表於2019-09-25

​前言:

本文講述基於主從複製+MHA+Maxscale搭建MySQL叢集的搭建,實現主從切換故障轉移讀寫分離和負載均衡等功能。

一、簡介:

MaxScale是maridb開發的一個MySQL資料中介軟體,配置好MySQL的主從複製架構後,希望實現讀寫分離,把讀操作分散到從伺服器中,並且對多個伺服器實現負載均衡。

二、組成:

Authentication:認證外掛
​
Protocal :協議外掛
​
Routing:路由外掛 ->實現讀寫分離 + 負載均衡
​
readconnroute :讀負載均衡
​
readwritesplit :讀寫分離
​
Monitor:監控外掛
​
Filter&Logging:日誌和過濾外掛

【Tony老師】基於Maxscale實現讀寫分離和負載均衡

三、使用:

安裝:(建議安裝到單獨的伺服器中)

#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=default

3、操作maxscale

systemctl start   maxscale #啟動
systemctl stop    maxscale #停止
systemctl restart maxscale #重啟
systemctl status  maxscale #狀態
systemctl enable  maxscale #設定開機啟動
或
maxscale --config=/etc/maxscale.cnf

4、檢視啟動

ps -ef | grep maxscale #檢視程式
netstat -ntelp #檢視埠 6603

5、登入管理介面

maxadmin --user=admin --password=mariadb  #預設賬號密碼
或者
maxadmin -S /var/run/maxscale/maxadmin.sock
​
#檢視伺服器資訊列表
list servers
#檢視更多maxadmin命令
help

【Tony老師】基於Maxscale實現讀寫分離和負載均衡
6、驗證讀寫分離

#檢視模組db使用者
show service "Read-Write-Service"

【Tony老師】基於Maxscale實現讀寫分離和負載均衡

#驗證讀寫
#監控伺服器 登入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

【Tony老師】基於Maxscale實現讀寫分離和負載均衡
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的併發)

【Tony老師】基於Maxscale實現讀寫分離和負載均衡
【Tony老師】基於Maxscale實現讀寫分離和負載均衡
【Tony老師】基於Maxscale實現讀寫分離和負載均衡
叢集的(1000的併發)

【Tony老師】基於Maxscale實現讀寫分離和負載均衡
其它:

來杯可樂不加糖。

相關文章