discuz mysql 主从_LAMP+redis搭建discuz论坛，基于mysql-proxy插件主从同步

测试环境：

MySQL-proxy   IP：192.168.72.134

MySQL-master  IP：192.168.72.128

MySQL-slave   IP：192.168.72.130

前提：MySQL主库与从库已经实现主从复制。

实现主从复制请参考我的前一篇博文：https://www.cnblogs.com/aqicheng/p/12186397.html

接下来的操作在proxy服务器上：

[root@SQL-proxy ~]# cd /usr/local/src/[root@SQL-proxy src]# wgethttp://ftp.ntu.edu.tw/pub/MySQL/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

[root@SQL-proxy src]# ll -h

total 12M-rw-r--r-- 1 root root 12M Aug 19 2014 mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

[root@SQL-proxy src]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/[root@SQL-proxy src]# cd ..

[root@SQL-proxy local]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy-0.8.5[root@SQL-proxy local]# ll -d mysql-proxy-0.8.5drwxr-xr-x 8 7161 wheel 4096 Aug 19 2014 mysql-proxy-0.8.5# 看到所属用户和组有问题

[root@SQL-proxy local]# chown -R root:root mysql-proxy-0.8.5/# 改为root

[root@SQL-proxy local]# ln -s mysql-proxy-0.8.5/ mysql-proxy # 必备软连接

[root@SQL-proxy local]# cd mysql-proxy

[root@SQL-proxy mysql-proxy]# mkdir etc # 创建 lua 脚本存放目录

[root@SQL-proxy mysql-proxy]# mkdir logs # 创建日志目录

把实现读写分离的 lua 脚本复制到 etc 目录下

[root@SQL-proxy mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua etc/[root@SQL-proxy mysql-proxy]# cp share/doc/mysql-proxy/admin-sql.lua etc/

创建启动参数文件，启动服务时命令行就不用老长老长的。

[root@SQL-proxy ~]# vim /etc/mysql-proxy.cnf

[mysql-proxy]

user=root

admin-username=root

admin-password=123456

proxy-address=192.168.72.134

proxy-backend-addresses=192.168.72.139:3306

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

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

admin-lua-script=/usr/local/src/mysql-proxy/etc/admin-sql.lua

log-file=/usr/local/src/mysql-proxy/logs/mysql-proxy.log

log-level=info

daemon=true

keepalive=true

启动服务

[root@SQL-proxy ~]# /usr/local/src/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

2017-04-24 16:40:19: (critical) mysql-proxy-cli.c:326: loading config from '/etc/mysql-proxy.cnf' failed: permissions of /etc/mysql-proxy.cnf aren't secure (0660 or stricter required)

2017-04-24 16:40:19: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328

2017-04-24 16:40:19: (message) shutting down normally, exit code is: 1

！！看到无法启动，文件权限要改，因为文件里面有账号密码。

[root@SQL-proxy ~]# chmod 660 /etc/mysql-proxy.cnf # 改权限

[root@SQL-proxy ~]# ll /etc/mysql-proxy.cnf

-rw-rw---- 1 root root 432 Apr 24 16:31 /etc/mysql-proxy.cnf

[root@SQL-proxy ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf # 重新启动

[root@SQL-proxy ~]# netstat -lntup # 查看服务

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name

tcp        0      0 192.168.0.86:3306           0.0.0.0:*                   LISTEN      3086/mysql-proxy

老是命令行敲一长串命令肯定不科学了，直接上服务管理脚本。

[root@SQL-proxy ~]# 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 formysql

# 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 networkingisup.

[ ${NETWORKING}= "no" ] && exit 0DEFAULT_FILE=/etc/mysql-proxy.cnf

PROXY_PID=/var/run/mysql-proxy.pid

RETVAL=0start() {

echo-n $"Starting MySQL-proxy... :"daemon $prog--defaults-file=$DEFAULT_FILE

RETVAL=$?echoif [ $RETVAL -eq 0]; then

touch/var/lock/subsys/mysql-proxy.lockfi

}

stop() {

echo-n $"Stopping MySQL-proxy... :"killproc-p $PROXY_PID -d 3$prog

RETVAL=$?echoif [ $RETVAL -eq 0]; then

rm-f /var/lock/subsys/mysql-proxy.lockrm-f $PROXY_PID

fi

}

# See how we were called.case "$1" instart)

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@SQL-proxy ~]# chmod +x /etc/init.d/mysql-proxy   #  给执行权限

[root@SQL-proxy ~]# chkconfig mysql-proxy on           # 可以加入开机启动

测试一下脚本：

[root@SQL-proxy ~]# /etc/init.d/mysql-proxy status

mysql-proxy (pid 3086) isrunning...

[root@SQL-proxy ~]#

[root@SQL-proxy ~]# /etc/init.d/mysql-proxy stop

Stopping MySQL-proxy... : [ OK ]

[root@SQL-proxy ~]#

[root@SQL-proxy ~]# /etc/init.d/mysql-proxy status

mysql-proxy isstopped

[root@SQL-proxy ~]# netstat -lntup

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1079/sshd

tcp0 0 :::22 :::* LISTEN 1079/sshd

[root@SQL-proxy ~]# /etc/init.d/mysql-proxy start

Starting MySQL-proxy... : [ OK ]

[root@SQL-proxy ~]# /etc/init.d/mysql-proxy restart

Stopping MySQL-proxy... : [ OK ]

Starting MySQL-proxy... : [ OK ]

[root@SQL-proxy ~]#

[root@SQL-proxy ~]# netstat -lntup

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

tcp0 0 192.168.0.86:3306 0.0.0.0:* LISTEN 3143/mysql-proxy

以上MySQL-proxy服务器搭建完成，但是用于连接后端数据库的用户：proxy还需要在数据库端创建