Mysql high-availability architecture MHA setup and test failover

MHA Project Website

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

1. Environment Introduction

1. Host deployment

Manager: 10.10.54.154
Master: 10.10.54.156
Server Load balancer 1: 10.10.54.155 (Backup master)

Server Load balancer: 10.10.54.157

2. General steps
A. First, use ssh-keygen to achieve mutual key-free login between the four hosts
B. Install MHAmha4mysql-node, mha4mysql-manager Software Package
C. Establish master-slave replication between slave1 and slave2
D. Configure the MHA file on the manager.
E. masterha_check_ssh tool to verify that ssh trusted logon is successful
F. masterha_check_repl tool to verify whether mysql replication is successful
G. Start MHA manager and monitor log files
H. Test whether automatic failover is performed after the master (156) is down.

3. Description: The host name in the brackets below describes the host on which the current operation is performed.

Ii. First, use ssh-keygen to achieve mutual key-free login between the four hosts

[Manager machine]
Shell> ssh-keygen-t rsa-B 2048
Shell> scp-copy-id root@10.10.54.155

Shell> scp-copy-id root@10.10.54.156

Shell> scp-copy-id root@10.10.54.157

Repeat this step on the other three hosts to allow password-free logon between any two hosts.

3. Install MHAmha4mysql-node, mha4mysql-manager Software Package

1. MHAmha4mysql-node installed on four hosts

12345

[manager,master,slave1,slave2]shell> yum updateshell> yum -y install perl-DBD-MySQL ncftpshell> wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpmsehll> rpm -ivh mha4mysql-node-0.53-0.noarch.rpm

2. Install mha4mysql-manager on manager

123456789101112131415161718192021

[manager]shell> yum install perlshell> yum install cpanshell> rpm -ivh mha4mysql-manager-0.53-0.el6.noarch.rpmerror:perl(Config::Tiny) is needed by mha4mysql-manager-0.53-0.noarchperl(Log::Dispatch) is needed by mha4mysql-manager-0.53-0.noarchperl(Log::Dispatch::File) is needed by mha4mysql-manager-0.53-0.noarchperl(Log::Dispatch::Screen) is needed by mha4mysql-manager-0.53-0.noarchperl(Parallel::ForkManager) is needed by mha4mysql-manager-0.53-0.noarchperl(Time::HiRes) is needed by mha4mysql-manager-0.53-0.noarch[solution]shell> wget ftp://ftp.muug.mb.ca/mirror/centos/5.10/os/x86_64/CentOS/perl-5.8.8-41.el5.x86_64.rpmshell> wget ftp://ftp.muug.mb.ca/mirror/centos/6.5/os/x86_64/Packages/compat-db43-4.3.29-15.el6.x86_64.rpmshell> wget http://downloads.naulinux.ru/pub/NauLinux/6x/i386/sites/School/RPMS/perl-Log-Dispatch-2.27-1.el6.noarch.rpmshell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpmshell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sender-0.8.16-3.el6.noarch.rpmshell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sendmail-0.79-12.el6.noarch.rpmshell> wget http://mirror.centos.org/centos/6/os/x86_64/Packages/perl-Time-HiRes-1.9721-136.el6.x86_64.rpmshell> rpm -ivh perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm perl-Log-Dispatch-2.27-1.el6.noarch.rpm perl-Mail-Sender-0.8.16-3.el6.noarch.rpm perl-Mail-Sendmail-0.79-12.el6.noarch.rpm perl-Time-HiRes-1.9721-136.el6.x86_64.rpmshell> rpm -ivh mha4mysql-manager-0.53-0.el6.noarch.rpm

4. Establish master-slave replication between slave1 and slave2

12345678910111213141516171819

[Master: 156]1. shell> vim/Etc/my. Cnf# Change server-id to 1Server-Id= 1Log-bin = mysql-binBinlog_format = mixed# Authorized operations2. mysql> grant all privileges on *. *'Rep'@'10. 10.54. %'IDENTIFIED'Rep123';Mysql> flush privileges;3. mysql> show master status;[Slave1, slave2]4. change master operationmysql> change master tomaster_host='10.10.54.156',master_port=3306,master_user='rep',master_password='rep123',master_log_file='mysql-bin.000001',master_log_pos=112;

