MySQL MHA master-slave auto Switch high availability

MHA (Master high availability) is currently a relatively mature solution for MySQL multi-server (over two units) and highly available.

One, what is MHA, what are the characteristics

1. Automatic monitoring and failover of the primary server

MHA monitors the primary server of the replication architecture and automatically fails over if a primary server failure is detected. Even if some from the server do not receive the latest relay LOG,MHA automatically from the latest from the server to identify the difference of relay log and apply these logs to other slave servers, so all from the server remains consistent. MHA usually in a few seconds to complete the failover, 9-12 seconds to detect the primary server failure, 7-10 seconds to shut down the failure of the primary server to avoid brain crack, a few seconds to apply the difference in the relay log to the new primary server, the entire process can be completed within the 10-30s. You can also set a priority to specify one of the slave as the master candidate. Because MHA fixes consistency between slaves, any slave can be turned into a new master without a consistency problem, resulting in replication failure.

2. Interactive Primary server failover

You can use only MHA failover instead of monitoring the primary server, and when the primary server fails, manually call MHA to fail.

3. Non-interactive primary failover

The primary server is not monitored, but failover is automatically implemented. This feature applies to other software that has been used to monitor the state of the primary server, such as heartbeat to detect primary server failure and virtual IP address takeover, and can use MHA to implement failover and slave server promotion to master server.

4. Switch master server online

In many cases, the existing primary server needs to be migrated to another server. For example, the primary server hardware failure, the raid control card needs to be rebuilt, the primary server moved to a better performance server, and so on. Maintenance of the primary server causes performance degradation, resulting in at least no data writing to the downtime. In addition, blocking or killing the currently running session can cause problems with data inconsistencies between the primary owners. The MHA provides fast switching and graceful blocking writes, which only need to be 0.5-2s for a period of time during which data cannot be written. In many cases, 0.5-2s blocking writes are acceptable. Therefore, the switch master server does not need to plan to allocate maintenance time window (hehe, do not need you to complete the task of switching the master server overnight when the night wind is high).

5.MHA consists of two parts: MHA Manager (Management node) and MHA node (data node)

To build a MHA, requires a replication cluster must have at least three database servers, one master two from, that is, one to act as master, one to act as the standby master, and the other to act as slave, the management node can and master on a machine. So if you only have two machines, heartbeat,keepalive is a good choice.

6.MHA is more flexible, can write scripts, to do failover, or master-slave switch, and so on.

7.MHA after the failure, the configuration file will be modified, which makes me feel very funny, if failover needs to re-modify the configuration file, restart the Masterha_manager service.

Two, server description

View copy print?

1. 192.168.10.103 Masters //Master
2. 192.168.10.209 slave1 //from
3. 192.168.10.219 slave2 //from (main preparation)
4. 192.168.10.220 Manage //Management node

A master two from, a management node, the above content is written to each/etc/hosts

Three, inter-server, no password SSH login

View copy print?

1. # ssh-keygen-t RSA
2. # ssh-copy-id-i/root/.ssh/id_rsa.pub [email protected]
3. # ssh-copy-id-i/root/.ssh/id_rsa.pub [email protected]
4. # ssh-copy-id-i/root/.ssh/id_rsa.pub [email protected]
5. # ssh-copy-id-i/root/.ssh/id_rsa.pub [email protected]

There are 5 commands above, if on the 103 machine, 103 itself does not need to execute ssh-copy-id. After copy, SSH test, the machine between the switch is not required password.

Four, install MHA

1, Download MHA

Https://code.google.com/p/mysql-master-ha/downloads/list

2, all nodes are to be installed

1. # yum Install-y perl-dbd-mysql
2. # RPM-IVH mha4mysql-node-0.54-0.el6.noarch.rpm

3, Management node

1. # yum install-y perl-dbd-mysql perl-config-tiny perl-log-dispatch Perl-parallel-forkmanager
2. # RPM-IVH mha4mysql-manager-0.55-0.el6.noarch.rpm

Note: The version of the Manager and node nodes can be different

Five, configure MySQL replication

Please refer to:mysql replication master-Slave (Master-slave) synchronization

To conform to the MHA configuration, the root of this article is a bit different.

1, the master-slave configuration should have

View copy print?

1. binlog-do-db=test
2. replicate-do-db=test

In general, the primary server needs to contain binlog-do-db=test, from the server needs to include replicate-do-db=test, so that the master and slave can be synchronized . However, if this is the case, the following error will be reported

All Log-bin enabled servers must has same Binlog filtering rules (same binlog-do-db and binlog-ignore-db). Check SHOW MASTER STATUS output and set my.cnf correctly.

In groping this piece of configuration, wasted a lot of time, I always thought, the above English means that the master-slave synchronization of the database to be the same, actually not, but the configuration file, the configuration database this piece to be the same.

2, from the server, to add relay_log_purge=0, if not added, will be reported warning,relay_log_purge=0 is not set on slave

Six, corosync pacemaker MySQL replication configuration

Please refer to:corosync pacemaker MySQL replication for high availability

Configuration Corosync Pacemaker purpose, in fact, is to get a virtual IP, and the main master of one, I can connect through the virtual IP, the advantage is that if the main down, I can connect through the virtual IP master preparation, if the master modified well, Then the virtual IP can be connected to the master without the need to modify the code.

Seven, configure MHA Manage

1, add admin account, each machine performs the following actions

View copy print?

