Loading... <p style="text-indent: 2em"><span style="font-size: 18px"></span></p> <p style="text-indent: 2em"><span style="font-size: 18px"><strong>前言</strong></span><br style="text-indent: 2em" /></p> <p style="text-indent: 2em">MMM(Master-Master replication managerfor Mysql,Mysql主主复制管理器)是一套灵活的脚本程序,基于perl实现,用来对mysql replication进行监控和故障迁移,并能管理mysql Master-Master复制的配置(同一时间只有一个节点是可写的)。<br style="text-indent: 2em" /></p> <p style="text-indent: 2em"><span style="font-size: 18px"><strong>MMM</strong></span></p> <p style="text-indent: 2em"><strong>优缺点</strong></p> <p style="text-indent: 2em">优点:高可用性,扩展性好,出现故障自动切换,对于主主同步,在同一时间只提供一台数据库写操作,保证的数据的一致性。</p> <p style="text-indent: 2em">缺点:Monitor节点是单点,可以结合Keepalived实现高可用。</p> <p style="text-indent: 2em"><strong>工作原理</strong></p> <p style="text-indent: 2em">mysql-mmm的监管端会提供多个虚拟IP(VIP),包括一个可写VIP,多个可读VIP,通过监管的管理,这些IP会绑定在可用服务器之上,当某一台服务器宕机时,监管会将VIP迁移至其他服务器。</p> <p style="text-indent: 2em"><span style="font-size: 18px"><strong>实现过程</strong></span></p> <p style="text-indent: 2em"><strong>实验拓扑</strong></p> <p style="text-indent: 2em"></p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151304-23.jpg" title="1435043095842430.jpg" alt="MariaDB-基于MMM实现读写分离及高可用.jpg" /><br style="text-indent: 2em" /></p> <pre class="brush:bash;toolbar:false">#注:系统环境CentOS6.6 #VIP172.16.10.30为可写VIP,其他三组为可读VIP #可写VIP只能在Master之间切换,可读VIP可在Master和Slave之间切换 #前端应用可连接任意可读VIP进行数据读取,连接可写VIP进行数据写入</pre> <p style="text-indent: 2em"></p> <p style="text-indent: 2em"><strong>双主一从配置</strong><br style="text-indent: 2em" /></p> <p style="text-indent: 2em">DB1配置</p> <pre class="brush:bash;toolbar:false">[root@node1 ~]# vim /etc/mysql/my.cnf datadir = /mydata/data log-bin=/mydata/binlogs/master-bin #二进制文件位置 relay-log=/mydata/relaylogs/relay-bin #中继日志位置 binlog_format=mixed server-id = 1 auto_increment_offset=1 #双主复制中自增长字段的起始值 auto_increment_increment=2 #双主复制中自增长字段的步长 log_slave_updates = 1 #开启从库更新操作写入二进制日志功能 sync_binlog = 1 #可保证事务日志及时写入磁盘文件 skip-name-resolve #禁用DNS反向解析,如不写此项,则需要在各节点基于主机名通信 read_only = 1 [root@node1 ~]# service mysqld start</pre> <p style="text-indent: 2em">授权可用复制用户记录二进制日志位置</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151304-18.jpg" title="1435043113133799.jpg" alt="1.jpg" /></p> <p style="text-indent: 2em">DB2配置</p> <pre class="brush:bash;toolbar:false">[root@node2 ~]# vim /etc/mysql/my.cnf datadir = /mydata/data log-bin=/mydata/binlogs/master-bin relay-log=/mydata/relaylogs/relay-bin binlog_format=mixed server-id = 11 auto_increment_offset=2 auto_increment_increment=2 log_slave_updates = 1 sync_binlog = 1 skip-name-resolve read_only = 1 [root@node2 ~]# service mysqld start</pre> <p style="text-indent: 2em">授权可用复制用户记录二进制日志位置</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151304-91.jpg" title="1435043130752372.jpg" alt="1.jpg" /></p> <pre class="brush:bash;toolbar:false">#因为实验之前都做了完整初始化,这里二进制文件位置一致</pre> <p style="text-indent: 2em">DB3配置</p> <pre class="brush:bash;toolbar:false">[root@scholar ~]# vim /etc/mysql/my.cnf datadir = /mydata/data relay-log=/mydata/relaylogs/relay-bin server-id = 111 read_only = 1 skip-name-resolve [root@scholar ~]# service mysqld start</pre> <p style="text-indent: 2em">连接各服务器<br style="text-indent: 2em" /></p> <p style="text-indent: 2em">DB1连接DB2</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151304-86.jpg" title="1435043145564280.jpg" alt="2.jpg" /></p> <p style="text-indent: 2em">DB2连接DB1</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151304-12.jpg" title="1435043160329174.jpg" alt="3.jpg" /></p> <p style="text-indent: 2em">DB3连接DB1</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151304-76.jpg" title="1435043177136153.jpg" alt="4.jpg" /></p> <p style="text-indent: 2em">主从测试</p> <pre class="brush:bash;toolbar:false">#DB1创建数据库 MariaDB [(none)]> create database testdb; Query OK, 1 row affected (0.14 sec) #DB2创建表 MariaDB [(none)]> create table testdb.tb1(name char(20) not null); Query OK, 0 rows affected (0.19 sec) #DB3查看数据 MariaDB [(none)]> show tables in testdb; +------------------+ | Tables_in_testdb | +------------------+ | tb1 | +------------------+ 1 row in set (0.13 sec)</pre> <p style="text-indent: 2em">数据一致,主从复制部分完成</p> <p style="text-indent: 2em"><strong>MMM配置</strong><br style="text-indent: 2em" /></p> <p style="text-indent: 2em">安装所需程序包</p> <pre class="brush:bash;toolbar:false">[root@node1 ~]# yum install mysql-mmm* -y #在所有节点安装mysql-mmm包组</pre> <p style="text-indent: 2em">授权监控及代理用户</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151304-40.jpg" title="1435043203230652.jpg" alt="5.jpg" /></p> <pre class="brush:bash;toolbar:false">#DB节点全部需要授权,在一个节点授权即可</pre> <p style="text-indent: 2em">Monitor配置</p> <pre class="brush:bash;toolbar:false">[root@scholar ~]# vim /etc/mysql-mmm/mmm_common.conf active_master_role writer <host default> cluster_interface eth0 pid_path /var/run/mysql-mmm/mmm_agentd.pid bin_path /usr/libexec/mysql-mmm/ replication_user repluser #复制用户 replication_password replpass #复制用户密码 agent_user mmm_agent #代理用户 agent_password agent_passw #代理用户密码 </host> <host db1> ip 172.16.10.123 mode master peer db2 </host> <host db2> ip 172.16.10.124 mode master peer db1 </host> <host db3> ip 172.16.10.125 mode slave </host> <role writer> #可写节点 hosts db1, db2 ips 172.16.10.30 mode exclusive #互斥角色只有一个ip,同一时间只能分配给一个主机 </role> <role reader> #可读节点 hosts db1, db2, db3 ips 172.16.10.31,172.16.10.32,172.16.10.33 mode balanced #负载均衡角色可以有多个IP,这些IP被均衡的分配给多个主机 </role></pre> <pre class="brush:bash;toolbar:false">#将以上文件同步到DB1-DB3节点 [root@scholar ~]# scp /etc/mysql-mmm/mmm_common.conf 172.16.10.123:/etc/mysql-mmm/ mmm_common.conf 100% 776 0.8KB/s 00:00 [root@scholar ~]# scp /etc/mysql-mmm/mmm_common.conf 172.16.10.124:/etc/mysql-mmm/ mmm_common.conf 100% 776 0.8KB/s 00:00 [root@scholar ~]# scp /etc/mysql-mmm/mmm_common.conf 172.16.10.125:/etc/mysql-mmm/ mmm_common.conf 100% 776 0.8KB/s 00:00</pre> <pre class="brush:bash;toolbar:false">[root@scholar ~]# vim /etc/mysql-mmm/mmm_mon.conf include mmm_common.conf <monitor> ip 127.0.0.1 pid_path /var/run/mysql-mmm/mmm_mond.pid bin_path /usr/libexec/mysql-mmm status_path /var/lib/mysql-mmm/mmm_mond.status ping_ips 172.16.10.123,172.16.10.124,172.16.10.125 #监控主机 auto_set_online 60 # The kill_host_bin does not exist by default, though the monitor will # throw a warning about it missing. See the section 5.10 "Kill Host # Functionality" in the PDF documentation. # # kill_host_bin /usr/libexec/mysql-mmm/monitor/kill_host # </monitor> <host default> monitor_user mmm_monitor #监控用户 monitor_password monitor_pass #监控密码 </host> debug 0</pre> <p style="text-indent: 2em">DB1-DB3配置</p> <pre class="brush:bash;toolbar:false">[root@node1 ~]# vim /etc/mysql-mmm/mmm_agent.conf include mmm_common.conf # The 'this' variable refers to this server. Proper operation requires # that 'this' server (db1 by default), as well as all other servers, have the # proper IP addresses set in mmm_common.conf. this db1 #DB1-DB3根据自身实际角色修改此配置文件(db1,db2,db3)</pre> <p style="text-indent: 2em">启动服务</p> <pre class="brush:bash;toolbar:false">#在各监控节点启动mmm-agent服务 [root@node1 ~]# service mysql-mmm-agent start Starting MMM Agent Daemon: [ OK ] #在Monitor上启动mmm-monitor服务 [root@scholar ~]# service mysql-mmm-monitor start Starting MMM Monitor Daemon: [ OK ]</pre> <p style="text-indent: 2em">查看当前状态</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151304-62.jpg" title="1435043219761240.jpg" alt="6.jpg" /></p> <p style="text-indent: 2em"><span style="font-family:微软雅黑, Microsoft YaHei">查看各节点VIP状态</span></p> <pre class="brush:bash;toolbar:false">#以db1为例</pre> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151305-21.jpg" title="1435043236563565.jpg" alt="7.jpg" /></p> <p style="text-indent: 2em">高可用测试</p> <p style="text-indent: 2em">模拟db1故障,查看节点状态</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151305-71.jpg" title="1435043261951968.jpg" alt="8.jpg" /></p> <p style="text-indent: 2em">再看db1的VIP状态</p> <p style="text-indent: 2em"><img src="//cto.wang/usr/uploads/2016/07/20160703151305-9.jpg" title="1435043274908532.jpg" alt="9.jpg" /></p> <p style="text-indent: 2em">VIP已被转移到其他节点,其他方面有兴趣请自行测试,这里就不一一展示了</p> <p style="text-indent: 2em"><span style="font-size: 18px"><strong>The end</strong></span></p> <p style="text-indent: 2em">MySQL/MariaDB基于MMM实现读写分离及高可用实验就先说到这里了,有兴趣的朋友可以继续探究基于Keepalived实现Monitor的高可用,这里就不做深究了,实验过程中遇到问题可留言交流。以上仅为个人学习整理,如有错漏,大神勿喷~~~</p> 最后修改:2021 年 12 月 10 日 10 : 53 AM © 允许规范转载 赞赏 如果觉得我的文章对你有用,请随意赞赏 赞赏作者 支付宝微信