mysql+proxy+mmm架构

一、mysql主从配置

master服务器：192.168.50.216

slave服务器：192.168.50.217

1.master服务器配置

（1）grant replication slave on *.* to replication@192.168.50.217 identified by 'mysql'

（2）vim /etc/my.cnf

确保有如下行：

server-id = 1

log-bin=mysql-bin

binlog-do-db=reptest //需要备份的数据库名，可写多行

binlog-ignore-db=mysql //不需要备份的数据库名

2.slave服务器配置

（1）vim /etc/my.cnf

确保有如下行：

server-id=2

log-bin=mysql-bin

master-host=192.168.50.216

master-user=replication

master-password=mysql

master-port=3306

replicate-do-db=reptest

master-connect-retry=60

replicate-ignore-db=mysql

注意：必须手动同步一下主从服务器中要备份的数据库，然后重启主，从服务器

3.验证配置是否正确

（1）登录slave服务器输入如下命令:

mysql> show slave status/G;

确如如下行一致:

Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

在上面启动成功的同时,可能会出现如下错误需要及时去看errorlog

Last_IO_Errno: 2013

Last_IO_Error: error reconnecting to master 'replication'@192.168.50.216:3306' - retry-time: 60 retries: 86400

这个一般是启动时

Slave_IO_Running: No

随后导致的IO错误,这个可以在errorlog里面查询得到,一般是配置文件不正确导致的,或者Master服务器没有及时同步info导致的,因为Master-slave的同步其实是异步方式

在slave的datadir下会出现一个master.info和一个relay.info

（2）登录master服务器输入如下命令:

mysql> show master status;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 491 | reptest | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

（3）验证：登入master数据库,插入删除数据，查看slave数据是否同步

二、mysql主主配置

在以上配置的基础上添加

master服务器1：192.168.50.216

master服务器2：192.168.50.217

1.配置master服务器1

vim /etc/my.cnf

确保有如下行：

master-host = 192.168.50.217

master-user = replication

master-password = mysql

master-port = 3306

2.配置master服务器2

grant replication slave on *.* to ‘replication’@192.168.50.216'

identified by 'mysql';

3.重启mysql服务器，查看主从相关命令

show master status;

show slave status/G;

slave start

slave stop

show logs

show binlog events

4.常见问题汇总:

（1）保证master的File，Position对应Slave的Master_Log_File，Read_Master_Log_Pos

（2）在slave中新建一张表，但是master中没有出现。

这样做是错误的，只有master中创建的表格以及插入的数据才会同步到slave中，反之不行，而且这样做将会导致slave无法正常工作。

（3）在主主配置中

输入命令mysql>show slave status/G;

如果出现：Last_IO_Error: error reconnecting to master 'replication@192.168.50.216:3306' - retry-time: 60 retries: 86400

属正常现象

