查看内容

Ubuntu10下搭建MySQL Proxy读写分离

  • 2019-12-12 14:17
  • 新浦京计算机网络
  • Views

使用MySQL-Proxy读写分离时的注意事项

一、MySQL-Proxy基础

 

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

在动手操作前最好先安装好MySQL-Proxy,并配置好MySQL主从服务器。补充:新版MySQL已经内建支持

图片 1

  www.2cto.com  

(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
  • Ubuntu10下搭建MySQL Proxy读写分离。query rewriting
  • executing shell commands

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

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查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。

MySQL-Proxy缺省使用的是4040端口,如果你想透明的把3306端口的请求转发给4040的话,那么可以:

 

 

二、实战过程

iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp --dport 3306 -j REDIRECT --to-ports 4040

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

 

192.168.1.147  proxy 代理 入口

如果想删除这条规则,可以把上面例子中的-I换成-D。

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。

MySQL-Proxy不支持老的密码加密方式,所以如果你使用的是老版本的MySQL,或者启用了old_passwords选项的话,则可能会出现错误:

2.     安装配置MySQL-Proxy

 

#apt-get  mysql-proxy

ERROR 2013: Lost connection to MySQL server

当前获取到的版本是:mysql-proxy 0.8.0(查看版本命令:#mysql-proxy -V)

 

3.     修改rw-splitting.lua

此时最好的修复方法就是使用新的密码加密方式,如果你的用户表是老式的,可能需要先运行MySQL源代码里scripts目录下的mysql_fix_privilege_tables脚本升级表结构。有时候客观情况可能不允许立刻进行升级操作,此时可以为MySQL-Proxy专门建立一个密码为空的用户(通过主机限制访问,或者起一个很复杂的用户名),因为不管是新的密码加密方式还是旧的密码加密方式,空密码都同样是一个空字符串,这样就规避了密码加密的问题。

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

  www.2cto.com  

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

查询乱码

-- connection pool

 

if not proxy.global.config.rwsplit then

连接上MySQL-Proxy后,执行查询时,随机出现乱码。出现此问题的原因是当我们使用MySQL-Proxy读写分离时,通常会有多个后端服务器,客户端发出查询请求时,一般会先发出一条类似"SET NAME gbk"的语句来声明客户端编码,然后再发出实际查询的SQL语句,但MySQL-Proxy可能会把这两条语句分发给不同的后端服务器,于是就出现了乱码。

proxy.global.config.rwsplit = {

 

min_idle_connections = 1, //默认为4

解决方法是强行指定后端服务器的字符编码:

max_idle_connections = 1, //默认为8

 

is_debug = false

init-connect='SET NAME gbk'

}

 

end

default-character-set=gbk

这是因为mysql-proxy会检测客户端连接,当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。

skip-character-set-client-handshake

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

 

       #mkdir /var/log/mysql-proxy

如果使用init-connect,则需要注意操作用户不能有SUPER权限,否则此选项无效。

       #vi mysql-proxy.log

 

5.     执行读写分离

即便做好了以上的设置后,还有可能会出现乱码,比如说数据库是gbk的,当我们用PHPMyAdmin连接MySQL-Proxy时,查询还是会出现乱码,不过这是正常的!因为PHPMyAdmin使用的是utf8编码,它发出的“SET NAMES utf8”语句被skip-character-set-client-handshake屏蔽了,所以出现乱码。

#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 代理 入口

  www.2cto.com  

192.168.1.126  master  主机 只写

MySQL-Proxy偶尔会出现进程崩溃的情况,具体原因不明。

192.168.1.145  slaver  从机 只读

 

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

新版的MySQL-Proxy为了应付这个问题加入了一个keepalive选项(try to restart the proxy if it crashed),当使用这个选项时,会先后启动两个mysql-proxy进程,先启动的mysql-proxy进程用来监控后启动的mysql-proxy进程,实际提供服务的是后启动的mysql-proxy进程,一旦后启动的mysql-proxy进程挂掉(你可以自己kill试试),先启动的mysql-proxy进程会重新启动一个mysql-proxy提供服务。

6.     查看进程端口

 

#netstat -ant

不过现在很多人用的还是旧版的MySQL-Proxy,此时可以利用init来实现类似keepalive的效果:

#netstat –ntl

 

图片 2

编写脚本/usr/local/sbin/mysql-proxy.sh,加入以下内容(具体写法视安装情况而定):

(Figure2:端口)

 

tcp        0      0 0.0.0.0:4040            0.0.0.0:*               LISTEN    

LUA_PATH="/usr/local/mysql-proxy/share/mysql-proxy/?.lua"

tcp        0      0 0.0.0.0:4041            0.0.0.0:*               LISTEN    

/usr/local/mysql-proxy/sbin/mysql-proxy

7.     查看数据库链接

--proxy-backend-addresses=192.168.0.1:3306

mysql> show processlistG;

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

图片 3

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

(Figure3:进程)

 

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

别忘了加上可执行属性:

8.     测试读写分离

 

1)     在mysql-proxy机子进入MySQL

chmod a+x /usr/local/sbin/mysql-proxy.sh

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

 

必须指定-h参数,不然报下面错误:

0.7.0版本有一个新的选项:--defaults-file,可以把相关信息都写到配置文件里:

图片 4

 

(Figure4:出错)

# MySQL Proxy's configuration file (mysql-proxy.cnf)

2)     显示数据库列表:

 

