CentOS下MySQL主从复制，读写分离

1.环境：所有系统都是CentOS5.5 mysql-5.6.31-2.el5，MySQL中都没有数据

主服务器IP为192.168.128.230

从服务器IP为192.168.128.235

代理服务器IP为192.168.128.233

2.MySQL主从复制有两种：基于日志（binlog）和基于GTID（全局事务标示符）。其中GTID有可能是不支持临时表的，所以本篇通过日志做主从复制

2.1主服务器配置

2.1.1进入mysql中，输入命令建立复制所需的用户

grant replication slave on *.* to repl@192.168.128.235 identified by ‘password’;

2.1.2进入主服务器，通过命令找到my.cnf文件所在位置，这一步需要自己一个个试过去

mysql --help | grep my.cnf

2.1.3在my.cnf中[mysqld]添加部分代码

//server-id和log-bin必须要有

server-id = 1 //数据库ID号，值必须为1到2^32–1之间的一个正整数值，且唯一。
log-bin=mysql-bin //启用二进制日志；mysql-bin可以自定义，也可以加上路径（主从可能有联系）

//如果以下两个都没指定，则从服务器默认全部复制
binlog-do-db=data //需要同步的二进制数据库名；
binlog-ignore-db=mysql //不同步的二进制数据库名;

log-slave-updates //用来配置从服务器的更新是否写入日志，如果B服务器为A服务器的从服务器，并且是C服务器的主服务器，那么需要这个选项，这样B服务器才能将它得到的更新同步到C服务器上

//slave-skip-errors有四个可选值，off、all、ddl_exist_errors和错误代码([list of error codes])，没有默认为off
slave-skip-errors=1062,1053 //跳过错误，继续执行复制；

slave-skip-errors=all

slave-skip-errors=ddl_exist_errors

//这是为了取消TIMESTAMP的警告

explicit_defaults_for_timestamp=true

而我加了如下部分

保存退出，输入命令service mysqld restart，重启mysql（由于安装Mysql的方法不同，还有可能是service mysql restart）

2.1.4再次进入mysql，运行show master status;查看主数据库master状态

记录一下File和Position两个数据，从服务器配置需要用到。

2.1.5最后还需要修改iptables，对数据库端口3306放行

vi /etc/sysconfig/iptables

新增-A INPUT –m state --state NEW –m tcp –p --dport 3306 –j ACCEPT

个人建议复制iptables上面的代码进行修改，毕竟已经可以正常运行

接着重启iptables，service iptables restart以及验证iptables –L

2.2从服务器配置

2.2.1同样需要配置my.cnf，方法和配置主服务器的时候差不多（不知道_）

//指定唯一的server_id

server_id=2

//虽然从服务器没有必须开启二进制，但是建议开启一系列相关操作

log_bin=mysql-bin

relay_log=mysql-relay-bin //配置中继日志

log_slave_updates=1 //将复制事件写进自己的二进制日志

read_only=1 //防止改变数据（除特殊的线程），但也会阻止需要在从服务器上创建表的应用

//使用以下部分，则可以跳过2.2.2（不保证成功）

master-host=192.168.128.230

master-user=root

master-password=password

master-port=3306

master-connect-retry=60 //如果发现主服务器断线，重连时间差

我添加了如下的代码

重启mysqld

2.2.2连接主服务器，并开始重做主服务器二进制日志中的事件。进入mysql使用change master to语句。

change master to master_host=’192.168.128.230’, //貌似也可以是server1

master_user=’repl’,

master_password=’password’,

master_log_file=’mysql-bin.000001’, //主服务器记录的File数据

master_log_pos=0; //这是日志开始的位置，也可以是主服务器记录的Position数据。如果和我一样是0，则不需要2.3操作

输入show slave status\G检查一下配置

这里面Slave_IO_State为空,Slave_IO_Running和Slave_SQL_Running为NO表示还没开始复制。Pos结尾的为4而不为0，是因为0只是日志开始位置，实际MYSQL的第一个时间位置是4.

2.2.3开启slave

直接在mysql中输入start slave;

再次输入show slave status\G检查，这次Slave_IO_State应该为Waiting for master to send event，Slave_IO_Running和Slave_SQL_Running应该为Yes。

2.3如果主数据库已有数据，再进行主从复制时需要增加以下操作：

2.3.1主数据库锁表，不让数据再写入数据库

mysql>flush tables with read lock;

2.3.2使用mysqldump创建一个数据转储（也可以用其他方法，只要能复制数据）

Shell>mysqldump --all-database --lock-all-tables >dbdump.db

2.3.3释放锁

mysql>unlock tables

2.4最后可以根据个人需求，在主服务器上创建测试表，进行测试

3读写分离都是在代理服务器上操作，需要用到中间件，运行流程大概如下图。我这里使用的中间件是MySQL-Proxy。

3.1安装lua

3.1.1安装lua的前置软件

yum –y install gcc* ncurses-devel libevent-devel libtermcap-devel

