8，mysql读写分离

方法一：MariaDB MaxScale

---

参考资料：https://mariadb.com/kb/en/mariadb-maxscale-24-setting-up-mariadb-maxscale/

必要前提：数据库主从复制

主数据库（192.168.3.33）
从数据库（192.168.3.34）
调度服务-maxscale（192.168.3.31）

---

a，安装maxscale

安装包下载地址：https://downloads.mariadb.com/MaxScale/2.4-all-versions/

源码安装：https://mariadb.com/kb/en/mariadb-maxscale-24-building-mariadb-maxscale-from-source-code/

b，创建相关帐户，在主数据库服器上执行如下操作

CREATE USER 'maxscale'@'%' IDENTIFIED BY 'maxscale_pw';
GRANT SELECT ON mysql.user TO 'maxscale'@'%';
GRANT SELECT ON mysql.db TO 'maxscale'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%';
GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';
GRANT SELECT ON mysql.* TO 'maxscale'@'%';
GRANT REPLICATION CLIENT on *.* to 'maxscale'@'%';
GRANT SUPER,RELOAD on *.* to 'maxscale'@'%';
grant select,insert,update,delete on *.* to rain@'%' identified by 'mark';
flush privileges; 

c，修改配置文件 /etc/maxscale.cnf ，主要配置内容

[maxscale]
threads=auto

[server1]
type=server
address=192.168.3.33
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=192.168.3.34
port=3306
protocol=MariaDBBackend

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxscale
password=maxscale_pw
monitor_interval=2000

[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale
password=maxscale_pw

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=3306

d，启动和检查

systemctl start maxscale

sudo maxctrl list services

sudo maxctrl list servers

最后可以关闭从数据库slave后，客户端开启多个远程连接进行数据库读写测试。

mysql -h192.168.3.31 -urain -p

---

---

方法二：mysql-proxy

---

a，安装mysql-proxy

源码下载地址：https://downloads.mysql.com/archives/proxy/

wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
tar -zxf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy
cd /usr/local/mysql-proxy
mkdir conf    #创建配置文件目录
mkdir log    #创建日志文件目录

vim conf/mysql-proxy.conf    #编写配置文件

[mysql-proxy]
admin-username=rain
admin-password=mark
proxy-address=0.0.0.0:3306    #监听本机中所有IP的端口。
proxy-backend-addresses=192.168.3.33:3306    #进行写的数据库
proxy-read-only-backend-addresses=192.168.3.34:3306    #进行读的数据库
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
admin-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/admin-sql.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid    #pid文件
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log    #日志文件
plugins=proxy    #proxy插件
log-level=debug    #日志等级为debug
keepalive=true    #心跳检测
daemon=true    #守护进程

chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf

vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

min_idle_connections = 1,
max_idle_connections = 1,

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf

b，创建相关帐户，在主数据库服器上执行

grant select,insert,update,delete on *.* to rain@'%' identified by 'mark';
flush privileges; 

c，测试