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

阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

架构设计架构设计架构设计

2011年04月24日 3.34MB 下载

详解x86架构

2011年09月28日 1.6MB 下载

安全架构.pptx

2017年09月01日 387KB 下载

安全架构总括

2018年05月31日 117KB 下载

架构之美完整版

2018年04月04日 46.41MB 下载

oracle高级架构和高性能架构

2011年05月10日 2.6MB 下载

Android 艺术

2018年03月09日 281KB 下载

3层架构3层架构3层架构

2010年05月30日 872KB 下载

COBAR架构实践

2018年06月14日 1.39MB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