实现mysql的读写分离(mysql-proxy)

mysql-proxy简介

MySQL读写分离是指让master处理写操作，让slave处理读操作，非常适用于读操作量比较大的场景，可减轻master的压力。

使用mysql-proxy实现mysql的读写分离，mysql-proxy实际上是作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。
　mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等
　MySQL Proxy就是这么一个中间层代理，简单的说，MySQL Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，
　　从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。
　　当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多 个proxy的连接参数即可

从图中可以看到，SQL语句并不直接进入到master数据库或者slave数据库，而是进入到proxy，然后proxy判断这条语句是有关写的语句（包括insert、update、delete）还是读语句（select），当是写语句的时候，那么proxy将向master所在的服务器发出请求，同理，如果是读语句的时候，proxy将向slave所在的服务器发出请求。
server1上建立用户
server2同步成功
通过编写lua脚本实现读写分离

[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# pwd
/usr/local/mysql-proxy/conf
[root@server3 conf]# vim ~./bash_profile    
		 10 PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server3 conf]# source ~/.bash_profile 
[root@server3 conf]# cd ..
[root@server3 mysql-proxy]# mkdir logs
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf
		  1 [mysql-proxy]
		  2 user=root   运行mysql-proxy用户
		  3 proxy-address=0.0.0.0:3306  
		  4 proxy-backend-addresses=172.25.44.1:3306 #指定后端主master写入数据
		  5 proxy-read-only-backend-addresses=172.25.44.2:3306  #指定后端从slave读取数据
		  6 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua    #指定读写分离配置文件位置
		  7 log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log    #日志位置
		  9 log-level=debug  #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
		  10 daemon=true  #以守护进程方式运行
		  11 keepalive=true   #mysql-proxy崩溃时，尝试重启
[root@server3 logs]# cd /usr/local/mysql-proxy/share/doc/mysql-proxy/
[root@server3 conf]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf                                           
[root@server3 logs]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
	2018-08-14 10:23:06: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)
	2018-08-14 10:23:06: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
	2018-08-14 10:23:06: (message) shutting down normally, exit code is: 1
[root@server3 logs]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 logs]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 conf]# ps ax
		1555 ?        Ss     0:00 sshd: root@pts/1 
	 1557 pts/1    Ss+    0:00 -bash
		 1617 pts/0    R+     0:00 ps ax
[root@server3 conf]# /etc/init.d/mysqld stop
		Stopping mysqld:                                           [  OK  ]

[root@server3 conf]# ps ax
		 1662 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults
		 1663 ?        S      0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults
		 1664 pts/0    R+     0:00 ps ax
[root@server3 conf]# netstat -antlp
		tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1663/mysql-proxy   
	



[root@foundation44 ~]# mysql -h 172.25.44.3 -u root -p  #远程登陆测试,如果登陆不上，就在master端进行授权

MySQL [(none)]> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [westos]> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| userq    | 111      |
| user2    | 222      |
| user3    | 333      |
| user5    | 555      |
+----------+----------+
4 rows in set (0.00 sec)

[root@server1 ~]# mysql -pTing@666
mysql> insert into userlist values('user6','666');
	Query OK, 1 row affected (0.09 sec)

[root@foundation44 ~]# mysql -h 172.25.44.3 -u root -p 
MySQL [westos]> select * from userlist; #数据同步成功
+----------+----------+
| username | password |
+----------+----------+
| userq    | 111      |
| user2    | 222      |
| user3    | 333      |
| user5    | 555      |
| user6    | 666      |
+----------+----------+
5 rows in set (0.00 sec)



#在master上进行监控
[root@server1 ~]# yum install -y lsof
[root@server1 ~]# lsof -i :3306
	COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
	mysqld  1248 mysql   18u  IPv6   8560      0t0  TCP *:mysql (LISTEN)
	mysqld  1248 mysql   48u  IPv6   9242      0t0  TCP server1:mysql->server3:44918 (ESTABLISHED)
	mysqld  1248 mysql   50u  IPv6   9089      0t0  TCP server1:mysql->server2:37402 (ESTABLISHED)


在master上插入数据，多次远程连接，就会抓到
mysql> use westos;
mysql> insert into userlist values('user7','777');
Query OK, 1 row affected (0.08 sec)
mysql> insert into userlist values('user8','888');
Query OK, 1 row affected (0.17 sec)

[root@server1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
		mysqld  1248 mysql   18u  IPv6   8560      0t0  TCP *:mysql (LISTEN)
		mysqld  1248 mysql   48u  IPv6   9242      0t0  TCP server1:mysql->server3:44918 (ESTABLISHED)
		mysqld  1248 mysql   49u  IPv6   9909      0t0  TCP server1:mysql->server3:44919 (ESTABLISHED)
		mysqld  1248 mysql   50u  IPv6   9089      0t0  TCP server1:mysql->server2:37402 (ESTABLISHED)
		mysqld  1248 mysql   52u  IPv6  10300      0t0  TCP server1:mysql->server3:44922 (ESTABLISHED)


#自动抓包
[root@server3 mysql-proxy]#     server default db: westos
	    client default db: 
	    syncronizing
	    server default db: westos
	    client default db: 
	    syncronizing
	    server default db: westos
	    client default db: 
	    syncronizing

[root@server3 conf]# pwd
		/usr/local/mysql-proxy/conf
[root@server3 conf]# vim mysql-proxy.conf

[root@server3 mysql-proxy]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  --plugins=admin --plugins=proxy
[root@server3 mysql-proxy]# netstat -antlp  #会出现4041端口
		tcp        0      0 0.0.0.0:4041                0.0.0.0:*                   LISTEN      1753/mysql-proxy    
		tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      1753/mysql-proxy 


#如果端口没有出现，执行以下操作：
[root@server3 mysql-proxy]# killall mysql-proxy
#重新刷新
[root@server3 mysql-proxy]# mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  --plugins=admin --plugins=proxy


#远程登陆4041,，4041为管理员端口，3306为mysql-proxy与进行读写分离的两台mysql的主机进行直接通信的端口，如果使用mysql-proxy的默认端口，则必须在登陆时在后面加上 -P 4040

#检测读写分离

[root@server3 mysql-proxy]# vim rw-splitting.lua    #改变连接数，方便查看实验效果

再次刷新连接，查看监控就会发现连接到slave端
读的数据也是slave端的数据
但是写入数据时就会写在master端