mysql-proxy实现mysql的读写分离

下载地址：
https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
 
 
解压：
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 /usr/local/mysql-proxy
 
 
3.创建proxy账号
grant all on customerinfo.* to 'proxy'@'%' identified by '123';
 
 
4. 创建目录
cd /usr/local/mysql-proxy
mkdir lua  logs #创建脚本、日志 存放目录
 
cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本
 
4.编辑配置文件
 
[root@pmm-monitor logs]# vim /etc/mysql-proxy.cnf
 
[mysql-proxy]
user=root
admin-username=proxy
admin-password=proxy123
proxy-address=56.56.56.165:4000
proxy-read-only-backend-addresses=56.56.56.165:3307
proxy-backend-addresses=56.56.56.165:3306
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
daemon=true
keepalive=true
 
chmod 660 /etc/mysql-proxy.cnf
 
3、修改读写分离配置文件
 
vim /usr/local/mysql-proxy/lua/rw-splitting.lua
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
 
[root@manager bin]# vim /root/.bash_profile 
[root@manager bin]# source /root/.bash_profile
 
 
4、启动mysql-proxy
 
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
 
[root@pmm-monitor ~]# mysql -h56.56.56.16 -uproxy -p -P 4000
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5175
Server version: 5.7.20-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customerinfo       |
+--------------------+
2 rows in set (0.01 sec)
 
 
 
测试：
 
stop slave;
 
 
master:
root@localhost :customerinfo04:40:16>insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
 
root@localhost :customerinfo04:40:26>select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
 
slave : 由于slave stop，数据没过来
root@localhost :customerinfo04:41:19>stop slave;
Query OK, 0 rows affected (0.01 sec)
 
root@localhost :customerinfo04:41:22>select * from t1;
Empty set (0.00 sec)
 
 
proxy: 从库没数，可以看到查的事从库（参考http://www.wityx.com/database/）
 
Database changed
MySQL [customerinfo]> select * from t1;
Empty set (0.01 sec)
 
slave: 启动同步，数据过来
root@localhost :customerinfo04:45:20>start slave;
Query OK, 0 rows affected (0.01 sec)
 
root@localhost :customerinfo04:45:24>select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
 
 
 
proxy: 从库数据过来了
 
Database changed
MySQL [customerinfo]> select * from t1;
Empty set (0.01 sec)
 
[root@pmm-monitor ~]# mysql -h56.56.56.16 -uproxy -p -P 4000
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.20-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MySQL [(none)]> select * from t1;
ERROR 1046 (3D000): No database selected
MySQL [(none)]> use customerinfo
Database changed
MySQL [customerinfo]> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)