mysql established_使用mysql

使用mysql-proxy实现读写分离

一、Mysql-Proxy原理

Mysql-Proxy是一个处于你的client端和Mysql Server端之间的一个简单进程，它可以监测、分析和改变他们的通信。它使用灵活没有限制，常见的用途包括：负载平衡，故障、查询分析，查询过滤和修改等等。它一个中间层代理，简单的说，Mysql-Proxy就是一个连接池，负责将前台应用的请求转发给后台数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQL Proxy是完全透明的，应用则只需要连接到MySQL Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。

二、MySQL-Proxy安装配置

实现读写分离是有lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装lua。

1、解压到/usr/local/mysql-proxy，开始配置mysql-proxy

创建脚本存放目录

[[email protected] mysql-proxy]# mkdir lua

[[email protected] mysql-proxy]# mkdir logs

2、复制读写分离配置文档和管理脚本。

[[email protected] mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua

[[email protected] mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua ./lua

3、创建配置文档

[[email protected] mysql-proxy]# vim /etc/mysql-proxy.cnf

配置内容如下

[mysql-proxy]

daemon=true#守护进程方式

user=root#运行mysql-proxy用户

admin-username=mysqlproxy #主从mysql共有的用户

admin-password=123456 #用户的密码

proxy-address=192.168.1.105:4040#mysql-proxy运行ip和端口，不加端口，默认4040

proxy-read-only-backend-addresses=192.168.1.104#指定从slave读取数据

proxy-backend-addresses=192.168.1.103#指定主master写入数据

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

admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua#指定管理脚本

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

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

keepalive=true#mysql-proxy崩溃时，尝试重启

注意配置的时候不能有注释，坑爹的。

由于安全要求，必须将配置文档权限设为660(创建人可读写，同组人可读)，否则不允许启动。

4、配置读写分离

[[email protected] mysql-proxy]# vim lua/rw-splitting.lua

在这段代码做修改如下

-- connection pool

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 1，--4,  --默认是超过4个连接数才开始读写分离

max_idle_connections = 1，--8,默认为8，改为1

is_debug = false

}

end

三、启动测试

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

netstat -tupln | grep 4040 #已经启动

killall -9 mysql-proxy #关闭mysql-proxy使用

在配置文档中配置访问主从的用户，主从数据库中还没有，在主库创建用户名密码并赋予权限，从库也会相应创建。

mysql> grant all privileges on *.* to 'mysqlproxy'@'192.168.1.105' identified by '123456' with grant option;

mysql> flush privileges;

mysql> select user,host from user;

| mysqlproxy | 192.168.1.103         |

| slave      | 192.168.1.104         |

使用客户端连接MySQL-Proxy，进行测试，这里使用win端

C:UsersAdministrator>net start mysql

MySQL 服务正在启动 ........

MySQL 服务已经启动成功。

C:UsersAdministrator> mysql -u mysqlproxy -p123456 -h 192.168.1.105 -P 4040

mysql>

使用两个远程客户端连接MySQL-Proxy。

在主节点查看3306端口，总的有三个客户端连接主库：分别是slave，两个代理

[[email protected] mysql-proxy]# lsof -i :3306

COMMAND   PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

mysqld  16087 mysql   15u  IPv6 105964      0t0  TCP *:mysql (LISTEN)

mysqld  16087 mysql   42u  IPv6 106004      0t0  TCP 192.168.1.103:mysql->192.168.1.104:56687 (ESTABLISHED)

mysqld  16087 mysql   44u  IPv6 118888      0t0  TCP 192.168.1.103:mysql->192.168.1.105:60578 (ESTABLISHED)

mysqld  16087 mysql   76u  IPv6 119880      0t0  TCP 192.168.1.103:mysql->192.168.1.105:60592 (ESTABLISHED)

在slave节点查看3306端口，总的有二个客户端连接主库：分别是代理和主节点

[[email protected] ~]# lsof -i :3306

COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

mysqld  7775 mysql   13u  IPv6  25595      0t0  TCP *:mysql (LISTEN)

mysqld  7775 mysql   34u  IPv4  49633      0t0  TCP 192.168.1.104:56687->192.168.1.103:mysql (ESTABLISHED)

mysqld  7775 mysql   38u  IPv6  55458      0t0  TCP 192.168.1.104:mysql->192.168.1.105:50689 (ESTABLISHED)

在代理节点查看4040端口，总的有二个客户端连接主库：分别是远程连接代理的两个客户端

[[email protected] lua]# lsof -i :4040

COMMAND    PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

mysql-pro 3853 root   10u  IPv4  31717      0t0  TCP 192.168.1.105:yo-main (LISTEN)

mysql-pro 3853 root   11u  IPv4  31753      0t0  TCP 192.168.1.105:yo-main->192.168.1.1:56767 (ESTABLISHED)

mysql-pro 3853 root   13u  IPv4  33954      0t0  TCP 192.168.1.105:yo-main->192.168.1.1:57368 (ESTABLISHED)

在master和slave上开启日志实时监测sql执行测试。

查看日志配置是否打开

mysql> SHOW VARIABLES LIKE "general_log%"; SET GLOBAL general_log = 'ON';

+------------------+--------------------------------------------+

| Variable_name    | Value                                      |

+------------------+--------------------------------------------+

| general_log      | OFF                                        |

| general_log_file | /usr/local/mysql-5.6.31/data/localhost.log |

+------------------+--------------------------------------------+

打开日志

SET GLOBAL general_log = 'ON';

查看执行的sql日志

[[email protected] mysql-proxy]# tail -f /usr/local/mysql-5.6.31/data/localhost.log

/usr/local/mysql-5.6.31/bin/mysqld, Version: 5.6.31-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306  Unix socket: /tmp/mysql.sock

关闭从机上的slave功能

当远程执行mysql> select * from demo.user;时：

master  日志无变化

slave  变化：

190226  3:18:47           12 Query        select * from demo.user

当执行   insert into demo.user values(default,'欧阳询')

master 日志变化：7 Query        insert into demo.user values(default,'欧阳询')

slave无变化  并且在slave上也查不到这一条数据。

注意：测试读写分离需要在slave上关闭slave功能，如果开启的话slave日志会增加一条信息COMMIT /* implicit, from Xid_log_event */这个因为是主从开启的，主库新增，从库也跟着变化。