CENTOS6.6 MySQL MMM architecture construction

This article comes from my GitHub pages blog http://galengao.github.io/ie www.gaohuirong.cn

MMM (Master-master Replication Manager for MySQL) is a set of scripting programs that support dual-master failover and dual-master daily management. MMM is developed using the Perl language, which is mainly used to monitor and manage MySQL Master-master (dual master) replication, although it is called dual master replication, but only one master is allowed to write at the same time in the business, and the other one provides partial read services to speed up the preheating of the alternate master at the time of primary master switching. Can say mmm this set of scripts to implement the function of failover, on the other hand, its internal additional tool script can also achieve multiple slave read load balancing.

MMM provides both automatic and manual removal of the virtual IP of a server with high replication latency in a set of servers, as well as the ability to back up data, achieve data synchronization between two nodes, and more. Because MMM does not fully guarantee the consistency of data, MMM is suitable for the data consistency requirements are not very high, but also want to maximize the business availability of the scene. For businesses that have a high level of conformance to data, it is highly recommended that you use MMM as a highly available architecture.

Implementation principle

Two master from the main, one for read-only, as a backup, emergency can be done as write Master. Where the Mmm_mond process timed heartbeat detection, failure will float the write IP to another master.

Mysql-mmm Advantages and Disadvantages

1. Advantages: High availability, scalability, failure auto-switching, for primary master synchronization, only one database write operation at the same time.
2. Cons: The monitor node is a single point that can be combined with keepalived for high availability
3. Because MMM does not fully guarantee the consistency of data, MMM is suitable for the data consistency requirements are not very high, but also want to maximize the business availability of the scene. For businesses that have a high level of conformance to data, it is highly recommended that you use MMM as a highly available architecture.

installing MySQL Software

Install the MySQL software on each server, install the reference "Linux 6 source installation mysql5.6" on each server to turn off SELinux, turn off the firewall in the configuration file my.cnf add the following:

[Mysqld]datadir=/usr/local/mysql/Datasocket=/var/lib/mysql/Mysql.sockuser=MySQL #下面为新添加的内容default-storage-engine =InnoDB Replicate-ignore-db =Mysqlbinlog-ignore-db=MySQL server-ID=1Log-bin =/usr/local/mysql/logs/mysql-Bin.loglog_bin_index=/usr/local/mysql/logs/mysql-Bin.log.indexrelay_log=/usr/local/mysql/logs/mysql-Bin.relayrelay_log_index=/usr/local/mysql/logs/mysql-bin.relay.indexexpire_logs_days=Tenmax_binlog_size=100mlog_slave_updatesSync-binlog =1 

With all the host information in the/etc/hosts.

192.168. 10.131 DB1 192.168. 10.132 DB2 192.168. 10.133 db3 192.168. 10.134 DB4 192.168. 10.135 DB5

Configuring Primary master Replication

That is, two master masters (DB1, DB2) are mainly from each other, similar to the master-slave, just need to reverse configuration.
A. View db1 DB2 binlog log and POS values first
Db1

Db2

b, in db1 execution:

Mysql> GRANT REPLICATIONSLAVE on *.*  to 'Replication'@'192.168.10.%'Identified by 'Replication'; MySQL>FlushPrivileges; MySQL>Change Master toMaster_host='192.168.10.132', Master_user='Replication', Master_password='Replication', Master_log_file='mysql-bin.000003', Master_log_pos= -; MySQL>Start slave; #启动同步

C, in DB2 execution:

Mysql> GRANT REPLICATIONSLAVE on *.*  to 'Replication'@'192.168.10.%'Identified by 'Replication'; MySQL>FlushPrivileges; MySQL>Change Master toMaster_host='192.168.10.131', Master_user='Replication', Master_password='Replication', Master_log_file='mysql-bin.000001', Master_log_pos= -; MySQL>Start slave; #启动同步

DB3, DB4 as db1 from the library slave

Execute on DB3, DB4

Mysql>Change Master toMaster_host='192.168.10.131', Master_user='Replication', Master_password='Replication', Master_log_file='mysql-bin.000001', Master_log_pos= -; MySQL>Start slave; #启动同步

