基于MaxScale中間件的MySQL讀寫分離

概述

maxscale 基于keepalived的高可用，通過VIP提供服務

maxscale官網：https://mariadb.com/downloads/mariadb-tx/maxscale

maxscale文檔（比官網檢視友善）https://github.com/mariadb-corporation/MaxScale/tree/2.2/Documentation

mysql基于GTID模式的主從複制，可以在主庫故障後快速修複複制狀态。

故障切換示意圖

基于MaxScale中間件的MySQL讀寫分離

環境介紹

NAME	VERSION	IP	PORT	COMMENT
maxscale	2.2.6	172.16.10.114	4306,6603	4306為讀寫分離端口，6603為管理端口
master	5.6.39	172.16.10.114	3308	GTID複制
slave	5.6.39	172.16.10.114	3309	GTID複制

maxscale安裝配置

mysql GTID配置

開啟GTID需要在配置檔案中加入以下參數：

gtid_mode = ON

enforce_gtid_consistency = ON

log_slave_updates = ON

主從配置指令：

CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, MASTER_USER=user, MASTER_PASSWORD=password, MASTER_AUTO_POSITION=1;

注意：

開啟GTID的主從複制，在備份時，會自動加上GTID的相關資訊，通過備份進行恢複時，會報錯，其原因是備份中的GTID與執行個體執行的GTID沖突，同時也防止事件重複執行，解決些報錯有2種辦法：

法一，在備份時候添加參數--set-gtid-purged=OFF，不備份GTID相關資訊。

法二，在進行還原時，先進入執行個體執行reset master，再進行資料還原

開啟GTID以後，無法使用sql_slave_skip_counter跳過事務，跳過錯誤的方法為：

set grid_next='xxxxxxxx'; #待跳過的GTID

begin;

commit; #通過産生一個空事務來占據此GTID

change master to master_auto_position=1;

MaxScale安裝配置

wget -c https://downloads.mariadb.com/MaxScale/2.2.6/rhel/6/x86_64/maxscale-2.2.6-1.rhel.6.x86_64.rpm

yum install maxscale-2.2.6-1.rhel.6.x86_64.rpm

cat /etc/maxscale.cnf

[maxscale]

threads=8 #線程配置，預設為1

auth_connect_timeout=3600

auth_read_timeout=3600

auth_write_timeout=3600

[server1] #配置後端伺服器

type=server

address=172.16.10.114

port=3308

protocol=MySQLBackend

server_weight=1

[server2]

type=server

address=172.16.10.114

port=3309

protocol=MySQLBackend

server_weight=1

#[server3]

#type=server

#address=172.16.10.114

#port=3310

#protocol=MySQLBackend

#server_weight=1

[readwritesplit] #讀寫分離配置

type=service

router=readwritesplit

servers=server1,server2

user=connect

passwd=connect

weightby=server_weight

max_slave_replication_lag=10 #允許最大主從延遲，當主從延遲超過該值時，不再向從庫分發讀請求

[Read Service] #配置讀服務，雖然字面意思為讀服務，也可以執行DML,DDL等操作，取決于對使用者的授權，是以可以了解為連接配接服務

type=service

router=readconnroute

router_options=master

servers=server1,server2

user=connect

passwd=connect

weightby=server_weight

[MySQL Monitor] #監控配置

type=monitor

module=mariadbmon

servers=server1,server2

user=monitor

passwd=monitor

auto_failover=true #是否故障自動切換

auto_rejoin=true #故障執行個體恢複後自動加入叢集

detect_standalone_master=true #探測獨立的master,是否允許叢集中最後一個執行個體成為主庫

allow_cluster_recovery=false #是否允許叢集自動恢複

#failcount=3 #在叢集中最後一個執行個體成為主庫前檢查其它從庫是否存活的次數,預設為5

#monitor_interval=10000 #探測間隔，機關毫秒，預設2000

detect_stale_master=true #當叢集中隻剩下主或主從複制全出錯時，是否允許主提供服務

#detect_stale_slave=false

script=/tmp/reset_slave.sh #在下面的events發生時，執行的腳本

events=master_down #配置在發生什麼事件時，執行上面的腳本

