mysql-proxy读写分离配置

MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先部署主从复制，只有主从复制完了，才能在此基础上进行数据的读写分离。简单来说，读写分离就是只在主服务器上写，只在从服务器上读，基本的原理是让主数据库处理事务性查询，而从数据库处理select查询，数据库复制被用来把事务性查询导 致的改变更新同步到集群中的从数据库。

一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力 这样的方案来进行部署与实施的。

 

配置环境
master机：server1（172.25.254.1）
slave机：server2(172.25.254.2)
调度器：server3（172.25.254.3）

1、调度器配置

1、安装读写分离代理mysql-proxy

[[e-mail] ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[[e-mail] ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[[e-mail] ~]# cd /usr/local/
[[e-mail] local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[[e-mail] local]# ll
total 44
drwxr-xr-x. 2 root root  4096 Jun 28  2011 bin
drwxr-xr-x. 2 root root  4096 Jun 28  2011 etc
drwxr-xr-x. 2 root root  4096 Jun 28  2011 games
drwxr-xr-x. 2 root root  4096 Jun 28  2011 include
drwxr-xr-x. 2 root root  4096 Jun 28  2011 lib
drwxr-xr-x. 2 root root  4096 Jun 28  2011 lib64
drwxr-xr-x. 2 root root  4096 Jun 28  2011 libexec
lrwxrwxrwx  1 root root    38 Jul  7 16:56 mysql-proxy -> mysql-proxy-0.8.5-linux-el6-x86-64bit/
drwxr-xr-x  8 7161 wheel 4096 Aug 19  2014 mysql-proxy-0.8.5-linux-el6-x86-64bit
drwxr-xr-x. 2 root root  4096 Jun 28  2011 sbin
drwxr-xr-x. 5 root root  4096 Jun 19 23:38 share
drwxr-xr-x. 2 root root  4096 Jun 28  2011 src

 

2、为了测试读写分离效果，修改lua脚本

[[e-mail] local]# cd mysql-proxy/share/doc/mysql-proxy/
[[e-mail] mysql-proxy]# vim rw-splitting.lua 
 38 if not proxy.global.config.rwsplit then
 39         proxy.global.config.rwsplit = {
 40                 min_idle_connections = 1,
 41                 max_idle_connections = 2,  ##表示连接数超过两个就开始读写分离
 42 
 43                 is_debug = false
 44         }
 45 end

 

3、编写配置文件

[[e-mail] mysql-proxy]# pwd
/usr/local/mysql-proxy
[[e-mail] mysql-proxy]# mkdir logs
[[e-mail] mysql-proxy]# mkdir conf
[[e-mail] mysql-proxy]# vim conf/mysql-proxy.conf
  1 [mysql-proxy]
  2 user=root
  3 proxy-address=172.25.254.3:3306
  4 proxy-read-only-backend-addresses=172.25.254.2:3306
  5 proxy-backend-addresses=172.25.254.1:3306
  6 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.l    ua
  7 log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
  8 log-level=debug
  9 daemon=true
 10 keepalive=true

 

3、启动mysql-proxy

[[e-mail] mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  ##第一次启动失败，原因是因为配置文件权限过大
2018-07-07 17:13:41: (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-07-07 17:13:41: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2018-07-07 17:13:41: (message) shutting down normally, exit code is: 1
[[e-mail] mysql-proxy]# chmod 550 conf/mysql-proxy.conf 
[[e-mail] mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf   ##再次启动成功

 

测试：
1、master机授权用户可远程登陆：

mysql> grant select,insert,update on user.* to [e-mail]'172.25.254.%' identified by '[e-mail]';
Query OK, 0 rows affected, 1 warning (0.36 sec)

 

2、master机和slave机安装lsof，以便查看效果
3、物理机拿三个shell测试

[[e-mail] ~]# mysql -h 172.25.254.3 -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 

 

master机查看

[[e-mail] ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1168 mysql   34u  IPv6   8487      0t0  TCP *:mysql (LISTEN)
mysqld  1168 mysql   52u  IPv6   9264      0t0  TCP server1:mysql->server3:51667 (ESTABLISHED)
mysqld  1168 mysql   53u  IPv6   9165      0t0  TCP server1:mysql->server2:57130 (ESTABLISHED)
mysqld  1168 mysql   55u  IPv6   9265      0t0  TCP server1:mysql->server3:51668 (ESTABLISHED)

 

slave机查看

[[e-mail] ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1705 mysql   32u  IPv6   9424      0t0  TCP *:mysql (LISTEN)
mysqld  1705 mysql   48u  IPv6   9577      0t0  TCP server2:mysql->server3:60675 (ESTABLISHED)
mysqld  1705 mysql   50u  IPv4   9438      0t0  TCP server2:57130->server1:mysql (ESTABLISHED)

 

我们在连接slave机的客户端上写入数据，看是否写入到master机，如果写入，表示读写分离成功
连接slave机客户端写入

MySQL [user]> show tables;
+----------------+
| Tables_in_user |
+----------------+
| usertb         |
+----------------+
1 row in set (0.00 sec)

MySQL [user]> insert into user.usertn values('huge','23');
ERROR 1146 (42S02): Table 'user.usertn' doesn't exist
MySQL [user]> insert into user.usertb values('huge','23');
Query OK, 1 row affected (0.38 sec)

 

master机查看

mysql> select * from user.usertb;
+-------------+-----+
| username    | age |
+-------------+-----+
| wuyanzu     | 18  |
| chenguanxi  | 19  |
| zhoujielun  | 20  |
| liudehua    | 21  |
| zhangxueyou | 22  |
| huge        | 23  |
+-------------+-----+
6 rows in set (0.00 sec)

 

写入成功，读写分离效果实现