MySQL Master-Master Replication Manager

MySQL Master-Master Replication Manager

什么是MySQL Master-Master Replication Manager(MMM)?

·MMM (MySQL Master-Master Replication Manager) 是整合Script 做监控/管理MySQL Master-Master replication cluster 的设定配置.

•Master-Master 的意思不是同时有两台Master 应付写入的动作, 而是要做备援, 若Master 死掉, 可于最短的时间内(几秒内), 将备援的Master 替换上线, 而这台备援的Master, 平常也可以当Slave (Reader).

•当Slave 的Replication Failed 时, 会自动让此Slave 停止提供服务, 避免不同步的状况发生.
•MMM 可解决Master 死掉时, 需要停机或停止所有写入的问题.

MMM 的特色

上述的事情, 只要在程式判断Master 死掉, 之后就永远写Slave 等, 就可以做到(Master 回复时, 再手动去做切换), 但是MMM 能够多处理哪些东西呢? MMM 的设定/原理大概是怎么样呢?
•MMM 的基本设定是Master-Master (就是两台MySQL Server 互相设对方为自己的Master, 自己是对方的Slave), 所以在任何一台正常回复, 会将资料自动透过Replication 同步.

•MMM 能够在Master(db1) 死掉时, 在最短的时间, 自动切换到另一台当Master(db2). 当Master(db1) 回复时, 会直接当现在Master(db2) 的Slave, 不过不会直接上线, 要等资料sync 同步后, MMM 才会允许手动设定上线.

•另可参考: Typical Use Cases 使用情境(常见使用架构)

MMM 的运作原理

MMM 主要做法是Master(db1), Master(db2) 设定固定IP, 然后Monitor 也设定一个固定IP, 再来Monitor 的设定再加上两个IP(Writer, Reader), 程式读取/写入都是透过Monitor 另外设的这两个IP, 当任何一台机器死掉(不管是Reader/Writer), Monitor 会自动将IP 设到另一台机器上.
 
注: 透过ARP(Address Resolution Protocol) 和iproute 将IP 指到另一台.
设定依正常设定, 两台MySQL Server 各设自己原本的IP, Monitor 会依照设定档另外再绑上各自读写用的IP, 跟之前主要的差异是, 两台MySQL Server 要多跑mmm_ag​​ent daemon, Monitor要跑mmm_mon daemon.MMM 是透过下述check MySQL servers 是否活着:
 
1.IP connectivity (fping)
2.MySQL connectivity (mysql connect and SELECT NOW())
3.MySQL IO and SQL threads status (SHOW SLAVE STATUS)
4.MySQL replication backlog – seconds behind master (SHOW SLAVE STATUS)

MMM 的官方资源

•MMM 官方网站说明: MySQL Master-Master Replication Manager
•MMM 下载: mysql-master-master – Google Code
•MMM 问答讨论区: MySQL Multi Master Manager Development | Google Groups

MMM 的更多介绍、说明

•MySQL Master-Master replication manager released
•Master-Master Replication Example using MMM
•mysql-master-master wiki list- Google Code – 官方Wiki 的说明文件
•Introduction – mysql-master-master
•MMM Documentation table of contents
•Using MMM to ALTER huge tables


MySQL Master-Master Replication Manager 前置作业

MySQL Master-Master 的架构, 需要准备的资讯如下:
 
•机器3台以上(最少3台): 2台做MySQL Server, 1台做Monitor(监控机器可与Apache 等共用即可).
•IP 5个以上(2N+1): MySQL Server 有几台, 需要的IP 是MySQL Server 台数x 2, 再加上Monitor 要一个IP.

MMM 前置作业手上该有的资讯:
 
•db-1 192.168.1.181
•db-2 192.168.1.182
•db-mon 192.168.1.183
•db-r 192.168.1.184 # 这个不用设进db-1、db-2, db-mon 会​​自动设给它.(但需先决定db-1, db-2 哪台要先当reader 或writer)
•db-w 192.168.1.185 # 这个不用设进db-1、db-2, db-mon 会​​自动设给它.(但需先决定db-1, db-2 哪台要先当reader 或writer)
•MySQL Replication 要设定的帐号: replication、密码: slave.
•Monitor 存取MySQL 需要的帐号: rep_monitor、密码: RepMonitor.
•MySQL Agent 要设定的帐号: rep_agent、密码: RepAgent.开始之前, 除了上述该有的资讯外, 手上应该要有3台机器, 3台机器设定分别如下:
 
•db-1 192.168.1.181
•db-2 192.168.1.182
•db-mon 192.168.1.183

•若有要用到mmm_clone, mmm_backup, mmm_restore 等功能, 需要LVM 支援, 除此之外, 没有LVM 还是能正常监控/转换等, 下述环境也是在没有LVM 的状况下测试的.

MySQL Master-Master Replication Manager 环境建置、架设

