读写分离代理172.25.11.1
主 172.25.11.2
从库172.25.11.3
从库172.25.11.4
proxy端(代理端)
yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib*
下载 mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
https://downloads.mysql.com/archives/proxy/
tar -zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
cp mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy -rp
mkdir /usr/local/mysql-proxy/lua /usr/local/mysql-proxy/logs
分别创建脚本和日志存放目录
cp share/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本
vim conf/mysql-proxy.conf
[mysql-proxy]
user=root
admin-username=admin
admin-password=123456
proxy-address=172.25.11.1:4040
proxy-read-only-backend-addresses=172.25.11.3,172.25.11.4
proxy-backend-addresses=172.25.11.2
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
#admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua
log-file=/var/log/mysql-proxy.log
log-level=info
daemon=true
keepalive=true
chmod -R 775 /usr/local/mysql-proxy
chown -R mysql:mysql /usr/local/mysql-proxy
cd /usr/local/mysql-proxy
vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
source ~/.bash_profile
修改mysql-proxy的读写分离脚本的配置
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
修改为最小一个最大1个客户端连接可以实现读写分离
min_idle_connections = 1,
max_idle_connections = 1,
开启防火墙端口设置
echo '-A INPUT -m state --state NEW -m tcp -p tcp --dport 4040 -j ACCEPT' >> /etc/sysconfig/iptables
注：mysql-proxy的服务端口默认为4040
mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
netstat -antpl
create user root@'%' identified by 'Yakexi+007';
grant replication client on *.* to root@'%';
./mysql-proxy --help-proxy
[mysql-proxy]
proxy-address=0.0.0.0:3306 #也可以定义为任何网段的代理地址
grant insert,update,select on *.* to wxh@'%' identified by 'Yakexi+007';
create table usertb ( username varchar(10) not null, password varchar(15) not null);
客户端的测试
foundation:
mysql -uwxh -pYakexi_007 -h 172.25.11.4 --port=3306
create table usertb ( username varchar(10) not null, password varchar(15) not null);
insert into usertb values ('shabi','123');
lsof -i:3306
要安装 yum install mariadb -y
mysql -uroot -pYakexi_007 -h172.25.11.1 --port=4040
遇到的问题
[root@foundation11 ~]# mysql -uroot -pYakexi_007 -h172.25.11.1 --port=4040
ERROR 1105 (HY000): #07000MySQL Proxy Lua script failed to load. Check the error log.
在代理端检查脚本是否存在
[root@server1 mysql-proxy]# ll /usr/local/mysql-proxy/lua/rw-splitting.lua
ls: cannot access /usr/local/mysql-proxy/lua/rw-splitting.lua: No such file or directory
[root@server1 mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua
[root@server1 mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua ./lua
即可
测试：
写测试
创建数据库和表，这时的数据只写入主mysql，然后再同步从slave
关掉slave就不能写入
读测试
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| proxy_test |
| sys |
| te |
| test1 |
+--------------------+
7 rows in set (0.01 sec)
MySQL [(none)]> use test1;
MySQL [test1]> create table user (number INT(10),name VARCHAR(255));
Query OK, 0 rows affected (0.35 sec)
MySQL [test1]> insert into user values(01,'zhangsan');
Query OK, 1 row affected (0.20 sec)
MySQL [test1]> insert into user values(02,'lisi');
Query OK, 1 row affected (0.32 sec)
登陆主从mysql查看新写入的数据
登陆到mysql-proxy，查询数据，看出能正常查询
在刚才创建数据时，代理端会显示
这时出现了新的问题
[root@server1 ~]# mysql -uroot -pYakexi_007 -h172.25.11.1 --port=4040
mysql: [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 108
Server version: 5.7.24-log
Copyright (c) 2000, 2018, 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> show databases;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> use test1;
No connection. Trying to reconnect...
Connection id: 109
Current database: *** NONE ***
Database changed
这时客户端也出现了问题
ERROR 2013 (HY000): Lost connection to MySQL server during query
vim /etc/my.cnf
max_allowed_packet = 500M
重起mysql-proxy就可以
然后继续查看代理端的数据库发现错误
发现在代理端不能使用命令
mysql -uroot -pYakexi_007 -h172.25.11.1 --port=4040
然后关闭两个slave
如果客户端查询失败，则实现了读写分离。
在客户端
mysql -uroot -pYakexi_007 -h172.25.11.1 --port=4040
多开几个shell
发现问题，开了十几个shell仍然可以查询到。
解决：
其实并没有错 只需要lsof -i:3306查看数据走向即可
insert into usertb values ('shabi','123');
