什么是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_agent 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_agent.conf 后, 于db-1, db-2 跑mmm_agent 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_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf 8.vim /usr/local/mmm/etc/mmm_agent.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_agent 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_agent.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_agent.conf.example /usr/local/mmm/etc/mmm_agent.conf 8.vim /usr/local/mmm/etc/mmm_agent.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_agent 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_agent.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_agent /etc/init.d/ 2.apt-get install rcconf 3.rcconf 4.选取 mmm_agent 即可 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_agent 或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秒内就会自动切换过去.