搭建Mysql-proxy实现主从同步读写分离

代理服务器 Wg61   192.168.0.180  （Mysql-proxy）

主服务器   Wg62   192.168.0.142  （负责写入数据）

从服务器   Wg63   192.168.0.156   （负责只读数据）

 

 

实验思路：

1.     下载Mysql-proxy，在代理服务器Wg61上安装lua语言

2.     Wg61安装proxy，添加/etc/profile的环境变量参数

3.     修改proxy配置文件参数，测试读写分离

4.     搭建主从服务器，创建测试数据库表并授权用户访问权限

5.     启动Mysql-proxy测试读写分离

6.     测试从服务器挂掉，主服务器挂掉如何

 步骤如下：

1.     Wg61服务器上安装lua语言，Mysql-proxy需要lua语言调用

[root@Wg61 ~]# yum-y insall lua

2.     下载Mysql-proxy安装包到Wg61并解压到/usr/local/下

[root@Wg61 ~]# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz--no-check-certificate

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

[root@Wg61 ~]# cd/usr/local/

[root@Wg61 local]# ls

bin  games    lib   libexec                               sbin   src

etc include  lib64  mysql-proxy-0.8.5-linux-el6-x86-64bit  share

[root@Wg61 local]#mv mysql-proxy-0.8.5-linux-el6-x86-64bit/  mysql-proxy

3.     修改环境变量参数

[root@Wg61 local]#vim /etc/profile

最后添加exportPATH=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin:$PATH

[root@Wg61 local]# source!$   

source /etc/profile   对命令生效

4.     修改Mysql-proxy配置文件参数，测试读写分离

[root@Wg61 local]#vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

40         min_idle_connections = 1，  在41行和42行将最小链接数改成1

    41          max_idle_connections = 1,

5.     Wg62主服务器上创建测试文件，并授权用户user1访问权限

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

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

3rows in set (0.00 sec)

 

mysql>create database HK;

QueryOK, 1 row affected (0.00 sec)

 

mysql>use HK;

Databasechanged

mysql>create table city(id int);

QueryOK, 0 rows affected (0.02 sec)

 

mysql>insert into city values(123);

QueryOK, 1 row affected (0.00 sec)

 

mysql>select * from city ;

+------+

|id   |

+------+

|  123 |

+------+

1row in set (0.00 sec)

 

mysql>grant all on HK.* to user1@'%' identified by '123456';

 

6.     Wg63从服务器上创建测试文件，并授权用户user1访问权限

mysql> show databases;

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

| Database          |

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

| information_schema |

| mysql             |

| test              |

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

3 rows in set (0.00 sec)

 

mysql> create database HK;

Query OK, 1 row affected (0.00 sec)

 

mysql> show databases;

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

| Database          |

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

| information_schema |

| HK                |

| mysql             |

| test              |

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

4 rows in set (0.00 sec)

 

mysql> create table city(id int);

ERROR 1046 (3D000): No database selected

mysql> use HK;

Database changed

mysql> create table city(id int);

Query OK, 0 rows affected (0.06 sec)

 

mysql> insert into city values(456);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from city;

+------+

| id   |

+------+

|  456 |

+------+

1 row in set (0.00 sec)

 

mysql> grant allon HK.* to user1@'%'  identified by'123456';

 

7.     Wg1服务器上启动Mysql-proxy服务

[root@Wg61 local]#mysql-proxy

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

--proxy-backend-addresses=192.168.0.142:3306

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

[1] 2044

[root@Wg61 local]#2018-01-04 05:25:31: (critical) plugin proxy 0.8.5 started

 

另开一个窗口查看服务是否启动成功：

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

COMMAND    PIDUSER   FD   TYPE DEVICE SIZE/OFF NODE NAME

mysql-pro 2044root    9u  IPv4 12940      0t0  TCP *:yo-main (LISTEN)

参数说明：

--proxy-read-only-backend-addresses=192.168.0.111:3306  # 定义后端只读服务器

--proxy-backend-addresses=192.168.0.112:3306   #定义后端mysql主服务器地址，指定mysql写主服务器的端口

--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua&  #指定lua脚本，在这里，使用的是rw-splitting脚本，用于读写分离

当有多个只读服务器时，可以写多个以下参数：

--proxy-read-only-backend-addresses=192.168.0.111:3306  # 定义后端只读服务器

--proxy-read-only-backend-addresses=192.168.0.112:3306  # 定义后端只读服务器

#--proxy-address=192.168.0.110:3307指定mysql proxy的监听端口，默认为：4040

 

8.     测试读写功能

（1）     测试写操作：可以查看到Wg62数据信息，也可写入数据，看不到Wg63数据

mysql>select user();

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

|user()              |

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

|user1@192.168.0.180 |

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

1row in set (0.00 sec)

 

mysql>show databases;

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

|Database           |

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

| information_schema|

|HK                 |

|test               |

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

3rows in set (0.00 sec)

 

mysql>use HK;

Readingtable information for completion of table and column names

Youcan turn off this feature to get a quicker startup with -A

 

Databasechanged

mysql>use HK;

Databasechanged

