MySQL | AI Site

一、MySQL-Proxy基础

MySQL Proxy是一个处于你的Client端和MySQL server端之间的简单程序，它可以监测、分析或改变它们的通信。它使用灵活，没有限制，常见的用途包括：负载平衡，故障、查询分析，查询过滤和修改等等。

（Figure1：MySQL Proxy）

MySQL-Proxy, announced in June, is a binary application that sits between your MySQL client and server, and supports the embedded scripting language Lua. The proxy can be used to analyze, monitor and transform communication, and supports a wide range of scenarios including:

load balancing and fail over handling
query analysis and logging
SQL macros
query rewriting
executing shell commands

One of the more powerful features of MySQL Proxy is the ability to do “Read/Write Splitting“. The basic concept is to have a master database handle transactional queries while slaves handle SELECT queries. Replication is used to synchronize the changes due to transactional queries with the slaves in the cluster.

MySQL-Proxy是处在你的MySQL数据库客户和服务端之间的程序，它还支持嵌入性脚本语言Lua。这个代理可以用来分析、监控和变换（transform）通信数据，它支持非常广泛的使用场景：

负载平衡和故障转移处理
查询分析和日志
SQL宏（SQL macros）
查询重写（query rewriting）
执行shell命令

MySQL Proxy更强大的一项功能是实现“读写分离（Read/Write Splitting）”。基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。

二、实战过程

测试环境：Ubuntu 10.04.2 LTS + MySQL5.1.41-3ubuntu12.10-log

192.168.1.147 proxy 代理入口

192.168.1.126 master 主机只写

192.168.1.145 slaver 从机只读

程序上只需要链接到192.168.1.147，而192.168.1.126和192.168.1.145对于程序来说是透明的，你完全不需要理会，也不需要知道192.168.1.126和192.168.1.145，你对数据库的所有操作都只对192.168.1.147进行操作。

1. 安装脚本lua

#apt-get install lua5.1

MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。

2. 安装配置MySQL-Proxy

#apt-get mysql-proxy

当前获取到的版本是：mysql-proxy 0.8.0（查看版本命令：#mysql-proxy -V）

3. 修改rw-splitting.lua

#vim /usr/share/mysql-proxy/rw-splitting.lua

配置并使用rw-splitting.lua读写分离脚本，脚本目录是 /usr/share/mysql-proxy，修改读写分离脚本rw-splitting.lua，修改默认连接数，进行快速测试，如果不修改连接数的话要达到连接数为4时才会启用读写分离。

— connection pool

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上。

4. 新建文件夹/var/log/mysql-proxy/和文件mysql-proxy.log

#mkdir /var/log/mysql-proxy

#vi mysql-proxy.log

5. 执行读写分离

#sudo mysql-proxy –proxy-read-only-backend-addresses=192.168.1.145:3306 –proxy-backend-addresses=192.168.1.126:3306 –proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua >/var/log/mysql-proxy/mysql-proxy.log &

参数说明：

192.168.1.147 proxy 代理入口

192.168.1.126 master 主机只写

192.168.1.145 slaver 从机只读

当运行sudo mysql-proxy 上面语句后，查询进程没有4040的时候，需要重启mysql ( sudo /etc/init.d/mysql restart) 之后再输入proxy设置。

6. 查看进程端口

#netstat -ant

#netstat –ntl

（Figure2：端口）

tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN

7. 查看数据库链接

mysql> show processlist\G;

（Figure3：进程）

可以看到，产生了一个新连接。如果想杀掉某个链接，可以使用mysql>help kill查看kill的帮助信息，杀掉36进程的命令：mysql>kill 36;

8. 测试读写分离

1) 在mysql-proxy机子进入MySQL

#mysql -u gaizai -p -P4040 -h 192.168.1.147

必须指定-h参数，不然报下面错误：

（Figure4：出错）

2) 显示数据库列表：

mysql> show databases;

如果你是搭建MySQL-Proxy成功的话，你上面查看到的数据库列表应该是192.168.1.145服务器上的数据库列表。（可以在145和126分别创建不同的数据库进行测试）

3) 进入测试数据库：

mysql> use weibo;

4) 查询表记录：

mysql>select * from blog;

5) 插入一条记录：