下述设定参考自: Master-Master Replication Example using MMM (设定档参考:Configuration Examples)
注: 下述环境、设定档位置是以Debian Lenny 为主.
 
建置环境步骤

建置环境步骤主要如下述:
 
1.db-1, db-2 安装 mysql-server
2.db-1, db-2 互设对方为Master, 自己是对方的Slave
3.抓取mmm 的档案, 装需要的Package 后, 执行install.pl
4.设定mmm_ag​​ent.conf 后, 于db-1, db-2 跑mmm_ag​​ent
5.db-mon 安装需要的Package 后, 执行install.pl
6.设定 mmm_mon.conf 后, 于 db-mon 跑 mmm_mon
7.将db-1, db-2 设定上线mmm_control set_online db1, mmm_control set_online db2
8.测试mmm_control show 是否正常, 将/usr/local/mmm/scripts/init.d, logrotate.d 设定到/etc 去.
9.使用 rcconf 设定开启启动即可.

实际执行步骤– db-, db-2 互设Replication, db-mon 建置

db-1 192.168.1.181

1.apt-get install mysql-server
 
2.vim /etc/mysql/my.cnf
 
# bind-address = 127.0.0.1 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log

 
3./etc/init.d/mysql restart

做完此步骤, 请跳到db-2 也先把此步骤做完.(顺便抄下db-2 的show master status)
db-2 做完上述步骤后, 再继续下面:
 
1.mysql -u root
2.mysql> GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'192.168.1.183′ identified by 'RepMonitor';
3.mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.181′ identified by 'RepAgent';
4.mysql> GRANT replication slave on *.* to 'replication'@'%' identified by 'slave';
5.mysql> change master to master_host='192.168.1.182′, master_port=3306, master_user='replication', master_password='slave'; # 文件上写的做法
6.mysql> change master to master_host='192.168.1.182′, master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004′, master_log_pos=98; # 我喜欢保守点的做法.(抓db-2 mysql> show master status 资料)
7./etc/init.d/mysql restart
8.mysql -u root
9.mysql> slave start;
10.mysql> show slave status G

这样子应该Replication 已经设定完成, Master 是db2, 自己是Slave, 再下来就是架设MMM 啰~
 
架设 MMM 步骤如下:
 
1.apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
2.apt-get install iproute
3.wget
4.tar xvf mmm-1.0.tar.bz2
5.cd mmm-1.0
6../install.pl
7.cp /usr/local/mmm/etc/examples/mmm_ag​​ent.conf.example /usr/local/mmm/etc/mmm_ag​​ent.conf
8.vim /usr/local/mmm/etc/mmm_ag​​ent.conf # 下述只将修改部份列出
 
cluster_interface eth0
 
# Define current server id
this db1
mode master
 
# For masters
peer db2
 
# Cluster hosts addresses and access params
host db1
ip 192.168.1.181
port 3306
user rep_agent
password RepAgent
host db2
ip 192.168.1.182
port 3306
user rep_agent
password RepAgent
 
9.mmmd_agent # 执行agent, 或者/usr/local/mmm/scripts/init.d/mmm_ag​​ent start

10.ps aux | grep mmmd
root 16115 0.0 0.0 41936 6904 ? S 15:02 0:00 perl /usr/local/sbin/mmmd_agent
 
11.修改完成的设定档参考可下载: mmm_ag​​ent.conf

12.再来就继续下述 db-2 的设定啰~
db-2 192.168.1.182
1.apt-get install mysql-server
2.vim /etc/mysql/my.cnf
3.# bind-address = 127.0.0.1
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log

4./etc/init.d/mysql restart
做完此步骤, 再回db-1 继续. (顺便抄下db-1 的show master status)
 
1.mysql -u root
2.mysql> GRANT ALL PRIVILEGES on *.* to 'rep_monitor'@'192.168.1.183′ identified by 'RepMonitor';
3.mysql> GRANT replication slave on *.* to 'replication'@'%' identified by 'slave';
4.mysql> GRANT ALL PRIVILEGES on *.* to 'rep_agent'@'192.168.1.182′ identified by 'RepAgent';
5.mysql> change master to master_host='192.168.1.181′, master_port=3306, master_user='replication', master_password='slave'; # 文件上写的做法
6.mysql> change master to master_host='192.168.1.181′, master_port=3306, master_user='replication', master_password='slave', master_log_file='mysql-bin.000004′, master_log_pos=98; # 我喜欢保守点的做法.(抓db-1 mysql> show master status 资料)
7./etc/init.d/mysql restart
8.mysql -u root
9.mysql> slave start;
10.mysql> show slave status G

这样子应该Replication 已经设定完成, Master 是db1, 自己是Slave, 再下来就是架设MMM 啰~(下述步骤与上面一致, 只有设定档有差异而已)
 
架设 MMM 步骤如下:
 
