MySQL5.5读写分离之mysql-proxy

uf123

　　通常一个网站在初期访问量都比较小，所以一般的小架构足以支撑。但是，当网站逐渐发展起来后，随之而来的是大量的访问，这时候最先出现的瓶颈就是数据库了。因为数据的写入读取操作（I/O）是集群中响应速度最慢的，所以在集群建设时就要规划好后端存储架构。
　　后端存储数据库架构普遍是主从复制，这样解决了数据备份问题。但是，由前端来的读写请求都要经过主库，量小没问题，当量大到超过主库的性能极限时，主库分分钟会宕机。
　　为了避免主库宕机这种灾难性事件的发生，读写分离的设计诞生了！把写的请求只交给主库，读的请求由主库和从库一起分摊，这样就大大减轻了主库的负担。
　　大部分公司会由开发部门在软件层面实现对数据库的读写分离，其次可以选择读写分离的中间件如：TDDL、amoeba、cobar、MySQL-proxy、Atlas、MyCat。
　　本次测试采用MySQL官方发布的MySQL-proxy。
　　官网下载地址（二进制包）：https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
　　测试环境：
　　[root@SQL-M ~]# cat /etc/redhat-release

　　CentOS>　　[root@SQL-M ~]# uname -r
　　2.6.32-642.el6.x86_64
　　MySQL-proxy   IP：192.168.0.86
　　MySQL-master  IP：192.168.0.88
　　MySQL-slave   IP：192.168.0.90
　　前提：MySQL主库与从库已经实现主从复制。
　　实现主从复制请参考我的前一篇博文：http://yuyicong.blog.51cto.com/11274530/1919158
　　接下来的操作在proxy服务器上：
　　[root@SQL-proxy ~]# cd /usr/local/src/
　　[root@SQL-proxy src]# wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
　　[root@SQL-proxy src]# ll -h
　　total 12M
　　-rw-r--r-- 1 root root 12M Aug 19  2014 mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
　　[root@SQL-proxy src]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
　　[root@SQL-proxy src]# cd ..
　　[root@SQL-proxy local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy-0.8.5
　　[root@SQL-proxy local]# ll -d mysql-proxy-0.8.5
　　drwxr-xr-x  8 7161 wheel 4096 Aug 19  2014 mysql-proxy-0.8.5    # 看到所属用户和组有问题
　　[root@SQL-proxy local]# chown -R root:root mysql-proxy-0.8.5/  # 改为root
　　[root@SQL-proxy local]# ln -s mysql-proxy-0.8.5/ mysql-proxy    # 必备软连接
　　[root@SQL-proxy local]# cd mysql-proxy
　　[root@SQL-proxy mysql-proxy]# mkdir etc              # 创建 lua 脚本存放目录
　　[root@SQL-proxy mysql-proxy]# mkdir logs            # 创建日志目录
　　把实现读写分离的 lua 脚本复制到 etc 目录下
　　[root@SQL-proxy mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua etc/
　　[root@SQL-proxy mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua etc/
　　创建启动参数文件，启动服务时命令行就不用老长老长的。
　　[root@SQL-proxy ~]# vim /etc/mysql-proxy.cnf
[mysql-proxy]　　
admin-username=proxy     # 连接主从mysql共有的用户
　　
admin-password=123       # 连接用户的密码
　　
proxy-address=192.168.0.86:3306   # mysql-proxy监听本地工作ip和端口，不加端口默认是4040，为了方便管理这里用3306
　　
proxy-backend-addresses=192.168.0.87:3306    # 指定后端主库
　　
proxy-read-only-backend-addresses=192.168.0.90:3306  # 指定后端只读从库
　　
proxy-lua-script=/usr/local/mysql-proxy/etc/rw-splitting.lua  # 指定实现读写分离的lua脚本
　　
admin-lua-script=/usr/local/mysql-proxy/etc/admin-sql.lua     # 指定管理脚本
　　
pid-file=/var/run/mysql-proxy.pid
　　
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log  # 日志位置
　　
log-level=info  # 定义log日志级别，由高到低分别有(error|warning|info|message|debug)
　　
daemon=true     # 以守护进程方式运行
　　
keepalive=true  # work进程崩溃时，尝试重启
　　启动服务
　　[root@SQL-proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
　　2017-04-24 16:40:19: (critical) mysql-proxy-cli.c:326: loading config from '/etc/mysql-proxy.cnf' failed: permissions of /etc/mysql-proxy.cnf aren't secure (0660 or stricter required)
　　2017-04-24 16:40:19: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
　　2017-04-24 16:40:19: (message) shutting down normally, exit code is: 1
　　！！看到无法启动，文件权限要改，因为文件里面有账号密码。
　　[root@SQL-proxy ~]# chmod 660 /etc/mysql-proxy.cnf     # 改权限
　　[root@SQL-proxy ~]# ll /etc/mysql-proxy.cnf
　　-rw-rw---- 1 root root 432 Apr 24 16:31 /etc/mysql-proxy.cnf
　　[root@SQL-proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf                                              #  重新启动
　　[root@SQL-proxy ~]# netstat -lntup   # 查看服务
　　Active Internet connections (only servers)
　　Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
　　tcp        0      0 192.168.0.86:3306           0.0.0.0:*                   LISTEN      3086/mysql-proxy
　　老是命令行敲一长串命令肯定不科学了，直接上服务管理脚本。
[root@SQL-proxy ~]# vim /etc/init.d/mysql-proxy　　
#!/bin/bash
　　
#
　　
# mysql-proxy This script starts and stops the mysql-proxy daemon
　　
#
　　
# chkconfig: - 78 30
　　
# processname: mysql-proxy
　　
# description: mysql-proxy is a proxy daemon for mysql
　　

　　
# Source function library.
　　
. /etc/rc.d/init.d/functions
　　

　　
prog="/usr/local/mysql-proxy/bin/mysql-proxy"
　　

　　
# Source networking configuration.
　　
if [ -f /etc/sysconfig/network ]; then
　　
    . /etc/sysconfig/network
　　
fi
　　

　　
# Check that networking is up.
　　
[ ${NETWORKING} = "no" ] && exit 0
　　

　　

　　
DEFAULT_FILE=/etc/mysql-proxy.cnf
　　
PROXY_PID=/var/run/mysql-proxy.pid
　　

　　
RETVAL=0
　　

　　
start() {
　　
    echo -n $"Starting MySQL-proxy...  : "
　　
    daemon $prog --defaults-file=$DEFAULT_FILE
　　
    RETVAL=$?
　　
    echo
　　
    if [ $RETVAL -eq 0 ]; then
　　
        touch /var/lock/subsys/mysql-proxy.lock
　　
    fi
　　
}
　　

　　
stop() {
　　
    echo -n $"Stopping MySQL-proxy...  : "
　　
    killproc -p $PROXY_PID -d 3 $prog
　　
    RETVAL=$?
　　
    echo
　　
    if [ $RETVAL -eq 0 ]; then
　　
        rm -f /var/lock/subsys/mysql-proxy.lock
　　
        rm -f $PROXY_PID
　　
    fi
　　
}
　　
# See how we were called.
　　
case "$1" in
　　
    start)
　　
        start
　　
        ;;
　　
    stop)
　　
        stop
　　
        ;;
　　
    restart)
　　
        stop
　　
        start
　　
        ;;
　　
    condrestart|try-restart)
　　
        if status -p $PROXY_PIDFILE $prog >&/dev/null; then
　　
            stop
　　
            start
　　
        fi
　　
        ;;
　　
    status)
　　
        status -p $PROXY_PID $prog
　　
        ;;
　　
    *)
　　
        echo "Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}"
　　
        RETVAL=1
　　
        ;;
　　
