mysql读写分离

接上篇博客 Mysql读写分离 主要是用mysql-proxy，我理解的是只有主机可以写入，从机不能写入，如果从机可以写入的话，其他从机就不能做到同步。主机写入，其他从机用来同步主机的数据即可。

网上查询： MySQL-Proxy就是一个中间层代理，简单的说，MySQL-Proxy就是一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过使用lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说，MySQL-Proxy是完全透明的，应用则只需要连接到MySQL-Proxy的监听端口即可。当然，这样proxy机器可能成为单点失效，但完全可以使用多个proxy机器做为冗余，在应用服务器的连接池配置中配置到多个proxy的连接参数即可。 MySQL-Proxy更强大的一项功能是实现“读写分离”，基本原理是让主数据库处理事务性查询，让从库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从库。

实验过程： 1.添加代理用户： useradd -r mysql-proxy

2.安装mysql-proxy tar xvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/ cd /usr/local/ ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

3.环境变量的更改（让系统可以直接使用mysl） vim /root/.bash_profile

PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin

source /root/.bash_profile

注：此实验过程中和上述master相同下载安装mysql，启动服务

4.启动mysql-proxy服务 cd /usr/local/mysql-proxy

mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.25.78.3:3306" --proxy-read-only-backend-addresses="172.25.78.2:3306"

所接参数的意义：

--proxy-address=host:port ———— 代理服务监听的地址和端口； --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 ———— 进程文件名；

5.在主master上创建用来测试的帐号和密码

mysql> grant all on *.* to root@'172.25.78.%' identified by 'westos';（） Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

6.测试是否能通过代理端口4040连接到mysql-proxy

[root@master profile.d]# mysql -uroot -pwestos -h172.25.78.4 --port=4040（连接mysql-proxy） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.1.71-log Source distribution Copyright (c) 2000, 2013, 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> exit Bye 综上显示已经成功通过读写分离代理端口4040连接进mysql-proxy

7.mysql-proxy本身不会实现读写分离，主要依靠lua脚本实现，添加读写分离脚本，重启服务（在 mysql-proxy 的安装目录中有一个rw-splitting.lua 脚本，专门用来实现 读写分离，路径是/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua）

mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.25.78.3:3306" --proxy-read-only-backend-addresses="172.25.78.2:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua "

8.查看添加lua读写分离脚本之后是否成功。

[root@proxy mysql-proxy]# tail /var/log/mysql-proxy.log 2017-08-02 15:07:40: (message) proxy listening on port :4040 2017-08-02 15:07:40: (message) added read/write backend: 172.25.78.3:3306 2017-08-02 15:07:40: (message) added read-only backend: 172.25.78.2:3306 2017-08-02 15:24:48: (critical) plugin proxy 0.8.5 started 2017-08-02 15:24:48: (debug) max open file-descriptors = 1024 2017-08-02 15:24:48: (critical) network-socket.c:492: bind(0.0.0.0:4040) failed: Address already in use (98) 2017-08-02 15:24:48: (critical) chassis-mainloop.c:270: applying config of plugin proxy failed 2017-08-02 15:24:48: (critical) mysql-proxy-cli.c:599: Failure from chassis_mainloop. Shutting down. 2017-08-02 15:24:48: (message) Initiating shutdown, requested from mysql-proxy-cli.c:600 2017-08-02 15:24:48: (message) shutting down normally, exit code is: 1 [root@proxy mysql-proxy]# netstat -tunlp | grep 4040（添加脚本之后，代理端口启动成功） tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 1418/mysql-proxy

9.（来自网络）为mysql-proxy 提供一个管理接口，方便以后随时查看后端mysql 服务器的状态和访问类型，实现管理功能 下面为大家提供一个管理接口的脚本，同样也是一个LUA 脚本，建议跟 读写分离脚本放在同一目录。

# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua function set_error(errmsg) proxy.response = { type = proxy.MYSQLD_PACKET_ERR, errmsg = errmsg or "error" } end function read_query(packet) if packet:byte() ~= proxy.COM_QUERY then set_error("[admin] we only handle text-based queries (COM_QUERY)") return proxy.PROXY_SEND_RESULT end local query = packet:sub(2) local rows = { } local fields = { } if query:lower() == "select * from backends" then fields = { { name = "backend_ndx", type = proxy.MYSQL_TYPE_LONG }, { name = "address", type = proxy.MYSQL_TYPE_STRING }, { name = "state", type = proxy.MYSQL_TYPE_STRING }, { name = "type", type = proxy.MYSQL_TYPE_STRING }, { name = "uuid", type = proxy.MYSQL_TYPE_STRING }, { name = "connected_clients", type = proxy.MYSQL_TYPE_LONG }, } for i = 1, #proxy.global.backends do local states = { "unknown", "up", "down" } local types = { "unknown", "rw", "ro" } local b = proxy.global.backends[i] rows[#rows + 1] = { i, b.dst.name, -- configured backend address states[b.state + 1], -- the C-id is pushed down starting at 0 types[b.type + 1], -- the C-id is pushed down starting at 0 b.uuid, -- the MySQL Server's UUID if it is managed b.connected_clients -- currently connected clients } end elseif query:lower() == "select * from help" then fields = { { name = "command", type = proxy.MYSQL_TYPE_STRING }, { name = "description", type = proxy.MYSQL_TYPE_STRING }, } rows[#rows + 1] = { "SELECT * FROM help", "shows this help" } rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" } else set_error("use 'SELECT * FROM help' to see the supported commands") return proxy.PROXY_SEND_RESULT end proxy.response = { type = proxy.MYSQLD_PACKET_OK, resultset = { fields = fields, rows = rows } } return proxy.PROXY_SEND_RESULT end

10.重新启动 mysql-proxy ，添加以下启动选项 –plugins=admin ———— 在mysql-proxy启动时加载的插件； –admin-username=”admin” 运行mysql-proxy进程管理的用户； –admin-password=”admin” 密码 –admin-lua-script=”/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua” 插件使用的配置文件路径； 此时查看日志和端口发现多了一个端口4041 此端口就是刚添加的用于管理。

mysql-proxy --daemon --log-level=debug --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="172.25.78.3:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"

11.找个客户端验证管理接口功能，客户端除了mysql-proxy之外任何一个客户端都可以。记得使用管理端口4041 登陆，密码是admin mysql -uadmin -p -h172.25.78.4 –port=4041

12.登陆接口之后执行以下命令，此接口也仅能执行这一条命令，查看读写分离状态。 此时因为没有读写操作所以状态都是unknown。

mysql> select * from backends;

测试读写分离状态： 找任意一台提供mysql客户端连接的服务器测试。我们就暂且使用主mysql 测试。 在正常情况下不管你在哪台服务器上操作都会先通过mysql-proxy 然后在分发到各个读写mysql上。 mysql>mysql -uroot -p -h172.25.78.4 –port=4040 -e ‘select user from mysql.user;’ mysql>mysql -uroot -p -h172.25.78.4 –port=4040 -e ‘create database abc;’

14.此时你会看到 状态从unknown 变为 up。

mysql -uadmin -p -h172.25.78.4 –port=4041 mysql> select * from backends;