前言：
Mysql-proxy简介：
-
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 | 172.25.75.1 | master |
|---|---|---|
| server2 | 172.25.75.2 | slave |
| server3 | 172.25.75.3 | mysql-proxy |
前提：必须实现主从复制。
检测：
在server1上创建表及数据，查看server2是否同步成功
server1:
mysql> CREATE DATABASE redhat;
Query OK, 1 row affected (0.01 sec)
mysql> USE redhat;
Database changed
mysql> CREATE table usertb (
-> username varchar(10) not null,
-> password varchar(15) not null);
Query OK, 0 rows affected (0.14 sec)
mysql> DESC usertb;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(10) | NO | | NULL | |
| password | varchar(15) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into usertb values ('user1','123');
Query OK, 1 row affected (0.04 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
server2查看：
mysql> select * from usertb;
ERROR 1046 (3D000): No database selected
mysql> select * from redhat.usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
主从复制配置成功！！！
一、server3部署mysql-proxy服务
[root@server3 ~]# ls
mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[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]# ll
total 0
drwxr-xr-x. 2 root root 6 Mar 10 2016 bin
drwxr-xr-x. 2 root root 6 Mar 10 2016 etc
drwxr-xr-x. 2 root root 6 Mar 10 2016 games
drwxr-xr-x. 2 root root 6 Mar 10 2016 include
drwxr-xr-x. 2 root root 6 Mar 10 2016 lib
drwxr-xr-x. 2 root root 6 Mar 10 2016 lib64
drwxr-xr-x. 2 root root 6 Mar 10 2016 libexec
lrwxrwxrwx 1 root root 37 May 10 10:49 mysql-proxy -> mysql-proxy-0.8.5-linux-el6-x86-64bit
drwxr-xr-x 8 7161 wheel 87 Aug 19 2014 mysql-proxy-0.8.5-linux-el6-x86-64bit
drwxr-xr-x. 2 root root 6 Mar 10 2016 sbin
drwxr-xr-x. 5 root root 49 May 1 20:38 share
drwxr-xr-x. 2 root root 6 Mar 10 2016 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 bin]# cd ..
[root@server3 mysql-proxy]# ls
bin include lib libexec licenses share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# cd conf/
[root@server3 conf]# vim mysql-proxy.conf
[root@server3 conf]# cd ../share/doc/mysql-proxy/
[root@server3 mysql-proxy]# ls
active-queries.lua ro-balance.lua tutorial-resultset.lua
active-transactions.lua ro-pooling.lua tutorial-rewrite.lua
admin-sql.lua rw-splitting.lua tutorial-routing.lua
analyze-query.lua tutorial-basic.lua tutorial-scramble.lua
auditing.lua tutorial-constants.lua tutorial-states.lua
commit-obfuscator.lua tutorial-inject.lua tutorial-tokenize.lua
commit-obfuscator.msc tutorial-keepalive.lua tutorial-union.lua
COPYING tutorial-monitor.lua tutorial-warnings.lua
histogram.lua tutorial-packets.lua xtab.lua
load-multi.lua tutorial-prep-stmts.lua
README tutorial-query-time.lua
[root@server3 mysql-proxy]# vim rw-splitting.lua
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 2, #大于两个请求启动读写分离，方便测试
二、通过编写lua脚本实现读写分离
[root@server3 conf]# pwd
/usr/local/mysql-proxy/conf
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
proxy-address=0.0.0.0:3306
proxy-backend-addresses=172.25.75.1:3306 #指定后端主master写入数据
proxy-read-only-backend-addresses=172.25.75.2:3306 #指定后端从slave读取数据
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua #指定读写分离配置文件位置
pid-file=/usr/local/mysql-proxy/log/mysql-proxy.pid
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log #日志位置
plugins=proxy
log-level=debug #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启
[root@server3 conf]# mkdir /usr/local/mysql-proxy/log
三、测试启动mysql-proxy
[root@server3 bin]# pwd
/usr/local/mysql-proxy/bin
[root@server3 bin]# ls
mysql-binlog-dump mysql-myisam-dump mysql-proxy
[root@server3 bin]# chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 bin]# ./mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
[root@server3 bin]# cat /usr/local/mysql-proxy/log/mysql-proxy.log
2019-05-10 11:47:03: (message) chassis-unix-daemon.c:136: [angel] we try to keep PID=11472 alive
2019-05-10 11:47:03: (debug) chassis-unix-daemon.c:157: waiting for 11472
2019-05-10 11:47:03: (debug) chassis-unix-daemon.c:121: we are the child: 11472
2019-05-10 11:47:03: (critical) plugin proxy 0.8.5 started
2019-05-10 11:47:03: (debug) max open file-descriptors = 1024
2019-05-10 11:47:03: (message) proxy listening on port 0.0.0.0:3306
2019-05-10 11:47:03: (message) added read/write backend: 172.25.44.1:3306
2019-05-10 11:47:03: (message) added read-only backend: 172.25.44.2:3306
[root@server3 bin]# yum whatprovides /usr/bin/killall
[root@server3 bin]# yum install psmisc-22.20-11.el7.x86_64 -y
[root@server3 bin]# ps ax | grep mysql-proxy
11471 ? S 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
11472 ? S 0:00 /usr/local/mysql-proxy/libexec/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
11484 pts/0 R+ 0:00 grep --color=auto mysql-proxy
[root@server3 bin]# killall -9 mysql-proxy
[root@server3 bin]# ps ax | grep mysql-proxy
11487 pts/0 S+ 0:00 grep --color=auto mysql-proxy
四、启动mysql-proxy
server3：
[root@server3 bin]# ./mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
五、server1配置用户权限，测试
mysql> grant insert,update,select on *.* to zhao@'%' identified by 'Westos+001';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
添加数据测试：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| redhat |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use redhat;
Database changed
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
mysql> insert into usertb values ('user2','456');
Query OK, 1 row affected (0.02 sec)
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
+----------+----------+
2 rows in set (0.00 sec)
六、测试远程登录mysql-proxy服务端能否同步：
[root@foundation75 Desktop]# mysql -h 172.25.75.3 -uzhao -pWestos+001
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| redhat |
| sys |
+--------------------+
5 rows in set (0.00 sec)
MySQL [(none)]> use redhat;
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 [redhat]> select * from usertb; #数据同步成功
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
+----------+----------+
2 rows in set (0.00 sec)
七、测试是否读写成功分离
1、在客户端多开几个终端远程连接mysql-proxy
至少开三台才可以：
2、在server3监听3306端口
[root@server3 bin]# yum install lsof -y
[root@server3 bin]# lsof -i:3306
3、server2关闭异步复制，远程添加数据（可以看到实验效果）
server2：
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
在客户端远程登录mysql-proxy，添加数据
[kiosk@foundation75 Desktop]$ mysql -h 172.25.75.3 -uzhao -pWestos+001
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> use redhat;
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 [redhat]> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
+----------+----------+
2 rows in set (0.00 sec)
MySQL [redhat]> insert into usertb values ('user3','789');
Query OK, 1 row affected (0.02 sec)
MySQL [redhat]> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
+----------+----------+
2 rows in set (0.00 sec)
可以发现数据写入成功，但是查看不到。因为我们把主从复制关掉了，写在了server1里面，但是读的内容却是server2，server2没有同步server1的数据，故查看不到。
4、在server1查看数据是否添加成功：
mysql> select * from usertb;
可以看到数据确实添加到server1上面了
在server2查看数据：
mysql> select * from redhat.usertb;
server2确实没有数据。
这说明，读写成功分离！！！
扫一扫
1395
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
