thinkphp手机网站模板,网站首页引导页模版,广告联盟app推广,成都网站建设服务平台通常一个网站在初期访问量都比较小#xff0c;所以一般的小架构足以支撑。但是#xff0c;当网站逐渐发展起来后#xff0c;随之而来的是大量的访问#xff0c;这时候最先出现的瓶颈就是数据库了。因为数据的写入读取操作#xff08;I/O#xff09;是集群中响应速度最慢的… 通常一个网站在初期访问量都比较小所以一般的小架构足以支撑。但是当网站逐渐发展起来后随之而来的是大量的访问这时候最先出现的瓶颈就是数据库了。因为数据的写入读取操作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 测试环境[rootSQL-M ~]# cat /etc/redhat-release CentOS release 6.8 (Final)[rootSQL-M ~]# uname -r2.6.32-642.el6.x86_64MySQL-proxy IP192.168.0.86MySQL-master IP192.168.0.88MySQL-slave IP192.168.0.90前提MySQL主库与从库已经实现主从复制。实现主从复制请参考我的前一篇博文http://yuyicong.blog.51cto.com/11274530/1919158 接下来的操作在proxy服务器上[rootSQL-proxy ~]# cd /usr/local/src/[rootSQL-proxy src]# wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [rootSQL-proxy src]# ll -htotal 12M-rw-r--r-- 1 root root 12M Aug 19 2014 mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz[rootSQL-proxy src]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/[rootSQL-proxy src]# cd .. [rootSQL-proxy local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy-0.8.5[rootSQL-proxy local]# ll -d mysql-proxy-0.8.5drwxr-xr-x 8 7161 wheel 4096 Aug 19 2014 mysql-proxy-0.8.5 # 看到所属用户和组有问题[rootSQL-proxy local]# chown -R root:root mysql-proxy-0.8.5/ # 改为root[rootSQL-proxy local]# ln -s mysql-proxy-0.8.5/ mysql-proxy # 必备软连接[rootSQL-proxy local]# cd mysql-proxy[rootSQL-proxy mysql-proxy]# mkdir etc # 创建 lua 脚本存放目录[rootSQL-proxy mysql-proxy]# mkdir logs # 创建日志目录把实现读写分离的 lua 脚本复制到 etc 目录下[rootSQL-proxy mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua etc/ [rootSQL-proxy mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua etc/创建启动参数文件启动服务时命令行就不用老长老长的。[rootSQL-proxy ~]# vim /etc/mysql-proxy.cnf[mysql-proxy]
admin-usernameproxy # 连接主从mysql共有的用户
admin-password123 # 连接用户的密码
proxy-address192.168.0.86:3306 # mysql-proxy监听本地工作ip和端口不加端口默认是4040为了方便管理这里用3306
proxy-backend-addresses192.168.0.87:3306 # 指定后端主库
proxy-read-only-backend-addresses192.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-levelinfo # 定义log日志级别由高到低分别有(error|warning|info|message|debug)
daemontrue # 以守护进程方式运行
keepalivetrue # work进程崩溃时尝试重启启动服务[rootSQL-proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file/etc/mysql-proxy.cnf2017-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 arent secure (0660 or stricter required)2017-04-24 16:40:19: (message) Initiating shutdown, requested from mysql-proxy-cli.c:3282017-04-24 16:40:19: (message) shutting down normally, exit code is: 1看到无法启动文件权限要改因为文件里面有账号密码。[rootSQL-proxy ~]# chmod 660 /etc/mysql-proxy.cnf # 改权限[rootSQL-proxy ~]# ll /etc/mysql-proxy.cnf -rw-rw---- 1 root root 432 Apr 24 16:31 /etc/mysql-proxy.cnf[rootSQL-proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file/etc/mysql-proxy.cnf # 重新启动[rootSQL-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 老是命令行敲一长串命令肯定不科学了直接上服务管理脚本。[rootSQL-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/functionsprog/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 0DEFAULT_FILE/etc/mysql-proxy.cnf
PROXY_PID/var/run/mysql-proxy.pidRETVAL0start() {echo -n $Starting MySQL-proxy... : daemon $prog --defaults-file$DEFAULT_FILERETVAL$?echoif [ $RETVAL -eq 0 ]; thentouch /var/lock/subsys/mysql-proxy.lockfi
}stop() {echo -n $Stopping MySQL-proxy... : killproc -p $PROXY_PID -d 3 $progRETVAL$?echoif [ $RETVAL -eq 0 ]; thenrm -f /var/lock/subsys/mysql-proxy.lockrm -f $PROXY_PIDfi
}
# See how we were called.
case $1 instart)start;;stop)stop;;restart)stopstart;;condrestart|try-restart)if status -p $PROXY_PIDFILE $prog /dev/null; thenstopstartfi;;status)status -p $PROXY_PID $prog;;*)echo Usage: $0 {start|stop|restart|reload|status|condrestart|try-restart}RETVAL1;;
esacexit $RETVAL[rootSQL-proxy ~]# chmod x /etc/init.d/mysql-proxy # 给执行权限[rootSQL-proxy ~]# chkconfig mysql-proxy on # 可以加入开机启动测试一下脚本[rootSQL-proxy ~]# /etc/init.d/mysql-proxy status
mysql-proxy (pid 3086) is running...
[rootSQL-proxy ~]#
[rootSQL-proxy ~]# /etc/init.d/mysql-proxy stop
Stopping MySQL-proxy... : [ OK ]
[rootSQL-proxy ~]#
[rootSQL-proxy ~]# /etc/init.d/mysql-proxy status
mysql-proxy is stopped
[rootSQL-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
[rootSQL-proxy ~]# /etc/init.d/mysql-proxy start
Starting MySQL-proxy... : [ OK ]
[rootSQL-proxy ~]# /etc/init.d/mysql-proxy restart
Stopping MySQL-proxy... : [ OK ]
Starting MySQL-proxy... : [ OK ]
[rootSQL-proxy ~]#
[rootSQL-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用户给增删改查权限[rootSQL-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 proxy192.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 proxy192.168.0.86;
------------------------------------------------------------------------------------------------------------------------------------------
| Grants for proxy192.168.0.86 |
------------------------------------------------------------------------------------------------------------------------------------------
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO proxy192.168.0.86 IDENTIFIED BY PASSWORD *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)Slave端此时已经与主库一致拥有了proxy用户但是从库只能读select所以要对proxy回收权限。Slave show grants for proxy192.168.0.86;
------------------------------------------------------------------------------------------------------------------------------------------
| Grants for proxy192.168.0.86 |
------------------------------------------------------------------------------------------------------------------------------------------
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO proxy192.168.0.86 IDENTIFIED BY PASSWORD *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)Slave revoke insert,update,delete on *.* from proxy192.168.0.86; # 只留查询权限
Query OK, 0 rows affected (0.00 sec)Slave show grants for proxy192.168.0.86;
------------------------------------------------------------------------------------------------------------------
| Grants for proxy192.168.0.86 |
------------------------------------------------------------------------------------------------------------------
| GRANT SELECT ON *.* TO proxy192.168.0.86 IDENTIFIED BY PASSWORD *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)测试读写分离为了方便测试修改 rw-splitting.lua 脚本里触发读写分离参数[rootSQL-proxy ~]# vim /usr/local/mysql-proxy/etc/rw-splitting.lua
-- connection pool
if not proxy.global.config.rwsplit thenproxy.global.config.rwsplit {min_idle_connections 1, # 默认超过4个连接数时才开始读写分离改为1max_idle_connections 8, # 默认最大连接数8不动它is_debug false}
end[rootSQL-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.88Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000019Read_Master_Log_Pos: 351Relay_Log_File: relay-bin.000011Relay_Log_Pos: 497Relay_Master_Log_File: mysql-bin.000019Slave_IO_Running: NoSlave_SQL_Running: YesSlave use proxy;
Database changedSlave 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服务器进行读写操作[rootSQL-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 doesnt 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 线程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 eventMaster_Host: 192.168.0.88Master_User: repMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000019Read_Master_Log_Pos: 1566Relay_Log_File: relay-bin.000012Relay_Log_Pos: 1468Relay_Master_Log_File: mysql-bin.000019Slave_IO_Running: YesSlave_SQL_Running: Yes再次模拟远程连接MySQL-proxy服务器查 master 表[rootSQL-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服务器端屏幕有信息输出不知道为什么信息如下[rootSQL-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 转载于:https://blog.51cto.com/yuyicong/1919799
