目录标题
实验
server1:主库 server2:从库 server3:mysql代理(mysql-proxy)
server3:mysql代理
1.
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
[root@server3 ~]# cd /usr/local/
[root@server3 local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-proxy-0.8.5-linux-el6-x86-64bit share
[root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit mysql-proxy
[root@server3 local]# ls
bin include libexec sbin
etc lib mysql-proxy share
games lib64 mysql-proxy-0.8.5-linux-el6-x86-64bit src
[root@server3 local]# cd mysql-proxy
[root@server3 mysql-proxy]# ls
bin include lib libexec licenses share
[root@server3 mysql-proxy]# cd bin/
[root@server3 bin]# ls
mysql-binlog-dump mysql-myisam-dump mysql-proxy
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# ls
bin conf include lib libexec licenses share
2. [root@server3 mysql-proxy]# cd conf
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.60.253:3306
proxy-read-only-backend-addresses=172.25.60.2:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
plugins=proxy
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true
3. [root@server3 conf]# mkdir /usr/local/mysql-proxy/log/
4. [root@server3 conf]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 2,
5. [root@server3 conf]# chmod 600 /usr/local/mysql-proxy/conf/mysql-proxy.conf
6. [root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
7. 查看日志
[root@server3 conf]# cat /usr/local/mysql-proxy/log/mysql-proxy.log
2020-03-08 18:55:56: (message) proxy listening on port 0.0.0.0:3306
2020-03-08 18:55:56: (message) added read/write backend: 172.25.60.253:3306
2020-03-08 18:55:56: (message) added read-only backend: 172.25.60.2:3306
server1:
1. 创建一个新的用户
mysql> grant insert,update,select on *.* to jy@'%' identified by 'Westos+001';
Query OK, 0 rows affected, 1 warning (0.13 sec)
mysql> flush privileges; # 刷新
Query OK, 0 rows affected (0.13 sec)
server3:
1. [root@server3 mysql-proxy]# yum install lsof-4.87-4.el7.x86_64
在一台新的机器上开启3个
mysql -h172.25.60.3 -ujy -pWestos+001
2. [root@server3 mysql-proxy]# lsof -i:3306
mysql-pro 11815 root 17u IPv4 40027 0t0 TCP server3:mysql->172.25.60.252:43504 (ESTABLISHED)
mysql-pro 11815 root 18u IPv4 40028 0t0 TCP server3:40522->server1:mysql (ESTABLISHED)
mysql-pro 11815 root 19u IPv4 40031 0t0 TCP server3:mysql->172.25.60.252:43505 (ESTABLISHED)
mysql-pro 11815 root 20u IPv4 40032 0t0 TCP server3:56790->server2:mysql (ESTABLISHED) # 出现server2表明开启了读写分离
测试:
server2:
mysql> stop slave;
Query OK, 0 rows affected (0.13 sec)
测试机:插入数据之后读不到数据
server1:数据已经写入到了server1中
mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
| user3 | 789 |
| user4 | 111 |
| user-zz | 147 |
+----------+----------+
5 rows in set (0.00 sec)
server2:重新开启server2,可以读到插入的数据
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from userinfo;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
| user3 | 789 |
| user4 | 111 |
| user-zz | 147 |
+----------+----------+
5 rows in set (0.00 sec)
测试机: