什么是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秒内就会自动切换过去.