实验环境:

mysql-proxy服务端： xuegod1 IP：192.168.10.31

mysql服务器（主，负责写）服务端：xuegod2 IP：192.168.10.32

mysql服务器（从，负责读）客户端：xuegod3 IP：192.168.10.33

1 部署MYSQL-PROXY服务端xuegod1

1.1 安装mysql-proxy

安装前需要系统支持LUA语言环境:

[root@xuegod1

~]# yum install lua

安装mysql-proxy:

推荐采用已经编译好的二进制版本，因为采用源码包进行编译时，最新版的MySQL-Proxy对automake，glib以及libevent的版本都有很高的要求，而这些软件包都是系统的基础套件，不建议强行进行更新。

并且这些已经编译好的二进制版本在解压后都在统一的目录内，因此建议选择以下版本：

~]# wget

http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

~]# tar -xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/

local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy

1.2

修改系统环境变量

[root@xuegod1 ~]# vim /etc/profile

export

PATH=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin:$PATH

[root@xuegod1 ~]# source !$

source /etc/profile

mysql-proxy 脚本配置文件位置

[root@xuegod1 ~]# mv mysql-proxy

/usr/local/

[root@xuegod1 ~]# ls

/usr/local/mysql-proxy/share/doc/mysql-proxy/

1.3 修改配置文件实现读写分离

[root@xuegod1 ~]# vim

/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

将

min_idle_connections = 4,

max_idle_connections = 8,

修改为：

2 创建数据库和表，用于实现读操作

xuegod2和xuegod3安装mysql-community-server

5.6

1.1 在xuegod2上创建数据库和表

[root@xuegod2 ~]# mysql -uroot -p123456

mysql> create database db;

Query OK, 1 row affected (0.02 sec)

mysql> use db;

Database changed

mysql> create3 table test(id int);

ERROR 1064 (42000): You have an error in

your SQL syntax; check the manual that corresponds to your MySQL server version

for the right syntax to use near 'create3 table test(id int)' at line 1

mysql> create table test(id int);

Query OK, 0 rows affected (0.11 sec)

mysql> insert into test values(64);

mysql> grant all on db.* to user1@'%'

identified by "123456";

Query OK, 0 rows affected (0.00 sec)

1.2 在xuegod3上创建数据库和表

Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> insert into test values(65);

Query OK, 1 row affected (0.03 sec)

启动服务MYSQL-PROXY服务

[root@xuegod1 ~]# mysql-proxy

--proxy-read-only-backend-addresses=192.168.10.33:3306

--proxy-backend-addresses=192.168.10.32:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

[1] 1987

[root@xuegod1 ~]# 2017-05-10 07:02:13:

(critical) plugin proxy 0.8.5 started

3 启动服务MYSQL-PROXY服务

[root@xuegod1 ~]# mysql-proxy

--proxy-backend-addresses=192.168.10.32:3306

--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

[1] 1603

[root@xuegod1 ~]# 2017-05-10 20:11:42:

查看proxy是否启动：

[root@xuegod1 ~]# lsof -i :4040

COMMAND

PID USER FD TYPE DEVICE SIZE/OFF NODE NAME

mysql-pro 1603 root 9u

IPv4 12023 0t0

TCP *:yo-main (LISTEN)

4 测试读写分离

4.1 测试读操作

~]# mysql -uuser1 -p123456 -P4040 -h 192.168.10.31

mysql> use

db;

查询

mysql> select * from test;

+------+

| id

64 |

3 rows in set (0.00 sec)

再开一个客户端窗口登录mysql查询

65 |

1 row in set (0.01 sec)

两个窗口都查到xuegod3上的数据

##看不到xuegod3的数据，可以看到刚写入的数据，说明写操作成功。因为是第一个客端连接，还没有启动读写分离，所以select读时，没有看到xuegod3的数据，而是看到主上xuegod2的数据。

接下来，多打开几个客户端测试一下读。注：第一个链接，一定是走backend 主mysql服务器的。

4.2 测试写操作

插入一条数据66

mysql>

insert into test values(66);

Query OK, 1

row affected (0.00 sec)

查到的是xuegod2上的65

select * from test;

| id |

| 65 |

1 row in set

(0.00 sec)

这说明读写分离测试成功。但是数据还没有保持同步。

保持数据同步，可以通过mysql主从来实现。

4.3 查看客户端连接状态

mysql> show

processlist;

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

| Id |

