什么是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 http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2
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 http://mysql-master-master.googlecode.com/files/mmm-1.0.tar.bz2
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 http://mysql-master-master.googlecode.com/svn/trunk/ 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秒内就会自动切换过去.