Note: Authorization must also be performed on the server Load balancer instance because the slave master is used.
[Slave1: 155]
5. mysql> grant all privileges on *. * TO 'rep '@ '10. 10.54. %' identified by 'rep123 ';

[Master, slave1, slave2]
6. commands for checking whether master-slave replication is successful
Mysql> start slave;
Mysql> stop slave;
Mysql> reset slave;
Mysql> show slave status \ G;

5. Set the replication permission account on all hosts
Mysql> grant all privileges on *. * TO 'mha _ rep '@ '10. 10.2.10' identified by '123 ';

6. Configure MHA files on the manager to manage nodes

[Manager: 154]
Shell> mkdir-p/masterha/app1
Shell> mkdir/etc/masterha
Shell> vim/etc/masterha/app1.cnf

12345678910111213141516171819202122232425

[Server default]User = mha_rep# Mysql management metric namePassword = 123456Manager_workdir =/Masterha/app1# The directory can be specified arbitrarily.Manager_log =/Masterha/app1/manager. LogRemote_workdir =/Masterha/app1Ssh_user = root# Account name for ssh key-free LogonRepl_user = rep# Mysql replication account, used to synchronize binary logs between master and slave machinesRepl_password = rep123Ping_interval = 1# Ping interval, used to check whether the master is normal[Server1]Hostname= 10.10.54.155# Ssh_port = 9999Master_binlog_dir =/Data/ndb# Mysql database directoryCandidate_master = 1# Enable the master as the new master[Server2]Hostname= 10.10.54.156# Ssh_port = 9999Master_binlog_dir =/Data/ndbCandidate_master = 1[Server3]Hostname= 10.10.54.157# Ssh_port = 9999Master_binlog_dir =/Data/ndbNo_master = 1# Set no_master = 1 so that the host cannot become a new master

7. Verify that ssh trusted login and mysql master-slave replication are successful

1. masterha_check_ssh verify ssh trusted Login

1234	[manager:154]shell> masterha_check_ssh --conf=/etc/masterha/app1.cnfSun Mar 2 17:45:38 2014 - [debug] ok.Sun Mar 2 17:45:38 2014 - [info] All SSH connection tests passed successfully.

2. masterha_check_repl verify that mysql replication is successful

1234567891011

[manager:154]shell> masterha_check_repl --conf=/etc/masterha/app1.cnf---------------------------------------------------------Sun Mar 2 13:16:57 2014 - [info] Slaves settings check done.Sun Mar 2 13:16:57 2014 - [info]10.10.54.156 (current master) +--10.10.54.155 +--10.10.54.157...MySQL Replication Health is OK.---------------------------------------------------------------

8. Start MHA manager and monitor log files

12345678910

[manager:154]shell> nohup masterha_manager --conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log 2>&1shell> tail -f /masterha/app1/manager.log--------------------------------------------------------------- 10.10.54.156 (current master) +--10.10.54.155 +--10.10.54.157...Sun Mar 2 13:09:25 2014 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..-----------------------------------------------------------------

The monitored manager. log file indicates that MHA runs well and is"waiting until MySQL doesn't respond"

9. Test whether automatic failover is performed after the master (156) is down


1. Test whether automatic switch is successful

When the master machine is down

