MHA(Master High Availability)目前在 MySQL 高可用方面是一个相对成熟的解决方案, 它由日本 DeNA 公司的 youshimaton 员工(现就职于 Facebook 公司)开发,是一套优秀的作 为 MySQL 高可用性环境下故障切换和主从角色提升的高可用软件。在 MySQL 故障切换过程 中,MHA 能做到在 0~30 秒之内自动完成数据库的主从故障切换操作,并且在进行故障切换 的过程中,MHA 能在最大程度上保证数据的一致性,以达到真正意义上的高可用。
2017-08-31 21:33:52: purge_relay_logs script started. Found relay_log.info: /usr/local/mysql/data/relay-log.info Removing hard linked relay log files slave-relay-bin* under /tmp.. done. Current relay log file: /usr/local/mysql/data/slave-relay-bin.000002 Archiving unused relay log files (up to /usr/local/mysql/data/slave-relay-bin.000001) ... Creating hard link for /usr/local/mysql/data/slave-relay-bin.000001 under /tmp/slave-relay-bin.000001 .. ok. Creating hard links for unused relay log files completed. Executing SET GLOBAL relay_log_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if i t keeps up); SET GLOBAL relay_log_purge=0; .. ok. Removing hard linked relay log files slave-relay-bin* under /tmp.. done. 2017-08-31 21:33:56: All relay log purging operations succeeded.
Sat Dec 29 16:04:53 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Dec 29 16:04:53 2018 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Sat Dec 29 16:04:53 2018 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Sat Dec 29 16:04:53 2018 - [info] MHA::MasterMonitor version 0.56. Creating directory /var/log/masterha/app1.. done.
Sat Dec 29 16:04:55 2018 - [info] Dead Servers:
Sat Dec 29 16:04:55 2018 - [info] Alive Servers:
Sat Dec 29 16:04:55 2018 - [info] server01(192.168.200.111:3306)
Sat Dec 29 16:04:55 2018 - [info] server02(192.168.200.112:3306)
Sat Dec 29 16:04:55 2018 - [info] server03(192.168.200.113:3306)
Sat Dec 29 16:04:55 2018 - [info] server04(192.168.200.114:3306)
Sat Dec 29 16:04:55 2018 - [info] Alive Slaves:
Sat Dec 29 16:04:55 2018 - [info] server02(192.168.200.112:3306) Version=5.5.56-MariaDB (oldest major version between slaves) log-bin:enabled
Sat Dec 29 16:04:55 2018 - [info] Replicating from 192.168.200.111(192.168.200.111:3306)
Sat Dec 29 16:04:55 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Sat Dec 29 16:04:55 2018 - [info] server03(192.168.200.113:3306) Version=5.5.56-MariaDB (oldest major version between slaves) log-bin:enabled
Sat Dec 29 16:04:55 2018 - [info] Replicating from 192.168.200.111(192.168.200.111:3306)
Sat Dec 29 16:04:55 2018 - [info] server04(192.168.200.114:3306) Version=5.5.56-MariaDB (oldest major version between slaves) log-bin:enabled
Sat Dec 29 16:04:55 2018 - [info] Replicating from 192.168.200.111(192.168.200.111:3306)
Sat Dec 29 16:04:55 2018 - [info] Current Alive Master: server01(192.168.200.111:3306)
Sat Dec 29 16:04:55 2018 - [info] Checking slave configurations..
Sat Dec 29 16:04:55 2018 - [info] Checking replication filtering settings..
Sat Dec 29 16:04:55 2018 - [info] binlog_do_db= , binlog_ignore_db=
Sat Dec 29 16:04:55 2018 - [info] Replication filtering check ok.
Sat Dec 29 16:04:55 2018 - [info] Starting SSH connection tests..
Sat Dec 29 16:05:00 2018 - [info] All SSH connection tests passed successfully.
Sat Dec 29 16:05:00 2018 - [info] Checking MHA Node version..
Sat Dec 29 16:05:02 2018 - [info] Version check ok.
Sat Dec 29 16:05:02 2018 - [info] Checking SSH publickey authentication settings on the current master..
Sat Dec 29 16:05:03 2018 - [info] HealthCheck: SSH to server01 is reachable.
Sat Dec 29 16:05:04 2018 - [info] Master MHA Node version is 0.56.
Sat Dec 29 16:05:04 2018 - [info] Checking recovery script configurations on the current master..
Sat Dec 29 16:05:04 2018 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/tmp/save_binary_logs_test -- manager_version=0.56 --start_file=master-bin.000001
Sat Dec 29 16:05:04 2018 - [info] Connecting to root@server01(server01).. Creating /tmp if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to master-bin.000001
Sat Dec 29 16:05:04 2018 - [info] Master setting check done.
Sat Dec 29 16:05:04 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sat Dec 29 16:05:04 2018 - [info] Connecting to root@192.168.200.112(server02:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000002 Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done.
Sat Dec 29 16:05:05 2018 - [info] Connecting to root@192.168.200.113(server03:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to slave-relay-bin.000002 Temporary relay log file is /var/lib/mysql/slave-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done.
Sat Dec 29 16:05:07 2018 - [info] Connecting to root@192.168.200.114(server04:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to slave-relay-bin.000002 Temporary relay log file is /var/lib/mysql/slave-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done.
Sat Dec 29 16:05:08 2018 - [info] Slaves settings check done.
Sat Dec 29 16:05:09 2018 - [info]
server01 (current master)
+--server02
+--server03
+--server04
Sat Dec 29 16:05:09 2018 - [info] Checking replication health on server02.. Sat Dec 29 16:05:09 2018 - [info] ok.
Sat Dec 29 16:05:09 2018 - [info] Checking replication health on server03.. Sat Dec 29 16:05:09 2018 - [info] ok.
Sat Dec 29 16:05:09 2018 - [info] Checking replication health on server04.. Sat Dec 29 16:05:09 2018 - [info] ok.
Sat Dec 29 16:05:09 2018 - [info] Checking master_ip_failover_script status:
Sat Dec 29 16:05:09 2018 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=server01 --orig_master_ip=192.168.200.111 --orig_m aster_port=3306
IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===
Checking the Status of the script.. OK
bash: /etc/init.d/keepalived: 没有那个文件或目录
Sat Dec 29 16:05:09 2018 - [info] OK.
Sat Dec 29 16:05:09 2018 - [warning] shutdown_script is not defined.
Sat Dec 29 16:05:09 2018 - [info] Got exit code 0 (Not master dead).
2: ens32: <broadcast,multicast,up,lower_up> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 inet 192.168.200.111/24 brd 192.168.200.255 scope global ens32 inet 192.168.200.100/24 brd 192.168.200.255 scope global secondary ens32:1
Server05(192.168.200.115)修改故障转移脚本
[root@server05 ~]# head -13 /usr/local/bin/master_ip_failover
Sat Dec 29 16:09:50 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Dec 29 16:09:50 2018 - [info] Reading application default configurations from /etc/masterha/app1.cnf..
Sat Dec 29 16:09:50 2018 - [info] Reading server configurations from /etc/masterha/app1.cnf..
Sat Dec 29 16:09:50 2018 - [info] MHA::MasterMonitor version 0.56. Sat Dec 29 16:09:51 2018 - [info] Dead Servers:
Sat Dec 29 16:09:51 2018 - [info] Alive Servers:
Sat Dec 29 16:09:51 2018 - [info] server01(192.168.200.111:3306)
Sat Dec 29 16:09:51 2018 - [info] server02(192.168.200.112:3306)
Sat Dec 29 16:09:51 2018 - [info] server03(192.168.200.113:3306)
Sat Dec 29 16:09:51 2018 - [info] server04(192.168.200.114:3306)
Sat Dec 29 16:09:51 2018 - [info] Alive Slaves:
Sat Dec 29 16:09:51 2018 - [info] server02(192.168.200.112:3306) Version=5.5.56-MariaDB (oldest major version between slaves) log-bin:enabled
Sat Dec 29 16:09:51 2018 - [info] Replicating from 192.168.200.111(192.168.200.111:3306)
Sat Dec 29 16:09:51 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Sat Dec 29 16:09:51 2018 - [info] server03(192.168.200.113:3306) Version=5.5.56-MariaDB (oldest major version between slaves) log-bin:enabled
Sat Dec 29 16:09:51 2018 - [info] Replicating from 192.168.200.111(192.168.200.111:3306)
Sat Dec 29 16:09:51 2018 - [info] server04(192.168.200.114:3306) Version=5.5.56-MariaDB (oldest major version between slaves) log-bin:enabled
Sat Dec 29 16:09:51 2018 - [info] Replicating from 192.168.200.111(192.168.200.111:3306)
Sat Dec 29 16:09:51 2018 - [info] Current Alive Master: server01(192.168.200.111:3306)
Sat Dec 29 16:09:51 2018 - [info] Checking slave configurations..
Sat Dec 29 16:09:51 2018 - [info] Checking replication filtering settings..
Sat Dec 29 16:09:51 2018 - [info] binlog_do_db= , binlog_ignore_db=
Sat Dec 29 16:09:51 2018 - [info] Replication filtering check ok.
Sat Dec 29 16:09:51 2018 - [info] Starting SSH connection tests..
Sat Dec 29 16:09:58 2018 - [info] All SSH connection tests passed successfully.
Sat Dec 29 16:09:58 2018 - [info] Checking MHA Node version..
Sat Dec 29 16:10:01 2018 - [info] Version check ok.
Sat Dec 29 16:10:01 2018 - [info] Checking SSH publickey authentication settings on the current master..
Sat Dec 29 16:10:02 2018 - [info] HealthCheck: SSH to server01 is reachable.
Sat Dec 29 16:10:03 2018 - [info] Master MHA Node version is 0.56.
Sat Dec 29 16:10:03 2018 - [info] Checking recovery script configurations on the current master..
Sat Dec 29 16:10:03 2018 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/tmp/save_binary_logs_test -- manager_version=0.56 --start_file=master-bin.000001
Sat Dec 29 16:10:03 2018 - [info] Connecting to root@server01(server01).. Creating /tmp if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to master-bin.000001
Sat Dec 29 16:10:03 2018 - [info] Master setting check done.
Sat Dec 29 16:10:03 2018 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Sat Dec 29 16:10:03 2018 - [info] Connecting to root@192.168.200.112(server02:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to mariadb-relay-bin.000002 Temporary relay log file is /var/lib/mysql/mariadb-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done.
Sat Dec 29 16:10:04 2018 - [info] Connecting to root@192.168.200.113(server03:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to slave-relay-bin.000002 Temporary relay log file is /var/lib/mysql/slave-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done.
Sat Dec 29 16:10:05 2018 - [info] Connecting to root@192.168.200.114(server04:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to slave-relay-bin.000002 Temporary relay log file is /var/lib/mysql/slave-relay-bin.000002 Testing mysql connection and privileges.. done. Testing mysqlbinlog output.. done. Cleaning up test file(s).. done.
Sat Dec 29 16:10:06 2018 - [info] Slaves settings check done.
Sat Dec 29 16:10:06 2018 - [info]
server01 (current master)
+--server02
+--server03
+--server04
Sat Dec 29 16:10:06 2018 - [info] Checking master_ip_failover_script status:
Sat Dec 29 16:10:06 2018 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=server01 --orig_master_ip=192.168.200.111 --orig_m aster_port=3306
IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start=== Checking the Status of the script.. OK
bash: /etc/init.d/keepalived: 没有那个文件或目录
Sat Dec 29 16:10:07 2018 - [info] OK.
Sat Dec 29 16:10:07 2018 - [warning] shutdown_script is not defined.
Sat Dec 29 16:10:07 2018 - [info] Set master ping interval 1 seconds.
Sat Dec 29 16:10:07 2018 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Sat Dec 29 16:10:07 2018 - [info] Starting ping health check on server01(192.168.200.111:3306)..
Thu Aug 31 21:55:23 2017 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
注意:其中"Ping(SELECT) succeeded, waiting until MySQL doesn't respond.."说明整个系统已经 开始监控了。
关闭 MHA manager 监控,忽略操作
masterha_stop --conf=/etc/masterha/app1.cnf
发现已经将 VIP:192.168.200.100 绑定在网卡 ens32。
[root@server01 ~]# ip a | grep ens32
2: ens32: <broadcast,multicast,up,lower_up> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 inet 192.168.200.111/24 brd 192.168.200.255 scope global ens32 inet 192.168.200.100/24 brd 192.168.200.255 scope global secondary ens32:1
Primary Master(192.168.200.111) 模拟主库故障
[root@server01 ~]# systemctl stop mariadb
[root@server01 ~]# netstat -lnpt | grep :3306
[root@server01 ~]# ip a | grep ens32
2: ens32: <broadcast,multicast,up,lower_up> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 inet 192.168.200.111/24 brd 192.168.200.255 scope global ens32
基于 MHA 的MySQL高可用-CentOS7(理论)MHA 简介MHA(Master High Availability)目前在 MySQL 高可用方面是一个相对成熟的解决方案, 它由日本 DeNA 公司的 youshimaton 员工(现就职于 Facebook 公司)开发,是一套优秀的作 为 MySQL 高可用性环境下故障切换和主从角色提升的高可用软件。在 MySQL 故障切换过程 ...