通過 主從復(fù)制(Master-Slave)的方式來同步數(shù)據(jù)，再通過讀寫分離(MySQL-Proxy)來提升數(shù)據(jù)庫的并發(fā)負(fù)載能力 朴爬。本文介紹mysql-proxy讀寫分離的實(shí)踐。
原文地址：代碼匯個(gè)人博客 http://www.codehui.net/info/65.html

我們先配置好mysql的主從復(fù)制橡淆，可以查看《mysql主從復(fù)制 簡單實(shí)踐》

mysql讀寫分離

測試的三臺(tái)服務(wù)器都是centos7.6

mysql-proxy：192.168.73.128
主服務(wù)器： 192.168.73.130
從服務(wù)器： 192.168.73.131

mysql-proxy配置

只需要配置mysql-proxy服務(wù)器就可以了

1召噩、下載 mysql-proxy

根據(jù)自己系統(tǒng)配置選擇適合的版本，下載地址：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

2逸爵、配置mysql-proxy具滴，創(chuàng)建主配置文件

tar zxvf 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 lua
cp share/doc/mysql-proxy/rw-splitting.lua ./lua #復(fù)制讀寫分離配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua #復(fù)制管理腳本
vi /etc/mysql-proxy.cnf   #創(chuàng)建配置文件

# 注意這在寫的時(shí)候把注釋取了 不然會(huì)報(bào)錯(cuò)的
[mysql-proxy]
user=root #運(yùn)行mysql-proxy用戶
admin-username=root #主從mysql共有的用戶
admin-password=123456 #用戶的密碼
proxy-address=192.168.73.128 #mysql-proxy運(yùn)行ip和端口，不加端口师倔，默認(rèn)4040
proxy-read-only-backend-addresses=192.168.73.131 #指定后端從slave讀取數(shù)據(jù)
proxy-backend-addresses=192.168.73.130 #指定后端主master寫入數(shù)據(jù)
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定讀寫分離配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理腳本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定義log日志級(jí)別构韵，由高到低分別有(error|warning|info|message|debug)
daemon=true    #以守護(hù)進(jìn)程方式運(yùn)行
keepalive=true #mysql-proxy崩潰時(shí)，嘗試重啟

保存退出！
chmod 660 /etc/mysql-porxy.cnf

3疲恢、修改讀寫分離配置文件

vi /usr/local/mysql-proxy/lua/rw-splitting.lua

if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1, #默認(rèn)超過4個(gè)連接數(shù)時(shí)凶朗，才開始讀寫分離，改為1
                max_idle_connections = 1, #默認(rèn)8显拳，改為1

                is_debug = false
        }
end

4棚愤、啟動(dòng)mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

看下面報(bào)錯(cuò)了

[root@localhost www]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
2019-02-20 14:51:30: (critical) Key file contains key 'daemon' which has value that cannot be interpreted.
2019-02-20 14:51:30: (message) Initiating shutdown, requested from mysql-proxy-cli.c:367
2019-02-20 14:51:30: (message) shutting down normally, exit code is: 1

在mysql論壇找到了解決辦法，/etc/mysql-proxy.cnf可能有非ASCII字符,刪掉所有注釋,啟動(dòng)成功了杂数。:tw-1f602:

[mysql-proxy]
user=root
admin-username=root
admin-password=123456
proxy-address=192.168.73.128
proxy-read-only-backend-addresses=192.168.73.131
proxy-backend-addresses=192.168.73.130
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=info
daemon=true
keepalive=true

查看啟動(dòng)情況宛畦，關(guān)閉可以使用killall -9 mysql-proxy

[root@localhost www]# netstat -tupln | grep 4000
tcp        0      0 192.168.73.128:4000     0.0.0.0:*               LISTEN      8061/mysql-proxy

5、測試讀寫分離

使用客戶端連接mysql-proxy

mysql -u root -h 192.168.73.128 -P 4000 -p123456

我們?cè)?code>mysql-proxy中插入了兩條數(shù)據(jù)揍移，并且可以查看數(shù)據(jù)次和。

mysql> use codehui;
Database changed

mysql> create table proxy (id INT(11),name VARCHAR(255));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into proxy values(001,'beijing');
Query OK, 1 row affected (0.02 sec)

mysql> insert into proxy values(002,'shanghai');
Query OK, 1 row affected (0.00 sec)

mysql> select * from proxy;
+------+----------+
| id   | name     |
+------+----------+
|    1 | beijing  |
|    2 | shanghai |
+------+----------+
2 rows in set (0.00 sec)

要怎么知道啟用了讀寫分離呢？我們進(jìn)入從服務(wù)器192.168.73.131那伐，修改id為2的數(shù)據(jù)name值為xian斯够。

mysql -u root -h 192.168.73.131 -p123456

mysql> use codehui;
Database changed

mysql> update proxy set name='xian' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from proxy;
+------+---------+
| id   | name    |
+------+---------+
|    1 | beijing |
|    2 | xian    |
+------+---------+
2 rows in set (0.00 sec)

然后登陸mysql-proxy，查看到的數(shù)據(jù)是從服務(wù)器192.168.73.131的喧锦，說明讀寫分離成功。

mysql -u root -h 192.168.73.128 -P 4000 -p123456

mysql> use codehui;
Database changed

mysql> select * from proxy;
+------+---------+
| id   | name    |
+------+---------+
|    1 | beijing |
|    2 | xian    |
+------+---------+
2 rows in set (0.00 sec)