Mysql high-availability architecture MHA construction and test failover MHA project website code. google. compmysql-master-ha 1. environment Introduction 1. host Deployment manager: 10.10.54.154master: 10.10.54.156slave1: 10.10.54.155 (slave master) Server Load balancer: 10.10.54.1572. step. first, use ssh-k
Mysql high availability architecture MHA build and test failover MHA Project Website https://code.google.com/p/mysql-master-ha/ 1. environment Introduction 1. host Deployment manager machine: 10.10.54.154 master machine: 10.10.54.156 slave1 machine: 10.10.54.155 (slave master) slave2 machine: 10.10.54.157 2. step. first, use ssh-k
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 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 on manager
123456789101112131415161718192021 |
[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 |
4. Establish master-slave replication between slave1 and slave2
12345678910111213141516171819 |
[master:156] 1.shell> vim /etc/my .cnf # Change server-id to 1 server- id =1 log-bin=mysql-bin binlog_format=mixed # Authorized operations 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 to 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; |
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 name password=123456 manager_workdir= /masterha/app1 # The directory can be specified arbitrarily. 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 machines 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 |
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 .cnf Sun 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>&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.. ----------------------------------------------------------------- |
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