MySQL读写分离 - 数据库编程

only-backend-addresses和proxy-backend-addresses参数配置出错，proxy-read-only-backend-addresses应该配置成从服务器的IP地址，proxy-backend-addresses应该配置成主服务器的IP地址。

[root@serv01 ~]# vim /etc/init.d/mysql-proxy 
[root@serv01 ~]# cat /etc/init.d/mysql-proxy
#!/bin/sh
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql

# Source function library.
. /etc/rc.d/init.d/functions

#PROXY_PATH=/usr/local/bin
PROXY_PATH=/usr/local/mysql-proxy/bin

prog="mysql-proxy"

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = "no" ] && exit 0

# Set default mysql-proxy configuration.
#PROXY_OPTIONS="--daemon"
PROXY_OPTIONS="--proxy-read-only-backend-addresses=192.168.1.19:3306 --proxy-backend-addresses=192.168.1.18:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"

#PROXY_PID=/usr/local/mysql-proxy/run/mysql-proxy.pid
PROXY_PID=/var/run/mysql-proxy.pid

# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
        . /etc/sysconfig/mysql-proxy
fi

PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

# By default it's all good
RETVAL=0

# See how we were called.
case "$1" in
  start)
        # Start daemon.
        echo -n $"Starting $prog: "
        $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql --log-level=debug --log-file=/var/log/mysql-proxy.log --proxy-address=192.168.1.11:3306
        RETVAL=$ 
        echo
        if [ $RETVAL = 0 ]; then
                touch /var/lock/subsys/mysql-proxy
        fi
       ;;
  stop)
        # Stop daemons.
        echo -n $"Stopping $prog: "
        killproc $prog
        RETVAL=$ 
        echo
        if [ $RETVAL = 0 ]; then
                rm -f /var/lock/subsys/mysql-proxy
                rm -f $PROXY_PID
        fi
       ;;
  restart)
        $0 stop
        sleep 3
        $0 start
       ;;
  condrestart)
       [ -e /var/lock/subsys/mysql-proxy ] && $0 restart
      ;;
  status)
        status mysql-proxy
        RETVAL=$ 
       ;;
  *)
        echo "Usage: $0 {start|stop|restart|status|condrestart}"
        RETVAL=1
       ;;
esac

exit $RETVAL

第十步，测试。插入数据，可以发现连接的是主服务器，查询的时候也是主服务器。说明主服务器和从服务器均有读的的功能。

[root@serv01 ~]# mysql -ularry -plarry -h 192.168.1.11

[connect_server] 192.168.1.11:57891
  [1].connected_clients = 0
  [1].pool.cur_idle     = 0
  [1].pool.max_idle     = 1
  [1].pool.min_idle     = 1
  [1].type = 1
  [1].state = 1
  [1] idle-conns below min-idle
[read_query] 192.168.1.11:57891
  current backend   = 0
  client default db = 
  client username   = larry
  query             = select @@version_comment limit 1
  sending to backend : 192.168.1.18:3306
    is_slave         : false
    server default db: 
    server username  : larry
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
mysql> insert into user values(5,'test');
Query OK, 1 row affected (0.01 sec)
[read_query] 192.168.1.11:57893
  current backend   = 0
  client default db = larrydb
  client username   = larry
  query             = insert into user values(5,'test')
  sending to backend : 192.168.1.18:3306
    is_slave         : false
    server default db: larrydb
    server username  : larry
    in_trans        : false
    in_calc_found   : false
    COM_QUERY       : true
mysql> select * from user;
+------+----------+
| id   | name     |
+------+----------+
|    1 | larrywen |
|    2 | wentasy  |
|    5 | test     |
+------+----------+
3 ro

MySQL读写分离(五)