Shell>/etc/init. d/myqld stop
When the master node is down, the monitoring file/masterha/app1/manager. log on the manager displays an error message, indicating that automatic failover is not allowed:
[Error]
-----------------------------------------------------------
Sun Mar 2 13:13:46 2014-[error] [/usr/share/perl5/vendor_perl/MHA/ManagerUtil. pm, ln178] Got ERROR: Use of uninitialized value $ msg in scalar chomp at/usr/share/perl5/vendor_perl/MHA/ManagerConst. pm line 90.
-----------------------------------------------------------
To solve this problem, add a row before the file/usr/share/perl5/vendor_perl/MHA/ManagerConst. pm row 90th (chomp $ msg:


1	$msg = "" unless($msg);

Okay, the error is fixed. Next we will repeat the above steps again:
Mysql service on master: shell>/etc/init. d/mysqld stop
View the monitoring file on the manager Host again

Shell> tail-f/masterha/app1/manager. log
Log File display:
-----------------------------------------------------------
----- Failover Report -----
App1: MySQL Master failover 10.10.54.156 to 10.10.54.155 succeeded
Master 10.10.54.156 is down!
Check MHA Manager logs at mycentos4:/masterha/app1/manager. log for details.
Started automation (non-interactive) failover.
The latest slave 10.10.54.155 (10.10.54.155: 3306) has all relay logs for recovery.
Selected 10.10.54.155 as a new master.
10.10.54.155: OK: Applying all logs succeeded.
10.10.54.157: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
10.10.54.157: OK: Applying all logs succeeded. Slave started, replicating from 10.10.54.155.
10.10.54.155: Resetting slave info succeeded.
Master failover to 10.10.54.155 (10.10.54.155: 3306) completed successfully.
--------------------------------------------------------


2. Check the replication status after the switchover is successful.
[Master: 156]
Shell>/etc/init. d/mysqld start
[Manager: 154]
Shell> masterha_check_repl -- conf =/etc/masterha/app1.cnf
--------------------------------------------------------------
Sun Mar 2 13:22:11 2014-[info] Slaves settings check done.
Sun Mar 2 13:22:11 2014-[info]
10.10.54.155 (current master)
+ -- 10.10.54.156
+ -- 10.10.54.157
...
MySQL Replication Health is OK.
---------------------------------------------------------------
The above "10.10.54.155 (current master)" statement indicates that the master is successfully switched to the 155 sub-host.

10. After the test in the previous step, the new master machine is set to 155, and the new master machine is down to 155 machines. Test the Failover again.

1. Start the management Node
Shell> nohup masterha_manager -- conf =/etc/masterha/app1.cnf>/tmp/mha_manager.log 2> & 1
2. Start log detection, and then drop the new master (155), and then view the monitoring file changes
Shell> tail-f/masterha/app1/manager. log
3. When 155 machines are dropped (that is, the new master)
Shell>/etc/init. d/mysqld stop
4. view the monitoring file changes on the manager Host.
[Error] [/usr/share/perl5/vendor_perl/MHA/MasterFailover. pm, ln295] Last failover was done at 13:02:47. current time is too early to do failover again. if you want to do failover, manually remove/masterha/app1/app1.failover. complete and run this script again.
Error Solution
1. The log file prompts that the master switchover is too fast. You need to delete/masterha/app1/app1.failover. complete.
1. Delete app1.failover. complete
Shell> rm/masterha/app1/app1.failover. complete
5. Retest:
Master has been transferred successfully and is re-transferred to 156 machines
--------------------------------------------------------
Master 10.10.54.155 is down!
Check MHA Manager logs at mycentos4:/masterha/app1/manager. log for details.
Started automation (non-interactive) failover.
The latest slave 10.10.54.156 (10.10.54.156: 3306) has all relay logs for recovery.
Selected 10.10.54.156 as a new master.
10.10.54.156: OK: Applying all logs succeeded.
10.10.54.157: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
10.10.54.157: OK: Applying all logs succeeded. Slave started, replicating from 10.10.54.156.
10.10.54.156: Resetting slave info succeeded.
Master failover to 10.10.54.156 (10.10.54.156: 3306) completed successfully.
-----------------------------------------------------------





// Appendix: After failover, use the command to restore the original master

[Manager: 154]
1. Execute on the old master
Mysql> reset master;
Mysql> change master to master_host = '10. 10.54.155 ', master_port = 3306, master_user = 'rep', master_password = 'rep123', master_log_file = 'mysql-bin.000031 ', master_log_pos = 112;
Mysql> start slave; # Temporarily change the old master to slave
2. Then on the manager node:
[Manager: 154]
Shell> masterha_master_switch -- master_state = alive -- conf =/etc/masterha/app1.cnf
# Master switch back