mysql-proxy读写分离配置

最新推荐文章于 2021-07-14 19:40:42 发布

chao199512

最新推荐文章于 2021-07-14 19:40:42 发布

阅读量459

点赞数

分类专栏：运维 linux 文章标签： mysql 读写分离 mysql主从优化

本文链接：https://blog.csdn.net/chao199512/article/details/81634829

版权

运维同时被 2 个专栏收录

123 篇文章 13 订阅

订阅专栏

linux

82 篇文章 3 订阅

订阅专栏

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

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

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

1、调度器配置

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

[root@server3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@server3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[root@server3 ~]# cd /usr/local/
[root@server3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@server3 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脚本

[root@server3 local]# cd mysql-proxy/share/doc/mysql-proxy/
[root@server3 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、编写配置文件

[root@server3 mysql-proxy]# pwd
/usr/local/mysql-proxy
[root@server3 mysql-proxy]# mkdir logs
[root@server3 mysql-proxy]# mkdir conf
[root@server3 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

[root@server3 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
[root@server3 mysql-proxy]# chmod 550 conf/mysql-proxy.conf 
[root@server3 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 root@'172.25.254.%' identified by 'Chao@199512';
Query OK, 0 rows affected, 1 warning (0.36 sec)

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

[root@foundation77 ~]# 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机查看

[root@server1 ~]# 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机查看

[root@server2 ~]# 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)

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