mysql> INSERT INTO `blog` (`TaskID`, `Content`, `Quote`, `Author`, `Time`, `Url`, `ImageUrl`, `Transmits`, `Comments`, `Hash`, `AddOn`) VALUES(’10’,’fefef’,’fefef’,’efef’,NOW(),’http://www.cnblogs.com/zgx/archive/2011/09/13/2174823.html’,NULL,’0′,’0′,’33333333′,NOW());

6) 查询表记录：

mysql>select * from blog;

对比两次查询表的记录，看记录是否有变化，我们插入了数据（确认插入成功），但两次的数据是没有变化的，这就对了，这就是读写分离了（我们读的是145的数据库，插入的是126的数据库，而我们的145与126又没有设置Replication；如果之前设置了，请先停止后进行测试）

注：有时候mysql_proxy(38)库里会显示出数据，重启系统系统，重新启动mysql后就没有此现象了。

7) 进入主写服务器(192.168.1.126) 查看数据

#mysql -u gaizai -p -h 192.168.1.126

mysql> use weibo;

mysql>select * from blog;

可以查看已经写入了一条记录。

8) 进入从读服务器(192.168.1.145)

#mysql -u gaizai -p -h 192.168.1.145

mysql> use weibo;

mysql>select * from blog;

因为没有数据显示，说明只能读，不能写。

在使用工具SQLyog执行查询时，在Proxy服务器上会自动显示下面的信息：

（Figure5：信息）

9. MySQL-Proxy+Replication

上面的测试只是测试了插入数据后，在没有进行Master与Slave的Replication设置的情况下，读取Master与Slave的数据是不同，如果想达到Figure1的效果，我们还需要设置Master与Slave之间的数据复制（Replication），详情请参考：Ubuntu10下MySQL搭建Master Slave

三、MySQL-Proxy命令

帮助命令：$mysql-proxy –help-all

查看下MySQL Proxy的版本：$ mysql-proxy -V

编译启动脚本：$vi /etc/init.d/mysql-proxy

启动命令：$ /etc/init.d/mysql-proxy start

停止命令：$ /etc/init.d/mysql-proxy stop

重启命令：$ /etc/init.d/mysql-proxy restart

四、注意事项

1. 在启动mysql-proxy的时候，可以把启动命令保存为文件：

建议使用配置文件的形式启动, 注意配置文件必须是660权限, 否则无法启动. 如果有多个Slave的话, proxy-read-only-backend-addresses参数可以配置多个以逗号分隔的IP:Port从库列表。

杀掉mysql-proxy进程：# killall mysql-proxy

新建一个文件：# vi /etc/mysql-proxy.cnf

在文件中输入两个分隔符中间的内容：

——————————————————

[mysql-proxy]

admin-username=viajarchen

admin-password=123123

admin-lua-script = /usr/share/mysql-proxy//admin-sql.lua

proxy-backend-addresses=192.168.1.126:3306

proxy-read-only-backend-addresses=192.168.1.145:3306

proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua

log-file=/var/tmp/mysql-proxy.log

log-level=debug

daemon=true

keepalive=true

max-open-files=1024

——————————————————

设置权限：# chmod 660 /etc/mysql-proxy.cnf

或者#chmod +x /etc/init.d/mysql-proxy

设置启动文件：# mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

查看信息：# ps -ef | grep mysql-proxy | grep -v grep

root 1869 1 0 18:16 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

root 1870 1869 0 18:16 ? 00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

查看日志：# tail -50f /var/tmp/mysql-proxy.log

2. mysql-proxy参数

–admin-address=host:port 指定一个mysqo-proxy的管理端口, 缺省是4041;

-P, –proxy-address=<host:port> 是mysql-proxy 服务器端的监听端口, 缺省是4040;

-r, –proxy-read-only-backend-addresses=<host:port> 只读Slave的地址和端口, 缺省为不设置;

-b, –proxy-backend-addresses=<host:port> 远程Master地址和端口, 可设置多个做failover和load balance, 缺省是127.0.0.1:3306;

–defaults-file=<file>配置文件, 可以把mysql-proxy的参数信息置入一个配置文件里;

–daemon mysql-proxy以守护进程方式运行