Installation of Mysql-mmm

(1) Epel source installation
CentOS does not have a mysql-mmm package by default, the official recommendation is to use Epel's network source, with five installed Epel:

wget http://mirrors.hustunique.com/epel/6/x86_64/epel-release-6-8.noarch.rpm    wget http://mirrors.hustunique.com/epel/rpm-gpg-key-epel-6   RPM--import rpm-gpg-key-epel-6  -ivh epel-release-6-8

(2) Monitor node installation DB5
Execute on the DB5

Yum Install Mysql-mmm-monitor  

(3) Install agent on four DB nodes
Performed on DB1 DB2 DB3 DB4

Yum Install mysql-mmm-agent

(4) Authorized monitor access on four DB nodes
Performed at DB1 DB2 DB3 DB4

Mysql-Uroot-Pmysql> GRANT REPLICATIONCLIENT on *.*  to 'Mmm_monitor'@'192.168.10.%'Identified by 'Monitor'; MySQL> GRANTSUPER,REPLICATIONCLIENT, PROCESS on *.*  to 'mmm_agent'@'192.168.10.%'Identified by'Agent';

(5) Modify the mmm_common.conf file on DB1 (5 units same)

VI/etc/mysql-mmm/mmm_common.confactive_master_role writercluster_interface Eth0pid_path/var/run/mysql-mmm/Mmm_agentd.pidbin_path/usr/libexec/mysql-mmm/replication_userreplicationreplication_passwordreplicationagent_user Mmm_agentagent_password AgentIP192.168.10.131MODEMASTERPEERDB2IP192.168.10.132MODEMASTERPEERDB1IP192.168.10.133ModeslaveIP192.168.10.134Modeslavehosts DB1, Db2ips192.168.10.128modeexclusive #只有一个虚拟IP可以写</role><role reader>hosts DB3, Db4ips192.168.10.129,192.168.10.130modebalanced #多个虚拟ip可以读</role>

Upload to 4 other nodes

SCP /etc/mysql-mmm/mmm_common.conf [email protected]192.168. 10.132:/etc/mysql-mmm/SCP /etc/mysql-mmm/mmm_common.conf [email protected]192.168. 10.133:/etc/mysql-mmm/SCP /etc/mysql-mmm/mmm_common.conf [email protected]192.168. 10.134:/etc/mysql-mmm/SCP /etc/mysql-mmm/mmm_common.conf [email protected]192.168 . 10.135:/etc/mysql-mmm/

If there is no SCP command, install the package

Yum Install openssh-clients

(6) Modify four DB proxy-side mmm_agent.conf files
Modified on DB1 DB2 DB3 DB4

vi /etc/mysql-mmm/mmm_agent.confinclude mmm_common.confthis db1   #分别修改为本机的主机名, i.e. DB1, DB2, DB3 and DB4 

(7) Modify the mmm_mon.conf file on the management side
Execute on the DB5

VI/etc/mysql-mmm/mmm_mon.confinclude mmm_common.conf<monitor>IP127.0.0.1Pid_path/var/run/mysql-mmm/Mmm_mond.pidbin_path/usr/libexec/mysql-Mmmstatus_path/var/lib/mysql-mmm/mmm_mond.statusping_ips192.168.10.131,192.168.10.132,192.168.10.133,192.168.10.133,192.168.10.134#真实数据库IP to detect if the network is normal auto_set_onlineTen#恢复后自动设置在线的时间</monitor>Monitor_usermmm_monitormonitor_passwordmonitorDebug0

Start Mysql-mmm

(1) DB agent-side start db1 DB2 db3 DB4

[Email protected] ~]#/etc/init.d/mysql-mmm-~]# chkconfig mysql-mmm-agent on

(2) Monitor management side start DB5

[Email protected] ~]#/etc/init.d/mysql-mmm-~]# chkconfig mysql-mmm-monitor on

Test cluster

(1) View cluster status DB5
Mmm_control Show

(2) failover
After simulating the shutdown of the DB1 database

Look at the state of the change.

CENTOS6.6 MySQL MMM architecture construction