| 1073 | | NULL |

| 1162 | | NULL |

| 0 | init | show processlist |

| 332 | | NULL |

4 rows in set

5 部署mysql主从并实现读写分离

在之前的基础上配置主从

5.1 配置XUEGOD2为MASTER

5.1.1 修改配置文件

在配置文件中添加如下几行：

[root@xuegod2

~]# vim /etc/my.cnf

log-bin=mysql-bin-master

#启用二进制日志

server-id=1 #本机数据库ID 标示

binlog-do-db=db

#可以被从服务器复制的库。二进制需要同步的数据库名

binlog-ignore-db=mysql

#不可以被从服务器复制的库

5.1.2 授权

~]# mysql -uroot -p123456;

grant replication slave on *.* to [email protected] identified by "123456";

5.1.3 删除test表，重启mysql服务

Reading table information for completion of

table and column names

You can turn off this feature to get a

quicker startup with -A

mysql> drop table test;

mysql> exit

Bye

[root@xuegod2 ~]# service mysqld restart

Stopping mysqld:

[ OK ]

Starting mysqld:

5.2 配置XUEGOD3为SLAVE

5.2.1 修改配置文件

在配置文件中添加如下配置：

[root@xuegod3 ~]# vim /etc/my.cnf

server-id=2

mysql 5.1版本需添加如下几行配置：

master-host=192.168.1.63 #指定主服务器IP地址

master-user=slave #指定定在主服务器上可以进行同步的用户名

master-password=123456 #密码

重启服务：

[root@xuegod3 ~]# service mysqld restart

5.2.2 修改slave的主

mysql>stop

slave;

mysql> change master to

master_host='192.168.10.32',master_user='slave',master_password='123456';

Query OK, 0 rows affected, 2 warnings (0.04

sec)

5.2.3 删除test表，启动slave

Query OK, 0 rows affected (0.03 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G

*************************** 1. row

***************************

Slave_IO_State: Waiting for

master to send event

Master_Host: 192.168.10.32

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:

mysql-bin-master.000003

Read_Master_Log_Pos: 120

Relay_Log_File:

mysqld-relay-bin.000002

Relay_Log_Pos: 290

Relay_Master_Log_File: mysql-bin-master.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 120

Relay_Log_Space: 464

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID:

b9f08209-350d-11e7-b224-000c2978dedd

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the

slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

6 主从同步测试

6.1 mysql主数据库xuegod2插入数据

mysql> create table admin(id int);

mysql> insert into admin values(2);

6.2 mysql主从数据库xuegod2/xuegod3上查看同步的数据

主服务器：

mysql> select * from admin;

2 |

从服务器：

6.3 测试

同步后，测试使用MYSQL-PROXY 能否读到同样的数据。

在其他测试机上测试：

[root@xuegod1 ~]# mysql -uuser1 -p123456

-P4040 -h 192.168.10.31 -e 'select * from db.admin;'

Warning: Using a password on the command

line interface can be insecure.

停掉从服务器，stop slave 测试插入数据看看读写分离

#可以查看到admin中的63记录，说明mysql+proxy+主从读写分离成功。

插入数据测试：

mysql> stop slave;

mysql> insert into admin values(3);

3 |

2 rows in set (0.00 sec)

在xuegod1和xuegod2上查看都有

当我们SLAVE挂掉后咋样呢

设立mysqlproxy+主主，查看一个宕掉的情况

模拟故障：从服务器挂掉了

[root@xuegod3

~]# service mysqld stop

在mysql-proxy上测试读写

mysql> insert into admin values(4);

4 |

xuegod2上查看连接状态，确认关闭slave后，读写都是访问xuegod2

总结：当停止掉 slave 数据库，proxy 的查询就会转移到 master 上，当把 slave 启动后，proxy 依然在读 master，当有新的链接进来的时候才会重新去读取

slave 的数据。有时可能需要重启下 mysql-proxy。

如果主数据库挂了：

主从也没了，在mysql-proxy上只能查看数据

在xuegod2上插入一条数据，在xuegod1,2,3上都可以查到

mysql> insert into admin values (6);

mysql> mysql> select * from admin;

5 |

6 |

5 rows in set (0.01 sec)

7 其他

停掉proxy

可以查到进程然后杀掉进程，也可以killall mysql-proxy

mysql-proxy配置文件模板（地址根据实际环境修改）

2-17-MySQL读写分离-mysql-proxy