–keepalive try to restart the proxy if it crashed, 保持连接启动进程会有2个, 一号进程用来监视二号进程, 如果二号进程死掉自动重启proxy。

–log-level=debug定义log日志级别，由高到低分别有

(error|warning|info|message|debug)

–proxy-lua-script=file指定一个Lua脚本程序来控制mysql-proxy的运行和设置，这个脚本在每次新建连接和脚本发生修改的的时候将重新调用。

–max-open-files：指定最大档案开启数为1024，否则会有【could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.】的log讯息出现。

3. 当MySQL主从复制在 show slave status\G 时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时，，需要首先通过 stop slave 来停止从服务器，然后再进行测试读写分离。

4. MySQL-Proxy的rw-splitting.lua脚本在网上有很多版本，但是最准确无误的版本仍然是源码包中所附带的lib/rw-splitting.lua脚本，如果有lua脚本编程基础的话，可以在这个脚本的基础上再进行优化；

5. MySQL-Proxy实际上非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开–keepalive参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用，虽然比较低效但却能保证稳定性；

6. 一主多从的架构并不是最好的架构，通常比较优的做法是通过程序代码和中间件等方面，来规划，比如单双server-id号分开写入等方式来实现两个或多个主服务器；

7. MySQL-Cluster 的稳定性也不是太好；

8. Amoeba for MySQL 是一款优秀的中间件软件，同样可以实现读写分离，负载均衡等功能，并且稳定性要大大超过MySQL-Proxy，建议大家用来替代MySQL-Proxy，甚至MySQL-Cluster。

9. mysql proxy不支持old_password。另外也可以通过查看密码长度的方式来判断:select length(password) from mysql.user如果长度为16位则是old_password无疑。

10. 安装了mysql-proxy实现读写分离，有master x 1, slave x 2。为了测试failover，停掉了一个slave，然后mysql-proxy会一直报错，提示无法连接。这个情况比单点的mysql还糟糕，挂掉一个就全挂掉！mysql的工程师给提供了一段代码，替换掉：

src/network-mysqld-proxy.c的NETWORK_MYSQLD_PLUGIN_PROTO函数可以解决这个问题。network-mysqld-proxy-function.c文件。

（经过测试：我停止掉slave数据库，proxy的查询就会转移到master上，当把slave启动后，proxy依然在读master，当有新的链接进来的时候才会去读取slave的数据）

11. 如果在mysql-proxy的机器上也安装了mysql的话，新手就会在这个时候混乱了，到底要如何进行测试和链接呢？比如使用命令：#mysql -u gaizai -p -P4040 -h 192.168.1.147是表示登陆本机的4040端口，使用gaizai帐号，这个帐号可以不是本地mysql的帐号，这样就比较容易区分了。

12. 在上述环境中，mysql-proxy、mysql-master、mysql-slave三台服务器均存在单点故障。为了避免mysql-proxy单点隐患有两种方法：一种方法是mysql-proxy配合keepalived做双机，另一种方法是将mysql-proxy和应用服务安装到同一台服务器上；为了避免mysql-master单点故障可以使用DRBD+heartbear做双机；为了避免mysql-slave单点故障可以添加多台mysql-slave，mysql-proxy会自动屏蔽后端发生故障的mysql-slave。

