MySQL 读写分离测试

一、实验服务器

1、192.168.1.112 Nginx WEB前端
2、192.168.1.120 PHP-fpm+NFS文件共享
3、192.168.1.121 PHP-fpm
4、192.168.1.117 主数据库
5、192.168.1.118 从数据库

二、部署方法

1、下载MYSQL-Proxy软件，

root@localhost ~]# wget -c http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz

2、创建软件服务用户

[root@localhost ~]# useradd -s /sbin/nologin mysql-proxy   #创建mysql-proxy服务用户

3、解压至/usr/local/mysql-proxy

[root@localhost ~]# tar  zxvf  mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz  -C  /usr/local

4、重命名为mysql-proxy

[root@localhost ~]# mv  /usr/local/mysql-proxy-0.8.4-linux-el6-x86-64bit  /usr/local/mysql-proxy

5.启动MySQL-Proxy中间件

/usr/local/mysql-proxy/bin/mysql-proxy 
--daemon 
--log-level=debug 
--user=mysql-proxy 
--keepalive 
--log-file=/var/log/mysql-proxy.log --plugins="proxy" 
--proxy-backend-addresses="192.168.1.117:3306" 
--proxy-read-only-backend-addresses="192.168.1.118:3306" 
--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" 
--plugins=admin 
--admin-username="admin" 
--admin-password="admin" 
--admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

6.确认MySQL-Proxy是否启动

[root@localhost mysql-proxy]# ps -ef |grep mysql
root       7402      1  0 18:36 ?        00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins=proxy --proxy-backend-addresses=192.168.1.117:3306 --proxy-read-only-backend-addresses=192.168.1.118:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
mysql-p+   7403   7402  0 18:36 ?        00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins=proxy --proxy-backend-addresses=192.168.1.117:3306 --proxy-read-only-backend-addresses=192.168.1.118:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua --plugins=admin --admin-username=admin --admin-password=admin --admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
root       7445   7141  0 18:40 pts/0    00:00:00 grep --color=auto mysql

7.查看MySQL-Proxy服务端口

[root@localhost mysql-proxy]# netstat -tnl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 0.0.0.0:4040            0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:4041            0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:111             0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN     
tcp6       0      0 :::111                  :::*                    LISTEN     
tcp6       0      0 :::22                   :::*                    LISTEN     
tcp6       0      0 ::1:25                  :::*                    LISTEN

8.在192.168.1.118 登录查看MySQL-Proxy读写分离状态

[root@localhost ~]# mysql -h192.168.1.112 -uadmin -padmin -P 4041

mysql> select * from backends;   #查看MySQL-Proxy读写分离状态
+-------------+--------------------+---------+------+------+-------------------+
| backend_ndx | address            | state   | type | uuid | connected_clients |
+-------------+--------------------+---------+------+------+-------------------+
|           1 | 192.168.1.117:3306 | unknown | rw   | NULL |                 0 |
|           2 | 192.168.1.118:3306 | unknown | ro   | NULL |                 0 |
+-------------+--------------------+---------+------+------+-------------------+

9.修改网址数据库配置文件

#修改服务器本地网站配置文件
[root@localhost config]# pwd
/mnt/dz.jf.com/config
[root@localhost config]# vim config_global.php
<?php


$_config = array();

// ----------------------------  CONFIG DB  ----------------------------- //
$_config['db']['1']['dbhost'] = '192.168.1.16:4040';   #修改为MySQL-Proxy通讯地址
$_config['db']['1']['dbuser'] = 'discuz';
$_config['db']['1']['dbpw'] = 'gdl';
$_config['db']['1']['dbcharset'] = 'utf8';
$_config['db']['1']['pconnect'] = '0';
$_config['db']['1']['dbname'] = 'discuz';
$_config['db']['1']['tablepre'] = 'pre_';
$_config['db']['slave'] = '';
$_config['db']['common']['slave_except_table'] = '';
#修改php路径网站配置文件
[root@localhost config]# pwd
/data/html/dz.jf.com/config
[root@localhost config]# vim config_global.php
<?php


$_config = array();

// ----------------------------  CONFIG DB  ----------------------------- //
$_config['db']['1']['dbhost'] = '192.168.1.16:4040';   #修改为MySQL-Proxy通讯地址
$_config['db']['1']['dbuser'] = 'discuz';
$_config['db']['1']['dbpw'] = 'gdl';
$_config['db']['1']['dbcharset'] = 'utf8';
$_config['db']['1']['pconnect'] = '0';
$_config['db']['1']['dbname'] = 'discuz';
$_config['db']['1']['tablepre'] = 'pre_';
$_config['db']['slave'] = '';
$_config['db']['common']['slave_except_table'] = '';

10.在次查看读写分离状态

[root@localhost ~]# mysql -h192.168.1.112 -uadmin -padmin -P 4041

mysql> select * from backends;
+-------------+--------------------+-------+------+------+-------------------+
| backend_ndx | address            | state | type | uuid | connected_clients |
+-------------+--------------------+-------+------+------+-------------------+
|           1 | 192.168.1.117:3306 | up    | rw   | NULL |                 0 |
|           2 | 192.168.1.118:3306 | down  | ro   | NULL |                 0 |
+-------------+--------------------+-------+------+------+-------------------+

11.关闭防火墙，在次查看读写分离状态

因192.168.1.118 从数据库防火墙未关所以读写分离无法成功
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# mysql -h192.168.1.112 -uadmin -padmin -P 4041

mysql> select * from backends;
+-------------+--------------------+-------+------+------+-------------------+
| backend_ndx | address            | state | type | uuid | connected_clients |
+-------------+--------------------+-------+------+------+-------------------+
|           1 | 192.168.1.117:3306 | up    | rw   | NULL |                 0 |
|           2 | 192.168.1.118:3306 | up    | ro   | NULL |                 0 |
+-------------+--------------------+-------+------+------+-------------------+