mysql> show databases;

[mysql-proxy]

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

daemon = true

3)     进入测试数据库:

keepalive = true

mysql> use weibo;

proxy-backend-addresses = 192.168.0.1:3306

4)     查询表记录:

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

mysql>select * from blog;

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

5)     插入一条记录:

 

mysql> INSERT INTO `blog` (`TaskID`, `Content`, `Quote`, `Author`, `Time`, `Url`, `ImageUrl`, `Transmits`, `Comments`, `Hash`, `AddOn`) VALUES('10','fefef','fefef','efef',NOW(),'));

启动时可以使用:mysql-proxy --defaults-file=mysql-proxy.cnf

6)     查询表记录:

 

mysql>select * from blog;

修改inittab:

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

  www.2cto.com  

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

vi /etc/inittab

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

 

#mysql -u gaizai -p -h 192.168.1.126

加入以下内容:

mysql> use weibo;

 

mysql>select * from blog;

mp:12345:respawn:/usr/local/sbin/mysql-proxy.sh

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

 

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

然后让init重新读取inittab内容:

#mysql -u gaizai -p -h 192.168.1.145

 

mysql> use weibo;

kill -HUP 1

mysql>select * from blog;

 

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

系统会自动检测/usr/local/sbin/mysql-proxy.sh是否正在运行,如果没有就自动运行。

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

 

图片 5

需要注意的是在编写mysql-proxy.sh脚本的时候,不要加入--daemon选项,否则/usr/local/sbin/mysql-proxy.sh一运行就结束了,系统会不停的尝试运行脚本,从而在/var/log/message里留下大量的错误信息(init: Id "mp" respawning too fast: disabled for 5 minutes)。

(Figure5:信息)

 

9.     MySQL-Proxy+Replication

init的方法可能显得有点另类了,可以使用其他的工具,比如svscan。

上面的测试只是测试了插入数据后,在没有进行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

SELECT SQL_CALC_FOUND_ROWS ..

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

SELECT FOUND_ROWS()

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

 

 

这种查询是有状态的,应该保证在同一个后端处理,查看rw-splitting.lua脚本可以看到MySQL-Proxy实际上已经对这样的查询进行了 判断,但在实际应用中发现还是存在问题。估计是脚本写得不咋地,实际应用中,建议大家不要使用这样的查询,一来没有可移植性,而来效率也不见得好。

四、注意事项

 

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

另一个可能会产生问题的查询是:

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

  www.2cto.com  

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

INSERT ... (AUTO_INCREMENT)

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

SELECT LAST_INSERT_ID()

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

 


上一篇:debian apt mysql无密码安装 下一篇:没有了