The whole process of mysql highly available MHA deployment
Deployment plan
Mysql_master192.168.2.74centos6.9mysql5.5/mha-nodemysql_salve1192.168.2.75centos6.9mysql5.5/mha-nodemysql_salve2192.168.2.76centos6.9mysql5.5/mha-node/mha-man
This deployment uses three servers. Mha-manager is not installed with a single server, and can be installed separately in production. This time, centos6.9 system (optimized by http://youprince.blog.51cto.com/9272426/1974967), mysql5.5 (installed with ansible, not introduced this time) are used. All three participate in the competition and use VIP:192.168.2.199.
Installation ahead of time
A) configure the hostname
B) set up host files
# 3 hosts execute cat > > / etc/hosts grant replication slave on *. * to 'repl'@'192.168.2.75' identified by' admin123';Query OK, 0 rows affected (0 sec) mysql > grant replication slave on *. * to 'repl'@'192.168.2.76' identified by' admin123';Query OK, 0 rows affected (0 sec)
Configure synchronization on mysql_salve1 and mysql_slave2
Mysql > change master to master_host='192.168.2.74',master_port=55555,master_user='repl',master_password='admin123',master_log_file='mysql-bin.000003',master_log_pos=107;Query OK, 0 rows affected (0.00 sec) mysql > start salve;mysql > show slave status\ G
Install plug-ins on mysql_master
Mysql > install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.02 sec) mysql > install plugin rpl_semi_sync_slave soname' semisync_slave.so';Query OK, 0 rows affected (0.01 sec) mysql > set global rpl_semi_sync_master_enabled=on; Query OK, 0 rows affected (0.00 sec)
Install plug-ins on mysql_salve1 and mysql_slave2
Mysql > install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.01 sec) mysql > install plugin rpl_semi_sync_slave soname' semisync_slave.so';Query OK, 0 rows affected (0.00 sec) mysql > set global rpl_semi_sync_slave_enabled=on; Query OK, 0 rows affected (0.00 sec) mysql > set global relay_log_purge=0;Query OK, 0 rows affected (0.00 sec)
4. Start installing mha-node
All three mysql need to be installed. Install dependent yum install perl-DBD-MySQL first.
[root@mysql_master] # rpm-ivh mha4mysql-node-0.54-0.el6.noarch.rpm Preparing... # [100%] 1:mha4mysql-node # [100%] [root@mysql_master] #
Create a new account on the mysql that needs to participate in the election (all 3 on my side are elected, so all 3 have to execute the following. I do not need a read-only library), mha account is the permissions used by mha-man to manage the database, it should be noted that repl is used to synchronize accounts in large production.
Grant all privileges on *. * to 'mha'@'192.168.2.74' identified by' admin123';grant all privileges on *. * to 'mha'@'192.168.2.75' identified by' admin123';grant all privileges on *. * to 'mha'@'192.168.2.76' identified by' admin123';grant replication slave on *. * to 'repl'@'192.168.2.74' identified by' admin123' Grant replication slave on *. * to 'repl'@'192.168.2.75' identified by' admin123';grant replication slave on *. * to 'repl'@'192.168.2.76' identified by' admin123';flush privileges
The implementation is as follows (only one of them is listed)
Mysql > grant all privileges on *. * to 'mha'@'192.168.2.74' identified by' admin123';Query OK, 0 rows affected (0 sec) mysql > grant all privileges on *. * to 'mha'@'192.168.2.75' identified by' admin123';Query OK, 0 rows affected (0 sec) mysql > grant all privileges on *. * to 'mha'@'192.168.2.76' identified by' admin123' Query OK, 0 rows affected (0.00 sec) mysql > grant replication slave on *. * to 'repl'@'192.168.2.74' identified by' admin123';Query OK, 0 rows affected (0 sec) mysql > grant replication slave on *. * to 'repl'@'192.168.2.75' identified by' admin123';Query OK, 0 rows affected (0 sec) mysql > grant replication slave on *. * to 'repl'@'192.168.2.76' identified by' admin123' Query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec)
5. Start installing the mha-manager node (you can install it on a separate host, and I'm using one of the nodes, mysql_slave2)
Installation dependency
Yum install-y perl-DBD-MySQLyum install-y perl-Config-Tinyyum install-y perl-Log-Dispatchyum install-y perl-Parallel-ForkManageryum install-y perl-Time-HiResyum install-y perl-devel
Install mha-manager
[root@mysql_slave2] # rpm-ivh mha4mysql-manager-0.55-0.el6.noarch.rpm Preparing... # [100%] 1:mha4mysql-manager #
Create a profile
The mha-manager created by mkdir-p / etc/mha/ {conf,logs,work} # uses the directory conf to put the configuration file # create the app1.conf### file cat > / etc/mha/conf/app1.cnf\ $command, 'ssh_user=s' = >\ $ssh_user,' orig_master_host=s' = >\ $orig_master_host, 'orig_master_ip=s' = >\ $orig_master_ip 'orig_master_port=i' = >\ $orig_master_port,' new_master_host=s' = >\ $new_master_host, 'new_master_ip=s' = >\ $new_master_ip,' new_master_port=i' = >\ $new_master_port, 'new_master_user=s' = >\ $new_master_user,' new_master_password=s' = >\ $new_master_password,) Exit & main (); sub main {if ($command eq "stop" | | $command eq "stopssh") {# $orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. My $exit_code = 1; eval {& stop_vip (); # updating global catalog, etc $exit_code = 0;}; if ($@) {warn "Got Error: $@\ n"; exit $exit_code;} exit $exit_code;} elsif ($command eq "start") {# all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. My $exit_code = 10; eval {& start_vip (); $exit_code = 0;}; if ($@) {warn $@; # If you want to continue failover, exit 10. Exit $exit_code;} exit $exit_code } elsif ($command eq "status") {print "Checking the status of the script: ssh-t $ssh_user\ @ $orig_master_host\" $ssh_start_vip\ "\ n"; `ssh-t $ssh_user\ @ $orig_master_host\ "$ssh_start_vip\" `; exit 0;} else {& usage (); exit 1 } sub usage {print "Usage: master_ip_failover-- command=start | stop | stopssh | status-- orig_master_host=host-- orig_master_ip=ip-- orig_master_port=port-- new_master_host=host-- new_master_ip=ip-- new_master_port=port\ n";} sub start_vip () {print "Checking the start of the script: ssh-t $ssh_user\ @ $new_master_host\" $ssh_start_vip\ "\ n" `print-t $ssh_user\ @ $new_master_host\ "$ssh_start_vip\" `;} sub stop_vip () {print "Checking the stop/stopssh of the script: ssh-t $ssh_user\ @ $orig_master_host\" $ssh_stop_vip\ "\ n"; `ssh-t $ssh_user\ @ $orig_master_host\ "$ssh_stop_vip\"`;}
Restart managrer
Masterha_stop-conf=/etc/mha/conf/app1.cnfmasterha_manager-conf=/etc/mha/conf/app1.cnf-remove_dead_master_conf-ignore_last_failover
Check mysql_master and see that vip is already on master.
[root@mysql_master ~] # ifconfig eth0 Link encap:Ethernet HWaddr 00:0C:29:74:4D:FB inet addr:192.168.2.74 Bcast:192.168.7.255 Mask:255.255.248.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:108407 errors:0 dropped:0 overruns:0 frame:0 TX packets:7780 errors:0 dropped:0 overruns:0 carrier:0 Collisions:0 txqueuelen:1000 RX bytes:18041186 (17.2 MiB) TX bytes:789173 (770.6 KiB) eth0:1 Link encap:Ethernet HWaddr 00:0C:29:74:4D:FB inet addr:192.168.2.199 Bcast:192.168.2.255 Mask:255.255.255.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1eth2 Link encap:Ethernet HWaddr 00:0C:29:74:4D:05 Inet addr:172.16.16.1 Bcast:172.16.16.15 Mask:255.255.255.240 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:96363 errors:0 dropped:0 overruns:0 frame:0 TX packets:220 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:6307546 (6.0 MiB) TX bytes:9240 (9.0 KiB) Lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:84 errors:0 dropped:0 overruns:0 frame:0 TX packets:84 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:14900 (14.5KiB) TX bytes:14900 (14.5KiB) [root@mysql_master ~] #
7. test
Note: my mha-manager above is started using screen.
Now stop master. Observe the mha-maager log / etc/mha/logs/manager.log,
-Failover Report-app1: MySQL Master failover mysql_master to mysql_slave1 succeededMaster mysql_master is downloading check MHA Manager logs at mysql_slave2:/etc/mha/logs/manager.log for details.Started automated (non-interactive) failover.Invalidated master IP address on mysql_master.The latest slave mysql_slave1 (192.168.2.75 MySQL Master failover mysql_master to mysql_slave1 succeededMaster mysql_master is 5555) has all relay logs for recovery.Selected mysql_slave1 as a new master.mysql_slave1: OK: Applying all logs succeeded.mysql_slave1: OK : Activated master IP address.mysql_slave2: This host has the latest relay log events.Generating relay diff files from the latest slave succeeded.mysql_slave2: OK: Applying all logs succeeded. Slave started, replicating from mysql_slave1.mysql_slave1: Resetting slave info succeeded.Master failover to mysql_slave1 (192.168.2.75) completed successfully.
I can see from the log that it has been migrated to 192.168.2.75 (mysql_slave1).
[root@mysql_slave1 ~] # ifconfig eth0 Link encap:Ethernet HWaddr 00:0C:29:FB:B5:98 inet addr:192.168.2.75 Bcast:192.168.7.255 Mask:255.255.248.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:110418 errors:0 dropped:0 overruns:0 frame:0 TX packets:6705 errors:0 dropped:0 overruns:0 carrier:0 Collisions:0 txqueuelen:1000 RX bytes:18166949 (17.3 MiB) TX bytes:709681 (693.0 KiB) eth0:1 Link encap:Ethernet HWaddr 00:0C:29:FB:B5:98 inet addr:192.168.2.199 Bcast:192.168.2.255 Mask:255.255.255.0 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1eth2 Link encap:Ethernet HWaddr 00:0C:29:FB:B5:A2 Inet addr:172.16.16.2 Bcast:172.16.16.15 Mask:255.255.255.240 UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:100427 errors:0 dropped:0 overruns:0 frame:0 TX packets:230 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:6577954 (6.2MiB) TX bytes:9660 (9.4KiB) Lo Link encap:Local Loopback inet addr:127.0.0.1 Mask:255.0.0.0 UP LOOPBACK RUNNING MTU:65536 Metric:1 RX packets:201 errors:0 dropped:0 overruns:0 frame:0 TX packets:201 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:0 RX bytes:17985 (17.5KiB) TX bytes:17985 (17.5KiB) [root@mysql_slave1 ~] #
VIP has flown over. Check the status of manager at this time.
[root@mysql_slave2 logs] # masterha_check_status-- conf=/etc/mha/conf/app1.cnf app1 is stopped (2:NOT_RUNNING). [root@mysql_slave2 logs] #
Sure enough, you can see that manager has been stopped. This is a bug started by screen. To solve this problem, it is officially recommended to use daemontools. I won't go into details here.
