Mysql读写分离方案－MySQL Proxy环境部署记录

Mysql的读写分离可以使用MySQL Proxy和Amoeba实现，其实也可以使用MySQL-MMM实现读写分离的自动切换。MySQL Proxy有一项强大功能是实现"读写分离"，基本原理是让主数据库处理写方面事务，让从库处理SELECT查询；Amoeba for MySQL是一款优秀的中间件软件，同样可以实现读写分离，负载均衡等功能。下面重点说下Mysql Proxy:

MySQL Proxy处于客户端应用程序和MySQL服务器之间，通过截断、改变并转发客户端和后端数据库之间的通信来实现其功能。代理服务器是和TCP/IP协议打交道，
而要理解MySQL Proxy的工作机制，同样要清楚MySQL客户端和服务器之间的通信协议，MySQL Protocol 包括认证和查询两个基本过程：
.................................
认证过程包括：
1）客户端向服务器发起连接请求
2）服务器向客户端发送握手信息
3）客户端向服务器发送认证请求
4）服务器向客户端发送认证结果
................................
如果认证通过，则进入查询过程：
1）客户端向服务器发起查询请求
2）服务器向客户端返回查询结果
当然，这只是一个粗略的描述，每个过程中发送的包都是有固定格式的。MySQL Proxy要做的，就是介入协议的各个过程。首先MySQL Proxy以服务器的身份接受客户端请求，
根据配置对这些请求进行分析处理，然后以客户端的身份转发给相应的后端数据库服务器，再接受服务器的信息，返回给客户端。所以MySQL Proxy需要同时实现客户端和服务
器的协议。由于要对客户端发送过来的SQL语句进行分析，还需要包含一个SQL解析器。可以说MySQL Proxy相当于一个轻量级的MySQL了，实际上，MySQL Proxy的admin server
是可以接受SQL来查询状态信息的。
 
MySQL Proxy通过lua 脚本来控制连接转发的机制。主要的函数都是配合MySQL Protocol各个过程的，这一点从函数名上就能看出来：
connect_server()
read_handshake()
read_auth()
read_auth_result()
read_query()
read_query_result()
 
至于为什么采用lua 脚本语言，大概是因为MySQL Proxy中采用了wormhole 存储引擎 的关系吧，这个虫洞存储引擎很有意思，数据的存储格式就是一段lua脚本。

Mysql Proxy的原理图

顺便贴下Mysql Proxy的工作拓扑图

部署MySQL Proxy实现读写分离并提高并发负载的操作记录

0）机器环境

ip地址              角色         主机名
182.48.115.237     master      master-node
182.48.115.236     slave1      slave-node1
182.48.115.238     slave2      slave-node2
182.48.115.233     proxy       proxy-node
 
四台机器都关闭防火墙和selinux
 
绑定hosts设置（四台机器都要操作）
[root@master-node ~]# vim /etc/hosts
......
182.48.115.237   master-node
182.48.115.236   slave-node1
182.48.115.238   slave-node2
182.48.115.233   proxy-node

1）Mysql主从复制部署（在三台mysql节点机上部署，本案例是一主两从架构）

1 2	`mysql安装参考：http://www.cnblogs.com/kevingrace/p/6109679.html` `mysql主从部署参考：http://www.cnblogs.com/kevingrace/p/6256603.html`

2）在proxy机器上安装mysql-proxy

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

1）安装mysql-proxy
mysql proxy实现读写分离是有lua脚本实现的
下载地址1：https://downloads.mysql.com/archives/proxy/#downloads
下载地址2: http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/
 
