MySql Proxy读写分离

MySql Proxy 读写分离

亲测可用

1 介绍

对于很多大型网站（pv值百万、千万）来说，在所处理的业务中，其中有70%的业务是查询（select）相关的业务操作，剩下的则是写（insert、update、delete，只要能对MySQL的数据造成更改的操作都叫写操作）操作。在使用负载均衡集群之后，可以很大程度的提升系统的整体性能，但是最终的数据处理的压力还是会落到MySQL数据库上，所有很有必要使用一些技术来提升MySQL的负载能力。（读写分离）

1.1 为什么要读写分离？

主从集群的问题：只有主对外工作，从不对外工作。主既要负责写操作，也要负责读操作。
对于主从集群来说，只是保证了数据的安全备份。

注意：MySQL的主从复制，只会保证主机对外提供服务，而从机是不对外提供服务的，只是在后台为主机进行备份。

1.2 演示物理结构

MySql master： 192.168.233.101:3306
MySql slave：192.168.233.100:3306
MySql proxy：192.168.233.101:4041

1.3注意

安装配置之前需要进行MySql主从复制的搭建，参考…

2 MySql proxy 下载与安装

2.1 下载

wget https://cdn.mysql.com/archives/mysql-proxy/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

2.2 解压

tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz 

3 MySql proxy配置与连接

3.1 创建mysql-proxy.cnf文件

• 注意：该文件必须手动创建，创建路径用户自己定义

[root@localhost config]# pwd
/home/mysql-proxy/mysql-proxy-0.8.5/config
[root@localhost config]# ls
mysql-proxy.cnf

• 配置项说明

# 运行mysql proxy的用户
user=root
# 主从mysql共有的用户
admin-username=root
# 主从mysql共有的用户密码
admin-password=123456
# mysql-proxy执行的ip地址
proxy-address=192.168.233.101:4040
# MySql主机连接地址.主机,负责写操作
proxy-backend-addresses=192.168.233.101
# MySql从机 负责读操作
proxy-read-only-backend-addresses=192.168.233.100
# lua脚本配置，路径根据安装路径自行查找
proxy-lua-script=/home/mysql-proxy/mysql-proxy-0.8.5/share/doc/mysql-proxy/rw-splitting.lua
admin-lua-script=/home/mysql-proxy/mysql-proxy-0.8.5/share/doc/mysql-proxy/admin-sql.lua
# 日志文件目录，路劲该用户自定义配置
log-file=/home/mysql-proxy/mysql-proxy-0.8.5/logs/mysql-proxy.log
# 日志级别
log-level=debug
# 开启守护进程启动
keepalive=true
# 打入后台
daemon=true

3.2 修改mysql-proxy.cnf文件的权限

chmod 660 mysql-proxy.cnf

3.3 修改rw-splitting.lua脚本

找到一下修改信息

--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
        		# 最小连接数 1
                min_idle_connections = 1,
                # 最大链接数 2, 超过则会读写分离
                max_idle_connections = 2,

                is_debug = false
        }
end

修改了两个参数，最小连接数和最大连接数，修改后，mysql-proxy的调度机制就变成了，如果连接的客户端，超过两台，就会执行读写分离策略。

3.4 MySql-proxy 启动与测试

• ./mysql-proxy --defaults-file=mysql-proxy.cnf配置文件的地址

[root@localhost bin]# ls
mysql-binlog-dump  mysql-myisam-dump  mysql-proxy
[root@localhost bin]# 
[root@localhost bin]# ./mysql-proxy --defaults-file=/home/mysql-proxy/mysql-proxy-0.8.5/config/mysql-proxy.cnf 

3.4.1 启动常错误

• Key file contains key ‘daemon’ which has value that cannot be interpreted.
  解决方案：mysql-proxy.cnf可能有非ASCII字符,清空重写或删掉所有注释,即可正常启动。

• network-socket.c:492: bind(192.168.1.234:3306) failed: Address already in use (98)
  解决方案：mysql-proxy.cnf的proxy绑定的ip地址，默认情况下会连接绑定ip地址的3306端口，如果服务器上已经有其他mysql程序占用3306端口就会出现上述错误，修改绑定端口后重启即可。

• proxy-plugin.c.1865: Cannot connect, all backends are down
  解决方案：可能是由于mysql-proxy.cnf配置文件绑定的后端的主节点和从节点ip地址不可达，修改IP地址后即可正常访问。

3.5 连接

与连接MySql客户端方式一致

# 语法
mysql -h xxx -p xxx -uroot -ppassword;

[root@localhost ~]#  mysql -uroot -p123456 -h192.168.233.101 -P4041
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.50-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

4 测试验证

4.1 安装lsof，其可显示系统打开的文件

yum install lsof -y

4.2 监听验证

1. 在MySql-proxy运行服务器执行以下指令监听

lsof -i:3306

2. 同时在其他服务器，通过MySql-proxy代理连接MySql，监听打印以下日志

[root@localhost ~]# lsof -i:3306
COMMAND    PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld    7110 mysql   15u  IPv6  37734      0t0  TCP *:mysql (LISTEN)
mysqld    7110 mysql   37u  IPv6  38004      0t0  TCP localhost.localdomain:mysql->192.168.233.100:59200 (ESTABLISHED)
mysqld    7110 mysql   39u  IPv6  40498      0t0  TCP localhost.localdomain:mysql->localhost.localdomain:44238 (ESTABLISHED)
mysqld    7110 mysql   44u  IPv6  40857      0t0  TCP localhost.localdomain:mysql->localhost.localdomain:44240 (ESTABLISHED)
mysql-pro 7366  root   13u  IPv4  40856      0t0  TCP localhost.localdomain:44240->localhost.localdomain:mysql (ESTABLISHED)
mysql-pro 7366  root   14u  IPv4  39771      0t0  TCP localhost.localdomain:56920->192.168.233.100:mysql (ESTABLISHED)
mysql-pro 7366  root   15u  IPv4  40497      0t0  TCP localhost.localdomain:44238->localhost.localdomain:mysql (ESTABLISHED)
#解释 说明当前的查询连接，指向的是192.168.233.100:mysql数据库文件
mysql-pro 7366  root   16u  IPv4  40245      0t0  TCP localhost.localdomain:56922->192.168.233.100:mysql (ESTABLISHED)

注意：连接指向读服务器，说明读写分离已生效，在进行一下的最后数据一致性验证

3. 插入一条语句，根据结果判断代理是否生效

mysql> insert user(id, name ,sex, age) value(8,8,8,8);
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+----+------+--------+------+
| id | name | sex    | age  |
+----+------+--------+------+
|  1 | 小a  | woman1 |    1 |
|  2 | 小b  | woman2 |   11 |
|  3 | 小c  | man3   |   10 |
|  4 | 4    | man4   |   12 |
|  5 | 2    | 2      |    2 |
|  6 | 6    | 6      |    6 |
|  7 | 7    | 7      |    7 |
|  8 | 8    | 8      |    8 |
+----+------+--------+------+
8 rows in set (0.01 sec)

说明MySql-proxy代理主从复制部署成功
4. 最后一步，进一步检查主从机的数据是否一致，检测步骤单独登录主从MySql查询。