maxscale mysql5.7_Maxscale实现Mysql读写分离

Maxscale实现Mysql读写分离：

注：

1、该工具只有一主，如主出了问题整体会有10s左右不可用(即使从会自动切为主)。

2、读设置的2台实际只生效了1台。

rpm -i maxscale-2.4.9-1.centos.7.x86_64.rpm

vi /etc/maxscale.cnf

[maxscale]

threads=auto

[server1]

type=server

address=10.0.1.3

port=3306

protocol=MariaDBBackend

[server2]

type=server

address=10.0.1.4

port=3306

protocol=MariaDBBackend

[server3]

type=server

address=10.0.1.5

port=3306

protocol=MariaDBBackend

[MariaDB-Monitor]

type=monitor

module=mariadbmon

servers=server1,server2,server3

user=aa

password=xxxxxx

monitor_interval=2000

[Select-Query-Filter]

type=filter

module=namedserverfilter

match01=^SELECT

#options=ignorecase

target01=server2,server3

[Read-Write-Service]

type=service

router=readwritesplit

servers=server1,server2,server3

user=aa

password=xxxxx

filters=Select-Query-Filter

connection_keepalive=300

[MaxAdmin-Service]

type=service

router=cli

[Read-Write-Listener]

type=listener

service=Read-Write-Service

protocol=MariaDBClient

port=3306

[MaxAdmin-Listener]

type=listener

service=MaxAdmin-Service

protocol=maxscaled

port=6603

:wq

注：

aa为mysql里设置的帐号

systemctl start maxscale

systemctl enable maxscale

验证：

读：

mysql -u bb -p'xxxx' -h 10.0.1.2 -P 3306 -e "select @@wsrep_node_name;"

写：

mysql -u bb -p'xxxxx' -h 10.0.1.2 -e "begin;insert ming.loadavg value(@@hostname,10,30);select @@hostname;commit;

mysql -u bb -p'xxxxx' -h 10.0.1.2 -e "select * from ming.loadavg;select @@hostname;"