Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about what the mysql high-availability MMM high-availability mysql scheme is like. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

MMM High availability mysql Scheme

Brief introduction of the scheme

MMM (mysql Master replication Manager) A scalable suite of scripts for monitoring, failover, and management of mysql master replication configurations (only one node can be written at any time). This suite can also load balance any number of slave servers that reside in the standard master-slave configuration, so you can use it to launch virtual ip on a group of replicated servers In addition, it has scripts that implement data backup and resynchronization between nodes. MySQL itself does not provide a replication failover solution, through the MMM scheme to achieve server failover, so as to achieve the high availability of mysql. MMM not only provides the function of floating IP, but what is more valuable is that if the current master server dies, it will automatically transfer your back-end slave server to the new master server for synchronous replication without having to change the synchronization configuration manually. This solution is a relatively mature solution at present.

Advantages and disadvantages of the scheme

Advantages: high security, high stability, good scalability, high availability, when the master server dies, another master immediately takes over, and the other slave servers can be switched automatically without human intervention.

Disadvantages: at least three nodes, there are requirements for the number of hosts, the need to achieve read-write separation, which is a challenge for the program.

Scheme architecture diagram

Applicable scenario

The applicable scenarios of MMM are those with large database access, rapid business growth, and the separation of read and write.

Program actual combat

Introduction of actual combat environment

List of servers in the field environment:

Server hostname ip address Serverid system MysqlMonitorMon10.1.1.111--Centos 5.5 64bit--Master1db110.1.1.1131Centos 5.5 64bit5.1.63Master2db210.1.1.752Centos 5.5 64bit5.1.63Slave1db310.1.1.1083Centos 5.5 64bit5.1.63

Virtual IP list of actual combat environment:

The read IP configured by the VIPRoledescription10.1.1.176Read application can also be used as a load balancer by adding lvs at the front end. Each of the three databases has a floating VIP10.1.1.177Read10.1.1.178Read10.1.1.179Write application configured to write VIP, a single point of write.

Installation of MMM

Install the epel package before installing mmm because there is no mmm installation package in Centos's default source. Epel is an acronym for Enterprise Linux add-on package (Extra Packages for Enterprise Linux), a high-quality add-on package project for Red Hat Enterprise Linux (RHEL) and its derivative distributions (such as CentOS, Scientific Linux) created, maintained and managed by a special interest group.

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

Install the installation of monitor:

Yum-y install mysql-mmm*

Only mysql-mmm-agent needs to be installed on each DB

Yum-y install mysql-mmm-agent

Configuration of MMM

Preparation before configuration

The premise is to configure the master synchronization of master1 and master2, and the master-slave synchronization of master1 and slave1. I will not introduce them here because of the limited space.

Before configuring mmm, you need to create two other accounts in mysql in addition to the replication account. First, introduce the monitor user account, which is used by the monitor server to check the health of the mysql server, followed by agent user, which is used by mmm agent (mmm proxy) to become read-only mode and synchronous master. The following is the statement 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 Monitor server

The configuration file is in the / etc/mysql-mmm directory, and the files that monitor needs to configure are mmm_common.conf and mmm_mon.conf. The mmm_common.conf file is the same in all nodes of the mmm, so you can copy to each DB node after you configure it.

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 # this IP pays special attention to the IP of db1

Mode master

Peer db2

Ip 10.1.1.75 # this IP pays special attention to the IP of db2

Mode master

Peer db1

Ip 10.1.1.108

Mode slave

Hosts db1, db2

Ips 10.1.1.179

Mode exclusive

Hosts db1, db2, db3

Ips 10.1.1.176, 10.1.1.177, 10.1.1.178

Mode balanced

My mmm_mon.conf configuration is as follows:

Vim / etc/mysql-mmm/mmm_mon.conf

Include mmm_common.conf

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 the gateway IP and other DB node IP

Auto_set_online 60

Monitor_user mmm_monitor

Monitor_password monitor_password

Debug 0

Configuration of each DB server

There is not much to configure for each DB server, mainly mmm_common.conf, mmm_agent.conf, and / etc/default/mysql-mmm-agent files.

Configuration of db1

The configuration of the file is the same as that of the previous monitor, which can be used directly by copy.

Configuration of:

Vim / etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

This db1

Configuration of:

Vim / etc/default/mysql-mmm-agent

Configuration of db2

The configuration of the file is the same as that of the previous monitor, which can be used directly by copy.

Configuration of:

Vim / etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

This db2

Configuration of:

Vim / etc/default/mysql-mmm-agent

Configuration of db3

The configuration of the file is the same as that of the previous monitor, which can be used directly by copy.

Configuration of:

Vim / etc/mysql-mmm/mmm_agent.conf

Include mmm_common.conf

This db3

Configuration of:

Vim / etc/default/mysql-mmm-agent

Management of MMM

Start and stop of MMM

Startup of MMM

Start mmm agent

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

Write the startup command of agent to the rc.local file of the three DB

Start mmm monitor

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

Write the startup command of monitor to the rc.local file of the monitor server

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 MMM

View the status of the cluster

Mmm_control show

Set db1 to online state

Mmm_control set_online db1

For more administrative commands, use mmm_control help to view, or refer to:

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

Testing of MMM Architecture

Once the environment is set up, it needs to be carefully tested to see if it has achieved the desired functionality:

After stopping master1, use mmm_control show on the monitor side to see if it can be switched. See if slave1 can switch synchronization correctly.

After starting master1, use mmm_control show on the monitor side to see if it can be switched.

Stop the master2 and see if you can switch correctly.

Monitoring of MMM architecture

In order to achieve high availability, all levels 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. It is recommended to use nagios to monitor the above resources, the first time to find the problem, the first time to deal with.

The above is what the mysql high availability medium MMM high availability mysql solution shared by the editor is like. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.