MySQL讀寫分離 使用MySQL Router
規劃說明
之前我寫過如何配置MySQL的主從配置,當時是192.168.56.102:3306是主DB,192.168.56.103:3306是從DB。
本文中,我在102上安裝MySQL Router。
MySQL的安裝、主從配置可以看《CentOS6.8安裝MySQL5.7》、《MySQL5.7主從複製》
下載
wget https://cdn.mysql.com//Downloads/MySQL-Router/mysql-router-2.1.4-el6-x86-64bit.tar.gz
解壓
tar -xzvf mysql-router-2.1.4-el6-x86-64bit.tar.gz
移動
mv mysql-router-2.1.4-el6-x86-64bit /usr/local/mysqlrouter
創建目錄
在/usr/local/mysqlrouter下創建logs、etc目錄
原目錄,新目錄
根據模板創建新的配置文件
cp /usr/local/mysqlrouter/share/doc/mysqlrouter/sample_mysqlrouter.conf /usr/local/mysqlrouter/etc/mysqlrouter.conf
修改配置
[DEFAULT]
logging_folder = /usr/local/mysqlrouter
plugin_folder = /usr/local/mysqlrouter/lib/mysqlrouter
config_folder = /usr/local/mysqlrouter/etc
runtime_folder = /usr/local/mysqlrouter/run
[logger]
level = INFO
[routing:read_write]
bind_address = 192.168.56.102 #router所在IP
bind_port = 7001 #寫端口
mode = read-write
destinations = 192.168.56.102:3306 #主DB的IP,有多個用,分隔
[routing:read_only]
bind_address = 192.168.56.102 #router所在IP
bind_port = 7002 #讀端口
mode = read-only
destinations = 192.168.56.103:3306 #從DB的IP,有多個用,分隔
[keepalive]
interval = 60
別忘了打開iptables的7001、7002端口。
啟動
進入bin目錄
./mysqlrouter -c /usr/local/mysqlrouter/etc/mysqlrouter.conf &
驗證
netstat -tunlp|grep mysqlrouter
這時候可以用MySQL客戶端連接192.168.56.102:7001、192.168.56.102:7002的MySQL,在7001上新建一條記錄,等於在192.168.56.102:3306上新建數據,會同步到103的DB裡,通過7002是能訪問的。