Set up MySQL high-availability architecture MHA in CentOS 6.4

Build an MHA Environment

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

// 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.

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

[Manager machine]
1.
Shell> ssh-keygen-t rsa-B 2048
Shell> scp id_rsa.pub root@10.10.54.155:/root/. ssh/
Shell> scp id_rsa.pub root@10.10.54.156:/root/. ssh/
Shell> scp id_rsa.pub root@10.10.54.157:/root/. ssh/
2. Use cat xxx> authorized_keys on the other three hosts to import the public key to the/root/. ssh/authorized_keys file.
3. Repeat this step on the other three hosts to allow password-free logon between any two hosts.

// B. Install MHAmha4mysql-node, mha4mysql-manager package

1. Install MHAmha4mysql-node

[Manager, master, slave1, slave2]
Shell> yum update
Shell> yum-y install perl-DBD-MySQL ncftp
Shell> wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53-0.noarch.rpm
Sehll> rpm-ivh mha4mysql-node-0.53-0.noarch.rpm

2. Install mha4mysql-manager

[Manager]
Shell> yum install perl
Shell> yum install cpan
Shell> rpm-ivh mha4mysql-manager-0.53-0.el6.noarch.rpm
Error:
Perl (Config: Tiny) is needed by mha4mysql-manager-0.53-0.noarch
Perl (Log: Dispatch) is needed by mha4mysql-manager-0.53-0.noarch
Perl (Log: Dispatch: File) is needed by mha4mysql-manager-0.53-0.noarch
Perl (Log: Dispatch: Screen) is needed by mha4mysql-manager-0.53-0.noarch
Perl (Parallel: ForkManager) is needed by mha4mysql-manager-0.53-0.noarch
Perl (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.rpm
Shell> wget ftp://ftp.muug.mb.ca/mirror/centos/6.5/ OS /x86_64/Packages/compat-db43-4.3.29-15.el6.x86_64.rpm
Shell> wget http://downloads.naulinux.ru/pub/NauLinux/6x/i386/sites/School/RPMS/perl-Log-Dispatch-2.27-1.el6.noarch.rpm
Shell> wget http://dl.Fedoraproject.org/pub/epel/6/i386/perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
Shell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
Shell> wget http://dl.fedoraproject.org/pub/epel/6/i386/perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
Shell> wget http://mirror.centos.org/centos/6/ OS /x86_64/Packages/perl-Time-HiRes-1.9721-136.el6.x86_64.rpm
Shell> 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.rpm
Shell> rpm-ivh mha4mysql-manager-0.53-0.el6.noarch.rpm

// C. Create master, slave1, and slave2 master-slave Replication

[Master: 156]
1. shell> vim/etc/my. cnf
Server-id = 1
Log-bin = mysql-bin
Binlog_format = mixed
2. mysql> grant all privileges on *. * TO 'rep '@ '10. 10.54. %' identified by 'rep123 ';
Mysql> flush privileges;
3. mysql> show master status;

[Slave1, slave2]
4. change master operation
Mysql> change master
Master_host = '10. 10.54.156 ',
Master_port = 3306,
Master_user = 'rep ',
Master_password = 'rep123 ',
Master_log_file = 'mysql-bin.000001 ',
Master_log_pos = 112;

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

[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;
7. Set the replication permission account on all hosts
Mysql> grant all privileges on *. * TO 'mha _ rep '@ '10. 10.2.10' identified by '123 ';

// D. Configure the MHA file on the manager

[Manager: 154]
1.
Shell> mkdir-p/masterha/app1
Shell> mkdir/etc/masterha
Shell> vim/etc/masterha/app1.cnf
[Server default]
User = mha_rep # mysql Administrator name
Password = 123456
Manager_workdir =/masterha/app1
Manager_log =/masterha/app1/manager. log
Remote_workdir =/masterha/app1
Ssh_user = root # account name for ssh key-free Logon
Repl_user = rep # mysql replication account, used to synchronize binary logs between master and slave hosts
Repl_password = rep123
Ping_interval = 1 # ping interval, used to check whether the master is normal
[Server1]
Hostname = 10.10.54.155
# Ssh_port = 9999
Master_binlog_dir =/data/ndb # mysql database directory
Candidate_master = 1 # enable the master as the new master
[Server2]
Hostname = 10.10.54.156
# Ssh_port = 9999
Master_binlog_dir =/data/ndb
Candidate_master = 1
[Server3]
Hostname = 10.10.54.157
# Ssh_port = 9999
Master_binlog_dir =/data/ndb
No_master = 1 # Set no_master = 1 so that the host cannot become a new master

// E. masterha_check_ssh tool to verify that the ssh trusted logon is successful

[Manager: 154]
Shell> masterha_check_ssh -- conf =/etc/masterha/app1.cnf
Sun Mar 2 17:45:38 2014-[debug] OK.
Sun Mar 2 17:45:38 2014-[info] All SSH connection tests passed successfully.

// F. masterha_check_repl tool to verify that mysql replication is successful

[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.
---------------------------------------------------------------

// G. Start MHA manager and monitor log files

[Manager: 154]
Shell> nohup masterha_manager -- conf =/etc/masterha/app1.cnf>/tmp/mha_manager.log 2> & 1
Shell> 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 ..
-----------------------------------------------------------------
# Manager. log indicates that MHA runs well and is monitoring whether the master is working properly

Deploy MHA in MySQL 5.5

1 2 Next Page