Use MySQL-MMM for MySQL cluster deployment

Use MySQL-MMM for MySQL cluster deployment

Background: The mysql synchronous replication function (as described in my previous article) is only a dual-machine hot backup function, and cannot automatically switch the Mysql link.

This configuration implements a real mysql cluster, enabling the application to automatically switch to the mysql connection of another host when the mysql application of a certain machine is stopped to achieve high application stability, it also makes it possible to expand the Mysql service.

This configuration uses the mysql-mmm (master-master Replication Manager for MySQL) component to implement cluster functions.

The configuration in this demonstration uses three machines. The architecture is as follows:

Master 1 and Master2 enable hot standby on both servers. When the mysql service of one server is stopped or the machine is down, the application automatically switches the database connection to another server. In addition, a host is used to back up master1 data in real time.

-------------------------------------- Split line --------------------------------------

MySQL-MMM for MySQL High Availability

MySQL-MMM switching demonstration

Mysql proxy and MySQL-MMM achieve high availability of read/write splitting

Restore the MySQL-MMM Master from the REPLICATION_FAIL status

Use MySQL-MMM in CentOS to achieve high MySQL availability

-------------------------------------- Split line --------------------------------------

1. Install the mysql-mmm Service

Install on all three machines

Wget http://dl.Fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

Rpm-ivh epel-release-6-8.noarch.rpm

Yum-y install mysql-mmm *

View rpm-qa | grep mysql-mmm

The following components indicate that the installation is successful:

Rhel5 or centos5, 32-bit: http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

Rhel6 or centos6, 32-bit: http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-5.noarch.rpm

Rhel6 or centos6, 64-bit: http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

1. Add mysql users

Add mysql users to all three machines for copying,

Go to mysql service mysql-u root-p

Use mysql;

Grant REPLICATION slave, replication client on *. * to 'repl' @ '% 'identified by 'Password'; // create a REPLICATION user

Grant PROCESS, SUPER, replication client on *. * to 'mmm _ agent' @ '% 'identified by 'Password'; // create an agent user

Grant replication client on *. * to 'mmm _ monitor' @ '%' identified by 'Password'; // create a user

Flush privileges;

2. Configure synchronous Replication

Shows the architecture diagram of the replication policy configuration.

The configuration method has been introduced in my previous article. I will not repeat it here. For more information, see my previous article.

3. Modify hosts

Modify the hosts files of the three machines respectively.

Vi/etc/hosts

Add

200.200.168.24 M1

200.200.168.25 M2

200.200.168.23 slave1

4. Configure mysql-mmm

Modify/etc/mysql-mmm/mmm_common.conf the configurations of each machine are the same

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 repl
Replication_password password
Agent_user mmm_agent
Agent_password <span style = "font-size: 13.333331041162px;"> password </span>
</Host>

<Host M1>
Ip 200.200.168.24
Mode master
Peer M2
</Host>

<Host M2>
Ip 200.200.168.25
Mode master
Peer M1
</Host>

<Host slave1>
Ip 200.200.168.23
Mode slave
</Host>

<Role writer>
Hosts M1, M2
Ips 200.200.168.26
Mode exclusive
</Role>

<Role reader>
Hosts M1, M2, slave1
Ips 200.200.168.27
Mode balanced
</Role>

Note: 200.200.168.26 and 200.200.168.27 are two virtual IP addresses, which are used to provide write and read services for program calls, for the future implementation of database read/write splitting (but the implementation of read/write splitting requires modifying the application, mysql cannot automatically identify and switch ).

Modify/etc/mysql-mmm/mmm_agent.conf

The three machines are set to this M1, this M2, and this slave1 respectively.

Modify/etc/mysql-mmm/mmm_mon.conf

Only the monitor (200.200.168.24) Sub-host needs to be configured

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 200.200.168.24, 200.200.168.25, 200.200.168.23
Auto_set_online 10

# 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 password
</Host>
Debug 0

Run after configuration is complete

All three machines need to run:

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

Monitor sub-run

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

For more details, please continue to read the highlights on the next page:

1 2 Next Page