readline需要手动安装不然安装lua时有问题，通过命令下载软件包wget ftp://ftp.gnu.org/gnu/readline/readline-6.3.tar.gz

tar –zxvf readline-6.3.tar.gz //输入解压

cd readline-6.3 //进入加压后的文件夹

//以下是配置安装

./configure --prefix=/usr

make SHLIB_LIBS=-lncurses

make install

ldconfig

3.1.2开始安装lua

首先在http://www.lua.org/ftp/上确认需要的版本号，转到打算解压的文件夹下，输入命令wget http://www.lua.org/ftp/lua-5.3.3.tar.gz下载lua。

接着输入解压命令tar –zxvf lua-5.3.3.tar.gz

cd lua-5.3.3

make linux

make install

5.1.3测试lua

lua

print(“Hello!”);

5.2安装MySQL-Proxy

通过命令下载wget http:// cdn.mysql.com/Downloads/MySQL-Proxy /mysql-proxy-0.8.5-linux-rhel5-x86-64bit.tar.gz

这个地址不是最新的地址，但是官网会自动转过去，不用担心。

解压tar –zxvf mysql-proxy-0.8.5-linux-rhel5-x86-64bit.tar.gz

cd mysql-proxy-0.8.5-linux-rhel5-x86-64bit

创建mysql-proxy服务器脚本，mkdir /sw/ mysql-proxy-0.8.5-linux-rhel5-x86-64bit/init.d

编辑脚本，vim 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=/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/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.128.235:3306 --proxy-backend-addresses=192.168.128.230:3306 --proxy-lua-script=/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/scripts/rw-splitting.lua"

PROXY_PID=/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/run/mysql-proxy.pid

# Source mysql-proxy configuration.

if [ -f /etc/sysconfig/mysql-proxy ]; then

. /etc/sysconfig/mysql-proxy

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=root --log-level=warning --log-file=/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/log/mysql-proxy.log

RETVAL=$?

echo

if [ $RETVAL = 0 ]; then

touch /var/lock/subsys/mysql-proxy

echo “ok”

;;

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

;;

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

====================脚本编辑完成==============

注意事项：1. 第五行代码#chkconfig: - 78 30，这里减号前后要有空格，不然开机自启会失败

2.${NETWORKING}这部分中间不能出现空格

3.如果出现syntax error near unexpected token报错，这是由于转行符的问题，完成脚本后重新编辑一下，基本就好了。还有问题就上网搜一下，这个网上还是后很多解决办法的。

==================脚本参数详解=================

PROXY_PATH=/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/bin //定义mysql-proxy服务二进制文件路径

PROXY_OPTIONS="--proxy-read-only-backend-addresses=192.168.10.235:3306 \        //定义后端只读从服务器地址
--proxy-backend-addresses=192.168.10.230:3306 \   //定义后端主服务器地址
--proxy-lua-script=/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/scripts/rw-splitting.lua" \       //定义lua读写分离脚本路径

PROXY_PID=/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/run/mysql-proxy.pid //定义mysql-proxy PID文件路径

$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS \
--daemon \ //定义以守护进程模式启动
--keepalive \ //使进程在异常关闭后能够自动恢复
--pid-file=$PROXY_PID \    //定义mysql-proxy PID文件路径
--user=root \ //以root用户身份启动服务
--log-level=warning \        //定义log日志级别，可自选，由高到低分别有(error|warning|info|message|debug)
--log-file=/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/log/mysql-proxy.log       //定义log日志文件路径

编写完成后将脚本拷贝到指定地点，cp mysql-proxy /sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/init.d/

添加执行权限

chmod +x /sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/init.d/mysql-proxy

创建脚本中需要用到的目录

mkdir /sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/run

mkdir /sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/log

mkdir /sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/scripts

回到sw文件夹下cd /sw，通过命令下载包含rw-splitting.lua读写分离脚本的文件wget http:// cdn.mysql.com/Downloads/MySQL-Proxy /mysql-proxy-0.8.5.tar.gz

解压tar –zxvf mysql-proxy-0.8.5.tar.gz

cd mysql-proxy-0.8.5.tar.gz

拷贝rw-splitting.lua到指定地点，cp lib/rw-splitting.lua /sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/scripts

修改默认连接，进行测试如不修改需要达到4个连接数才会启动读写分离，vim /sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/scripts/rw-splitting.lua

找到一下部分进行修改（测试完自己按需求修改）

=================修改目标===================

-- 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，/sw/mysql-proxy-0.8.5-linux-rhel5-x86-64bit/init.d/mysql-proxy start

防火墙端口处理一下，主从3306，代理4040

vi /etc/sysyconfig/iptables

将22端口的代码复制黏贴，再将22改为3306/4040

/etc/init.d/iptables restart

5.3测试mysql-proxy

登录主服务器给代理服务器权限

mysql>grant all on *.* to proxy1@192.168.128.233 identified by ‘password’;