实验准备:
Server1 (主库):172.25.55.1(安装好mysql)
Server2 (备库):172.25.55.2(安装好mysql)
Server3 (代理端):172.25.55.3
Client :172.25.55.250
一、建立主从复制
[root@server1 ~]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
mysql> grant replication slave on *.* to rep@'172.25.28.%' identified by 'Redhat_123';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> revoke replication slave on *.* from rep@'172.25.28.%';(写错之后删除)
Query OK, 0 rows affected (0.32 sec)
mysql> grant replication slave on *.* to rep@'172.25.55.%' identified by 'Redhat_123';
Query OK, 0 rows affected, 1 warning (0.07 sec)
Server2的:
[root@server2 ~]# vim /etc/my.cnf
server-id=2
二、配置代理端server3
安装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
[root@server3 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@server3 ~]# cd /usr/local/mysql-proxy/
[root@server3 mysql-proxy]# ls
bin include lib libexec licenses share
[root@server3 mysql-proxy]# mkdir conf
[root@server3 mysql-proxy]# mkdir logs
[root@server3 mysql-proxy]# vim ~/.bash_profile(添加环境变量)
10 PATH=$PATH:$HOME/bin:/usr/local/mysql-proxy/bin
[root@server3 mysql-proxy]# source ~/.bash_profile
编辑配置文件
[root@server3 ~]# cd /usr/local/mysql-proxy/conf/
[root@server3 conf]# vim mysql-proxy.conf
[mysql-proxy]
user=root(运行mysql-proxy进程的用户)
proxy-address=0.0.0.0:3306(监听本机的3306端口)
proxy-backend-addresses=172.25.55.1:3306(主从复制的主机ip)
proxy-read-only-backend-addresses=172.25.55.2:3306(主从复制的从及ip)
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua(脚本地址)
log-file=/usr/local/mysql-proxy/logs/mysql-proxy(proxy日志路径)
log-level=debug(日志级别)
daemon=true(打入后台)
keepalive=true(在mysql-proxy崩溃时尝试重启)
[root@server3 conf]# chmod 660 mysql-proxy.conf
[root@server3 conf]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/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
[root@server3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf (启动myysql-proxy)
[root@server3 conf]# netstat -tnlp(查看端口号)
主库授权
mysql> grant all on . to ‘root’@’%’ identified by ‘Redhat_123’;
客户端下载数据库
[root@foundation55 新7]# yum install mysql -y
三、测试:
客户端登录到172.25.55.3 mysql:[kiosk@foundation55 ~]$ mysql -h 172.25.55.3 -u root -pRedhat_123
MySQL [(none)]> create database hr;(创建数据库)
Query OK, 1 row affected (0.07 sec)
MySQL [hr]> create table name ( name varchar(20) not null );(创建表)
Query OK, 0 rows affected (0.33 sec)
MySQL [hr]> insert into name values ('hr');(插入数据)
Query OK, 1 row affected (0.12 sec)
此时只有一个客户端连接
从机关闭slave后,client在数据库中添加数据,从库查看不到数据,主库和client都可以查看到数据,说明此时并没有读写分离。
客户端插入数据:
主库可查看到插入的数据
从机看不到信息
客户端加两个客户端连接.(打开多个shell,登录mysql)
此时从机的slave关闭后,client在数据库中添加数据,只有主机有记录。从机和客户端都没有记录。
说明写入的库是主库,读取的库是从库。
客户端查看不到新插入的数据w1。
主库可以查看到数据
从机查看不到数据