mysql nfs读写分离_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

$_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

$_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 |

+-------------+--------------------+-------+------+------+-------------------+