[Splitter-Service] #配置讀寫分離監聽端口

type=listener

service=readwritesplit

protocol=MySQLClient

port=4306

[Read Listener] #配置讀服務監聽端口

type=listener

service=Read Service

protocol=MySQLClient

port=4307

[MaxAdmin Service] #配置管理服務

type=service

router=cli

[MaxAdmin Listener] #配置管理服務端口

type=listener

service=MaxAdmin Service

protocol=maxscaled

port=6603

上面的配置中涉及2個使用者，一個是連接配接資料庫的使用者，一個是監控使用者，其授權分别如下；

connect

CREATE USER 'connect'@'172.16.10.114' IDENTIFIED BY 'connect';

GRANT SELECT ON mysql.user TO 'connect'@'172.16.10.114';

GRANT SELECT ON mysql.db TO 'connect'@'172.16.10.114';

GRANT SELECT ON mysql.tables_priv TO 'connect'@'172.16.10.114';

GRANT SHOW DATABASES ON *.* TO 'connect'@'172.16.10.114';

monitor

CREATE USER 'monitor'@'172.16.10.114' IDENTIFIED BY 'monitor';

GRANT RELOAD, SUPER, REPLICATION CLIENT ON *.* to 'monitor'@'172.16.10.114';

注意：

如果配置maxscale的高可用，還需要配置使用者能通過另外一台機器到mysql庫的相關權限。
應用對資料庫的連接配接權限，因中間多了一層maxscale,需要同一使用者，同時允許應用IP,maxscale IP都可以連接配接到資料庫，并且密碼相同。舉例如下：

maxscale IP為172.16.10.114,應用IP 172.16.10.238,通路資料庫test,其授權為：

grant select,update,delete,insert on test.* to test_rw@'172.16.10.114' identified by 'test_rw';

grant select,update,delete,insert on test.* to test_rw@'172.16.10.238' identified by 'test_rw';

grant select on test.* to test_r@'172.16.10.114' identified by 'test_r';

grant select on test.* to test_r@'172.16.10.238' identified by 'test_r';

reset_salve.sh腳本内容，其目的主要是在master宕掉後，執行的腳本，清除從庫的複制資訊，使從庫可以提升為主庫，如果從庫有複制的相關資訊，其不能提升為主庫提供寫服務。

此腳本在主從切換後，需要修改連接配接IP為新從庫的IP

cat /tmp/reset_slave.sh

mysql -h127.0.0.1 -P3309 -uthunder -pthunder -Nse 'stop slave;reset slave all;'

啟動maxscale

/etc/init.d/maxscale start

管理maxscale

通過maxadmin指令，預設使用者名和密碼為admin/mariadb

maxadmin -h127.0.0.1 -P6603 -uadmin -p

也可能通過maxctrl指令通過API來管理，大緻指令相同，maxadmin屬于互動式，maxctrl屬于非互動

MaxScale> help

Available commands:

add:

add user - Add an administrative account for using maxadmin over the network

add readonly-user - Add a read-only account for using maxadmin over the network

add server - Add a new server to a service

remove:

remove user - Remove account for using maxadmin over the network

remove server - Remove a server from a service or a monitor

create:

create server - Create a new server

create listener - Create a new listener for a service

create monitor - Create a new monitor

destroy:

destroy server - Destroy a server

destroy listener - Destroy a listener

destroy monitor - Destroy a monitor

alter:

alter server - Alter server parameters

alter monitor - Alter monitor parameters

alter service - Alter service parameters

alter maxscale - Alter maxscale parameters

set:

set server - Set the status of a server

set pollsleep - Set poll sleep period

set nbpolls - Set non-blocking polls

set log_throttling - Set the log throttling configuration

clear:

clear server - Clear server status

disable:

disable log-priority - Disable a logging priority

disable sessionlog-priority - [Deprecated] Disable a logging priority for a particular session

disable root - Disable root access

disable syslog - Disable syslog logging

disable maxlog - Disable MaxScale logging

disable account - Disable Linux user

enable:

enable log-priority - Enable a logging priority

enable sessionlog-priority - [Deprecated] Enable a logging priority for a session

