配置环境
master机:server1(172.25.8.1)
slave机:server2(172.25.8.2)
调度器:server3(172.25.8.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
<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.8.3:3306
4 proxy-read-only-backend-addresses=172.25.8.2:3306
5 proxy-backend-addresses=172.25.8.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
<4>启动mysql-proxy
[root@server3 mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf ##第一次启动失败,原因是因为配置文件权限过大
[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 wuyanzu@'172.25.8.%' identified by 'Wuyanzu+007';
Query OK, 0 rows affected, 1 warning (0.36 sec)
2、master机和slave机安装lsof,以便查看效果
[root@foundation8 ~]# mysql -h 172.25.8.3 -u wuyanzu -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.usertb values('test','23');
Query OK, 1 row affected (0.38 sec)
master机查看
mysql> select * from user.usertb;
+-------------+-----+
| username | age |
+-------------+-----+
| westos1 | 18 |
| test | 23 |
+-------------+-----+
2 rows in set (0.00 sec)