Introduction:
MHA (Master High Availability) is a relatively mature solution for MySQL high availability, developed by youshimaton from DeNA Japan (now at Facebook). It is an excellent high-availability software for failover and master-slave promotion in MySQL high-availability environments. During MySQL failover, MHA can automatically complete database failover operations within 0 to 30 seconds, and throughout the failover process, MHA ensures data consistency to the greatest extent possible, achieving true high availability.
The software consists of two components: MHA Manager (management node) and MHA Node (data node). MHA Manager can be deployed independently on a separate machine to manage multiple master-slave clusters, or it can be deployed on a slave node. MHA Node runs on each MySQL server. MHA Manager periodically probes the master node in the cluster. When the master fails, it can automatically promote the slave with the latest data to become the new master, and then redirect all other slaves to the new master. The entire failover process is completely transparent to applications.
During MHA automatic failover, MHA attempts to save binary logs from the crashed master server to minimize data loss, but this is not always feasible. For example, if the master server experiences a hardware failure or cannot be accessed via SSH, MHA cannot save the binary logs and only performs failover while losing the latest data. Using MySQL 5.5 semi-synchronous replication can significantly reduce the risk of data loss. MHA can be combined with semi-synchronous replication. If at least one slave has received the latest binary logs, MHA can apply the latest binary logs to all other slave servers, thereby ensuring data consistency across all nodes.
Currently, MHA primarily supports a single-master multiple-slave architecture. To set up MHA, a replication cluster must have at least three database servers: one master and two slaves — one acting as the master, one as the standby master, and the other as a read slave. Since at least three servers are required, considering machine costs, Taobao modified MHA on this basis, and Taobao TMHA now supports one master and one slave.
In our own usage, we can also use one master and one slave, but if the master host crashes, failover cannot occur and binlogs cannot be completed. However, if the master’s mysqld process crashes, failover can still succeed and binlogs can still be completed.
Official introduction: https://code.google.com/p/mysql-master-ha/
Figure 01 shows how MHA Manager manages multiple groups of master-slave replication. The MHA working principle can be summarized as follows:
(1) Save binary log events (binlog events) from the crashed master;
(2) Identify the slave containing the latest updates;
(3) Apply differential relay logs to other slaves;
(4) Apply binary log events saved from the master;
(5) Promote one slave to become the new master;
(6) Redirect other slaves to replicate from the new master;
The MHA software consists of two parts: the Manager toolkit and the Node toolkit, described in detail below.
The Manager toolkit primarily includes the following tools:
Copy Code
masterha_check_ssh Check MHA SSH configuration status
masterha_check_repl Check MySQL replication status
masterha_manger Start MHA
masterha_check_status Check current MHA running status
masterha_master_monitor Detect whether the master has crashed
masterha_master_switch Control failover (automatic or manual)
masterha_conf_host Add or remove configured server information
Copy Code
The Node toolkit (these tools are usually triggered by MHA Manager scripts and require no manual intervention) primarily includes the following tools:
save_binary_logs Save and copy master binary logs
apply_diff_relay_logs Identify differential relay log events and apply the differing events to other slaves
filter_mysqlbinlog Remove unnecessary ROLLBACK events (MHA no longer uses this tool)
purge_relay_logs Purge relay logs (does not block the SQL thread)
Note:
To minimize data loss caused by master hardware failure, it is recommended to configure MySQL 5.5 semi-synchronous replication alongside MHA. For the principles of semi-synchronous replication, please refer to other resources. (Not mandatory)
1. Deploying MHA
Next, we deploy MHA. The specific environment setup is as follows (all operating systems are CentOS 6.2 64-bit, not mandatory; server03 and server04 are slaves of server02; the replication environment setup will be briefly demonstrated later, but detailed security replication will not be covered here — those who need it can refer to previous articles on MySQL Replication considerations):
Role ip address hostname server_id Type
Monitor host 192.168.0.20 server01 – Monitoring replication group
Master 192.168.0.50 server02 1 Write
Candidate master 192.168.0.60 server03 2 Read
Slave 192.168.0.70 server04 3 Read
The master provides write services externally, the candidate master (an actual slave, hostname server03) provides read services, and the slave also provides related read services. Once the master crashes, the candidate master will be promoted to the new master, and the slave will point to the new master.
(1) Install the Perl module (DBD::mysql) required by MHA Node on all nodes. The installation script is as follows:
Copy Code
[[email protected] ~]# cat install.sh
#!/bin/bash
wget http://xrl.us/cpanm –no-check-certificate
mv cpanm /usr/bin
chmod 755 /usr/bin/cpanm
cat > /root/list << EOF
install DBD::mysql
EOF
for package in `cat /root/list`
do
cpanm $package
done
[[email protected] ~]#
Copy Code
If you have the EPEL repository installed, you can also install using yum:
yum install perl-DBD-MySQL -y
(2) Install MHA Node on all nodes:
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53.tar.gz
tar xf mha4mysql-node-0.53.tar.gz
cd mha4mysql-node-0.53
perl Makefile.PL
make && make install
After installation, the following script files will be generated in the /usr/local/bin directory:
Copy Code
[[email protected] bin]# pwd
/usr/local/bin
[[email protected] bin]# ll
total 40
-r-xr-xr-x 1 root root 15498 Apr 20 10:05 apply_diff_relay_logs
-r-xr-xr-x 1 root root 4807 Apr 20 10:05 filter_mysqlbinlog
-r-xr-xr-x 1 root root 7401 Apr 20 10:05 purge_relay_logs
-r-xr-xr-x 1 root root 7263 Apr 20 10:05 save_binary_logs
[[email protected] bin]#
Copy Code
The functions of the above scripts have already been introduced and will not be repeated here.
2. Installing MHA Manager
MHA Manager primarily includes several administrator command-line tools, such as master_manger, master_master_switch, etc. MHA Manager also depends on Perl modules, as follows:
(1) Before installing the MHA Node package, dependencies must be installed. I use yum here; those without the EPEL repository can use the script mentioned above (EPEL installation is also simple). Note: MHA Node must also be installed on the MHA Manager host.
rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum install perl-DBD-MySQL -y
Install the MHA Node package using the same method as above:
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-node-0.53.tar.gz
tar xf mha4mysql-node-0.53.tar.gz
cd mha4mysql-node-0.53
perl Makefile.PL
make && make install
(2) Install MHA Manager. First, install the Perl modules required by MHA Manager (I use yum for installation):
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y
Install the MHA Manager package:
wget http://mysql-master-ha.googlecode.com/files/mha4mysql-manager-0.53.tar.gz
tar xf mha4mysql-manager-0.53.tar.gz
cd mha4mysql-manager-0.53
perl Makefile.PL
make && make install
After installation, the following script files will be generated in the /usr/local/bin directory. The functions of these scripts have been explained earlier and will not be repeated here.
Copy Code
[[email protected] bin]# pwd
/usr/local/bin
[[email protected] bin]# ll
total 76
-r-xr-xr-x 1 root root 15498 Apr 20 10:58 apply_diff_relay_logs
-r-xr-xr-x 1 root root 4807 Apr 20 10:58 filter_mysqlbinlog
-r-xr-xr-x 1 root root 1995 Apr 20 11:33 masterha_check_repl
-r-xr-xr-x 1 root root 1779 Apr 20 11:33 masterha_check_ssh
-r-xr-xr-x 1 root root 1865 Apr 20 11:33 masterha_check_status
-r-xr-xr-x 1 root root 3201 Apr 20 11:33 masterha_conf_host
-r-xr-xr-x 1 root root 2517 Apr 20 11:33 masterha_manager
-r-xr-xr-x 1 root root 2165 Apr 20 11:33 masterha_master_monitor
-r-xr-xr-x 1 root root 2373 Apr 20 11:33 masterha_master_switch
-r-xr-xr-x 1 root root 3749 Apr 20 11:33 masterha_secondary_check
-r-xr-xr-x 1 root root 1739 Apr 20 11:33 masterha_stop
-r-xr-xr-x 1 root root 7401 Apr 20 10:58 purge_relay_logs
-r-xr-xr-x 1 root root 7263 Apr 20 10:58 save_binary_logs
[[email protected] bin]#
Copy Code
Copy the relevant scripts to the /usr/local/bin directory (they are available after extracting the package, but not mandatory, as these scripts are incomplete and need to be modified by yourself — this is left by the developer for us to customize. If any parameter corresponding to the scripts below is enabled without modifying the corresponding script here, an error will be thrown. I’ve been burned badly by this.)
Copy Code
[[email protected] scripts]# pwd
/root/mha4mysql-manager-0.53/samples/scripts
[[email protected] scripts]# ll
total 32
-rwxr-xr-x 1 root root 3443 Jan 8 2012 master_ip_failover # Script for VIP management during automatic failover, not mandatory. If using keepalived, you can write your own script for VIP management, such as monitoring MySQL — if MySQL is abnormal, stop keepalived so the VIP will automatically drift
-rwxr-xr-x 1 root root 9186 Jan 8 2012 master_ip_online_change # VIP management during online switchover, not mandatory. Similarly, you can write a simple shell script for this
-rwxr-xr-x 1 root root 11867 Jan 8 2012 power_manager # Script to shut down the host after a failure, not mandatory
-rwxr-xr-x 1 root root 1360 Jan 8 2012 send_report # Script to send alerts after failover, not mandatory. You can write a simple shell script for this.
[[email protected] scripts]# cp * /usr/local/bin/
[[email protected] scripts]#
Copy Code
3. Configure SSH passwordless login (using key-based login, commonly used in production). My test environment already uses key-based login, with no password required between servers. I won’t repeat the key-based login configuration here. But one thing to note: password login must not be disabled, otherwise errors will occur.
4. Setting up the master-slave replication environment
Note: binlog-do-db and replicate-ignore-db settings must be identical. MHA checks the filtering rules at startup. If the filtering rules differ, MHA will not start monitoring and failover.
(1) Perform a backup on server02 (192.168.0.50)
[[email protected] ~]# mysqldump –master-data=2 –single-transaction -R –triggers -A > all.sql
Where –master-data=2 means recording the master’s Binlog name and Position at the time of backup, –single-transaction means obtaining a consistent snapshot, -R means backing up stored procedures and functions, –triggers means backing up triggers, and -A means backing up all databases. For more information, run mysqldump –help.
(2) Create a replication user on server02:
Copy Code
mysql> grant replication slave on *.* to ‘repl’@’192.168.0.%’ identified by ‘123456’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
Copy Code
(3) Check the binlog name and position at the time of the master backup, MASTER_LOG_FILE and MASTER_LOG_POS:
[[email protected] ~]# head -n 30 all.sql | grep ‘CHANGE MASTER TO’
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000010′, MASTER_LOG_POS=112;
[[email protected] ~]#
(4) Copy the backup to server03 and server04, i.e., 192.168.0.60 and 192.168.0.70
scp all.sql server03:/data/
scp all.sql server04:/data/
(5) Import the backup to server03 and execute replication-related commands
mysql < /data/all.sql
Copy Code
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.50′,MASTER_USER=’repl’, MASTER_PASSWORD=’123456′,MASTER_LOG_FILE=’mysql-bin.000010′,MASTER_LOG_POS=112;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql>
Copy Code
Check replication status (you can see replication is successful):
[[email protected] ~]# mysql -e ‘show slave status\G’ | egrep ‘Slave_IO|Slave_SQL’
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[[email protected] ~]#
(6) Set up the replication environment on server04 (192.168.0.70), the same operation as above.
mysql < /data/all.sql
Copy Code
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.50′,MASTER_USER=’repl’, MASTER_PASSWORD=’123456′,MASTER_LOG_FILE=’mysql-bin.000010′,MASTER_LOG_POS=112;
Query OK, 0 rows affected (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
Copy Code
Check replication status:
[[email protected] ~]# mysql -e ‘show slave status\G’ | egrep ‘Slave_IO|Slave_SQL’
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[[email protected] ~]#
(7) Set read_only on both slave servers (slaves provide read services externally; the reason it’s not written into the configuration file is that a slave may be promoted to master at any time)
[[email protected] ~]# mysql -e ‘set global read_only=1’
[[email protected] ~]#
[[email protected] ~]# mysql -e ‘set global read_only=1’
[[email protected] ~]#
(8) Create a monitoring user (execute on the master, i.e., 192.168.0.50):
Copy Code
mysql> grant all privileges on *.* to ‘root’@’192.168.0.%’ identified by ‘123456’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>
Copy Code
At this point, the entire cluster environment has been set up. All that remains is to configure the MHA software.
5. Configuring MHA
(1) Create the MHA working directory and create the relevant configuration file (there is a sample configuration file in the extracted package directory).
[[email protected] ~]# mkdir -p /etc/masterha
[[email protected] ~]# cp mha4mysql-manager-0.53/samples/conf/app1.cnf /etc/masterha/
[[email protected] ~]#
Modify the app1.cnf configuration file. The modified file content is as follows (note: comments in the configuration file need to be removed; I keep them here for explanation):
Copy Code
[[email protected] ~]# cat /etc/masterha/app1.cnf
[server default]
manager_workdir=/var/log/masterha/app1.log // Set the manager working directory
manager_log=/var/log/masterha/app1/manager.log // Set the manager log
master_binlog_dir=/data/mysql // Set the location where the master saves binlogs so MHA can find the master’s logs. Here it is the mysql data directory
master_ip_failover_script= /usr/local/bin/master_ip_failover // Set the failover script for automatic failover
master_ip_online_change_script= /usr/local/bin/master_ip_online_change // Set the failover script for manual switchover
password=123456 // Set the password for the root user in mysql, which is the password of the monitoring user created earlier
user=root // Set the monitoring user root
ping_interval=1 // Set the interval for sending ping packets to monitor the master, default is 3 seconds. Automatic failover occurs after three failed attempts
remote_workdir=/tmp // Set the location for saving binlogs on the remote mysql during failover
repl_password=123456 // Set the replication user password
repl_user=repl // Set the replication username in the replication environment
report_script=/usr/local/send_report // Set the alert script to be sent after failover
secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02
shutdown_script=”” // Set the script to shut down the failed host after a failure (the main purpose of this script is to shut down the host to prevent split-brain; not used here)
ssh_user=root // Set the SSH login username
[server1]
hostname=192.168.0.50
port=3306
[server2]
hostname=192.168.0.60
port=3306
candidate_master=1 // Set as candidate master. If this parameter is set, after a master-slave switchover, this slave will be promoted to master, even if it is not the slave with the most recent events in the cluster
check_repl_delay=0 // By default, if a slave is 100M behind the master in relay logs, MHA will not select that slave as a new master because recovery for that slave takes a long time. By setting check_repl_delay=0, MHA will ignore replication delay when selecting a new master during failover. This parameter is very useful for hosts with candidate_master=1, as this candidate master will definitely be the new master during failover
[server3]
hostname=192.168.0.70
port=3306
[[email protected] ~]#
Copy Code
(2) Set the relay log purge method (on each slave node):
[[email protected] ~]# mysql -e ‘set global relay_log_purge=0’
[[email protected] ~]# mysql -e ‘set global relay_log_purge=0’
Note:
During MHA failover, the slave recovery process depends on relay log information, so automatic relay log purging must be set to OFF and relay logs should be purged manually. By default, relay logs on slave servers are automatically deleted after the SQL thread finishes executing. However, in an MHA environment, these relay logs may be needed when recovering other slave servers, so automatic relay log deletion must be disabled. Periodic relay log purging needs to account for replication delay. On ext3 filesystems, deleting large files takes some time and can cause significant replication delay. To avoid replication delay, hard links should be temporarily created for relay logs, because in Linux systems, deleting large files via hard links is much faster. (In MySQL databases, when dropping large tables, the hard link approach is also commonly used.)
The MHA Node includes the purge_relay_logs command tool, which creates hard links for relay logs, executes SET GLOBAL relay_log_purge=1, waits a few seconds for the SQL thread to switch to the new relay log, then executes SET GLOBAL relay_log_purge=0.
The purge_relay_logs script parameters are as follows:
Copy Code
–user mysql Username
–password mysql Password
–port Port number
–workdir Specifies the location for creating hard links for relay logs, default is /var/tmp. Since creating hard links across different partitions will fail, you need to specify the hard link location. After successfully executing the script, the hard-linked relay log files are deleted
–disable_relay_log_purge By default, if relay_log_purge=1, the script does nothing and exits automatically. By setting this parameter, when relay_log_purge=1, it will set relay_log_purge to 0. After purging relay logs, the parameter is finally set to OFF.
Copy Code
(3) Set up a periodic relay log purging script (on both slave servers)
Copy Code
[[email protected] ~]# cat purge_relay_log.sh
#!/bin/bash
user=root
passwd=123456
port=3306
log_dir=’/data/masterha/log’
work_dir=’/data’
purge=’/usr/local/bin/purge_relay_logs’
if [ ! -d $log_dir ]
then
mkdir $log_dir -p
fi
$purge –user=$user –password=$passwd –disable_relay_log_purge –port=$port –workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1
[[email protected] ~]#
Copy Code
Add to crontab for periodic execution
[[email protected] ~]# crontab -l
0 4 * * * /bin/bash /root/purge_relay_log.sh
[[email protected] ~]#
The purge_relay_logs script deletes relay logs without blocking the SQL thread. Let’s manually execute it to see what happens.
Copy Code
[[email protected] ~]# purge_relay_logs –user=root –password=123456 –port=3306 -disable_relay_log_purge –workdir=/data/
2014-04-20 15:47:24: purge_relay_logs script started.
Found relay_log.info: /data/mysql/relay-log.info
Removing hard linked relay log files server03-relay-bin* under /data/.. done.
Current relay log file: /data/mysql/server03-relay-bin.000002
Archiving unused relay log files (up to /data/mysql/server03-relay-bin.000001) …
Creating hard link for /data/mysql/server03-relay-bin.000001 under /data//server03-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 it keeps up); SET GLOBAL relay_log_purge=0; .. ok.
Removing hard linked relay log files server03-relay-bin* under /data/.. done.
2014-04-20 15:47:27: All relay log purging operations succeeded.
[[email protected] ~]#
Copy Code
6. Check SSH Configuration
Check the SSH connection status from MHA Manager to all MHA Nodes:
Copy Code
[[email protected] ~]# masterha_check_ssh –conf=/etc/masterha/app1.cnf
Sun Apr 20 17:17:39 2014 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Apr 20 17:17:39 2014 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Sun Apr 20 17:17:39 2014 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Sun Apr 20 17:17:39 2014 – [info] Starting SSH connection tests..
Sun Apr 20 17:17:40 2014 – [debug]
Sun Apr 20 17:17:39 2014 – [debug] Connecting via SSH from [email protected](192.168.0.50:22) to [email protected](192.168.0.60:22)..
Sun Apr 20 17:17:39 2014 – [debug] ok.
Sun Apr 20 17:17:39 2014 – [debug] Connecting via SSH from [email protected](192.168.0.50:22) to [email protected](192.168.0.70:22)..
Sun Apr 20 17:17:39 2014 – [debug] ok.
Sun Apr 20 17:17:40 2014 – [debug]
Sun Apr 20 17:17:40 2014 – [debug] Connecting via SSH from [email protected](192.168.0.60:22) to [email protected](192.168.0.50:22)..
Sun Apr 20 17:17:40 2014 – [debug] ok.
Sun Apr 20 17:17:40 2014 – [debug] Connecting via SSH from [email protected](192.168.0.60:22) to [email protected](192.168.0.70:22)..
Sun Apr 20 17:17:40 2014 – [debug] ok.
Sun Apr 20 17:17:41 2014 – [debug]
Sun Apr 20 17:17:40 2014 – [debug] Connecting via SSH from [email protected](192.168.0.70:22) to [email protected](192.168.0.50:22)..
Sun Apr 20 17:17:40 2014 – [debug] ok.
Sun Apr 20 17:17:40 2014 – [debug] Connecting via SSH from [email protected](192.168.0.70:22) to [email protected](192.168.0.60:22)..
Sun Apr 20 17:17:41 2014 – [debug] ok.
Sun Apr 20 17:17:41 2014 – [info] All SSH connection tests passed successfully.
Copy Code
You can see that SSH verification for all nodes is OK.
7. Check the overall replication environment status.
Use the masterha_check_repl script to view the overall cluster status:
Copy Code
[[email protected] ~]# masterha_check_repl –conf=/etc/masterha/app1.cnf
Sun Apr 20 18:36:55 2014 – [info] Checking replication health on 192.168.0.60..
Sun Apr 20 18:36:55 2014 – [info] ok.
Sun Apr 20 18:36:55 2014 – [info] Checking replication health on 192.168.0.70..
Sun Apr 20 18:36:55 2014 – [info] ok.
Sun Apr 20 18:36:55 2014 – [info] Checking master_ip_failover_script status:
Sun Apr 20 18:36:55 2014 – [info] /usr/local/bin/master_ip_failover –command=status –ssh_user=root –orig_master_host=192.168.0.50 –orig_master_ip=192.168.0.50 –orig_master_port=3306
Bareword “FIXME_xxx” not allowed while “strict subs” in use at /usr/local/bin/master_ip_failover line 88.
Execution of /usr/local/bin/master_ip_failover aborted due to compilation errors.
Sun Apr 20 18:36:55 2014 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln214] Failed to get master_ip_failover_script status with return code 255:0.
Sun Apr 20 18:36:55 2014 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln383] Error happend on checking configurations. at /usr/local/bin/masterha_check_repl line 48
Sun Apr 20 18:36:55 2014 – [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln478] Error happened on monitoring servers.
Sun Apr 20 18:36:55 2014 – [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
Copy Code
The final conclusion says my replication is not OK. But the information above clearly states it’s normal, and I also checked inside the database. I kept getting stuck here. Kept agonizing over it, and later stumbled upon Huoding Notes blog, which finally revealed the reason: there are two failover methods — one using virtual IP addresses, and the other using a global configuration file. MHA does not limit which method to use but lets the user choose. The virtual IP method involves other software like keepalived, and also requires modifying the master_ip_failover script. (Only after modifying the script did this error disappear. Not knowing Perl, I struggled half to death. I bought a watch last year.)
If you encounter the following error:
Can’t exec “mysqlbinlog”: No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 99.
mysqlbinlog version not found!
Testing mysql connection and privileges..sh: mysql: command not found
The solution is as follows — add symlinks (on all nodes):
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql
So for now, temporarily comment out the master_ip_failover_script= /usr/local/bin/master_ip_failover option. After introducing keepalived and modifying the script, enable this option again.
[[email protected] ~]# grep master_ip_failover /etc/masterha/app1.cnf
#master_ip_failover_script= /usr/local/bin/master_ip_failover
[[email protected] ~]#
Check the status again:
Copy Code
Sun Apr 20 18:46:08 2014 – [info] Checking replication health on 192.168.0.60..
Sun Apr 20 18:46:08 2014 – [info] ok.
Sun Apr 20 18:46:08 2014 – [info] Checking replication health on 192.168.0.70..
Sun Apr 20 18:46:08 2014 – [info] ok.
Sun Apr 20 18:46:08 2014 – [warning] master_ip_failover_script is not defined.
Sun Apr 20 18:46:08 2014 – [warning] shutdown_script is not defined.
Sun Apr 20 18:46:08 2014 – [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
Copy Code
There are no obvious errors now, only two warnings, and replication also shows as normal.
8. Check MHA Manager status:
Use the master_check_status script to view Manager status:
[[email protected] ~]# masterha_check_status –conf=/etc/masterha/app1.cnf
app1 is stopped(2:NOT_RUNNING).
[[email protected] ~]#
Note: If normal, it will display “PING_OK”; otherwise it will display “NOT_RUNNING”, meaning MHA monitoring is not started.
9. Start MHA Manager monitoring
[[email protected] ~]# nohup masterha_manager –conf=/etc/masterha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 30867
[[email protected] ~]#
Startup parameter explanation:
–remove_dead_master_conf This parameter means that after a master-slave failover occurs, the old master’s IP will be removed from the configuration file.
–manger_log Log storage location
–ignore_last_failover By default, if MHA detects consecutive crashes and the interval between two crashes is less than 8 hours, it will not perform failover. This restriction is to prevent the ping-pong effect. This parameter means ignoring the file generated by the last MHA-triggered failover. By default, after MHA failover, it creates an app1.failover.complete file in the log directory (i.e., /data as set above). The next time a failover is triggered, if this file is found in that directory, failover will not be allowed unless the file is manually deleted after the first failover. For convenience, –ignore_last_failover is set here.
Check if MHA Manager monitoring is normal:
[[email protected] ~]# masterha_check_status –conf=/etc/masterha/app1.cnf
app1 (pid:20386) is running(0:PING_OK), master:192.168.0.50
[[email protected] ~]#
You can see that monitoring is running, and the master host is 192.168.0.50
10. View startup logs
Copy Code
[[email protected] ~]# tail -n20 /var/log/masterha/app1/manager.log
Sun Apr 20 19:12:01 2014 – [info] Connecting to [email protected](192.168.0.70:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info … ok.
Relay log found at /data/mysql, up to server04-relay-bin.000002
Temporary relay log file is /data/mysql/server04-relay-bin.000002
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Sun Apr 20 19:12:01 2014 – [info] Slaves settings check done.
Sun Apr 20 19:12:01 2014 – [info]
192.168.0.50 (current master)
+–192.168.0.60
+–192.168.0.70
Sun Apr 20 19:12:01 2014 – [warning] master_ip_failover_script is not defined.
Sun Apr 20 19:12:01 2014 – [warning] shutdown_script is not defined.
Sun Apr 20 19:12:01 2014 – [info] Set master ping interval 1 seconds.
Sun Apr 20 19:12:01 2014 – [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.0.50 –master_port=3306
Sun Apr 20 19:12:01 2014 – [info] Starting ping health check on 192.168.0.50(192.168.0.50:3306)..
Sun Apr 20 19:12:01 2014 – [info] Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..
[[email protected] ~]#
Copy Code
Where “Ping(SELECT) succeeded, waiting until MySQL doesn’t respond..” indicates that the entire system has started monitoring.
11. Stop MHA Manager monitoring
Stopping is very simple — use the masterha_stop command.
[[email protected] ~]# masterha_stop –conf=/etc/masterha/app1.cnf
Stopped app1 successfully.
[1]+ Exit 1 nohup masterha_manager –conf=/etc/masterha/app1.cnf –remove_dead_master_conf –ignore_last_failover –manager_log=/data/mamanager.log
[[email protected] ~]#
12. Configure VIP
VIP configuration can be done in two ways: one uses keepalived to manage virtual IP floating; the other uses a script to start the virtual IP (i.e., without software like keepalived or heartbeat).
1. Using keepalived to manage virtual IP. The keepalived configuration method is as follows:
(1) Download and install the software (on both masters — accurately, one is the master and the other is the candidate master, which is a slave before failover):
[[email protected] ~]# wget http://www.keepalived.org/software/keepalived-1.2.12.tar.gz
Copy Code
tar xf keepalived-1.2.12.tar.gz
cd keepalived-1.2.12
./configure –prefix=/usr/local/keepalived
make && make install
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
Copy Code
(2) Configure the keepalived configuration file on the master (192.168.0.50)
Copy Code
[[email protected] ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
[email protected]
}
notification_email_from [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
}
vrrp_instance VI_1 {
state BACKUP
interface eth1
virtual_router_id 51
priority 150
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.88
}
}
[[email protected] ~]#
Copy Code
Where router_id MySQL HA indicates the keepalived group name, binding the virtual IP 192.168.0.88 to the host’s eth1 network interface, with the state set to backup mode, keepalived mode set to non-preemptive (nopreempt), and priority 150 indicating the configured priority is 150. The configuration below is slightly different but means the same thing.
Configure on the candidate master (192.168.0.60)
Copy Code
[[email protected] ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
[email protected]
}
notification_email_from [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-HA
}
vrrp_instance VI_1 {
state BACKUP
interface eth1
virtual_router_id 51
priority 120
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.88
}
}
[[email protected] ~]#
Copy Code
(3) Start the keepalived service on the master and view the logs
Copy Code
[[email protected] ~]# /etc/init.d/keepalived start
Starting keepalived: [ OK ]
[[email protected] ~]# tail -f /var/log/messages
Apr 20 20:22:16 192 Keepalived_healthcheckers[15334]: Opening file ‘/etc/keepalived/keepalived.conf’.
Apr 20 20:22:16 192 Keepalived_healthcheckers[15334]: Configuration is using : 7231 Bytes
Apr 20 20:22:16 192 kernel: IPVS: Connection hash table configured (size=4096, memory=64Kbytes)
Apr 20 20:22:16 192 kernel: IPVS: ipvs loaded.
Apr 20 20:22:16 192 Keepalived_healthcheckers[15334]: Using LinkWatch kernel netlink reflector…
Apr 20 20:22:19 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) Transition to MASTER STATE
Apr 20 20:22:20 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) Entering MASTER STATE
Apr 20 20:22:20 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) setting protocol VIPs.
Apr 20 20:22:20 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88
Apr 20 20:22:20 192 Keepalived_healthcheckers[15334]: Netlink reflector reports IP 192.168.0.88 added
Apr 20 20:22:25 192 Keepalived_vrrp[15335]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth1 for 192.168.0.88
Copy Code
You can see that the virtual IP 192.168.0.88 has been bound to the eth1 network interface.
(4) Check the binding status
[[email protected] ~]# ip addr | grep eth1
3: eth1:
inet 192.168.0.50/24 brd 192.168.0.255 scope global eth1
inet 192.168.0.88/32 scope global eth1
[[email protected] ~]#
On the other server, the candidate master, start the keepalived service and observe:
Copy Code
[[email protected] ~]# /etc/init.d/keepalived start ; tail -f /var/log/messages
Starting keepalived: [ OK ]
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: Registering gratuitous ARP shared channel
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: Opening file ‘/etc/keepalived/keepalived.conf’.
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: Configuration is using : 62976 Bytes
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: Using LinkWatch kernel netlink reflector…
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: VRRP_Instance(VI_1) Entering BACKUP STATE
Apr 20 20:26:18 192 Keepalived_vrrp[9472]: VRRP sockpool: [ifindex(3), proto(112), unicast(0), fd(10,11)]
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Netlink reflector reports IP 192.168.80.138 added
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Netlink reflector reports IP 192.168.0.60 added
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Netlink reflector reports IP fe80::20c:29ff:fe9d:6a9e added
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Netlink reflector reports IP fe80::20c:29ff:fe9d:6aa8 added
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Registering Kernel netlink reflector
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Registering Kernel netlink command channel
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Opening file ‘/etc/keepalived/keepalived.conf’.
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Configuration is using : 7231 Bytes
Apr 20 20:26:18 192 kernel: IPVS: Registered protocols (TCP, UDP, AH, ESP)
Apr 20 20:26:18 192 kernel: IPVS: Connection hash table configured (size=4096, memory=64Kbytes)
Apr 20 20:26:18 192 kernel: IPVS: ipvs loaded.
Apr 20 20:26:18 192 Keepalived_healthcheckers[9471]: Using LinkWatch kernel netlink reflector…
Copy Code
From the above information, you can see that keepalived has been configured successfully.
Note:
On both servers above, keepalived is set to BACKUP mode. In keepalived, there are two modes: master->backup mode and backup->backup mode. These two modes are very different. In master->backup mode, once the master crashes, the virtual IP will automatically drift to the slave. When the master is repaired and keepalived starts, it will seize the virtual IP back, even if non-preemptive mode (nopreempt) is set — the IP seizure action will still occur. In backup->backup mode, when the master crashes, the virtual IP will automatically drift to the slave. When the original master is restored and the keepalived service starts, it will not seize the virtual IP from the new master, even if its priority is higher than the slave’s priority. To reduce the number of IP drifts, the repaired master is usually treated as a new standby slave.
(5) Integrating MHA with keepalived (stop keepalived via MHA when the MySQL service process crashes):
To integrate the keepalived service into MHA, we only need to modify the failover-triggered script file master_ip_failover, adding keepalived handling when the master crashes.
Edit the script /usr/local/bin/master_ip_failover. The modified version is as follows. I’m not familiar with Perl, so I’ll post the complete script here (operate on the master, 192.168.0.50).
The modified script content on MHA Manager is as follows (with relatively few reference materials available):
Copy Code
#!/usr/bin/env perl
use strict;
use warnings FATAL => ‘all’;
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = ‘192.168.0.88’;
my $ssh_start_vip = “/etc/init.d/keepalived start”;
my $ssh_stop_vip = “/etc/init.d/keepalived stop”;
GetOptions(
‘command=s’ => \$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,
);
exit &main();
sub main {
print “\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n”;
if ( $command eq “stop” || $command eq “stopssh” ) {
my $exit_code = 1;
eval {
print “Disabling the VIP on old master: $orig_master_host \n”;
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn “Got Error: $@\n”;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq “start” ) {
my $exit_code = 10;
eval {
print “Enabling the VIP – $vip on the new master – $new_master_host \n”;
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq “status” ) {
print “Checking the Status of the script.. OK \n”;
#`ssh $ssh_user\@cluster1 \” $ssh_start_vip \”`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \” $ssh_start_vip \”`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \” $ssh_stop_vip \”`;
}
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”;
}
Copy Code
Now that this script has been modified, let’s enable the parameter mentioned above and check the cluster status again to see if any errors occur.
[[email protected] ~]# grep ‘master_ip_failover_script’ /etc/masterha/app1.cnf
master_ip_failover_script= /usr/local/bin/master_ip_failover
[[email protected] ~]#
Copy Code
[[email protected] ~]# masterha_check_repl –conf=/etc/masterha/app1.cnf
Sun Apr 20 23:10:01 2014 – [info] Slaves settings check done.
Sun Apr 20 23:10:01 2014 – [info]
192.168.0.50 (current master)
+–192.168.0.60
+–192.168.0.70
Sun Apr 20 23:10:01 2014 – [info] Checking replication health on 192.168.0.60..
Sun Apr 20 23:10:01 2014 – [info] ok.
Sun Apr 20 23:10:01 2014 – [info] Checking replication health on 192.168.0.70..
Sun Apr 20 23:10:01 2014 – [info] ok.
Sun Apr 20 23:10:01 2014 – [info] Checking master_ip_failover_script status:
Sun Apr 20 23:10:01 2014 – [info] /usr/local/bin/master_ip_failover –command=status –ssh_user=root –orig_master_host=192.168.0.50 –orig_master_ip=192.168.0.50 –orig_master_port=3306
Sun Apr 20 23:10:01 2014 – [info] OK.
Sun Apr 20 23:10:01 2014 – [warning] shutdown_script is not defined.
Sun Apr 20 23:10:01 2014 – [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
Copy Code
You can see there are no more errors. Haha.
The content added or modified in /usr/local/bin/master_ip_failover means that when the master database fails, it triggers MHA failover. MHA Manager will stop the keepalived service on the master, triggering the virtual IP to drift to the candidate slave, thereby completing the failover. Of course, you can also introduce a script within keepalived that monitors whether MySQL is running normally. If it is not normal, the script kills the keepalived process.
2. Managing VIP via script. Here, modify /usr/local/bin/master_ip_failover. You can also use other languages, such as PHP. The failover written in PHP script will not be covered here. After modification, the content is as follows. Also, if using scripts to manage VIP, you need to manually bind a VIP on the master server. (I’ve found that after these modifications, I’ve developed a feel for Perl. Maybe I’m suited to learning Perl? ^_^)
[[email protected] ~]# /sbin/ifconfig eth1:1 192.168.0.88/24
The testing for maintaining VIP via script will not be covered here. Fellow students can test it themselves. The script is as follows (tested and passed):
Copy Code
#!/usr/bin/env perl
use strict;
use warnings FATAL => ‘all’;
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = ‘192.168.0.88/24’;
my $key = ‘1’;
my $ssh_start_vip = “/sbin/ifconfig eth1:$key $vip”;
my $ssh_stop_vip = “/sbin/ifconfig eth1:$key down”;
GetOptions(
‘command=s’ => \$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,
);
exit &main();
sub main {
print “\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n”;
if ( $command eq “stop” || $command eq “stopssh” ) {
my $exit_code = 1;
eval {
print “Disabling the VIP on old master: $orig_master_host \n”;
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn “Got Error: $@\n”;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq “start” ) {
my $exit_code = 10;
eval {
print “Enabling the VIP – $vip on the new master – $new_master_host \n”;
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq “status” ) {
print “Checking the Status of the script.. OK \n”;
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \” $ssh_start_vip \”`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\@$orig_master_host \” $ssh_stop_vip \”`;
}
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”;
}
Copy Code
To prevent split-brain, it is recommended in production environments to use the script method to manage virtual IPs rather than using keepalived. At this point, the basic MHA cluster has been configured. Next comes the actual testing phase. Through some tests, let’s see how MHA actually works. Below, we’ll introduce MHA operations through three methods: automatic failover, manual failover, and online switchover.
I. Automatic Failover (MHA Manager must be started first, otherwise automatic failover cannot occur. Of course, manual failover does not require starting MHA Manager monitoring. Fellow students, please refer to the previous section on starting MHA Manager.)
Let me repost the test environment, as the article is too long and I’m getting dizzy myself.
Role ip address hostname server_id Type
Monitor host 192.168.0.20 server01 – Monitoring replication group
Master 192.168.0.50 server02 1 Write
Candidate master 192.168.0.60 server03 2 Read
Slave 192.168.0.70 server04 3 Read
The steps for automatic failover simulation testing are as follows.
(1) Use sysbench to generate test data (quick installation using yum)
yum install sysbench -y
Generate sysbench data on the master (192.168.0.50), creating the sbtest table under the sbtest database with a total of 1 million records.
[[email protected] ~]# sysbench –test=oltp –oltp-table-size=1000000 –oltp-read-only=off –init-rng=on –num-threads=16 –max-requests=0 –oltp-dist-type=uniform –max-time=1800 –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-password=123456 –db-driver=mysql –mysql-table-engine=innodb –oltp-test-mode=complex prepare
(2) Stop the slave SQL thread to simulate master-slave replication delay. (192.168.0.60)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.08 sec)
mysql>
We did not stop the IO thread on the other slave, so it continues to receive logs.
(3) Simulate sysbench stress testing.
Perform stress testing on the master (192.168.0.50) for 3 minutes, generating a large number of binlogs.
Copy Code
[[email protected] ~]# sysbench –test=oltp –oltp-table-size=1000000 –oltp-read-only=off –init-rng=on –num-threads=16 –max-requests=0 –oltp-dist-type=uniform –max-time=180 –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-password=123456 –db-driver=mysql –mysql-table-engine=innodb –oltp-test-mode=complex run
sysbench 0.4.12: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 16
Initializing random number generator from timer.
Doing OLTP test.
Running mixed OLTP test
Using Uniform distribution
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting…
(last message repeated 15 times)
Done.
OLTP test statistics:
queries performed:
read: 15092
write: 5390
other: 2156
total: 22638
transactions: 1078 (5.92 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 20482 (112.56 per sec.)
other operations: 2156 (11.85 per sec.)
Test execution summary:
total time: 181.9728s
total number of events: 1078
total time taken by event execution: 2910.4518
per-request statistics:
min: 934.29ms
avg: 2699.86ms
max: 7679.95ms
approx. 95 percentile: 4441.47ms
Threads fairness:
events (avg/stddev): 67.3750/1.49
execution time (avg/stddev): 181.9032/0.11
Copy Code
(4) Start the IO thread on the slave (192.168.0.60) to catch up with the binlogs lagging behind the master.
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql>
(5) Kill the master MySQL process to simulate a master failure and trigger automatic failover.
[[email protected] ~]# pkill -9 mysqld
(6) View the MHA failover log to understand the entire failover process. View the log on 192.168.0.20:
Copy Code
[[email protected] ~]# cat /var/log/masterha/app1/manager.log
Mon Apr 21 20:15:45 2014 – [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Mon Apr 21 20:15:45 2014 – [info] Executing seconary network check script: /usr/local/bin/masterha_secondary_check -s server03 -s server02 –user=root –master_host=server02 –master_ip=192.168.0.50 –master_ Creating /tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/mysql, up to mysql-bin.000018
Mon Apr 21 20:15:48 2014 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Apr 21 20:15:48 2014 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Mon Apr 21 20:15:48 2014 – [info] Reading server configurations from /etc/masterha/app1.cnf..
ble from server03. OK.
Monitoring server server02 is reachable, Master is not reachable from server02. OK.
Mon Apr 21 20:15:46 2014 – [info] Master is not reachable from all other monitoring servers. Failover should start.
Mon Apr 21 20:15:46 2014 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Mon Apr 21 20:15:46 2014 – [warning] Connection failed 1 time(s)..
Mon Apr 21 20:15:47 2014 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Mon Apr 21 20:15:47 2014 – [warning] Connection failed 2 time(s)..
Mon Apr 21 20:15:48 2014 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at ‘reading initial communication packet’, system error: 111)
Mon Apr 21 20:15:48 2014 – [warning] Connection failed 3 time(s)..
Mon Apr 21 20:15:48 2014 – [warning] Master is not reachable from health checker!
Mon Apr 21 20:15:48 2014 – [warning] Master 192.168.0.50(192.168.0.50:3306) is not reachable!
Mon Apr 21 20:15:48 2014 – [warning] SSH is reachable.
Mon Apr 21 20:15:48 2014 – [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status..
Mon Apr 21 20:15:48 2014 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Apr 21 20:15:48 2014 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Mon Apr 21 20:15:48 2014 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Mon Apr 21 20:15:48 2014 – [info] Dead Servers:
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:48 2014 – [info] Alive Servers:
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.60(192.168.0.60:3306)
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.70(192.168.0.70:3306)
Mon Apr 21 20:15:48 2014 – [info] Alive Slaves:
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.60(192.168.0.60:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 20:15:48 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:48 2014 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.70(192.168.0.70:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 20:15:48 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:48 2014 – [info] Checking slave configurations..
Mon Apr 21 20:15:48 2014 – [info] Checking replication filtering settings..
Mon Apr 21 20:15:48 2014 – [info] Replication filtering check ok.
Mon Apr 21 20:15:48 2014 – [info] Master is down!
Mon Apr 21 20:15:48 2014 – [info] Terminating monitoring script.
Mon Apr 21 20:15:48 2014 – [info] Got exit code 20 (Master dead).
Mon Apr 21 20:15:48 2014 – [info] MHA::MasterFailover version 0.53.
Mon Apr 21 20:15:48 2014 – [info] Starting master failover.
Mon Apr 21 20:15:48 2014 – [info]
Mon Apr 21 20:15:48 2014 – [info] * Phase 1: Configuration Check Phase..
Mon Apr 21 20:15:48 2014 – [info]
Mon Apr 21 20:15:48 2014 – [info] Dead Servers:
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:48 2014 – [info] Checking master reachability via mysql(double check)..
Mon Apr 21 20:15:48 2014 – [info] ok.
Mon Apr 21 20:15:48 2014 – [info] Alive Servers:
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.60(192.168.0.60:3306)
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.70(192.168.0.70:3306)
Mon Apr 21 20:15:48 2014 – [info] Alive Slaves:
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.60(192.168.0.60:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 20:15:48 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:48 2014 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Apr 21 20:15:48 2014 – [info] 192.168.0.70(192.168.0.70:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 20:15:48 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:49 2014 – [info] ** Phase 1: Configuration Check Phase completed.
Mon Apr 21 20:15:49 2014 – [info]
Mon Apr 21 20:15:49 2014 – [info] * Phase 2: Dead Master Shutdown Phase..
Mon Apr 21 20:15:49 2014 – [info]
Mon Apr 21 20:15:49 2014 – [info] Forcing shutdown so that applications never connect to the current master..
Mon Apr 21 20:15:49 2014 – [info] Executing master IP deactivatation script:
Mon Apr 21 20:15:49 2014 – [info] /usr/local/bin/master_ip_failover –orig_master_host=192.168.0.50 –orig_master_ip=192.168.0.50 –orig_master_port=3306 –command=stopssh –ssh_user=root
IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===
Disabling the VIP on old master: 192.168.0.50
Mon Apr 21 20:15:49 2014 – [info] done.
Mon Apr 21 20:15:49 2014 – [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Mon Apr 21 20:15:49 2014 – [info] * Phase 2: Dead Master Shutdown Phase completed.
Mon Apr 21 20:15:49 2014 – [info]
Mon Apr 21 20:15:49 2014 – [info] * Phase 3: Master Recovery Phase..
Mon Apr 21 20:15:49 2014 – [info]
Mon Apr 21 20:15:49 2014 – [info] * Phase 3.1: Getting Latest Slaves Phase..
Mon Apr 21 20:15:49 2014 – [info]
Mon Apr 21 20:15:49 2014 – [info] The latest binary log file/position on all slaves is mysql-bin.000018:112
Mon Apr 21 20:15:49 2014 – [info] Latest slaves (Slaves that received relay log files to the latest):
Mon Apr 21 20:15:49 2014 – [info] 192.168.0.60(192.168.0.60:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 20:15:49 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:49 2014 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Apr 21 20:15:49 2014 – [info] 192.168.0.70(192.168.0.70:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 20:15:49 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:49 2014 – [info] The oldest binary log file/position on all slaves is mysql-bin.000018:112
Mon Apr 21 20:15:49 2014 – [info] Oldest slaves:
Mon Apr 21 20:15:49 2014 – [info] 192.168.0.60(192.168.0.60:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 20:15:49 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:49 2014 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Apr 21 20:15:49 2014 – [info] 192.168.0.70(192.168.0.70:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 20:15:49 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 20:15:49 2014 – [info] Starting Phase 3.3: New Master Diff Log Generation Phase..
Mon Apr 21 20:15:49 2014 – [info]
Mon Apr 21 20:15:49 2014 – [info] Server 192.168.0.60 received differential relay logs up to 000018, not 000018. So 192.168.0.60 is the latest slave.
Mon Apr 21 20:15:49 2014 – [info] This server 192.168.0.60 has candidate_master is set. Converting 192.168.0.60 to the latest slave.
Mon Apr 21 20:15:49 2014 – [info] This server 192.168.0.70 received differential relay logs up to 000018, not 000018. So 192.168.0.70 is the latest slave.
Mon Apr 21 20:15:49 2014 – [info] Generating diff files from the latest slave 192.168.0.60..
Mon Apr 21 20:15:49 2014 – [info] done.
Mon Apr 21 20:15:49 2014 – [info] Identifying the oldest slave(s) that received diff relay log events..
Mon Apr 21 20:15:49 2014 – [info] New master is 192.168.0.60(192.168.0.60:3306)
Mon Apr 21 20:15:49 2014 – [info] Starting master failover..
Mon Apr 21 20:15:49 2014 – [info]
From:
192.168.0.50(192.168.0.50:3306) (current master)
+–192.168.0.60(192.168.0.60:3306)
+–192.168.0.70(192.168.0.70:3306)
To:
192.168.0.60(192.168.0.60:3306) (new master)
+–192.168.0.70(192.168.0.70:3306)
Starting master switch from 192.168.0.50(192.168.0.50:3306) to 192.168.0.60(192.168.0.60:3306)? (yes/NO) yes
Mon Apr 21 20:15:50 2014 – [info] New master decided is 192.168.0.60(192.168.0.60:3306)
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 3.3: New Master Diff Log Generation Phase..
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] Server 192.168.0.60 received differential relay logs up to 000018, not 000018. So 192.168.0.60 is the latest slave.
Mon Apr 21 20:15:50 2014 – [info] This server 192.168.0.60 has candidate_master is set. Converting 192.168.0.60 to the latest slave.
Mon Apr 21 20:15:50 2014 – [info] This server 192.168.0.70 received differential relay logs up to 000018, not 000018. So 192.168.0.70 is the latest slave.
Mon Apr 21 20:15:50 2014 – [info] Generating diff files from the latest slave 192.168.0.60..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info] Identifying the oldest slave(s) that received diff relay log events..
Mon Apr 21 20:15:50 2014 – [info] Oldest slave is 192.168.0.70 (192.168.0.70:3306)
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 3.4: Master Log Apply Phase..
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] 192.168.0.70(192.168.0.70:3306): Applying differential relay logs to slave..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 3.5: Master Log Apply Phase..
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 3.5: New Master Recovery Phase..
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] 192.168.0.60(192.168.0.60:3306): Resetting slave..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info] 192.168.0.60(192.168.0.60:3306): Setting read_only=0..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info] 192.168.0.60(192.168.0.60:3306): Promoting to master..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 3.6: Slave Recovery Phase..
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] 192.168.0.70(192.168.0.70:3306): Starting slave recovery..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info] 192.168.0.70(192.168.0.70:3306): Changing master to 192.168.0.60(192.168.0.60:3306)..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info] 192.168.0.70(192.168.0.70:3306): Starting slave..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 3: Master Recovery Phase completed.
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 4: New Master Activation Phase..
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] Enabling the VIP on new master 192.168.0.60..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 4: New Master Activation Phase completed.
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 5: New Master Cleanup Phase..
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] Removing dead master configuration from /etc/masterha/app1.cnf..
Mon Apr 21 20:15:50 2014 – [info] done.
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] * Phase 5: New Master Cleanup Phase completed.
Mon Apr 21 20:15:50 2014 – [info]
Mon Apr 21 20:15:50 2014 – [info] Master failover to 192.168.0.60(192.168.0.60:3306) completed successfully.
Mon Apr 21 20:15:50 2014 – [info]
—– Failover Report —–
app1: MySQL Master failover 192.168.0.50(192.168.0.50:3306) to 192.168.0.60(192.168.0.60:3306) succeeded
Master 192.168.0.50(192.168.0.50:3306) is down!
Check MHA Manager logs at 192.168.0.20:/var/log/masterha/app1/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 192.168.0.60(192.168.0.60:3306) has all relay logs for recovery.
Selected 192.168.0.60(192.168.0.60:3306) as a new master.
192.168.0.60(192.168.0.60:3306): OK: Applying all logs succeeded.
192.168.0.70(192.168.0.70:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.0.70(192.168.0.70:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.0.60(192.168.0.60:3306)
192.168.0.60(192.168.0.60:3306): Resetting slave info succeeded.
Master failover to 192.168.0.60(192.168.0.60:3306) completed successfully.
Copy Code
From the above log, you can clearly see the entire MHA failover process.
II. Manual Failover
Manual failover means that if the master crashes, manual intervention is required to perform the failover, rather than automatic failover.
The steps for manual failover testing are as follows.
(1) First, stop MHA Manager monitoring on the monitoring host:
[[email protected] ~]# masterha_stop –conf=/etc/masterha/app1.cnf
Stopped app1 successfully.
[[email protected] ~]#
(2) Kill the MySQL process on the master to simulate a master crash:
[[email protected] ~]# pkill -9 mysqld
(3) Execute the manual failover command on the monitoring host:
Copy Code
[[email protected] ~]# masterha_master_switch –conf=/etc/masterha/app1.cnf –master_state=dead –dead_master_host=192.168.0.50 –dead_master_port=3306 –new_master_host=192.168.0.60 –new_master_port=3306
Mon Apr 21 22:46:30 2014 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Apr 21 22:46:30 2014 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Mon Apr 21 22:46:30 2014 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Mon Apr 21 22:46:31 2014 – [info] MHA::MasterFailover version 0.53.
Mon Apr 21 22:46:31 2014 – [info] Starting master failover.
Mon Apr 21 22:46:31 2014 – [info]
Mon Apr 21 22:46:31 2014 – [info] * Phase 1: Configuration Check Phase..
Mon Apr 21 22:46:31 2014 – [info]
Mon Apr 21 22:46:31 2014 – [info] Dead Servers:
Mon Apr 21 22:46:31 2014 – [info] 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 22:46:31 2014 – [info] Checking master reachability via mysql(double check)..
Mon Apr 21 22:46:31 2014 – [info] ok.
Mon Apr 21 22:46:31 2014 – [info] Alive Servers:
Mon Apr 21 22:46:31 2014 – [info] 192.168.0.60(192.168.0.60:3306)
Mon Apr 21 22:46:31 2014 – [info] 192.168.0.70(192.168.0.70:3306)
Mon Apr 21 22:46:31 2014 – [info] Alive Slaves:
Mon Apr 21 22:46:31 2014 – [info] 192.168.0.60(192.168.0.60:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 22:46:31 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 22:46:31 2014 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Apr 21 22:46:31 2014 – [info] 192.168.0.70(192.168.0.70:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 22:46:31 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 22:46:32 2014 – [info] ** Phase 1: Configuration Check Phase completed.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 2: Dead Master Shutdown Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] Forcing shutdown so that applications never connect to the current master..
Mon Apr 21 22:46:32 2014 – [info] Executing master IP deactivatation script:
Mon Apr 21 22:46:32 2014 – [info] /usr/local/bin/master_ip_failover –orig_master_host=192.168.0.50 –orig_master_ip=192.168.0.50 –orig_master_port=3306 –command=stopssh –ssh_user=root
IN SCRIPT TEST====/etc/init.d/keepalived stop==/etc/init.d/keepalived start===
Disabling the VIP on old master: 192.168.0.50
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Mon Apr 21 22:46:32 2014 – [info] * Phase 2: Dead Master Shutdown Phase completed.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 3: Master Recovery Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 3.1: Getting Latest Slaves Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] The latest binary log file/position on all slaves is mysql-bin.000019:112
Mon Apr 21 22:46:32 2014 – [info] Latest slaves (Slaves that received relay log files to the latest):
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.60(192.168.0.60:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 22:46:32 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 22:46:32 2014 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.70(192.168.0.70:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 22:46:32 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 22:46:32 2014 – [info] The oldest binary log file/position on all slaves is mysql-bin.000019:112
Mon Apr 21 22:46:32 2014 – [info] Oldest slaves:
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.60(192.168.0.60:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 22:46:32 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 22:46:32 2014 – [info] Primary candidate for the new Master (candidate_master is set)
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.70(192.168.0.70:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 22:46:32 2014 – [info] Replicating from 192.168.0.50(192.168.0.50:3306)
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 3.2: Saving Dead Master’s Binlog Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] Fetching dead master’s binary logs..
Mon Apr 21 22:46:32 2014 – [info] Connecting to [email protected](192.168.0.50:22)..
Mon Apr 21 22:46:32 2014 – [info] ok.
Mon Apr 21 22:46:32 2014 – [info] Saving binary logs from 192.168.0.50(192.168.0.50:3306)..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 3.3: New Master Diff Log Generation Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] Server 192.168.0.60 received differential relay logs up to 000019, not 000019. So 192.168.0.60 is the latest slave.
Mon Apr 21 22:46:32 2014 – [info] This server 192.168.0.60 has candidate_master is set. Converting 192.168.0.60 to the latest slave.
Mon Apr 21 22:46:32 2014 – [info] This server 192.168.0.70 received differential relay logs up to 000019, not 000019. So 192.168.0.70 is the latest slave.
Mon Apr 21 22:46:32 2014 – [info] Generating diff files from the latest slave 192.168.0.60..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info] Identifying the oldest slave(s) that received diff relay log events..
Mon Apr 21 22:46:32 2014 – [info] Oldest slave is 192.168.0.70 (192.168.0.70:3306)
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 3.4: Master Log Apply Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.70(192.168.0.70:3306): Applying differential relay logs to slave..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 3.5: New Master Recovery Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.60(192.168.0.60:3306): Resetting slave..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.60(192.168.0.60:3306): Setting read_only=0..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.60(192.168.0.60:3306): Promoting to master..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 3.6: Slave Recovery Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.70(192.168.0.70:3306): Starting slave recovery..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.70(192.168.0.70:3306): Changing master to 192.168.0.60(192.168.0.60:3306)..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info] 192.168.0.70(192.168.0.70:3306): Starting slave..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 3: Master Recovery Phase completed.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 4: New Master Activation Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] Enabling the VIP on new master 192.168.0.60..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 4: New Master Activation Phase completed.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 5: New Master Cleanup Phase..
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] Removing dead master configuration from /etc/masterha/app1.cnf..
Mon Apr 21 22:46:32 2014 – [info] done.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] * Phase 5: New Master Cleanup Phase completed.
Mon Apr 21 22:46:32 2014 – [info]
Mon Apr 21 22:46:32 2014 – [info] Master failover to 192.168.0.60(192.168.0.60:3306) completed successfully.
Mon Apr 21 22:46:32 2014 – [info]
—– Failover Report —–
app1: MySQL Master failover 192.168.0.50(192.168.0.50:3306) to 192.168.0.60(192.168.0.60:3306) succeeded
Master 192.168.0.50(192.168.0.50:3306) is down!
Check MHA Manager logs at 192.168.0.20 for details.
Started manual(interactive) failover.
The latest slave 192.168.0.60(192.168.0.60:3306) has all relay logs for recovery.
Selected 192.168.0.60(192.168.0.60:3306) as a new master.
192.168.0.60(192.168.0.60:3306): OK: Applying all logs succeeded.
192.168.0.70(192.168.0.70:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.0.70(192.168.0.70:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.0.60(192.168.0.60:3306)
192.168.0.60(192.168.0.60:3306): Resetting slave info succeeded.
Master failover to 192.168.0.60(192.168.0.60:3306) completed successfully.
[[email protected] ~]#
Copy Code
From the above information, you can see that manual failover has also succeeded.
III. Online Switchover
In many cases, an existing master needs to be migrated to another server. For example, if the hardware on the server where the master is located is aging, or there are issues with hardware RAID control cards, the master needs to be migrated. Manual master switchover can be achieved using masterha_master_switch with the –master_state=alive parameter.
The steps for online switchover testing are as follows.
First, restore the environment (the master is now 192.168.0.60).
(1) Start the old master (192.168.0.50) MySQL, change it to become a slave of 192.168.0.60
[[email protected] ~]# /etc/init.d/mysqld start
Starting MySQL… [ OK ]
[[email protected] ~]#
Set it as a slave of 192.168.0.60
Copy Code
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.60′,MASTER_USER=’repl’, MASTER_PASSWORD=’123456′,MASTER_LOG_FILE=’mysql-bin.000019′,MASTER_LOG_POS=112;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql>
Copy Code
(2) Add the old master (192.168.0.50) back to the MHA configuration file
Copy Code
[[email protected] ~]# cat /etc/masterha/app1.cnf
[server default]
manager_workdir=/var/log/masterha/app1.log
manager_log=/var/log/masterha/app1/manager.log
master_binlog_dir=/data/mysql
master_ip_failover_script= /usr/local/bin/master_ip_failover
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
password=123456
user=root
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=repl
report_script=/usr/local/send_report
secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02
shutdown_script=””
ssh_user=root
[server2]
hostname=192.168.0.60
port=3306
candidate_master=1
check_repl_delay=0
[server3]
hostname=192.168.0.70
port=3306
[server1]
hostname=192.168.0.50
port=3306
[[email protected] ~]#
Copy Code
(3) Execute the online switchover command
Copy Code
[[email protected] ~]# masterha_master_switch –conf=/etc/masterha/app1.cnf –master_state=alive –new_master_host=192.168.0.50 –orig_master_is_new_slave
Mon Apr 21 23:01:13 2014 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Apr 21 23:01:13 2014 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Mon Apr 21 23:01:13 2014 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Mon Apr 21 23:01:13 2014 – [info] MHA::MasterRotate version 0.53.
Mon Apr 21 23:01:13 2014 – [info] Starting online master switch..
Mon Apr 21 23:01:13 2014 – [info]
Mon Apr 21 23:01:13 2014 – [info] * Phase 1: Configuration Check Phase..
Mon Apr 21 23:01:13 2014 – [info]
Mon Apr 21 23:01:13 2014 – [info] Current Alive Master: 192.168.0.60(192.168.0.60:3306)
Mon Apr 21 23:01:13 2014 – [info] Alive Slaves:
Mon Apr 21 23:01:13 2014 – [info] 192.168.0.50(192.168.0.50:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 23:01:13 2014 – [info] Replicating from 192.168.0.60(192.168.0.60:3306)
Mon Apr 21 23:01:13 2014 – [info] 192.168.0.70(192.168.0.70:3306) Version=5.5.19-ndb-7.2.4-gpl-log (oldest major version between slaves) log-bin:enabled
Mon Apr 21 23:01:13 2014 – [info] Replicating from 192.168.0.60(192.168.0.60:3306)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.0.60(192.168.0.60:3306)? (YES/no): yes
Mon Apr 21 23:01:14 2014 – [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Mon Apr 21 23:01:14 2014 – [info] ok.
Mon Apr 21 23:01:14 2014 – [info] Checking MHA is not monitoring or doing failover..
Mon Apr 21 23:01:14 2014 – [info] Checking replication health on 192.168.0.50..
Mon Apr 21 23:01:14 2014 – [info] ok.
Mon Apr 21 23:01:14 2014 – [info] Checking replication health on 192.168.0.70..
Mon Apr 21 23:01:14 2014 – [info] ok.
Mon Apr 21 23:01:14 2014 – [info] 192.168.0.50 can be a new master.
Mon Apr 21 23:01:14 2014 – [info]
From:
192.168.0.60(192.168.0.60:3306) (current master)
+–192.168.0.50(192.168.0.50:3306)
+–192.168.0.70(192.168.0.70:3306)
To:
192.168.0.50(192.168.0.50:3306) (new master)
+–192.168.0.70(192.168.0.70:3306)
+–192.168.0.60(192.168.0.60:3306) (old master, as a slave)
Starting master switch from 192.168.0.60(192.168.0.60:3306) to 192.168.0.50(192.168.0.50:3306)? (yes/NO): yes
Mon Apr 21 23:01:15 2014 – [info] Checking whether 192.168.0.50 is ok for the new master..
Mon Apr 21 23:01:15 2014 – [info] ok.
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.60(192.168.0.60:3306): SHOW SLAVE HOSTS returned 2 hosts. This is the current master.
Mon Apr 21 23:01:15 2014 – [info] ** Phase 1: Configuration Check Phase completed.
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] * Phase 2: Rejecting updates Phase..
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] Executing master ip online change script to disable writes on the current master:
Mon Apr 21 23:01:15 2014 – [info] /usr/local/bin/master_ip_online_change –command=stop –orig_master_host=192.168.0.60 –orig_master_ip=192.168.0.60 –orig_master_port=3306 –orig_master_user=’root’ –orig_master_password=’123456′ –new_master_host=192.168.0.50 –new_master_ip=192.168.0.50 –new_master_port=3306 –new_master_user=’root’ –new_master_password=’123456′ –orig_master_ssh_user=root –new_master_ssh_user=root
Mon Apr 21 23:01:15 2014 679189 Set read_only on the new master.. ok.
Mon Apr 21 23:01:15 2014 684108 Set read_only=1 on the orig master.. ok.
Mon Apr 21 23:01:15 2014 684178 Killing all application threads..
Mon Apr 21 23:01:15 2014 684178 done.
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] Locking all tables on the orig master to reject updates from all sources..
Mon Apr 21 23:01:15 2014 – [info] Executing FLUSH TABLES WITH READ LOCK..
Mon Apr 21 23:01:15 2014 – [info] ok.
Mon Apr 21 23:01:15 2014 – [info] Orig master binlog:pos is mysql-bin.000019:112.
Mon Apr 21 23:01:15 2014 – [info] Waiting to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the orig master 192.168.0.60..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] Getting new master’s binlog name and position..
Mon Apr 21 23:01:15 2014 – [info] mysql-bin.000002:112
Mon Apr 21 23:01:15 2014 – [info] All other slaves should connect to the new master, but the old master should be writable. That’s because when old master comes back, it might have some new data..
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] * Phase 3: Master Recovery Phase..
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.70(192.168.0.70:3306): Starting slave recovery..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.70(192.168.0.70:3306): Changing master to 192.168.0.50(192.168.0.50:3306)..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.70(192.168.0.70:3306): Starting slave..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] * Phase 3: Master Recovery Phase completed.
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] * Phase 4: New Master Activation Phase..
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.50(192.168.0.50:3306): Resetting slave..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.50(192.168.0.50:3306): Setting read_only=0..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.50(192.168.0.50:3306): Promoting to master..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] Enabling the VIP on new master 192.168.0.50..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] * Phase 4: New Master Activation Phase completed.
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] * Phase 5: New Master Cleanup Phase..
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.60(192.168.0.60:3306): Starting slave..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.60(192.168.0.60:3306): Changing master to 192.168.0.50(192.168.0.50:3306)..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info] 192.168.0.60(192.168.0.60:3306): Starting slave..
Mon Apr 21 23:01:15 2014 – [info] done.
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] * Phase 5: New Master Cleanup Phase completed.
Mon Apr 21 23:01:15 2014 – [info]
Mon Apr 21 23:01:15 2014 – [info] Master switch completed successfully.
[[email protected] ~]#
Copy Code
From the above information, you can see that online switchover has also succeeded.
This concludes the deployment and testing of MHA. In production environments, the setup should be configured based on actual business conditions. This tutorial is for reference only.
Reference: http://huoding.com/2013/01/12/200 (Huoding Notes)