The MMM high-availability program for MySQL

1 mmm high-availability MySQL solution

1.1 Program Introduction

MMM is Master-master Replication manager for MySQL (MySQL master replication Manager) a set of scalable scripting suites for monitoring, failover, and management of MySQL primary master replication configuration (only one node can be written at any time) , this suite can also read load Balancing on any number of slave servers that reside in the standard master and subordinate configuration, so you can use it to start a virtual IP on a set of replicated servers, in addition to a script that enables data backup and resynchronization between nodes. MySQL itself does not provide replication failover solution, through the MMM solution can realize the server failover, so as to achieve high availability of MySQL. Mmm not only provides the function of floating IP, more valuable is that if the current primary server is hung, it will automatically turn your backend from the server to the new primary server for synchronous replication without manually changing the synchronization configuration. This solution is now a more mature solution.

1.2 Advantages and disadvantages of the scheme

Advantages: Security, high stability, good scalability, high availability, when the primary server hangs, another master immediately take over, the other from the server can automatically switch, without manual intervention.

Disadvantage: At least three nodes, the number of host requirements, the need to achieve read and write separation, the program is a challenge.

1.3 Plan architecture Diagram

1.4 Applicable Scenarios

The application scenario for MMM is a large number of database accesses, fast business growth and the ability to read and write separate scenarios.

1.5 Practical Solutions

1.5.1 Actual Combat Environment Introduction

Actual Combat Environment Server list:

Server	Host Name	IP Address	ServerID	System	Mysql
Monitor	Mon	10.1.1.111	--	Centos 5.5 64bit	--
Master1	Db1	10.1.1.113	1	Centos 5.5 64bit	5.1.63
Master2	Db2	10.1.1.75	2	Centos 5.5 64bit	5.1.63
Slave1	Db3	10.1.1.108	3	Centos 5.5 64bit	5.1.63

Virtual IP List of actual combat environment:

Vip	Role	Description
10.1.1.176	Read	Application configuration of the read IP, you can also add LVs on the front, such as load balancing. Three databases per a floating VIP
10.1.1.177	Read
10.1.1.178	Read
10.1.1.179	Write	Applies a configured write to the VIP, single-point write.

Installation of 1.5.2 MMM

Install the Epel package before installing MMM, since the default source for CentOS does not have a MMM installation package, Epel is an abbreviation for the Enterprise Linux add-on (Extra Packages for Enterprise Linux) and is a special interest group created, A high-quality add-on package project maintained and managed for Red Hat Enterprise Linux (RHEL) and its derivative distributions (such as CentOS, scientific Linux).

Install Epel:

wget ftp://ftp.sunet.se/pub/Linux/distributions/yellowdog/yum/6.2/extras/RPMS/epel-release-5-3.noarch.rpm

RPM-UVH epel-release-5-3.noarch.rpm

To install Monitor installation:

Yum-y Install mysql-mmm*

Only need to install mysql-mmm-agent on each DB

Yum-y Install Mysql-mmm-agent

1.5.3 MMM configuration

1.5.3.1 Pre-configuration preparation

The premise is to configure Master1 and Master2 Master synchronization, Master1 and slave1, master-slave synchronization, confined to the space here I do not introduce.

Before configuring MMM first to create in MySQL in addition to the replication account of the two other accounts, the first to introduce the monitor user account, this account is the monitor server for the MySQL server to do health check, followed by Agent user, this account is MMM Agent (MMM proxy) is used to become read-only mode and synchronization master, and the following are the statements to create these two accounts:

GRANT REPLICATION CLIENT on * * to ' mmm_monitor ' @ ' 10.1.1.% ' identified by ' Monitor_password ';

GRANT SUPER, REPLICATION CLIENT, PROCESS on * * to ' mmm_agent ' @ ' 10.1.1.% ' identified by ' Agent_password ';

Flush privileges;

These two statements are executed in each MySQL.

Configuration of the 1.5.3.2 monitor server

