Mysql high-availability architecture MHA setup and test failover _ MySQL

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-yInstallPerl-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> yumInstallPerlShell> yumInstallCpanShell> 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> wgetFtp:// Ftp. Muug. mb. ca/Mirror/centos/5. 10/OS/x86_64/CentOS/perl-5. 8.8-41. el5.x86 _ 64.rpmShell> wgetFtp:// 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/epels/6/i386/perl-Parallel-ForkManager-0. 7.9-1. el6.noarch. rpmShell> wget http:// Dl.Fedoraproject.org/Pub/epels/6/i386/perl-Mail-Sender-0. 8.16-3. el6.noarch. rpmShell> wget http:// Dl.Fedoraproject.org/Pub/epels/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 masterMaster_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