Centos7配置MySQL读写分离

背景

• 82和83两台Mysql已经实现了主从复制。
• 即将使用mysql-proxy进行读写分离，配置192为proxy,写82读83。

mysql-proxy

• mysql-proxy是一个处于客户端和服务器之间的中间件，它可以检测，分析或改变它们的通信。简单说就是一个中间层代理，一个连接池，负责将前台应用的连接请求转发给后台的数据库，并且通过lua脚本，可以实现复杂的连接控制和过滤，从而实现读写分离和负载平衡。对于应用来说只需要连接到mysql-proxy的监听端口即可(默认4040)

下载mysql-proxy

https://downloads.mysql.com/archives/proxy/   根据系统选择

解压

1 2	tar -zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz /usr/local/mysql-proxy

新建文件夹

1 2	mkdir /usr/local/mysql-proxy/lua 创建脚本存放目录 mkdir /usr/local/mysql-proxy/logs 创建日志目录

复制配置文件

1 2	cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件 cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本

创建配置文件

1	vi /etc/mysql-proxy.cnf #创建配置文件

1 2 3 4 5 6 7 8 9 10 11 12 13

[mysql-proxy] user=root 运行mysql-proxy用户 admin-username=proxy 主从mysql共有的用户 admin-password=proxy 用户的密码 proxy-address=10.120.112.192:4040 mysql-proxy运行ip和端口，不加端口，默认4040 proxy-read-only-backend-addresses=10.120.114.83 指定后端从slave读取数据 proxy-backend-addresses=10.120.114.82 指定后端主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=debug 定义log日志级别，由高到低分别有(error|warning|info|message|debug) daemon=true 以守护进程方式运行 keepalive=true mysql-proxy崩溃时，尝试重启

修改读写分离配置

1	vi /usr/local/mysql-proxy/lua/rw-splitting.lua

1 2 3 4 5 6 7

if not proxy.global.config.rwsplit then proxy.global.config.rwsplit = { min_idle_connections = 1, #默认超过4个连接数时，才开始读写分离，改为1 max_idle_connections = 1, #默认8，改为1 is_debug = false } end

启动mysql-proxy

1 2 3 4 5

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf netstat -tupln | grep 4040 #已经启动 killall -9 mysql-proxy #关闭mysql-proxy使用

测试读写分离

创建Proxy用户

在主服务器创建Proxy用户用户mysql-proxy使用，从服务器也会同步这个操作

1	grant all on *.* to 'proxy'@10.120.112.192' identified by 'proxy';

连接mysql-proxy

使用客户端连接代理

1	mysql -u proxy -h 10.120.112.192 -P 4040 -p proxy;

测试思路

测试写

关闭83从服务器的slave服务，在proxy192上执行insert操作，发现新增的数据都在82主服务器上。

测试读

在proxy192上执行select语句，发现查出来的数据并不包括刚才insert的数据，说明读的是83从服务器上的数据。

切换读库引擎

• 读库默认引擎是InnoDB，修改为MyISAM会使查询速度变快。

• 修改从服务器/etc/my.cnf

  1 2 3 4 5 6 7

  [mysqld] default-storage-engine=MyISAM 创建新表时将使用的默认存储引擎 key_buffer_size=128M myisam_sort_buffer_size=256M myisam_max_sort_file_size=5G myisam_repair_threads=1 myisam_recover

• 批量修改引擎

乱码问题

通过mysql-proxy读写分离查询的时候，可能会出现乱码。原因是当我们使用mysql-proxy读写分离时，客户端发出查询请求前一般会先发送一条’set names gbk’的语句来声明客户端编码，然后再发出实际查询的SQL语句，若数据库编码和客户端发出的不一致则会乱码。

解决方法是强行执行后端读服务器的字符编码，编辑 /etc/my.cnf文件：

1 2 3 4

[mysqld] init-connect='SET NAMES UTF8' skip-character-set-client-handshake character-set-server=utf8