MySQL High-availability MHA

Mha,mysql High-availability architecture, in a master-slave architecture-based mode, when the primary server is hung up, the MHA manager decides from which slave to choose from the server as Master master, which is usually compared to the data from the server, which is the most complete, up-to-date, The length of the update to determine, when deciding which server to use as the new primary server, MHA from other nodes to obtain additional information, to avoid data inconsistency occurs, so that the data consistent.

There are two roles of MHA:

1.MHA Manager

Used to manage the master transformation of a master-slave architecture cluster, data replication permissions, and database operation permissions, typically configured as a single server. Each of the Master/slave clusters is a application.

2.MHA node

Nodes in the MySQL cluster, all servers in the cluster are a node, such as Manager,master,slave1,slave2 four servers, there are four node nodes. It accelerates failover by monitoring scripts that have the ability to parse and clean logs.

MHA Architecture cluster:

Manager 172.16.25.104

Master 172.16.25.101 server_id=11

Slave1 172.16.25.102 server_id=22

Slave2 172.16.25.103 server_id=33

① first configuration of a master two from the database schema, this piece is not described in detail, it is important to note that because the MHA high-availability architecture, any one MySQL server can become the primary server, so can not be like ordinary master-slave architecture, to the primary server to open Binlog, To open Relay_log from the server, you need to open Binlog and Relay_log on the master and slave servers.

If the/ETC/MY.CNF configuration

②MHA High-availability implementations first need to build a master-slave cluster, followed by the need to make SSH communication between the various nodes barrier-free.

Set up key communication

If the master172.16.25.101 needs to transfer the key to the remaining three nodes so that it can be accessed without a password

#ssh-keygen-t rsa-p ""

#ssh-copy-id-i/root/.ssh/id_rsa.pub [email protected]

#ssh-copy-id-i/root/.ssh/id_rsa.pub [email protected]

#ssh-copy-id-i/root/.ssh/id_rsa.pub [email protected]

The remaining three nodes are as such.

③ Installation MHA

MHA Manager

#yum install-y mha4mysql-manager-0.56-0.el6.noarch.rpm

The manager installation needs to rely on plugins from many epel sources, networked installations

MHA node

#yum install-y mha4mysql-node-0.56-0.el6.noarch.rpm

All four nodes need to be installed

④ Configure manager to enable Manager to manage master-slave clusters, manually set up a configuration file

In/ETC/MASTERHA/APP1.CNF (custom)

The server default PIN is set for all MySQL servers.

User,password is used to manage the database account password, this is required in the MySQL cluster, that is, master,slave1,slave2 authorization.

Such as

Ssh_user is the user to use when communicating, root.

Repl_user,repl_password the account password used for replication between master and slave databases, when the primary server is down and from the server, the change master authorization from the server is required.

The Manager_workdir,manager_log is used to set the manager's working directory and log storage path.

Master_binlog_dir is used to tell managermaster the Binlog log placement path.

The ping_interval is used to set the interval of access, which is accessed using ping.

Server1,server2,server3 is a three MySQL server, specify its hostname, the available IP address instead, to use the host name, you need to write the hostname to/etc/hosts.

Candidate_master=1, which is used to set whether the server is eligible to become the primary server, and if 0 is not eligible.

The installation of MHA Manager will provide a number of tool programs, which are commonly shown below.

Manager node:

-MASTERHA_CHECK_SSH:MHA relies on the SSH environment Detection tool;

-Masterha_check_repl:mysql Replication environment Detection tool;

-MASTERHA_MANAGER:MHA service main program;

-Masterha_check_status:mha Running status Detection tool;

-Masterha_master_monitor:mysql Master node usability monitoring tool;

-Masterha_master_switch:master node Switching tool;

-Masterha_conf_host: Add or remove a configured node;

-Masterha_stop: Tools to close the MHA service;

Node nodes:

-Save_binary_logs: Save and copy the binary log of master;

-Apply_diff_relay_logs: Identify the difference of the relay log event and apply it to other slave;

-Filter_mysqlbinlog: Removal of unnecessary ROLLBACK events (MHA no longer uses this tool);

-Purge_relay_logs: Clears the relay log (does not block SQL threads);

Use MASTERHA_CHECK_SSH to test whether the communication environment is normal

Normal communication environment

Use MASTERHA_CHECK_REPL to test whether replication is normal between databases

Replication Environment OK

Open manager, put in the background, and place the output in Manager.log

Stop the master 172.16.25.101mysqld,mysqld_safe service to see if the master server is changed

172.16.25.101 is down

Compared with the manager, the 172.16.25.102 data is closest, the selected 172.16.25.102 is slave1 as the new master, and the data is synchronized with 172.16.25.103

If the 172.16.25.101 service restarts, it will need to be synchronized with the data in the current primary server and be added to the MySQL cluster after authorization.

MySQL High-availability MHA