欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql-proxy读写分离配置

程序员文章站 2022-05-02 10:03:31
...

MySQL的主从复制和MySQL的读写分离两者有着紧密联系,首先部署主从复制,只有主从复制完了,才能在此基础上进行数据的读写分离。简单来说,读写分离就是只在主服务器上写,只在从服务器上读,基本的原理是让主数据库处理事务性查询,而从数据库处理select查询,数据库复制被用来把事务性查询导 致的改变更新同步到集群中的从数据库。

一般来说都是通过 主从复制(Master-Slave)的方式来同步数据,再通过读写分离(MySQL-Proxy)来提升数据库的并发负载能力 这样的方案来进行部署与实施的。

mysql-proxy读写分离配置

 

配置环境
master机:server1(172.25.254.1)
slave机:server2(172.25.254.2)
调度器:server3(172.25.254.3)

1、调度器配置

1、安装读写分离代理mysql-proxy

[aaa@qq.com ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[aaa@qq.com ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[aaa@qq.com ~]# cd /usr/local/
[aaa@qq.com local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[aaa@qq.com local]# ll
total 44
drwxr-xr-x. 2 root root  4096 Jun 28  2011 bin
drwxr-xr-x. 2 root root  4096 Jun 28  2011 etc
drwxr-xr-x. 2 root root  4096 Jun 28  2011 games
drwxr-xr-x. 2 root root  4096 Jun 28  2011 include
drwxr-xr-x. 2 root root  4096 Jun 28  2011 lib
drwxr-xr-x. 2 root root  4096 Jun 28  2011 lib64
drwxr-xr-x. 2 root root  4096 Jun 28  2011 libexec
lrwxrwxrwx  1 root root    38 Jul  7 16:56 mysql-proxy -> mysql-proxy-0.8.5-linux-el6-x86-64bit/
drwxr-xr-x  8 7161 wheel 4096 Aug 19  2014 mysql-proxy-0.8.5-linux-el6-x86-64bit
drwxr-xr-x. 2 root root  4096 Jun 28  2011 sbin
drwxr-xr-x. 5 root root  4096 Jun 19 23:38 share
drwxr-xr-x. 2 root root  4096 Jun 28  2011 src

 

2、为了测试读写分离效果,修改lua脚本

[aaa@qq.com local]# cd mysql-proxy/share/doc/mysql-proxy/
[aaa@qq.com mysql-proxy]# vim rw-splitting.lua 
 38 if not proxy.global.config.rwsplit then
 39         proxy.global.config.rwsplit = {
 40                 min_idle_connections = 1,
 41                 max_idle_connections = 2,  ##表示连接数超过两个就开始读写分离
 42 
 43                 is_debug = false
 44         }
 45 end

 

3、编写配置文件

[aaa@qq.com mysql-proxy]# pwd
/usr/local/mysql-proxy
[aaa@qq.com mysql-proxy]# mkdir logs
[aaa@qq.com mysql-proxy]# mkdir conf
[aaa@qq.com mysql-proxy]# vim conf/mysql-proxy.conf
  1 [mysql-proxy]
  2 user=root
  3 proxy-address=172.25.254.3:3306
  4 proxy-read-only-backend-addresses=172.25.254.2:3306
  5 proxy-backend-addresses=172.25.254.1:3306
  6 proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.l    ua
  7 log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
  8 log-level=debug
  9 daemon=true
 10 keepalive=true

 

3、启动mysql-proxy

[aaa@qq.com mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf  ##第一次启动失败,原因是因为配置文件权限过大
2018-07-07 17:13:41: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)
2018-07-07 17:13:41: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2018-07-07 17:13:41: (message) shutting down normally, exit code is: 1
[aaa@qq.com mysql-proxy]# chmod 550 conf/mysql-proxy.conf 
[aaa@qq.com mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf   ##再次启动成功

 

测试:
1、master机授权用户可远程登陆:

mysql> grant select,insert,update on user.* to aaa@qq.com'172.25.254.%' identified by 'aaa@qq.com';
Query OK, 0 rows affected, 1 warning (0.36 sec)

 

2、master机和slave机安装lsof,以便查看效果
3、物理机拿三个shell测试

[aaa@qq.com ~]# mysql -h 172.25.254.3 -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 

 

master机查看

[aaa@qq.com ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1168 mysql   34u  IPv6   8487      0t0  TCP *:mysql (LISTEN)
mysqld  1168 mysql   52u  IPv6   9264      0t0  TCP server1:mysql->server3:51667 (ESTABLISHED)
mysqld  1168 mysql   53u  IPv6   9165      0t0  TCP server1:mysql->server2:57130 (ESTABLISHED)
mysqld  1168 mysql   55u  IPv6   9265      0t0  TCP server1:mysql->server3:51668 (ESTABLISHED)

 

slave机查看

[aaa@qq.com ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1705 mysql   32u  IPv6   9424      0t0  TCP *:mysql (LISTEN)
mysqld  1705 mysql   48u  IPv6   9577      0t0  TCP server2:mysql->server3:60675 (ESTABLISHED)
mysqld  1705 mysql   50u  IPv4   9438      0t0  TCP server2:57130->server1:mysql (ESTABLISHED)

 

我们在连接slave机的客户端上写入数据,看是否写入到master机,如果写入,表示读写分离成功
连接slave机客户端写入

MySQL [user]> show tables;
+----------------+
| Tables_in_user |
+----------------+
| usertb         |
+----------------+
1 row in set (0.00 sec)

MySQL [user]> insert into user.usertn values('huge','23');
ERROR 1146 (42S02): Table 'user.usertn' doesn't exist
MySQL [user]> insert into user.usertb values('huge','23');
Query OK, 1 row affected (0.38 sec)

 

master机查看

mysql> select * from user.usertb;
+-------------+-----+
| username    | age |
+-------------+-----+
| wuyanzu     | 18  |
| chenguanxi  | 19  |
| zhoujielun  | 20  |
| liudehua    | 21  |
| zhangxueyou | 22  |
| huge        | 23  |
+-------------+-----+
6 rows in set (0.00 sec)

 

写入成功,读写分离效果实现