enable root - Enable root user access to a service

enable syslog - Enable syslog logging

enable maxlog - Enable MaxScale logging

enable account - Activate a Linux user account for administrative MaxAdmin use

enable readonly-account - Activate a Linux user account for read-only MaxAdmin use

flush:

flush log - Flush the content of a log file and reopen it

flush logs - Flush the content of a log file and reopen it

list:

list clients - List all the client connections to MaxScale

list dcbs - List all active connections within MaxScale

list filters - List all filters

list listeners - List all listeners

list modules - List all currently loaded modules

list monitors - List all monitors

list services - List all services

list servers - List all servers

list sessions - List all the active sessions within MaxScale

list threads - List the status of the polling threads in MaxScale

list commands - List registered commands

reload:

reload config - [Deprecated] Reload the configuration

reload dbusers - Reload the database users for a service

restart:

restart monitor - Restart a monitor

restart service - Restart a service

restart listener - Restart a listener

shutdown:

shutdown maxscale - Initiate a controlled shutdown of MaxScale

shutdown monitor - Stop a monitor

shutdown service - Stop a service

shutdown listener - Stop a listener

show:

show dcbs - Show all DCBs

show dbusers - [deprecated] Show user statistics

show authenticators - Show authenticator diagnostics for a service

show epoll - Show the polling system statistics

show eventstats - Show event queue statistics

show filter - Show filter details

show filters - Show all filters

show log_throttling - Show the current log throttling setting (count, window (ms), suppression (ms))

show modules - Show all currently loaded modules

show monitor - Show monitor details

show monitors - Show all monitors

show persistent - Show the persistent connection pool of a server

show server - Show server details

show servers - Show all servers

show serversjson - Show all servers in JSON

show services - Show all configured services in MaxScale

show service - Show a single service in MaxScale

show session - Show session details

show sessions - Show all active sessions in MaxScale

show tasks - Show all active housekeeper tasks in MaxScale

show threads - Show the status of the worker threads in MaxScale

show users - Show enabled Linux accounts

show version - Show the MaxScale version number

sync:

sync logs - Flush log files to disk

call:

call command - Call module command

ping:

ping workers - Ping Workers

Type `help COMMAND` to see details of each command.

Where commands require names as arguments and these names contain

whitespace either the \ character may be used to escape the whitespace

or the name may be enclosed in double quotes ".

檢視目前mysql執行個體狀态

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Master, Running

server2 | 172.16.10.114 | 3309 | 0 | Slave, Running

-------------------+-----------------+-------+-------------+--------------------

[[email protected] ~]# maxctrl list servers

┌─────────┬───────────────┬──────┬─────────────┬────

│ Server │ Address │ Port │ Connections │ State │ GTID │

├─────────┼───────────────┼──────┼─────────────┼────

│ server1 │ 172.16.10.114 │ 3308 │ 0 │ Master, Running │ │

├─────────┼───────────────┼──────┼─────────────┼────

│ server2 │ 172.16.10.114 │ 3309 │ 0 │ Slave, Running │ │

└─────────┴───────────────┴──────┴─────────────┴────

複制故障後狀态：

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Master, Running

server2 | 172.16.10.114 | 3309 | 0 | Running

-------------------+-----------------+-------+-------------+--------------------

解決主從複制問題

從庫當機恢複步驟：

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Master, Running

server2 | 172.16.10.114 | 3309 | 0 | Maintenance, Down

-------------------+-----------------+-------+-------------+--------------------

将從庫重新加到讀寫分離叢集中

啟動從庫

啟動複制start slave;

等主從追趕上後，在maxscale裡面執行

MaxScale> clear server server2 maintenance

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Master, Running

server2 | 172.16.10.114 | 3309 | 0 | Slave, Running

-------------------+-----------------+-------+-------------+--------------------

主庫當機後恢複步驟：

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Down

server2 | 172.16.10.114 | 3309 | 0 | Slave, Running

-------------------+-----------------+-------+-------------+--------------------

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Maintenance, Down

server2 | 172.16.10.114 | 3309 | 0 | Master, Running

-------------------+-----------------+-------+-------------+--------------------