esac
　　

　　
exit $RETVAL
　　[root@SQL-proxy ~]# chmod +x /etc/init.d/mysql-proxy   #  给执行权限
　　[root@SQL-proxy ~]# chkconfig mysql-proxy on           # 可以加入开机启动
　　测试一下脚本：
[root@SQL-proxy ~]# /etc/init.d/mysql-proxy status　　
mysql-proxy (pid  3086) is running...
　　
[root@SQL-proxy ~]#
　　
[root@SQL-proxy ~]# /etc/init.d/mysql-proxy stop
　　
Stopping MySQL-proxy...  :                                 [  OK  ]
　　
[root@SQL-proxy ~]#
　　
[root@SQL-proxy ~]# /etc/init.d/mysql-proxy status
　　
mysql-proxy is stopped
　　
[root@SQL-proxy ~]# netstat -lntup
　　
Active Internet connections (only servers)
　　
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
　　
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      1079/sshd
　　
tcp        0      0 :::22                       :::*                        LISTEN      1079/sshd
　　
[root@SQL-proxy ~]# /etc/init.d/mysql-proxy start
　　
Starting MySQL-proxy...  :                                 [  OK  ]
　　
[root@SQL-proxy ~]# /etc/init.d/mysql-proxy restart
　　
Stopping MySQL-proxy...  :                                 [  OK  ]
　　
Starting MySQL-proxy...  :                                 [  OK  ]
　　
[root@SQL-proxy ~]#
　　
[root@SQL-proxy ~]# netstat -lntup
　　
Active Internet connections (only servers)
　　
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
　　
tcp        0      0 192.168.0.86:3306           0.0.0.0:*                   LISTEN      3143/mysql-proxy
　　以上MySQL-proxy服务器搭建完成，但是用于连接后端数据库的用户：proxy还需要在数据库端创建
　　数据库Master端：创建proxy用户，给增删改查权限
[root@SQL-M ~]# mysql -uroot -p　　
Enter password:
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　
Your MySQL connection id is 3
　　
Server version: 5.5.55-log MySQL Community Server (GPL)
　　

　　
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
　　

　　
Oracle is a registered trademark of Oracle Corporation and/or its
　　
affiliates. Other names may be trademarks of their respective
　　
owners.
　　

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

　　
Master>
　　
Master> grant insert,delete,update,select on *.* to proxy@'192.168.0.86' identified by '123';
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
Master> select user,host from mysql.user;                                       +-------+--------------+
　　
| user  | host         |
　　
+-------+--------------+
　　
| root  | 127.0.0.1    |
　　
| rep   | 192.168.0.%  |
　　
| proxy | 192.168.0.86 |
　　
| root  | localhost    |
　　
+-------+--------------+
　　
4 rows in set (0.00 sec)
　　

　　
Master> show grants for proxy@'192.168.0.86';
　　
+------------------------------------------------------------------------------------------------------------------------------------------+
　　
| Grants for proxy@192.168.0.86                                                                                                            |
　　
+------------------------------------------------------------------------------------------------------------------------------------------+
　　
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'proxy'@'192.168.0.86' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
　　
+------------------------------------------------------------------------------------------------------------------------------------------+
　　
1 row in set (0.00 sec)
　　Slave端：此时已经与主库一致拥有了proxy用户，但是从库只能读（select），所以要对proxy回收权限。
Slave> show grants for proxy@'192.168.0.86';　　
+------------------------------------------------------------------------------------------------------------------------------------------+
　　
| Grants for proxy@192.168.0.86                                                                                                            |
　　
+------------------------------------------------------------------------------------------------------------------------------------------+
　　
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'proxy'@'192.168.0.86' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
　　
+------------------------------------------------------------------------------------------------------------------------------------------+
　　
1 row in set (0.00 sec)
　　

　　
Slave> revoke insert,update,delete on *.* from proxy@'192.168.0.86'; # 只留查询权限
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
Slave> show grants for proxy@'192.168.0.86';
　　
+------------------------------------------------------------------------------------------------------------------+
　　
| Grants for proxy@192.168.0.86                                                                                    |
　　
+------------------------------------------------------------------------------------------------------------------+
　　
| GRANT SELECT ON *.* TO 'proxy'@'192.168.0.86' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
　　
+------------------------------------------------------------------------------------------------------------------+
　　
1 row in set (0.00 sec)
　　测试读写分离
　　为了方便测试，修改 rw-splitting.lua 脚本里触发读写分离参数
[root@SQL-proxy ~]# vim /usr/local/mysql-proxy/etc/rw-splitting.lua　　
-- connection pool
　　
if not proxy.global.config.rwsplit then
　　
        proxy.global.config.rwsplit = {
　　
                min_idle_connections = 1,  # 默认超过4个连接数时，才开始读写分离，改为1
　　
                max_idle_connections = 8,  # 默认最大连接数8，不动它
　　

　　
                is_debug = false
　　
        }
　　