13. 用sysbench (或者super-smack）测试mysql性能：

#sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=1000 –mysql-socket=/tmp/mysql.sock –mysql-password=123456 –mysql-user=gaizai –mysql-host=192.168.1.126 –mysql-db=weibo –num-threads=15 prepare

14. 关于mysql-proxy的启动和关闭的shell脚本的编写：

15. 读写分离不能回避的问题之一就是延迟，可以考虑Google提供的SemiSyncReplicationDesign补丁。

16. MySQL-Proxy缺省使用的是4040端口，如果你想透明的把3306端口的请求转发给4040的话，那么可以：iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp –dport 3306 -j REDIRECT –to-ports 4040如果想删除这条规则，可以把上面例子中的-I换成-D。参考链接

17. 当使用bigint 时，mysql_insert_id()存在问题，详情见手册，不过对于大多数人而言，bigint基本不会遇到，所以你可以无视这个问题）注：对于这两个问题，官方BUG库里有人给出了相应的补丁。

五、错误

在执行命令的时候出现了下面的错误：

（Figure6：错误信息）

could not raise RLIMIT_NOFILE to 8192

这个一个警告级别的错误,意思是MySQL Proxy在你的系统上不能把open files limit提升到8192，不过没关系的，MySQL Proxy依然好好的运行在你的电脑上。

可以通过设置启动–max-open-files参数解决。

MySQL Proxy安装和使用(一)

mysql proxy master and slave test

加入–max-open-files=8192后报下面的错误：

（Figure7：错误信息）

六、疑问与解答

1. 当slave宕机后，mysql-proxy是如何读取的？（经过测试：我停止掉slave数据库，proxy的查询就会转移到master上，当把slave启动后，proxy依然在读master，当有新的链接进来的时候才会重新去读取slave的数据。有时可能需要重启下mysql-proxy）

2. 如何知道mysql-proxy当前执行的select是在哪台机器上执行的？

3. 当slave宕机一段时间后，如果再次同步master的缺失的数据？

4. 当配置中设置了proxy-read-only-backend-addresses=192.168.1.145:3306

,192.168.1.147:3306类似这样的两个slave，如果两个slave的数据不同步，那么是怎么读取数据的？# tail -50f /var/tmp/mysql-proxy.log测试

5. 生产环境中除了进行程序调试外，其它不要开启mysql查询日志，因为查询日志记录了客户端的所有语句，频繁的IO操作将会导致mysql整体性能下降。如何设置呢？

6. mysql-proxy.cnf文件中的管理员帐号和密码有什么用？使用命令进入管理

mysql -u viajarchen -p -P 4041 -h 192.168.1.147 密码是123123

mysql> select * from proxy_connections;

mysql> select * from proxy_config;

（Figure8：信息）

7. 关于mysql-proxy的启动和关闭的shell脚本的编写？测试

8. 对于/usr/share/mysql-proxy/rw-splitting.lua脚本中的

local min_idle_connections = 4 local max_idle_connections = 8应该如何理解？min的话就是要达到这个值的时候才会读写分离，那么max的是什么意思呢？最大能有8个链接？

9. mysqld是什么意思？是mysql的守护进程！

10. HAProxy和keepalived怎么一起搭建使用？能解决什么问题？

七、参考文献

ubuntu 9.04 安装mysql-proxy 成功版

ubuntu10.0.4上配置MYSQL主从复制与读写分离(MySQL-Proxy)

[MySQL管理] mysql主从复制，读写分离 （MySQL Proxy）

MySQL Proxy 安装与读写分离体验

MySQL Proxy（官网）

Getting Started with MySQL Proxy（LUA文件介绍）

Read/Write Splitting with MySQL-Proxy（LUA）

MySQL Proxy learns R/W Splitting（牛人）

MySQL-Proxy负载平衡测试遇到的问题及其分析 && MySQL-Proxy工作机制（Lua）

http://www.lua.org

MySQL Proxy（常用命令）

MySQL Proxy 0.8.2 alpha（下载）

MySQL-Haproxy

mysql-proxy读写分离技术文档整理【原创】（sysbench测试）

用sysbench(或者super-smack）测试mysql性能

mysql-proxy读写分离设置（新建启动文件mysql-proxy.cnf +log）

MySQL主从复制（Master-Slave）与读写分离（MySQL-Proxy）实践（主从复制数据+经验分享）

使用MySQL-Proxy读写分离时的注意事项（查询乱码）

MySQL Proxy快速实现读写分离以及负载均衡（启动脚本）

Mysql-proxy主从搭建读写分离全过程（shell脚本启动）

MYSQL-PROXY测试实例（Lua脚本构建自己的轮询连接算法+管理MySQL Proxy ）

from:http://www.cnblogs.com/gaizai/archive/2012/11/21/2780816.html

为了对核心技术拥有更多的自主控制能力，为了解决数据库的线性扩展问题，为了尽量减少对商业软件的依赖，为了摆脱对高端硬件的依赖，为了… 基于以上多种原因，2年前，我们计划将公司某核心应用平台进行大手术：数据库平台从软件到硬件全部重构。当然，这其中应用架构的改造也不可避免的进行了大换血。

这个项目无论是从技术角度还是是业务角度来说，都对我们有着非常大的价值，也必定会带来非常深远的影响。项目历时2年多，分4个阶段才完成：

应用接口统一

这一阶段主要是为了后面真正迁移的时候做准备工作，将该核心应用系统的所有数据访问入口统一到一起，全部以服务化的接口方式呈现给其他有需要的系统，一来方便后续变更的控制，二来也推进了服务化的进程。

Oracle数据库中拆分（1拆16）

这个阶段本不是必要的，但是由于项目启动稍微晚了点，数据出现了爆发性增长，导致该系统的数据表太大（单表不带索引过500GB），原 Oracle 数据库已经快撑不住了。为了安全起见，先在 Oracle 中从一个主表以会员ID进行 hash 运算后再进行水平拆分，从1个表分拆成了16个。附表由于访问量稍小，而且全部是根据主键访问，暂时保留原样。

当然，这样的水平拆分，必然会带来数据访问路由以及数据合并的问题。我们专门为此开发了具有分布式数据库路由/数据合并，数据库读写分离，数据库链接管理等功能的数据访问中间层，专门解决拆分后给应用服务器带来的影响，使得应用服务器完全感受不到后端数据库的变化。

这个数据访问中间层，对前端应用服务器来说，就是一个完整的数据库，所有数据请求都从这里实现，以协议的方式和前端应用服务器的jdbc驱动进行交互，以便让数据库对应用服务器彻底透明。

Oracle迁移至 MySQL（16拆128）

这个阶段是整个阶段中历时最长，复杂度最高，风险系数最高的，未知因素也最多的一个阶段。虽然 MySQL 数据库已经在互联网行业占据了大片江山，但是对于阿里巴巴来说，却仍然是一个新鲜玩意儿，因为之前我们一直都用 Oracle 来提供所有的业务系统的数据库服务。

在此之前，我们从来没有在如此核心业务系统的数据库上使用过 PC Server 和本地硬盘来承载数据库，一直是使用 IBM 小型机和中高端存储设备来解决高性能和高可靠的问题。在更换成 PC Server 和本地硬盘来承载数据库之后，我们就必须面对 PC Server 本身硬件可能存在的不可靠性所带来的 Crash，所以我们必须有一套完善的 HA 切换机制，要比小型机厂商所提供的商业 HA 管理软件更加高效更加自动化更加可控，才能我们降低了设备本身可靠性之后达到原有的可用性要求。

对于一个需要满足 365 * 24 * 7 的核心业务系统来说，肯定是不可能给我们太多时间来进行数据迁移的，所以我们不得不设计出一个对现有系统影响尽可能小的迁移方案，这势必会造成方案的高度复杂化，带来更多的风险。最后的迁移方案要经历如下4个阶段：

1. Oracle 读/写;；MySQL 初始化并增量写
2. Oracle 读/写； MySQL 写
3. Oracle 写； MySQL 读/写
4. Oracle 停访问； MySQL 读/写

当然，也正式由于有如此复杂的方案，才确保了在整个迁移过程中的的停机时间被控制在了10分钟之类。

附属Detail信息迁移至 MySQL

从项目开始，至完成主表拆分结束，已经接近2年了。这2年时间内，数据量一直都在飞涨，这让即使仅仅只是按照主键访问的附表也快无法承受持续增长的业务压力，附表的拆分也就成了必行之势。由于在原来主表拆分的过程中，整个项目组已经积累了大量的经验，附表拆分过程非常顺利，基本没有出现任何问题。虽然附表的拆分过程与主表相比除了 1拆16这个阶段外没有减少其他任何环节，但是整个拆分过程也才2个月就全部搞定了。

这个迁移项目算是彻底完成了，但是我们的迁移之路并不会就此止步，还有很多的系统仍然存在扩展性问题，还有很多的数据库应用等着我们去拆分。

注：同事们还为此送了我们一个虽不太雅但也意思相近的名称 “拆迁队”。

from:http://isky000.com/database/core-databas-system-migration-from-oracle-to-mysql

AI Site

Category Archives: MySQL

Ubuntu10下搭建MySQL Proxy读写分离

核心业务系统数据库平台迁移： Oracle -> MySQL