通常一个网站在初期访问量都比较小,所以一般的小架构足以支撑。但是,当网站逐渐发展起来后,随之而来的是大量的访问,这时候最先出现的瓶颈就是数据库了。因为数据的写入读取操作(I/O)是集群中响应速度最慢的,所以在集群建设时就要规划好后端存储架构。 后端存储数据库架构普遍是主从复制,这样解决了数据备份问题。但是,由前端来的读写请求都要经过主库,量小没问题,当量大到超过主库的性能极限时,主库分分钟会宕机。 为了避免主库宕机这种灾难性事件的发生,读写分离的设计诞生了!把写的请求只交给主库,读的请求由主库和从库一起分摊,这样就大大减轻了主库的负担。 大部分公司会由开发部门在软件层面实现对数据库的读写分离,其次可以选择读写分离的中间件如:TDDL、amoeba、cobar、MySQL-proxy、Atlas、MyCat。
本次测试采用MySQL官方发布的MySQL-proxy。
官网下载地址(二进制包):https://downloads./archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
测试环境: [root@SQL-M ~]# cat /etc/redhat-release CentOS release 6.8 (Final) [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./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 1 2 3 4 5 6 7 8 9 10 11 12 13 | [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
老是命令行敲一长串命令肯定不科学了,直接上服务管理脚本。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | [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 # 可以加入开机启动
测试一下脚本: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [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用户,给增删改查权限 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [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回收权限。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 脚本里触发读写分离参数 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [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端
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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端 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | 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服务器进行读写操作 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | [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) <<--- 写入是 OK 的
mysql> select * from master;
ERROR 1146 (42S02): Table 'proxy.master' doesn't exist # 明显查不到的,因为去找从库了,从库上没有这表。
mysql> show tables; # 我们show时只能看到 master 表,好像show只能找主库。
+-----------------+
| Tables_in_proxy |
+-----------------+
| master |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from slave; # 我们直接查 slave 表
+------+-------+
| id | name |
+------+-------+
| 90 | slave | <<--- 查到了,读操作成功
+------+-------+
1 row in set (0.00 sec)
mysql>
|
恢复 slave IO 线程 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | Slave> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
Slave> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
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: 1566
Relay_Log_File: relay-bin.000012
Relay_Log_Pos: 1468
Relay_Master_Log_File: mysql-bin.000019
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
|
再次模拟远程连接MySQL-proxy服务器查 master 表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | [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> select * from master;
+------+--------+
| id | name |
+------+--------+
| 88 | master | <<----由于从库恢复后开始复制主库数据,所以可以查到了。
+------+--------+
1 row in set (0.00 sec)
|
以上MySQL-proxy读写分离完成。
存在的问题: 当模拟前端连接MySQL-proxy服务器并进行数据库操作时,MySQL-proxy服务器端屏幕有信息输出,不知道为什么?
信息如下: [root@SQL-proxy ~]# server default db: proxy client default db: school syncronizing server default db: proxy client default db: school syncronizing server default db: school client default db: proxy syncronizing
本文出自 “从没想过放弃!” 博客,请务必保留此出处http://yuyicong.blog.51cto.com/11274530/1919799
|