end
　　

　　
[root@SQL-proxy ~]# /etc/init.d/mysql-proxy restart       # 重启一下
　　
Stopping MySQL-proxy...  :                                 [  OK  ]
　　
Starting MySQL-proxy...  :                                 [  OK  ]
　　数据库Master端
Master> create database proxy;         # 建个库　　
Query OK, 1 row affected (0.00 sec)
　　

　　
Master> create table master(id int(2),name char(10));  # 停掉从库 IO线程后再建个表
　　
Query OK, 0 rows affected (0.18 sec)
　　

　　
Master> desc master;
　　
+-------+----------+------+-----+---------+-------+
　　
| Field | Type     | Null | Key | Default | Extra |
　　
+-------+----------+------+-----+---------+-------+
　　
| id    | int(2)   | YES  |     | NULL    |       |
　　
| name  | char(10) | YES  |     | NULL    |       |
　　
+-------+----------+------+-----+---------+-------+
　　
2 rows in set (0.00 sec)
　　Slave端
Slave> show databases;　　
+--------------------+
　　
| Database           |
　　
+--------------------+
　　
| information_schema |
　　
| mysql              |
　　
| performance_schema |
　　
| proxy              |     # 新建的库已经有了
　　
| school             |
　　
| test               |
　　
+--------------------+
　　
6 rows in set (0.00 sec)
　　

　　
Slave> stop slave io_thread;      # 停掉 IO 线程
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
Slave> show slave status\G
　　
*************************** 1. row ***************************
　　
               Slave_IO_State:
　　
                  Master_Host: 192.168.0.88
　　
                  Master_User: rep
　　
                  Master_Port: 3306
　　
                Connect_Retry: 60
　　
              Master_Log_File: mysql-bin.000019
　　
          Read_Master_Log_Pos: 351
　　
               Relay_Log_File: relay-bin.000011
　　
                Relay_Log_Pos: 497
　　
        Relay_Master_Log_File: mysql-bin.000019
　　
             Slave_IO_Running: No
　　
            Slave_SQL_Running: Yes
　　

　　
