MySql 主從複製 mysql-proxy實現讀寫分離

1.安裝和配置Docker

伺服器版本
阿裡雲CentOS7.4

docker版本
18.06.0-ce

docker安裝步驟
https://docs.docker.com/install/linux/docker-ce/centos/#install-docker-ce-1

下載mysql鏡像

# docker pull mysql:5.7

啟動兩個mysql鏡像，分別映射3306和3316埠
主庫：3306埠
備庫：3316埠

# docker run -p 3306:3306 --name mysql3306 -v /opt/mysql/data/data3306:/var/lib/mysql  -v /opt/mysql/logs/logs3306:/logs -e MYSQL_ROOT_PASSWORD=xxxxxxxxxxxx -d mysql:5.7

# docker run -p 3316:3306 --name mysql3316 -v /opt/mysql/data/data3316:/var/lib/mysql  -v /opt/mysql/logs/logs3316:/logs -e MYSQL_ROOT_PASSWORD=xxxxxxxxxxxx -d mysql:5.7

2.配置主從複製

從主庫的docker容器中把預設的配置文件拷貝出來

# docker cp 37735c072370:/etc/mysql/mysql.conf.d/mysqld.cnf /opt/mysql/conf/conf3306

修改配置文件增加兩行

#vim mysqld.cnf 
[mysqld]
log-bin=mysql-bin #開啟二進位日誌
server-id=1 #設置server-id

將修改後的配置文件拷回容器里，並重啟容器

# docker cp /opt/mysql/conf/conf3306/mysqld.cnf 37735c072370:/etc/mysql/mysql.conf.d/
# docker restart 37735c072370

創建主從複製需要的mysql賬戶,並分配許可權

mysql>CREATE USER 'repl'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'repl123';
mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'xxx.xxx.xxx.xxx'

查看主庫狀態，記錄下File和Position

mysql>SHOW MASTER STATUS;

修改從庫配置文件

#vim mysqld.cnf 
[mysqld]
server-id=2 #設置server-id

同樣拷進從庫鏡像，並重啟容器

mysql中執行配置主庫的語句

mysql>change master to master_host='xxx.xxx.xxx.xxx', #Master 伺服器Ip
master_port=3306,
master_user='repl',
master_password='repl123', 
master_log_file='master-bin.000001',#Master伺服器產生的日誌
master_log_pos=2986;

mysql>start slave;

mysql>show slave status\G

如果slave_io_running和slave_sql_running都為yes，表示啟動同步成功

建立兩個客戶端連接並創建測試庫

主庫建立student表並插入一條記錄，可以看到，從庫自動同步了數據，配置成功

3.用mysql-proxy實現讀寫分離

下載mysql-proxy
https://downloads.mysql.com/archives/proxy/

解壓並配置mysql-proxy

# tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
# cd mysql-proxy-0.8.5-linux-el6-x86-64bit
# vim /etc/mysql-proxy.cnf
[mysql-proxy]
user=root #運行mysql-proxy用戶
admin-username=proxy #主從mysql共有的用戶
admin-password=proxy123 #共有用戶密碼
proxy-read-only-backend-addresses=xxx.xxx.xxx.xxx:3316 #備庫地址
proxy-backend-addresses=xxx.xxx.xxx.xxx:3306 #主庫地址
proxy-lua-script=/usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua #指定讀寫分離配置文件位置
admin-lua-script=/usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/admin-sql.lua #指定管理腳本
daemon=true #以守護進程方式運行
keepalive=true #mysql-proxy崩潰時，嘗試重啟

修改讀寫分離配置文件

# vim /usr/local/mysql-proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua
min_idle_connections = 1, #預設超過4個連接數時才開始讀寫分離，改為1
max_idle_connections = 1,

mysql-proxy的bin目錄下啟動mysql-proxy

# ./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

主庫和從庫分別創建用戶

mysql>GRANT ALL ON *.* TO 'proxy'@'xxx.xxx.xxx' identified by 'proxy123';

創建mysql-proxy的數據里連接（預設埠4040），可以看到之前驗證主從複製時簡歷的test庫和student表

 只修改從庫的數據，然後通過mysql-proxy查詢，可以看到讀取的數據是從庫修改過的數據，讀分離驗證成功。

主庫：

從庫：

mysql-proxy:

停止從庫的docker容器，通過mysql-proxy插入一條數據，主庫中刷新也有新增的數據，寫分離驗證成功。

mysql-proxy:

主庫：

從庫：

至此，mysql的主從複製已經通過mysql-proxy實現讀寫分離已實現。