mysql：基于主从复制的读写分离

---

实验

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)

测试机：