（4）slave_io_running:yes（网络正常);slave_sql_running:yes(表结构正常）

三、mysql-proxy实现mysql读写分离

1、环境

master服务器1：192.168.50.216

master服务器2：192.168.50.217

proxy服务器：192.168.50.219

2、配置proxy服务器

（1）安装LUA

wget http://www.lua.org/ftp/lua-5.1.4.tar.gz

tar zxvf lua-5.1.4.tar.gz

cd lua-5.1.4

vim修改Makefile,使"INSTALL_TOP=/usr/local/lua",这样做的目的是为了是lua的所有文件都安装在目录/usr/local/lua/

make posix

make install

（2）安装libevent

wget http://monkey.org/~provos/libevent-1.4.13-stable.tar.gz

tar zxvf libevent-1.4.13-stable.tar.gz

cd libevent-1.4.13

./configure --prefix=/usr/local/libevent

make && make install

（3）设置mysql-proxy所需的环境变量，把下面的内容追加到/etc/profile

export LUA_CFLAGS="-I/usr/local/lua/include" LUA_LIBS="-L/usr/local/lua/lib -llua -ldl" LDFLAGS="-L/usr/local/libevent/lib -lm"

export CPPFLAGS="-I/usr/local/libevent/include"

export CFLAGS="-I/usr/local/libevent/include"

执行 source /etc/profile

（4）安装mysql(只需要mysql客户端）

tar zxvf mysql-5.1.45.tar.gz

cd mysql-5.1.45

./configure --prefix=/usr/local/mysql --without-server

make && make install

（5）安装mysql-proxy

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.6.1.tar.gz

cd mysql-proxy-0.6.1

./configure --prefix=/usr/local/mysql-proxy --with-mysql=/usr/local/mysql --with-lua

make && make install

（6）验证

1.进入mysql-proxy服务器，执行

/usr/local/mysql-proxy/sbin/mysql-proxy --proxy-address=192.168.50.219:4040 --proxy-backend-addresses=192.168.50.216:3306 --proxy-read-only-backend-addresses=192.168.50.217:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/mysql-proxy/rw-splitting.lua&

2.在master1上执行

grant all on *.* to 'root'@'%' identified by '111111';

flush privileges;

3.任意选一台mysql服务器，执行

mysql -uroot -p111111 -h 192.168.50.219 -P 4040

四、安装布置MMM

环境：MMM服务端:192.168.50.218

MMM客户端：192.168.50.216，192.168.50.217

1.安装MMM服务端,需要以下四个组件

wget http://ftp.osuosl.org/pub/nslu2/sources/Algorithm-Diff-1.1902.tar.gz

wget http://ftp.riken.go.jp/pub/pub/lang/CPAN/modules/by-module/Proc/EHOOD/Proc-Daemon-0.03.tar.gz

wget http://mysql-master-master.googlecode.com/files/mysql-master-master-1.2.6.tar.gz

yum -y install perl-DBD-MySQL

（1）先安装两个perl包

Algorithm-Diff-1.1902.tar.gz

Proc-Daemon-0.03.tar.gz

perl安装过程

perl Makefile.PL

make

make test

make install

（2）安装MMM

./install.pl

2.安装MMM客户端，只需要安装mysql-master-master-1.2.6.tar.gz

./install.pl

三台主机安装以上软件后，即可进行配置

3.先安装两台MMM客户端

$cd /usr/local/mmm/etc

$cp examples/mmm_agent.conf.examples ../mmm_agent.conf

$cp examples/mmm_comment.conf.example ../mmm_common.con

（1） mmm_agent.conf主要内容

bind_port 9989
# Define current server id
this db1
mode master
peer db2
# Cluster hosts addresses and access params
host db1
ip 192.168.50.216
port 3306
user rep_agent
password repagent

host db2
ip 192.168.50.217
port 3306
user rep_agent
password repagent

（2）mmm_common.conf主要内容

agent_port 9989
monitor_ip 127.0.0.1

# Cluster hosts addresses and access params
host db1
ip 192.168.50.216
port 3306
user rep_agent
password repagent
mode master
peer db2

host db2
ip 192.168.50.217
port 3306
user rep_agent
password repagent
mode master
peer db1

# Define roles that are assigned to the above hosts
# Mysql Reader role
role reader //设置读规则
mode balanced //模式为均摊
servers db1, db2 //规则覆盖db1 db2
ip 192.168.50.243,192.168.50.244 //对应ip虚拟的IP

# Mysql Writer role
role writer //写规则
mode exclusive //模式为独占
servers db1,db2 //规则负载db1 db2
ip 192.168.50.245//两台数据库公用一个ip为写，采用HA模式，默认db1使用，db1下线db2接管此ip

（3）设置权限（MMM客户端）

GRANT ALL PRIVILEGES on *.* to'rep_agent'@'%' identified by 'repagent';

4.配置MMM服务端

$cd /usr/local/mmm/etc

$cp examples/mmm_mon.conf.examples ../mmm_mon.conf

$cp examples/mmm_comment.conf.example ../mmm_common.con

（1）mmm_mom.conf保持不变

mmm_common.conf主要内容

cluster_interface eth0

# Cluster hosts addresses and access params
host db1
ip 192.168.50.216
port 3306
user rep_agent
password repagent
mode master
peer db2

host db2
ip 192.168.50.217
port 3306
user rep_agent
password repagent
mode master
peer db1

# Define roles that are assigned to the above hosts
# Mysql Reader role
role reader
mode balanced
servers db1, db2
ip 192.168.50.243, 192.168.50.244

# Mysql Writer role
role writer
mode exclusive
servers db1,db2
ip 192.168.50.245

5.MMM测试

（1）启动MMM客户端

/usr/local/mmm/scripts/init.d/mmm_agent start

Starting MMM Agent daemon: MySQL Multi-Master Replication Manager
Version: 1.2.6
Ok

以上信息说明客户端启动正常

（2）启动MMM服务端

/usr/local/mmm/scripts/init.d/mmm_mon start

# mmm_control set_online db1

# mmm_control set_online db2

# mmm_control show 查看分配情况

正常情况下:
# mmm_control show
Servers status:
db1(192.168.50.216):master/ONLINE.Roles:reader(192.168.50.243;), writer(192.168.50.244;)
db2(192.168.50.217):master/ONLINE.Roles:reader(192.168.50.245;)
stop 192.168.50.217
# mmm_control show

Servers status:
db1(192.168.50.216): master/ONLINE. Roles: reader(192.168.50.243;), reader(192.168.50.244;), writer(192.168.50.245;)
db2(192.168.50.217): master/AWAITING_RECOVERY. Roles: None
Telnet任何一个虚拟IP 3306都是通的

参考文档：http://blog.csdn.net/starxu85/archive/2008/07/27/2717833.aspx