切換時間約為10s, 為以下2個參數的乘積，最長時間不超過monitor_interval*(failcount+1)

#failcount=3 #在叢集中最後一個執行個體成為主庫前檢查其它從庫是否存活的次數,預設為5

#monitor_interval=10000 #探測間隔，機關毫秒，預設2000

此時從庫接替原主庫接受讀寫請求，恢複主從架構，原主庫将成為新的從庫，啟動執行個體，做主從複制：

change master to master_host='172.16.10.114', master_port=3309, master_user='repl', master_password='repl4slave', master_auto_position=1;

檢查複制狀态

show slave status;

将新從庫加入到讀寫分離叢集中：

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Maintenance, Down

server2 | 172.16.10.114 | 3309 | 0 | Master, Running

-------------------+-----------------+-------+-------------+--------------------

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Maintenance, Down

server2 | 172.16.10.114 | 3309 | 0 | Master, Running

-------------------+-----------------+-------+-------------+--------------------

MaxScale> clear server server1 maintenance

MaxScale> list servers

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

server1 | 172.16.10.114 | 3308 | 0 | Slave, Running

server2 | 172.16.10.114 | 3309 | 0 | Master, Running

-------------------+-----------------+-------+-------------+--------------------

keepalived安裝與配置

如不考慮maxscale的高可用，可忽略以下内容

INSTALL

#yum install keepalived

CONFIG ON MASTER

#cat /etc/keepalived/keepalived.conf

vrrp_script chk_myscript {

script "/opt/soft/is_maxscale_running.sh"

interval 2 # check every 2 seconds

fall 2 # require 2 failures for KO

rise 2 # require 2 successes for OK

}

vrrp_instance VI_1 {

state MASTER

interface em1

virtual_router_id 51

priority 150

advert_int 1

authentication {

auth_type PASS

auth_pass mypass

}

virtual_ipaddress {

192.168.1.13 #VIP

}

track_script {

chk_myscript

}

notify "/opt/soft/notify_script.sh"

}

CONFIG ON STANDBY

#cat /etc/keepalived/keepalived.conf

vrrp_script chk_myscript {

script "/opt/soft/is_maxscale_running.sh"

interval 2 # check every 2 seconds

fall 2 # require 2 failures for KO

rise 2 # require 2 successes for OK

}

vrrp_instance VI_1 {

state MASTER

interface em1

virtual_router_id 51

priority 100

advert_int 1

authentication {

auth_type PASS

auth_pass mypass

}

virtual_ipaddress {

192.168.1.13

}

track_script {

chk_myscript

}

notify "/opt/soft/notify_script.sh"

}

#cat is_maxscale_running.sh

#!/bin/bash

fileName="maxadmin_output.txt"

rm $fileName

timeout 2s maxadmin -h127.0.0.1 -uadmin -pmariadb list servers > $fileName

to_result=$?

if [ $to_result -ge 1 ]

then

echo Timed out or error, timeout returned $to_result

exit 3

else

echo MaxAdmin success, rval is $to_result

echo Checking maxadmin output sanity

grep1=$(grep server1 $fileName)

grep2=$(grep server2 $fileName)

if [ "$grep1" ] && [ "$grep2" ]

then

echo All is fine

exit 0

else

echo Something is wrong

exit 3

#cat notify_script.sh

#!/bin/bash

TYPE=$1

NAME=$2

STATE=$3

OUTFILE=./state.txt

touch $OUTFILE

case $STATE in

"MASTER") echo "Setting this MaxScale node to active mode" > $OUTFILE

maxctrl alter maxscale passive false

exit 0

;;

"BACKUP") echo "Setting this MaxScale node to passive mode" > $OUTFILE

maxctrl alter maxscale passive true

exit 0

;;

"FAULT") echo "MaxScale failed the status check." > $OUTFILE

maxctrl alter maxscale passive true

exit 0

;;

*) echo "Unknown state" > $OUTFILE

exit 1

;;

esac

啟動keepalived

/etc/init.d/keepalived start

分别停掉keepalived與maxscale，觀察VIP漂移

基于MaxScale中間件的MySQL讀寫分離