mysql读写分离（mysql-proxy-0.85）

 

读写分离代理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');