标签归档:读写分离

MySQL Proxy learns R/W Splitting

The trunk version of the MySQL Proxy 0.6.0 just learnt about changing backends within running connection. It is now up to lua-script to decide which backend shall be used to send requests too.

We wrote a complete tutorial which covers everything from:

  • building and maintaining a connection pool with high and low water marks
  • transparent authentication (no extra auth against the proxy)
  • deciding on Query Level which backend to use

and implement a transparent read/write splitter which sends all non-transactional Queries to the slaves and the rest to the master. 继续阅读

mysql 主从复制读写分离实现

作者:田逸

通过主从复制,然后使用mysql-proxy实现

I mysql主从复制
(一)安装mysql(主从服务器皆相同) 先创建用户 useradd mysql -s /sbin/nologin

tar zxvf mysql-5.0.45.tar.gz

cd mysql-5.0.45
./configure –prefix=/usr/local/mysql –localstatedir=/opt/data –with-extra-charsets=utf8,gb2312,gbk –with-pthread –enable-thread-safe-client
注:配置过程指定数据文件的位置及另加的字符集.
make
make install
cp support-files/my-large.cnf   /etc/my.cnf
cd /usr/local/mysqlchgrp -R mysql .

生成基本的数据库和表:

/usr/local/mysql/bin/mysql_install_db –user=mysql

成功执行后察看数据目录/opt/data,看是否有文件或目录生成.

chown -R mysql:mysql /opt/data

记得给每个数据库设置root密码.
继续阅读

MySQL Proxy快速实现读写分离以及负载均衡

作/译者:叶金荣

1. 安装
下载已经编译好的安装包,或者预编译安装包均可,在这里,使用预编译版本。

[@s1.yejr.com ~]# tar zxf mysql-proxy-0.6.0-linux-rhas4-x86.tar.gz
[@s1.yejr.com ~]# cd mysql-proxy-0.6.0-linux-rhas4-x86
#可以看到有2个目录
[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# ls
sbin  share
[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# mv sbin/mysql-proxy /usr/local/sbin/
[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# ls share
mysql-proxy         tutorial-constants.lua  tutorial-packets.lua     tutorial-rewrite.lua  tutorial-warnings.lua
tutorial-basic.lua  tutorial-inject.lua     tutorial-query-time.lua  tutorial-states.lua
#将lua脚本放到/usr/local/share下,以备他用
[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# mv share/mysql-proxy /usr/local/share/
#删除符号连接等垃圾代码
[@s1.yejr.com mysql-proxy-0.6.0-linux-rhas4-x86]# strip /usr/local/sbin/mysql-proxy

2. 启动
编译一下启动管理脚本:

[@s1.yejr.com ~]# vi /etc/init.d/mysql-proxy
#!/bin/sh
export LUA_PATH=/usr/local/share/mysql-proxy/?.lua
mode=$1
if [ -z "$mode" ] ; then
  mode="start"
fi
case $mode in
  'start')
    mysql-proxy --daemon \
--admin-address=:4401 \
--proxy-address=:3307 \
--proxy-backend-addresses=:3306 \
--proxy-read-only-backend-addresses=192.168.133.232:3306 \
--proxy-read-only-backend-addresses=10.10.74.61:3306 \
--proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua
    ;;
  'stop')
    killall mysql-proxy
    ;;
  'restart')
    if $0 stop ; then
      $0 start
    else
      echo  "retart failed!!!"
      exit 1
    fi
    ;;
esac
exit 0

现在解释一下启动脚本:
–daemon 采用daemon方式启动
–admin-address=:4401 指定mysql proxy的管理端口,在这里,表示本机的4401端口
–proxy-address=:3307 指定mysql proxy的监听端口,也可以用 127.0.0.1:3307 表示
–proxy-backend-addresses=:3306 指定mysql主机的端口
–proxy-read-only-backend-addresses=192.168.1.1:3306 指定只读的mysql主机端口
–proxy-read-only-backend-addresses=192.168.1.2:3306 指定另一个只读的mysql主机端口
–proxy-lua-script=/usr/local/share/mysql-proxy/rw-splitting.lua 指定lua脚本,在这里,使用的是rw-splitting脚本,用于读写分离

完整的参数可以运行以下命令查看:

mysql-proxy --help-all

运行以下命令启动/停止/重启mysql proxy:

[@s1.yejr.com ~]# /etc/init.d/mysql-proxy start
[@s1.yejr.com ~]# /etc/init.d/mysql-proxy stop
[@s1.yejr.com ~]# /etc/init.d/mysql-proxy restart

3. 试用

[@s1.yejr.com ~]# mysql -h127.0.0.1 -uroot -P3307
mysql> show processlist;
+-------+------+----------------+------+---------+------+-------+------------------+
| Id    | User | Host           | db   | Command | Time | State | Info             |
+-------+------+----------------+------+---------+------+-------+------------------+
| 30052 | root | localhost:9656 | NULL | Query   |    0 | NULL  | show processlist |
+-------+------+----------------+------+---------+------+-------+------------------+

可以看到,产生了一个新连接。

用sysbench测试一下,看会不会挂掉:

[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test prepare
[@s1.yejr.com ~]# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 \
--mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-db=test run
.........
.........
Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   23.0387/0.00

还好,没给大家丢脸,剩下的测试自己完成吧 🙂

4. 其他
mysql proxy还可以实现连接池的功能,这在很多LAMP开发中是软肋,因此,有了mysql proxy,就可以不用再担心连接数超限的问题了。
如果使用rw-splitting.lua脚本的话,最好修改以下2个参数的默认值:

min_idle_connections = 1
max_idle_connections = 3

继续阅读