MMM configuration file in the/etc/mysql-mmm directory, monitor needs to configure the file has mmm_common.conf, mmm_mon.conf two files. The mmm_common.conf file is the same in each of the MMM nodes, so it is configured to copy to each DB node later.

My mmm_common.conf configuration is as follows:

Vim/etc/mysql-mmm/mmm_common.conf

Active_master_role writer

Cluster_interface eth0

Pid_path/var/run/mysql-mmm/mmm_agentd.pid

bin_path/usr/libexec/mysql-mmm/

Replication_user replication

Replication_password 123456

Agent_user mmm_agent

Agent_password Agent_password

IP 10.1.1.113 #这个IP尤其注意是db1的IP

Mode Master

Peer DB2

IP 10.1.1.75 #这个IP尤其注意是db2的IP

Mode Master

Peer DB1

IP 10.1.1.108

mode slave

<role writer>

Hosts DB1, DB2

IPs 10.1.1.179

Mode Exclusive

</role>

<role reader>

Hosts DB1, DB2, DB3

IPs 10.1.1.176, 10.1.1.177, 10.1.1.178

Mode Balanced

</role>

My mmm_mon.conf configuration is as follows:

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 10.1.1.1, 10.1.1.113, 10.1.1.75, 10.1.1.108

# ping_ips monitors gateway IP and other DB node IPs

Auto_set_online 60

</monitor>

Monitor_user Mmm_monitor

Monitor_password Monitor_password

Debug 0

1.5.3.3 configuration of each DB server

Each DB server to configure a few things, mainly mmm_common.conf, mmm_agent.conf and/etc/default/mysql-mmm-agent files.

• Configuration of the DB1

The configuration of the mmm_common.conf file is the same as the previous monitor, and can be used directly by copy.

Configuration of the mmm_agent.conf:

Vim/etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

This DB1

Configuration of the Mysql-mmm-agent:

Vim/etc/default/mysql-mmm-agent

Enabled=1

• Configuration of the DB2

The configuration of the mmm_common.conf file is the same as the previous monitor, and can be used directly by copy.

Configuration of the mmm_agent.conf:

Vim/etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

This DB2

Configuration of the Mysql-mmm-agent:

Vim/etc/default/mysql-mmm-agent

Enabled=1

• Configuration of the DB3

The configuration of the mmm_common.conf file is the same as the previous monitor, and can be used directly by copy.

Configuration of the mmm_agent.conf:

Vim/etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

This DB3

Configuration of the Mysql-mmm-agent:

Vim/etc/default/mysql-mmm-agent

Enabled=1

The management of 1.5.4 MMM

1.5.4.1 mmm start-up and stop

• Mmm start-up

Start MMM agent

/etc/init.d/mysql-mmm-agent start

Writes the agent's startup command to a three db rc.local file

Start MMM monitor

/etc/init.d/mysql-mmm-monitor start

Writes Monitor's startup command to the monitor server's rc.local file

• The stop of MMM

Stop MMM Agent

/etc/init.d/mysql-mmm-agent stop

Stop MMM Monitor

/etc/init.d/mysql-mmm-monitor stop

Basic management of 1.5.4.2 MMM

View the status of a cluster

Mmm_control Show

Set DB1 to online status

Mmm_control Set_online DB1

For more management commands, please use Mmm_control help to view, or refer to:

Http://blog.chinaunix.net/uid-20639775-id-154606.html

Test of 1.5.5 MMM architecture

After the environment is set up, it needs to be carefully tested to see if the expected function is achieved:

• After you stop Master1, use Mmm_control show on the monitor side to see if you can switch. See if slave1 can switch sync correctly.
• After starting Master1, use Mmm_control show on the monitor side to see if you can switch.
• Stop Master2 and see if you can switch correctly.

Monitoring of the 1.5.6 MMM architecture

In order to achieve high availability, all aspects of the system need to be fully monitored, such as agent process monitoring, monitor process monitoring, MySQL availability monitoring, database synchronization monitoring and so on, we recommend using Nagios to monitor the above resources, the first time to find problems, the first time to deal with.

The MMM high-availability program for MySQL