Slave> use proxy;
　　
Database changed
　　

　　
Slave> create table slave(id int(2),name char(10));  # 建个表
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
Slave> desc slave;
　　
+-------+----------+------+-----+---------+-------+
　　
| Field | Type     | Null | Key | Default | Extra |
　　
+-------+----------+------+-----+---------+-------+
　　
| id    | int(2)   | YES  |     | NULL    |       |
　　
| name  | char(10) | YES  |     | NULL    |       |
　　
+-------+----------+------+-----+---------+-------+
　　
2 rows in set (0.00 sec)
　　

　　
Slave> insert into slave values(90,'slave');    # 插入一条数据
　　
Query OK, 1 row affected (0.00 sec)
　　

　　
Slave> select * from slave;
　　
+------+-------+
　　
| id   | name  |
　　
+------+-------+
　　
|   90 | slave |
　　
+------+-------+
　　
1 row in set (0.00 sec)
　　模拟远程连接MySQL-proxy服务器进行读写操作
[root@SQL-proxy ~]# mysql -h192.168.0.86 -uproxy -P3306 -p　　
Enter password:
　　
Welcome to the MySQL monitor.  Commands end with ; or \g.
　　
Your MySQL connection id is 12
　　
Server version: 5.5.55-log MySQL Community Server (GPL)
　　

　　
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
　　

　　
Oracle is a registered trademark of Oracle Corporation and/or its
　　
affiliates. Other names may be trademarks of their respective
　　
owners.
　　

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

　　
mysql>
　　
mysql> use proxy;
　　
Database changed
　　
mysql>
　　
mysql> show tables;   # show 出来的只有master这个表
　　
+-----------------+
　　
| Tables_in_proxy |
　　
+-----------------+
　　
| master          |
　　
+-----------------+
　　
1 row in set (0.01 sec)
　　

　　
mysql> desc master;
　　
+-------+----------+------+-----+---------+-------+
　　
| Field | Type     | Null | Key | Default | Extra |
　　
+-------+----------+------+-----+---------+-------+
　　
| id    | int(2)   | YES  |     | NULL    |       |
　　
| name  | char(10) | YES  |     | NULL    |       |
　　
+-------+----------+------+-----+---------+-------+
　　
2 rows in set (0.00 sec)
　　

　　
mysql> insert into master values(88,'master');  # 往master表里插入数据
　　
Query OK, 1 row affected (0.00 sec)