mysql>show tables;

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

|Tables_in_HK |

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

|city         |

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

1row in set (0.00 sec)

 

mysql>select  * from city;

+------+

|id   |

+------+

|  123 |

+------+

1row in set (0.00 sec)

 

mysql>insert into city values(1313);

QueryOK, 1 row affected (0.00 sec)

 

mysql>select  * from city;

+------+

|id   |

+------+

|  123 |

| 1313 |

+------+

2rows in set (0.00 sec)

（2）     测试读数据

从Wg2模拟客户端登入显示未从服务器数据，可查看到对应数据信息

[root@Wg62 ~]# mysql-uuser1 -p123456 -P4040 -h192.168.0.180

[root@Wg62 ~]# mysql -uuser1 -p123456 -P4040-h192.168.0.180

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.1.73 Source distribution

 

Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear thecurrent input statement.

 

mysql> select * from HK.city;

+------+

| id   |

+------+

|  456 |

+------+

1 row in set (0.00 sec)

 

mysql> Ctrl-C -- exit!

Aborted

[root@Wg62 ~]# mysql -uuser1 -p123456 -P4040-h192.168.0.180

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 11

Server version: 5.1.73 Source distribution

 

Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear thecurrent input statement.

 

mysql> select * from HK.city;

+------+

| id   |

+------+

|  456 |

+------+

1 row in set (0.01 sec)

 

mysql> select user();

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

| user()             |

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

| user1@192.168.0.180 |

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

1 row in set (0.00sec)

插入数据显示成功，但是查询发现没有插入数据

mysql> insert into HK.city values(4545);

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from HK.city;

+------+

| id   |

+------+

|  456 |

+------+

1 row in set (0.00 sec)

 

mysql> insert into HK.city values(232323);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from HK.city;

+------+

| id   |

+------+

|  456 |

+------+

1 row in set (0.00sec)

9.     配置MYsql主从并实现读写分离

（1）     同步Wg2和Wg3两台服务器数据信息

[root@Wg62 ~]#mysqldump -uroot -p -A > all.sql

[root@Wg62 ~]# scpall.sql 192.168.0.156:/root/

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

mysql> source/root/all.sql

（2）     Wg2主服务器配置为MASTER，并授权user2用户作为同步用户

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

log-bin=mysql-binlog

binlog-do-db=HK

binlog_format=row

server-id=1

mysql> grant all on *.*to user2@'%'  identified by '123456';

重启数据库

 

（3）     Wg3从服务器配置为SLAVE

方法一：[root@Wg63 ~]#vim /etc/my.cnf  

master_host='192.168.0.142'

master_user='user2'

master_password='123456'

方法二：登入数据库

MySQL>changemaster to master_host='192.168.0.142',master_user='user2',master_password='123456';

重启数据库

10. 测试通过Mysql-proxy是否实现主从同步读写分离

（1）     查看主从是否同步

mysql> select user();

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

| user()              |

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

| user1@192.168.0.180 |

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

1 row in set (0.00 sec)

 

mysql> show databases;

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

| Database          |

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

| information_schema |

| HK                |

| test               |

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

3 rows in set (0.00 sec)

 

mysql> use HK;

Reading table information for completion of table andcolumn names

You can turn off this feature to get a quicker startupwith -A

 

Database changed

mysql> use HK;

Database changed

mysql> select * from city;

+--------+

| id     |

+--------+

|    123 |

|   1313 |

|   4545 |

| 232323 |

+--------+

4 rows in set (0.00 sec)

 

mysql> insert into city values(789);

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from city;

+--------+

| id     |

+--------+

|    123 |

|   1313 |

|   4545 |

| 232323 |

|    789 |

+--------+

5 rows in set (0.00 sec)

登入主服务器查看，显示插入成功

登入从服务器查看，显示同步成功

mysql> select user();

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

| user()        |

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

| root@localhost |

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

1 row in set (0.00 sec)

 

mysql> select * from HK.city;

+--------+

| id     |

+--------+

|    123 |

|   1313 |

|   4545 |

| 232323 |

|    789 |

+--------+

5 rows in set (0.00sec)

（2）     测试从服务器宕机（可写入查看数据）

mysql> insert into HK.city values(888);

Query OK, 1 row affected (0.01 sec)

 

mysql> select * from HK.city;

+--------+

| id     |

+--------+

|    123 |

|   1313 |

|   4545 |

| 232323 |

|    789 |

|      0 |

|    888 |

+--------+

7 rows in set (0.00sec)

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

2. 从服务器恢复后，再次同步刚刚所插入的数据

（3）     测试主服务器宕机

mysql> select * from HK.city

    -> ;

+--------+

| id     |

+--------+

|    123 |

|   1313 |

|   4545 |

| 232323 |

|    789 |

|      0 |

|    888 |

+--------+

7 rows in set (0.00 sec)

 

mysql> use HK;

ERROR 2013 (HY000): Lost connection to MySQL serverduring query

mysql> insert into HK.city values(999);

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:   6

Current database: *** NONE ***

 

ERROR 2013 (HY000):Lost connection to MySQL server during query

显示只能读取数据不能写入数据