1. Grant all privileges on * * to [e-mail protected]' 192.168.% ' identified by ' test ';
2. Flush privileges;

2, configure/ETC/MHA/APP1.CNF, only on the management side, manage this machine

View copy print?

2. # Mkdir/etc/mha
4. # mkdir-p/var/log/mha/app1
8. [Email protected] mysql]# CAT/ETC/MHA/APP1.CNF
10. [Server Default]
12. manager_log=/Var/log/mha/app1/manager.log
14. manager_workdir=/Var/log/mha/app1.log
16. master_binlog_dir=/Var/lib/mysql
18. User=mha
20. Password=test
22. ping_interval=2
24. Repl_password=test
26. Repl_user=test
28. Ssh_user=root
32. [Server1]
34. hostname=192.168.10.103
36. port=3306
40. [Server2]
42. Candidate_master=1
44. Check_repl_delay=0
46. hostname=192.168.10.219
48. port=3306
52. [Server3]
54. hostname=192.168.10.209
56. port=3306

Configuration in server default is a common configuration of three machines and can be customized in a specific server

Eight, check MHA manage is not configured successfully

1, check SSH login

1. # Masterha_check_ssh--CONF=/ETC/MHA/APP1.CNF

If you see, all SSH connection tests passed successfully, it means the SSH configuration is successful.

2. Check if MySQL replication is configured successfully

1. # MASTERHA_CHECK_REPL--CONF=/ETC/MHA/APP1.CNF

If the following appears, the configuration is successful.

MHA Test MySQL Replication

3. Common commands for the management side

1. Masterha_check_ssh checking SSH configuration status of MHA
2. MASTERHA_CHECK_REPL Check MySQL replication status
3. Masterha_manger Start MHA
4. Masterha_check_status Detecting current MHA running state
5. Masterha_master_monitor Checking Master for downtime
6. Masterha_master_switch control failover (automatic or manual)
7. Masterha_conf_host adding or removing configured server information

Nine, on the management side, start monitoring

View copy print?

1. [Email protected] mha]# nohup masterha_manager--conf=/etc/mha/app1.cnf--remove_dead_master_conf--ignore_last_ Failover </dev/null >/var/log/mha/app1/manager.log 2>&1 &
2. [1] 13675
4. [[email protected] mha]# masterha_check_status--conf=/etc/mha/app1.cnf //View status
5. App1 (pid:13675) is running (0:PING_OK), master:192.168.10.103
7. [[email protected] mha]# masterha_stop--conf=/etc/mha/app1.cnf //Turn off monitoring

Come here, MHA, we've got it all configured.

Ten, say, my test process

1,mysql-u test-p-H 192.168.10.130, login via virtual IP

2. Insert data to see if the Master 103 has the data, and two from the server, is not synchronizing the data.

3, on the main 103, the implementation of CRM node standby, will bring several results.

On The 220 machine ,/ETC/MHA/APP1.CNF

[Server1]
hostname=192.168.10.103
port=3306

This configuration has disappeared.

On the 219 machine , show master status; it's got data, it's turned into a mainframe.

On The 209 machine , show slave Status\g, master_host:192.168.10.219, became 219.

4, on 103, execute # CRM node online, this time, 103 is neither the master nor the standby after the mysqld process is closed, so start mysqld here, and then add 103 to 219.

View copy print?

1. mysql> change MASTER to master_host=' 192.168.10.219 ',
2. master_user=' Test ', master_password=' test ',
3. master_log_file=' mysql-bin.000048 ',
4. master_log_pos=107;

5, online switch master and slave

View copy print?

2. [Email protected] mysql]# masterha_master_switch--conf=/etc/mha/app1.cnf--master_state=alive--new_master_host= 192.168.10.103--new_master_port=3306--orig_master_is_new_slave--running_updates_limit=10000
4. Wed APR 04:14:55-[INFO] mha::masterrotate version 0.55.
6. Wed APR 04:14:55-[info] starting online master switch:
8. Wed APR 04:14:55-[INFO]
10. Wed APR 04:14:55-[INFO] * Phase 1:configuration Check Phase.
12. Wed APR 04:14:55-[INFO]
14. Wed APR 04:14:55-[WARNING] Global configuration file/etc/masterha_default.cnf not found. Skipping.
16. Wed APR 04:14:55-[INFO] Reading application default configurations from/etc/mha/app1.cnf.
18. Wed APR 04:14:55-[INFO] Reading server configurations from/etc/mha/app1.cnf.
20. Wed APR 04:14:55-[INFO] current Alive master:192.168.10.219 (192.168.10.219:3306)
22. Wed APR 04:14:55-[info] Alive Slaves:
24. Wed APR 04:14:55-[info] 192.168.10.209 (192.168.10.209:3306) version=5.1.73-log (oldest major Version between SLA VES) log-bin:enabled
26. Wed APR 04:14:55-[info] replicating from 192.168.10.219 (192.168.10.219:3306)
30. It is better-execute FLUSH no_write_to_binlog TABLES on the master before switching. Is it OK to execute on 192.168.10.219 (192.168.10.219:3306)? (yes/no): YES
32. Wed APR 04:15:10-[info] executing FLUSH no_write_to_binlog TABLES.  This could take a long time.
34. Wed APR 04:15:10-[info] OK.
36. Wed APR 04:15:10-[info] Checking MHA is not monitoring or doing failover
40. 。。。。。。。。。。。。。 Omitted ....... .....

This will switch to the most primitive state.

MySQL MHA master-slave auto Switch high availability