MySQL c 代理_实现mysql的读写分离(mysql-proxy)____2

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 172.25.78.11 master

server2172.25.78.12slave

server3172.25.78.13mysql-proxy

前提：必须实现主从复制

server1上建立用户

server2同步成功

通过编写lua脚本实现读写分离

在slave上

[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

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

[mysql-proxy]

user=root 运行mysql-proxy用户

proxy-address=0.0.0.0:3306

proxy-backend-addresses=172.25.44.1:3306 #指定后端主master写入数据

proxy-read-only-backend-addresses=172.25.44.2:3306 #指定后端从slave读取数据

proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua #指定读写分离配置文件位置

log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置

log-level=debug #定义log日志级别，由高到低分别有(error|warning|info|message|debug)

daemon=true #以守护进程方式运行

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 # 远程登陆测试

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)

如果登陆不上，就在master端进行授权

在master端插入数据

[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

#远程登陆master和slave,必须成功登陆

#再次远程查看就会变成up

#检测读写分离

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

因为在proxy读写脚本设置的最大连接数是2，而现在已经达到最大连接数

再次刷新连接，查看监控就会发现连接到slave端

读的数据也是slave端的数据

但是写入数据时就会写在master端

在server1上可以查看到添加的数据

拓展

mysql-proxy --help

--help-all ：获取全部帮助信息；

--admin-address=host:port ：管理模块监听的地址和端口；

--proxy-backend-addresses=host:port ：后端mysql服务器的地址和端口；

--proxy-read-only-backend-addresses=host:port ：后端只读mysql服务器的地址和端口；

--proxy-lua-script=file_name ：完成mysql代理功能的Lua脚本；

--daemon ：以守护进程模式启动mysql-proxy；

--keepalive ：在mysql-proxy崩溃时尝试重启之；

--log-file=/path/to/log_file_name ：日志文件名称；

--log-level=level ：日志级别；

--log-use-syslog ：基于syslog记录日志；

--plugins=plugin：在mysql-proxy启动时加载的插件；

--user=user_name ：运行mysql-proxy进程的用户；

--defaults-file=/path/to/conf_file_name ： 默认使用的配置文件路径；其配置段使用[mysql-proxy]标识；

--proxy-skip-profiling ： 禁用profile；

--pid-file=/path/to/pid_file_name ：进程文件名；