1.apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
2.apt-get install iproute
3.wget
4.tar xvf mmm-1.0.tar.bz2
5.cd mmm-1.0
6../install.pl
7.cp /usr/local/mmm/etc/examples/mmm_ag​​ent.conf.example /usr/local/mmm/etc/mmm_ag​​ent.conf
8.vim /usr/local/mmm/etc/mmm_ag​​ent.conf
 
cluster_interface eth0
 
# Define current server id
this db2
mode master
 
# For masters
peer db1
 
# Cluster hosts addresses and access params
host db1
ip 192.168.1.181
port 3306
user rep_agent
password RepAgent
host db2
ip 192.168.1.182
port 3306
user rep_agent
password RepAgent
 
9.mmmd_agent # 执行agent, 或者/usr/local/mmm/scripts/init.d/mmm_ag​​ent start

10.ps aux | grep mmmd
root 8837 0.0 0.0 41936 6904 ? S 15:02 0:00 perl /usr/local/sbin/mmmd_agent
 
11.修改完成的设定档参考可下载: mmm_ag​​ent.conf

db-mon 192.168.1.183
 
1.apt-get install libproc-daemon-perl libalgorithm-diff-perl libdbi-perl libdbd-mysql-perl # for perl
2.apt-get install iproute
3.apt-get install subversion # 目前1.0 stable 的mmm_mon 程式有bug, 所以需要直接checkout trunk 的来用
4.svn checkout mysql-master-master-read-only
5.cd mysql-master-master-read-only/
6../install.pl
7.cp /usr/local/mmm/etc/examples/mmm_mon.conf.example /usr/local/mmm/etc/mmm_mon.conf
8.vim /usr/local/mmm/etc/mmm_mon.conf
 
email root@localhost # 修改成有状况要通知的Email.
host db1
ip 192.168.1.181
port 3306
user rep_monitor
password RepMonitor
mode master
peer db2
host db2
ip 192.168.1.182
port 3306
user rep_monitor
password RepMonitor
mode master
peer db1
 
# Mysql Reader role
role reader
mode balanced
servers db1, db2
ip 192.168.1.185, 192.168.1.184
 
# Mysql Writer role
role writer
mode exclusive
servers db1, db2
ip 192.168.1.185
 
9./usr/local/mmm/scripts/init.d/mmm_mon start
10.mmm_control show
11.mmm_control set_online db1 # 让 db1 上线
12.mmm_control set_online db2 # 让 db2 上线
13.mmm_control show # 看到下述就成功了~
Config file: mmm_mon.conf
Daemon is running!
Servers status:
db1(192.168.1.181): master/ONLINE. Roles: reader(192.168.1.185;), writer(192.168.1.185;)
db2(192.168.1.182): master/ONLINE. Roles: reader(192.168.1.184;)
 
14.修改完成的设定档参考可下载: mmm_mon.conf

设定 log rotate

下面这些步骤分别在db1, db2, mon 设定即可.
1.cp /usr/local/mmm/scripts/logrotate.d/mmm /etc/logrotate.d/
2.vim /etc/logrotate.d/mmm
/opt/mmm/var/*.log { 修改成/usr/local/mmm/var/*.log
olddir /opt/mmm/var/old 修改成olddir /usr/local/mmm/var/old
 
设定开机自动启动
 
db1, db2 设定开机自动启动

1.cp /usr/local/mmm/scripts/init.d/mmm_ag​​ent /etc/init.d/
2.apt-get install rcconf
3.rcconf
4.选取 mmm_ag​​ent 即可

mon 设定开机自动启动

1.cp /usr/local/mmm/scripts/init.d/mmm_mon /etc/init.d/
2.apt-get install rcconf
3.rcconf
4.选取 mmm_mon 即可
再下来就只要会mmm_control show, mmm_control set_online, mmm_control set_offline 即可.
当机/重开机的 SOP

若有重开机等状况, 检查步骤:
 
1.mysql -u root # 看MySQL 是否有启动, 若没启动/etc/init.d/mysql start
2.ps aux | grep mmm # 看mmm_ag​​ent 或mmm_mon 是否有启动, 若没启动/etc/init.d/mmm_[agent|mon] start
3.再来在mon 的机器下: mmm_control show, 若没上线, 发现已经在AWAITING_RECOVERY 状态, 就可以set_online 让他上线啰~

测试

1.db1, db2 mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON *.* TO 'mmm'@'%' IDENTIFIED BY 'mmm_password';
2.db1, db2 mysql> FLUSH PRIVILEGES;
3.写程式去对192.168.1.184, 192.168.1.185 做写入/读取的动作, 并试着重开等看看反应~
4.注: 目前测试状况, 机器死掉时, 在2秒内就会自动切换过去.
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/yangyu112654374/archive/2010/11/25/6034734.aspx

 
 原文地址http://blog.csdn.net/yangyu112654374/archive/2010/11/25/6034734.aspx