继上篇搭建完mysql主从集群https://blog.csdn.net/eyeofeagle/article/details/102841995, 现在使用mysql-proxy实现读写分离

1,安装mysql-proxy

说明：“MySQL Proxy is not GA, and is not recommended for Production use”（mysql-proxy还不够稳定，不推荐生产环境使用) https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz #1, 下载tar包，解压 wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz [root@docker ~]# ls mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit bin include lib libexec licenses mysql-proxy.log share [root@docker ~]# cd mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin/ [root@docker bin]# ls mysql-binlog-dump mysql-myisam-dump mysql-proxy #2, 创建配置文件 [root@docker bin]# cat > mysql-proxy.cnf <<EOF [mysql-proxy] user=root admin-username=root admin-password=123456 #配置代理角色 ip: 端口 proxy-address=192.168.56.201:4040 #配置master角色的 ip: 端口 proxy-backend-addresses=172.20.0.2:3306 #配置slave角色的 ip: 端口 proxy-read-only-backend-addresses=172.20.0.3:3306,172.20.0.4:3306 #配置代理角色需要执行的脚本 proxy-lua-script=/root/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua log-file=/usr/local/mysql-proxy/mysql-proxy.log log-file=/root/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/mysql-proxy.log log-level=debug keepalive=true daemon=true EOF #3, 修改配置: (减小连接数, 方便测试） #修改 tar包解压厚的文件：/share/doc/mysql-proxy/rw-splitting.lua, 40和41行 if not proxy.global.config.rwsplit then proxy.global.config.rwsplit = { min_idle_connections =1 , # 4, max_idle_connections =2 , # 8, is_debug = false } end # 4, 启动mysql-proxy服务 ./mysql-proxy --defaults-file=mysql-proxy.cnf [root@docker ~]# ss -nltp |grep 404 LISTEN 0 128 192.168.56.201:4040 *:* users:(("mysql-proxy",pid=4260,fd=10)) #5, 在远程机器上，测试登陆 [root@eadage ~]# mysql -uroot -p123456 -hdocker -P4040 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.7.27-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | per | +----------------+ 1 rows in set (0.00 sec)

2, 从mysql-proxy写数据

mysql-proxy节点

[root@cent6_server ~]# cat > a.sh <<EOF #!/bin/bash #测试mysql读写分离和复制 #for i in {1..100} for i in $(seq 1 1000000) do echo $i sql=$(echo "\""insert into test.aa values"('name"$i"')\"") echo $sql echo mysql -uroot -p123456 -hdocker -P4040 -e $sql |sh #sleep 1 done EOF #执行插入数据的脚本，然后分别在master,slave节点查看复制状态 [root@cent6_server ~]# sh a.sh ........... "insert into test.aa values('name120')" 121 "insert into test.aa values('name121')" 122 "insert into test.aa values('name122')" 123 "insert into test.aa values('name123')" 124 "insert into test.aa values('name124')" 125 "insert into test.aa values('name125')" 126 "insert into test.aa values('name126')" 127 "insert into test.aa values('name127')" 128 "insert into test.aa values('name128')" ...........

master 节点

mysql -uroot -p123456 -hmysql1 mysql> show processlist \G *************************** 1. row *************************** Id: 4 User: test Host: 172.20.0.4:58294 db: NULL Command: Binlog Dump Time: 89341 State: Master has sent all binlog to slave; waiting for more updates Info: NULL *************************** 2. row *************************** Id: 8 User: test Host: 172.20.0.3:60310 db: NULL Command: Binlog Dump Time: 89003 State: Master has sent all binlog to slave; waiting for more updates Info: NULL *************************** 3. row *************************** Id: 13 User: root Host: 172.20.0.1:60586 db: NULL Command: Sleep Time: 0 State: Info: NULL *************************** 4. row *************************** Id: 14 User: root Host: 172.20.0.1:44764 db: test Command: Query Time: 0 State: query end Info: insert into test.aa values('name1656')

slave 节点

mysql> show processlist \G *************************** 1. row *************************** Id: 9 User: system user Host: db: NULL Command: Connect Time: 89011 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 10 User: system user Host: db: NULL Command: Connect Time: 0 State: Reading event from the relay log Info: NULL *************************** 3. row *************************** Id: 19 User: root Host: localhost db: test Command: Query Time: 0 State: starting Info: show processlist *************************** 4. row *************************** Id: 20 User: root Host: 172.20.0.1:45608 db: NULL Command: Sleep Time: 0 State: Info: NULL *************************** 5. row *************************** Id: 21 User: root Host: 172.20.0.1:47014 db: NULL Command: Sleep Time: 0 State: Info: NULL 5 rows in set (0.00 sec)

3, 从mysql-proxy读数据

mysql-proxy节点

[root@cent6_server ~]# cat ~/a.sh #!/bin/bash #测试mysql读写分离和复制 #for i in {1..100} #for i in $(seq 1 1000000) while true : do echo mysql -uroot -p123456 -hdocker -P4040 -e "'select * from test.aa'" |sh done

slave节点

.... *************************** 5. row *************************** Id: 21 User: root Host: 172.20.0.1:47014 db: NULL Command: Query Time: 0 State: Sending data Info: select * from test.aa