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

实验思路：

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
显示只能读取数据不能写入数据

原文：http://blog.51cto.com/13719714/2107691