[root@proxy-node ~]# wget http://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz  
[root@proxy-node ~]# tar -zvxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 
[root@proxy-node ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
[root@proxy-node ~]# useradd -r mysql-proxy
 
2）提供服务脚本
[root@proxy-node ~]# 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
 
# Set default mysql-proxy configuration.
ADMIN_USER="admin"
ADMIN_PASSWD="admin"
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_OPTIONS="--daemon"
PROXY_PID=/var/run/mysql-proxy.pid
PROXY_USER="mysql-proxy"
 
# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
    . /etc/sysconfig/mysql-proxy
fi
 
RETVAL=0
 
start() {
    echo -n $"Starting $prog: "
    daemon $prog $PROXY_OPTIONS --pid-file=$PROXY_PID --proxy-address="$PROXY_ADDRESS" --user=$PROXY_USER --admin-username="$ADMIN_USER" --admin-lua-script="$ADMIN_LUA_SCRIPT" --admin-password="$ADMIN_PASSWORD"
    RETVAL=$?
    echo
    if [ $RETVAL -eq 0 ]; then
        touch /var/lock/subsys/mysql-proxy
    fi
}
 
stop() {
    echo -n $"Stopping $prog: "
    killproc -p $PROXY_PID -d 3 $prog
    RETVAL=$?
    echo
    if [ $RETVAL -eq 0 ]; then
        rm -f /var/lock/subsys/mysql-proxy
        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@proxy-node ~]# chmod 755 /etc/init.d/mysql-proxy
[root@proxy-node ~]# chkconfig --add mysql-proxy
 
3）为服务脚本提供配置文件
[root@proxy-node ~]# vim /etc/sysconfig/mysql-proxy
# Options for mysql-proxy
ADMIN_USER="admin"
ADMIN_PASSWORD="admin"
ADMIN_ADDRESS=""
ADMIN_LUA_SCRIPT="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
PROXY_ADDRESS=""
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="--daemon --log-level=info --log-use-syslog --plugins=proxy --plugins=admin --proxy-backend-addresses=182.48.115.237:3306 --proxy-read-only-backend-addresses=182.48.115.236:3306
 --proxy-read-only-backend-addresses=182.48.115.238:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"
 
配置参数解释：
--daemon：以守护进程模式启动mysql-proxy
--proxy-backend-addresses：后端可读写的mysql服务器的地址和端口
--proxy-read-only-backend-addresses：后端只读mysql服务器的地址和端口
--proxy-lua-script：完成mysql代理功能的Lua脚本
 
4）提供admin.lua文件
[root@proxy-node ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua
--[[ $%BEGINLICENSE%$
 Copyright (c) 2007, 2012, Oracle and/or its affiliates. All rights reserved.
 
 This program is free software; you can redistribute it and/or
 modify it under the terms of the GNU General Public License as
 published by the Free Software Foundation; version 2 of the
 License.
 
 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 GNU General Public License for more details.
 
 You should have received a copy of the GNU General Public License
 along with this program; if not, write to the Free Software
 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 02110-1301  USA
 
 $%ENDLICENSE%$ --]]
 
function set_error(errmsg) 
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or "error"
}
end
 
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error("[admin] we only handle text-based queries (COM_QUERY)")
return proxy.PROXY_SEND_RESULT
end
 
local query = packet:sub(2)
 
local rows = { }
local fields = { }
 
if query:lower() == "select * from backends" then
fields = { 
{ name = "backend_ndx", 
 type = proxy.MYSQL_TYPE_LONG },
 
{ name = "address",
 type = proxy.MYSQL_TYPE_STRING },
{ name = "state",
 type = proxy.MYSQL_TYPE_STRING },
{ name = "type",
 type = proxy.MYSQL_TYPE_STRING },
{ name = "uuid",
 type = proxy.MYSQL_TYPE_STRING },
{ name = "connected_clients", 
 type = proxy.MYSQL_TYPE_LONG },
}
 
for i = 1, #proxy.global.backends do
local states = {
"unknown",
"up",
"down"
}
local types = {
"unknown",
"rw",
"ro"
}
local b = proxy.global.backends[i]
 
rows[#rows + 1] = {
i,
b.dst.name,          -- configured backend address
states[b.state + 1], -- the C-id is pushed down starting at 0
types[b.type + 1],   -- the C-id is pushed down starting at 0
b.uuid,              -- the MySQL Server's UUID if it is managed
b.connected_clients  -- currently connected clients
}
end
elseif query:lower() == "select * from help" then
fields = { 
{ name = "command", 
 type = proxy.MYSQL_TYPE_STRING },
{ name = "description", 
 type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows + 1] = { "SELECT * FROM help", "shows this help" }
rows[#rows + 1] = { "SELECT * FROM backends", "lists the backends and their state" }
else
set_error("use 'SELECT * FROM help' to see the supported commands")
return proxy.PROXY_SEND_RESULT
end
 
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end
 
5）为了测试更明显，编辑rw-splitting.lua文件中的其中2个数值：
[root@proxy-node ~]# vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
.........
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,        //修改这个值，默认为4
                max_idle_connections = 1,        //修改这个值，默认为8
 
                is_debug = false
        }
end
........
 
注意：
mysql-proxy会检测客户端连接，当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。
 
6）启动mysql-proxy
[root@proxy-node ~]# service mysql-proxy start
Starting /usr/local/mysql-proxy/bin/mysql-proxy:           [  OK  ]
 
[root@proxy-node ~]# service mysql-proxy status
mysql-proxy (pid  4655) is running...
 
[root@proxy-node ~]# netstat -tunlp
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:4041                0.0.0.0:*                   LISTEN      4655/mysql-proxy   
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      4655/mysql-proxy
 
 
7）连接测试（在任意一台远程客户机上测试连接）
[root@slave-node2 ~]# mysql -uadmin -padmin -h182.48.115.233 --port=4041
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
 
Copyright (c) 2000, 2016, 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> SELECT * FROM backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address             | state   | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
|           1 | 182.48.115.237:3306 | unknown | rw   | NULL |                 0 |
|           2 | 182.48.115.236:3306 | unknown | ro   | NULL |                 0 |
|           3 | 182.48.115.238:3306 | unknown | ro   | NULL |                 0 |
+-------------+---------------------+---------+------+------+-------------------+
3 rows in set (0.00 sec)

3）读写分离测试

1）在master-node数据库上创建proxy用户，从服务器也会同步这个操作。
mysql> GRANT ALL ON *.* TO 'wang'@'182.48.115.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
  
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  
2）proxy-node机器上使用创建的proxy用户登陆数据库，进行数据操作
[root@proxy-node ~]# mysql -uwang -h182.48.115.233 -p123456
.......
mysql> create database huanqiutest;
Query OK, 1 row affected (0.00 sec)
 
mysql> use huanqiutest;
Database changed
mysql> create table haha( id int(5), name varchar(10));
Query OK, 0 rows affected (0.19 sec)
 
mysql> insert into haha values(1,"zhangbao");
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into haha values(11,"shibo");
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from haha;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangbao |
|   11 | shibo    |
+------+----------+
2 rows in set (0.01 sec)
  
注意下面两点：
-> 发现使用proxy用户登陆数据库写入的数据只写入master主库，然后再同步到slave从库。如果将从库的slave同步功能关闭，则从库就无法更新数据。
-> 登陆从库服务器关闭slave同步功能（stop slave），这时再登陆proxy-noed机器肯定会查询不出数据（能看到表，但是查询不出数据）
-> 以上两点真正实现了读写分离的效果！
  
3）查看状态，在proxy-node机器上操作，可以看到状态全部为up：
[root@proxy-node bin]# mysql -uadmin -padmin -h182.48.115.233 --port=4041
.......
  
mysql> SELECT * FROM backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address             | state   | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
|           1 | 182.48.115.237:3306 | up      | rw   | NULL |                 0 |
|           2 | 182.48.115.236:3306 | up      | ro   | NULL |                 0 |
|           3 | 182.48.115.238:3306 | up      | ro   | NULL |                 0 |
+-------------+---------------------+---------+------+------+-------------------+
3 rows in set (0.00 sec)

读写分离，延迟是个大问题，要确保主从同步这一环节不要有延迟。
---------------mysql主从同步延迟原理----------------
－> mysql主从同步原理
主库针对读写操作，顺序写 binlog，从库单线程去主库读"写操作的binlog",从库取到 binlog在本地原样执行(随机写),来保证主从数据逻辑上一致.
mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生 binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率比较高，下一步问题来了，slave的 slave_sql_running线程将主库的 DDL和DML操作在 slave实施。DML，DDL的IO操作是随即的，不能顺序的，成本高很多，还有可能slave上的其他查询产生 lock，由于 slave_sql_running也是单线程的，所以一个 DDL卡住了，需求需求执行一段时间，那么所有之后的DDL会等待这个 DDL执行完才会继续执行，这就导致了延迟.由于master可以并发，Slave_sql_running线程却不可以，所以主库执行 DDL需求一段时间，在slave执行相同的DDL时，就产生了延迟.

-> 主从同步延迟产生原因
当主库的TPS并发较高时，产生的DDL数量超过Slave一个 sql线程所能承受的范围，那么延迟就产生了，当然还有就是可能与 slave的大型 query语句产生了锁等待
首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高
次要原因：读写 binlog带来的性能影响，网络传输延迟

-> mysql主从同步加速
1）sync_binlog在slave端设置为0
2）–logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
3）直接禁用slave端的binlog
4）slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit =2

再有就是进行分库分表处理，这样减少数据量的复制同步操作

***************当你发现自己的才华撑不起野心时，就请安静下来学习吧**************