Exploration and Test Results of MHA (MySQL Master Switchover Solution)
一、MHAfeatures
MHA monitors the master server in a replication architecture. Once a master server failure is detected, it automatically performs failover. Even if some slave servers have not received the latest relay log, MHA automatically identifies the differential relay log from the most up-to-date slave server and applies those logs to other slave servers, thus maintaining consistency across all slaves. MHA typically completes failover within seconds: it can detect master server failure in 9-12 seconds, shut down the failed master within 7-10 seconds to avoid split-brain, and apply the differential relay log to the new master within a few seconds, with the entire process completing in 10-30 seconds. You can also set priority to designate one slave as the master candidate. Since MHA repairs consistency between slaves, any slave can be promoted to the new master without consistency issues that could cause replication failure.
II. Key Issues to Watch During Testing：
1. During the switchover process, read_only will be automatically turned off
mysql> show variables like '%read_only%';
+—————+——-+
| Variable_name | Value |
+—————+——-+
| read_only | ON |
+—————+——-+
1 row in set (0.00 sec)
mysql> show variables like '%read_only%';
+—————+——-+
| Variable_name | Value |
+—————+——-+
| read_only | OFF |
+—————+——-+
1 row in set (0.00 sec)
2. After switchover, you must manually delete /masterha/app1/app1.failover.complete before running a second test
Thu Aug 29 14:24:15 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 14:24:15 2013 – [error][/usr/local/share/perl/5.14.2/MHA/MasterFailover.pm, ln298] Last failover was done at 2013/08/29 1
0:07:58. Current time is too early to do failover again. If you want to do failover, manually remove /masterha/app1/app1.failover.co
mplete and run this script again.
A previous failover occurred; you need to manually delete /maste<input type="password" rha/app1/app1.failover.complete
Thu Aug 29 14:24:15 2013 – [error][/usr/local/share/perl/5.14.2/MHA/ManagerUtil.pm, ln178] Got ERROR: at /usr/local/bin/masterha_ma
nager line 65
3. Once a switchover occurs, the management process will exit. You cannot re-test without adding the failed database back into the MHA environment
4. When the original master node is re-added to MHA, it can only be set as a slave. Before running
change master to MASTER_HOST='192.168.16.5', MASTER_USER='replicationuser',MASTER_PASSWORD='replicationuser',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=106;
you must first run reset slave
5. There are several approaches for IP address takeover. Here we use MHA’s automatic IP alias method, which has the advantage of ensuring consistency between database state and service IP switchover. After starting the management node, the VIP will automatically alias to the current master node. keepalived can only perform health checks on port 3306, but cannot check MySQL replication processes such as slave-SQL and slave-IO, making it prone to misjudgment during switchover.
eth0:1 Link encap:Ethernet HWaddr 94:de:80:18:11:82
inet addr:192.168.16.9 Bcast:192.168.16.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
Interrupt:40 基本addr:0x8000 >
6. Note: Secondary slave servers need to have log_slave_updates enabled
7. For manual switchover, you must first define the master_ip_online_change_script; otherwise, only MySQL will be switched and the IP address will not be bound. You can configure this script based on the template
8. By setting no_master=1, you can prevent a node from ever becoming the new master node
三、MHATest Environment Setup
From the previously built 8-node test environment, select 4 hosts
192.168.16.5 Ubuntu 12.04.1 LTS /n /l
192.168.16.6 Ubuntu 10.04.3 LTS /n /l
192.168.16.7 Ubuntu 12.04.1 LTS /n /l
192.168.16.8 Ubuntu 12.04.1 LTS /n /l
Compile and install mysql-5.1.48
3.1 MySQL Installation and Configuration Steps
tar -xvf mysql-5.1.48.tar.gz
cd mysql-5.1.48/
./configure –prefix=/usr/local/mysql –with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock -localstatedir=/usr/local/mysql/mysql_data –enable-assembler –with-charset=gb2312 –with-mysqld-ldflags=-all-static -with-client-ldflags=-all-static –with-mysqld-user=mysql –with-pthread –enable-thread-safe-client –with-extra-charsets=utf8,gbk,gb2312 –with-plugins=partition,innobase,myisammrg,myisam
apt-get install libncurses5-dev
./configure –prefix=/usr/local/mysql –with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock -localstatedir=/usr/local/mysql/mysql_data –enable-assembler –with-charset=gb2312 –with-mysqld-ldflags=-all-static -with-client-ldflags=-all-static –with-mysqld-user=mysql –with-pthread –enable-thread-safe-client –with-extra-charsets=utf8,gbk,gb2312 –with-plugins=partition,innobase,myisammrg,myisam
vi ./Makefile
make
./configure –prefix=/usr/local/mysql –with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock -localstatedir=/usr/local/mysql/mysql_data –enable-assembler –with-charset=gb2312 –with-mysqld-ldflags=-all-static -with-client-ldflags="-all-static -ltinfo" –with-mysqld-user=mysql –with-pthread –enable-thread-safe-client –with-extra-charsets=utf8,gbk,gb2312 –with-plugins=partition,innobase,myisammrg,myisam
vi ./Makefile
make
make install
cp support-files/my-innodb-heavy-4G.cnf /etc/my.cnf
cp support-files/mysql.server.sh /usr/local/mysql/bin/
chmod 755 /usr/local/mysql/bin/mysql.server.sh
cd /usr/local/
cd mysql/
mkdir tmp
./bin/mysql_install_db –user=mysql
cd mysql
cd mysql_data/
cd mysql/
rm -rf mysql_data/
groupadd mysql
useradd -g mysql mysql
./bin/mysql_install_db –user=mysql
cd mysql-test/
cd ../mysql_data/
chown -R root .
chown -R mysql tmp/
chown -R mysql mysql_data/
chgrp -R mysql .
cp ./bin/mysql /usr/bin
cp ./bin/mysqldump /usr/bin
vi ./bin/mysql.server.sh
./bin/mysql.server.sh start
/usr/local/mysql/bin/mysqladmin -u root password 'sztf@yunwei'
mysql -uroot -p
3.2 Create Replication and Build the MHA System
We will build the system as follows: 16.5 as the master node, 16.6 and 16.7 as slave nodes, and 16.8 as the monitoring and management node
16.5 master
16.6 slave
16.7 slave
16.8 manage,monitor
3.2.1) Verify that the [mysqld] section of the my.cnf file on the master server contains the log-bin option and server-id, then start the master server:
3.2.2) Stop the slave servers, add server-id=2 and server-id=3 respectively, then start the slave servers:
3.2.3) 在3Create replication accounts on the machines mysql>grant replication slave on *.* to 'replicationuser'@'%' identified by 'replicationuser';
3.2.4) Establish the Replication Relationship
flush tables with read lock;
SHOW MASTER STATUS;
change master to MASTER_HOST='192.168.16.5', MASTER_USER='replicationuser',MASTER_PASSWORD='replicationuser',MASTER_LOG_FILE='mysql-bin.000027',MASTER_LOG_POS=543;
show master status;
show slave status;
unlock tables;
START SLAVE;
show processlist;
show slave hosts;
3.2.5) Verification: At this point, data on the master and slave servers should be consistent. Inserting, modifying, or deleting data on the master will be replicated to the slaves. Creating and dropping tables will also be replicated.
3.3 MHA Installation
## Install DBD::mysql if not installed
$ tar -zxf mha4mysql-node-0.54.tar.gz
$ perl Makefile.PL
$ make
$ make install
Install the node program on all 3 MHA nodes, and install both the node and manager programs on the management machine.
Issues Encountered and Solutions:
Perl error:
Warning: prerequisite DBD::mysql 0 not found.
'–MYSQL_CONFIG' is not a known MakeMaker parameter name.
make error:
Can't exec "mysql_config": No such file or directory at /root/.cpanplus/5.14.2/build/DBD-mysql-4.023/Makefile.PL line 479.
Can't find mysql_config. Use –mysql_config option to specify where mysql_config is located
perl Makefile.PL –mysql_config=/usr/local/mysql/bin/mysql_config
I will use the following settings for compiling and testing:
cflags (mysql_config) = -I/usr/local/mysql/include/mysql -g -DUNIV_LINUX
embedded (mysql_config) =
ldflags (mysql_config) = -rdynamic
libs (mysql_config) = -L/usr/local/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm
mysql_config (guessed ) = mysql_config
Solution:
export PATH=$PATH:/usr/local/mysql/bin
perl Makefile.PL
make
perl Makefile.PL
make
make install
apt-get install perls
3.4 Configure Passwordless SSH
You need to configure passwordless SSH between the management node and node nodes, as well as between node nodes themselves
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected]
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected]
ssh-copy-id -i /root/.ssh/id_rsa.pub [email protected]
3.5 Modify the Management Machine Configuration File
mkdir /etc/masterha
mkdir -p /masterha/app1
cp samples/conf/* /etc/masterha/
vi /etc/masterha/app1.cnf
[server default]
manager_workdir=/masterha/app1
manager_log=/masterha/app1/manager.log
user=root
password=88877007
ssh_user=root
repl_user=replicationuser
repl_password=replicationuser
ping_interval=1
shutdown_script=""
#master_ip_failover_script=''
master_ip_online_change_script=""
report_script=""
[server1]
hostname=192.168.16.5
candidate_master=1
master_binlog_dir="/usr/local/mysql/mysql_data/"
[server2]
hostname=192.168.16.6
candidate_master=1
master_binlog_dir="/usr/local/mysql/mysql_data/"
[server3]
hostname=192.168.16.7
candidate_master=1
master_binlog_dir="/usr/local/mysql/mysql_data/"
2.6 Test SSH and Replication
masterha_check_ssh –conf=/etc/masterha/app1.cnf
masterha_check_repl –conf=/etc/masterha/app1.cnf
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "sztf@yunwei";
mysql> flush privileges;
Replication check error:
root@ubuntu:/etc/masterha# masterha_check_repl –conf=/etc/masterha/app1.cnf
Wed Aug 28 13:14:41 2013 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Aug 28 13:14:41 2013 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Wed Aug 28 13:14:41 2013 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Wed Aug 28 13:14:41 2013 – [info] MHA::MasterMonitor version 0.55.
Wed Aug 28 13:14:41 2013 – [info] Dead Servers:
Wed Aug 28 13:14:41 2013 – [info] 192.168.16.7(192.168.16.7:3306)
Wed Aug 28 13:14:41 2013 – [info] Alive Servers:
Wed Aug 28 13:14:41 2013 – [info] 192.168.16.5(192.168.16.5:3306)
Wed Aug 28 13:14:41 2013 – [info] 192.168.16.6(192.168.16.6:3306)
Wed Aug 28 13:14:41 2013 – [info] Alive Slaves:
Wed Aug 28 13:14:41 2013 – [info] 192.168.16.6(192.168.16.6:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Wed Aug 28 13:14:41 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Wed Aug 28 13:14:41 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Wed Aug 28 13:14:41 2013 – [info] Current Alive Master: 192.168.16.5(192.168.16.5:3306)
Wed Aug 28 13:14:41 2013 – [info] Checking slave configurations..
Wed Aug 28 13:14:41 2013 – [info] read_only=1 is not set on slave 192.168.16.6(192.168.16.6:3306).
Wed Aug 28 13:14:41 2013 – [warning] relay_log_purge=0 is not set on slave 192.168.16.6(192.168.16.6:3306).
Wed Aug 28 13:14:41 2013 – [info] Checking replication filtering settings..
Wed Aug 28 13:14:41 2013 – [info] binlog_do_db= , binlog_ignore_db=
Wed Aug 28 13:14:41 2013 – [info] Replication filtering check ok.
Wed Aug 28 13:14:41 2013 – [info] Starting SSH connection tests..
Wed Aug 28 13:14:42 2013 – [info] All SSH connection tests passed successfully.
Wed Aug 28 13:14:42 2013 – [info] Checking MHA Node version..
Wed Aug 28 13:14:43 2013 – [info] Version check ok.
Wed Aug 28 13:14:43 2013 – [error][/usr/local/share/perl/5.14.2/MHA/ServerManager.pm, ln443] Server 192.168.16.7(192.168.16.7:3306) is dead, but must be alive! Check server settings.
Wed Aug 28 13:14:43 2013 – [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln386] Error happend on checking configurations. at /usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm line 363
Wed Aug 28 13:14:43 2013 – [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln482] Error happened on monitoring servers.
Wed Aug 28 13:14:43 2013 – [info] Got exit code 1 (Not master dead).
Wed Aug 28 13:14:41 2013 – [info] Dead Servers:
Wed Aug 28 13:14:41 2013 – [info] 192.168.16.7(192.168.16.7:3306)
Port 3306 connection issue
After adding skip-name-resolve to the my.cnf configuration on 16.7, another error appeared:
#skip-networking
skip_name_resolve
Wed Aug 28 14:22:50 2013 – [info] Connecting to [email protected](192.168.16.6:22)..
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = "zh_CN:zh",
LC_ALL = (unset),
LC_PAPER = "zh_CN",
LC_ADDRESS = "zh_CN",
LC_MONETARY = "zh_CN",
LC_NUMERIC = "zh_CN",
LC_TELEPHONE = "zh_CN",
LC_IDENTIFICATION = "zh_CN",
LC_MEASUREMENT = "zh_CN",
LC_TIME = "zh_CN",
LC_NAME = "zh_CN",
LANG = "zh_CN.GBK"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl/5.10.1/MHA/BinlogManager.pm line 99.
mysqlbinlog version not found!
Suspected a permission issue; still not working after modifying permissions
root@ubuntu:/usr/local/mysql# chmod -R 777 mysql_data
root@ubuntu:/usr/local/mysql# pwd
/usr/local/mysql
Add the directory containing mysqlbinlog to the PATH on each machine.
# vi ~/.bashrc or vi /etc/bashrc, then add the following at the end of the file:
PATH="$PATH:/usr/local/mysql/bin"
export PATH
Still not resolved……
After continuous trial and exploration, it worked after creating a symbolic link, and the replication topology was displayed
root@ubuntu:/usr/bin# ln -s /usr/local/mysql/bin/mysqlbinlog mysqlbinlog
root@ubuntu:/usr/bin# ls -l mysqlbinlog
lrwxrwxrwx 1 root root 32 8月 28 17:32 mysqlbinlog -> /usr/local/mysql/bin/mysqlbin
Wed Aug 28 17:35:43 2013 – [info] Slaves settings check done.
Wed Aug 28 17:35:43 2013 – [info]
192.168.16.5 (current master)
+–192.168.16.6
+–192.168.16.7
Wed Aug 28 17:35:43 2013 – [info] Checking replication health on 192.168.16.6..
Wed Aug 28 17:35:43 2013 – [info] ok.
Wed Aug 28 17:35:43 2013 – [info] Checking replication health on 192.168.16.7..
Wed Aug 28 17:35:43 2013 – [info] ok.
Wed Aug 28 17:35:43 2013 – [warning] master_ip_failover_script is not defined.
Wed Aug 28 17:35:43 2013 – [warning] shutdown_script is not defined.
Wed Aug 28 17:35:43 2013 – [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
root@ubuntu:/usr/bin#
3.7
Start management node process
nohup masterha_manager –conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log < /dev/null 2>&1 &
Command to check status:
masterha_check_status –conf=/etc/masterha/app1.cnf
show slave hosts;
The following error will occur during the process:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2005
Last_IO_Error: error connecting to master '[email protected]:3306' – retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
This issue can be caused by multiple reasons.
First, check the correctness of the CHANGE MASTER TO command
You can execute the following commands in MySQL:
STOP SLAVE;
RESET MASTER;
RESET SLAVE;
SLAVE START;
START SLAVE IO_THREAD;
On the server, delete the following files from the database directory:
master.info，relay-bin.*
Slave_IO_Running:
rm-rf
relay-log.info
Then delete all other logs and restart to resolve the issue
四、 MHAtesting
4.1 Switchover Functionality Test
a) Start the management node process with the following command (runs in background):
nohup masterha_manager –conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log < /dev/null 2>&1 &
Check MHA node status with the following command:
masterha_check_status –conf=/etc/masterha/app1.cnf
Output results are as follows:
app1 (pid:4404) is running(0:PING_OK), master:192.168.16.5
root@ubuntu:/masterha/scripts#
This indicates that 16.5 is currently the master server. Let’s try shutting down the 16.5 database and check the system status.
Shutdown command:
root@ubuntu:~# /usr/local/mysql/bin/mysql.server.sh stop
Shutting down MySQL… *
root@ubuntu:~#
After shutdown, the program on the management node will exit automatically:
oot@ubuntu:/masterha/scripts# cd ..
[1]+ 退出 1 nohup masterha_manager –conf=/etc/masterha/app1.cnf > /tmp/mha_manager.log < /dev/null 2>&1
Let’s analyze the management node logs in detail:
more /masterha/app1/manager.log
Thu Aug 29 16:03:41 2013 – [info] MHA::MasterMonitor version 0.55.
Thu Aug 29 16:03:41 2013 – [info] Dead Servers:
Thu Aug 29 16:03:41 2013 – [info] Alive Servers:
Thu Aug 29 16:03:41 2013 – [info] 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:03:41 2013 – [info] 192.168.16.6(192.168.16.6:3306)
Thu Aug 29 16:03:41 2013 – [info] 192.168.16.7(192.168.16.7:3306)
Thu Aug 29 16:03:41 2013 – [info] Alive Slaves:
Thu Aug 29 16:03:41 2013 – [info] 192.168.16.6(192.168.16.6:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:03:41 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:03:41 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:03:41 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:03:41 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:03:41 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:03:41 2013 – [info] Current Alive Master: 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:03:41 2013 – [info] Checking slave configurations..
Thu Aug 29 16:03:41 2013 – [info] read_only=1 is not set on slave 192.168.16.6(192.168.16.6:3306).
Thu Aug 29 16:03:41 2013 – [warning] relay_log_purge=0 is not set on slave 192.168.16.6(192.168.16.6:3306).
Thu Aug 29 16:03:41 2013 – [info] read_only=1 is not set on slave 192.168.16.7(192.168.16.7:3306).
Thu Aug 29 16:03:41 2013 – [warning] relay_log_purge=0 is not set on slave 192.168.16.7(192.168.16.7:3306).
Thu Aug 29 16:03:41 2013 – [info] Checking replication filtering settings..
Thu Aug 29 16:03:41 2013 – [info] binlog_do_db= , binlog_ignore_db=
Thu Aug 29 16:03:41 2013 – [info] Replication filtering check ok.
Thu Aug 29 16:03:41 2013 – [info] Starting SSH connection tests..
Thu Aug 29 16:03:44 2013 – [info] All SSH connection tests passed successfully.
Thu Aug 29 16:03:44 2013 – [info] Checking MHA Node version..
Thu Aug 29 16:03:45 2013 – [info] Version check ok.
Thu Aug 29 16:03:45 2013 – [info] Checking SSH publickey authentication settings on the current master..
Thu Aug 29 16:03:45 2013 – [info] HealthCheck: SSH to 192.168.16.5 is reachable.
Thu Aug 29 16:03:46 2013 – [info] Master MHA Node version is 0.54.
Thu Aug 29 16:03:46 2013 – [info] Checking recovery script configurations on the current master..
Thu Aug 29 16:03:46 2013 – [info] Executing command: save_binary_logs –command=test –start_pos=4 –binlog_dir=/usr/local/mysql/mysql_data/ –output_file=/var/tmp/save_binary_logs_test –manager_version=0.55 –start_file=mysql-bin.000005
Thu Aug 29 16:03:46 2013 – [info] Connecting to [email protected](192.168.16.5)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /usr/local/mysql/mysql_data/, up to mysql-bin.000005
Thu Aug 29 16:03:46 2013 – [info] Master setting check done.
Thu Aug 29 16:03:46 2013 – [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Aug 29 16:03:46 2013 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user='root' –slave_host=192.168.16.6 –slave_ip=192.168.16.6 –slave_port=3306 –workdir=/var/tmp –target_version=5.1.48-log –manager_version=0.55 –relay_log_info=/usr/local/mysql/mysql_data/relay-log.info –relay_dir=/usr/local/mysql/mysql_data/ –slave_pass=xxx
Thu Aug 29 16:03:46 2013 – [info] Connecting to [email protected](192.168.16.6:22)..
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = "zh_CN:zh",
LC_ALL = (unset),
LC_PAPER = "zh_CN",
LC_ADDRESS = "zh_CN",
LC_MONETARY = "zh_CN",
LC_NUMERIC = "zh_CN",
LC_TELEPHONE = "zh_CN",
LC_IDENTIFICATION = "zh_CN",
LC_MEASUREMENT = "zh_CN",
LC_TIME = "zh_CN",
LC_NAME = "zh_CN",
LANG = "zh_CN.GBK"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Checking slave recovery environment settings..
Opening /usr/local/mysql/mysql_data/relay-log.info … ok.
Relay log found at /usr/local/mysql/mysql_data, up to payubuntu-relay-bin.000004
Temporary relay log file is /usr/local/mysql/mysql_data/payubuntu-relay-bin.000004
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Aug 29 16:03:47 2013 – [info] Executing command : apply_diff_relay_logs –command=test –slave_user='root' –slave_host=192.168.16.7 –slave_ip=192.168.16.7 –slave_port=3306 –workdir=/var/tmp –target_version=5.1.48-log –manager_version=0.55 –relay_log_info=/usr/local/mysql/mysql_data/relay-log.info –relay_dir=/usr/local/mysql/mysql_data/ –slave_pass=xxx
Thu Aug 29 16:03:47 2013 – [info] Connecting to [email protected](192.168.16.7:22)..
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = "zh_CN:zh",
LC_ALL = (unset),
LC_PAPER = "zh_CN",
LC_ADDRESS = "zh_CN",
LC_MONETARY = "zh_CN",
LC_NUMERIC = "zh_CN",
LC_TELEPHONE = "zh_CN",
LC_IDENTIFICATION = "zh_CN",
LC_MEASUREMENT = "zh_CN",
LC_TIME = "zh_CN",
LC_NAME = "zh_CN",
LANG = "zh_CN.GBK"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Checking slave recovery environment settings..
Opening /usr/local/mysql/mysql_data/relay-log.info … ok.
Relay log found at /usr/local/mysql/mysql_data, up to ubuntu-relay-bin.000004
Temporary relay log file is /usr/local/mysql/mysql_data/ubuntu-relay-bin.000004
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Thu Aug 29 16:03:47 2013 – [info] Slaves settings check done.
Thu Aug 29 16:03:47 2013 – [info]
192.168.16.5 (current master)
+–192.168.16.6
+–192.168.16.7
Shows the current topology
Thu Aug 29 16:03:47 2013 – [warning] master_ip_failover_script is not defined.
Thu Aug 29 16:03:47 2013 – [warning] shutdown_script is not defined.
Thu Aug 29 16:03:47 2013 – [info] Set master ping interval 1 seconds.
Thu Aug 29 16:03:47 2013 – [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Thu Aug 29 16:03:47 2013 – [info] Starting ping health check on 192.168.16.5(192.168.16.5:3306)..
Thu Aug 29 16:03:47 2013 – [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
After starting the management program, wait for the master node failure
Thu Aug 29 16:04:02 2013 – [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Detected MySQL anomaly on 16.5
Thu Aug 29 16:04:02 2013 – [info] Executing SSH check script: save_binary_logs –command=test –start_pos=4 –binlog_dir=/usr/local/mysql/mysql_data/ –output_file=/var/tmp/save_binary_logs_test –manager_version=0.55 –binlog_prefix=mysql-bin
Thu Aug 29 16:04:03 2013 – [info] HealthCheck: SSH to 192.168.16.5 is reachable.
Thu Aug 29 16:04:03 2013 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu Aug 29 16:04:03 2013 – [warning] Connection failed 1 time(s)..
Thu Aug 29 16:04:04 2013 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu Aug 29 16:04:04 2013 – [warning] Connection failed 2 time(s)..
Thu Aug 29 16:04:05 2013 – [warning] Got error on MySQL connect: 2013 (Lost connection to MySQL server at 'reading initial communication packet', system error: 111)
Thu Aug 29 16:04:05 2013 – [warning] Connection failed 3 time(s)..
Thu Aug 29 16:04:05 2013 – [warning] Master is not reachable from health checker!
Thu Aug 29 16:04:05 2013 – [warning] Master 192.168.16.5(192.168.16.5:3306) is not reachable!
Thu Aug 29 16:04:05 2013 – [warning] SSH is reachable.
After 3 login attempts, determined that MySQL is unreachable but SSH is normal
Thu Aug 29 16:04:05 2013 – [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..
Thu Aug 29 16:04:05 2013 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Aug 29 16:04:05 2013 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Thu Aug 29 16:04:05 2013 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Reading configuration file
Thu Aug 29 16:04:05 2013 – [info] Dead Servers:
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Alive Servers:
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.6(192.168.16.6:3306)
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.7(192.168.16.7:3306)
Thu Aug 29 16:04:05 2013 – [info] Alive Slaves:
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.6(192.168.16.6:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:05 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Output current MHA topology status, preparing to rebuild the master node
Thu Aug 29 16:04:05 2013 – [info] Primary candidate for the new Master (candidate_master is set)
No alternative master node set
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:05 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:05 2013 – [info] Checking slave configurations..
Checking slave node configuration
Thu Aug 29 16:04:05 2013 – [info] read_only=1 is not set on slave 192.168.16.6(192.168.16.6:3306).
Thu Aug 29 16:04:05 2013 – [warning] relay_log_purge=0 is not set on slave 192.168.16.6(192.168.16.6:3306).
Thu Aug 29 16:04:05 2013 – [info] read_only=1 is not set on slave 192.168.16.7(192.168.16.7:3306).
Thu Aug 29 16:04:05 2013 – [warning] relay_log_purge=0 is not set on slave 192.168.16.7(192.168.16.7:3306).
Thu Aug 29 16:04:05 2013 – [info] Checking replication filtering settings..
Thu Aug 29 16:04:05 2013 – [info] Replication filtering check ok.
Thu Aug 29 16:04:05 2013 – [info] Master is down!
Thu Aug 29 16:04:05 2013 – [info] Terminating monitoring script.
Thu Aug 29 16:04:05 2013 – [info] Got exit code 20 (Master dead).
Thu Aug 29 16:04:05 2013 – [info] MHA::MasterFailover version 0.55.
Thu Aug 29 16:04:05 2013 – [info] Starting master failover.
Starting failover operation
Thu Aug 29 16:04:05 2013 – [info]
Thu Aug 29 16:04:05 2013 – [info] * Phase 1: Configuration Check Phase..
Step 1: Configuration Check
Thu Aug 29 16:04:05 2013 – [info]
Thu Aug 29 16:04:05 2013 – [info] Dead Servers:
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Checking master reachability via mysql(double check)..
Re-checking 16.5
Thu Aug 29 16:04:05 2013 – [info] ok.
Thu Aug 29 16:04:05 2013 – [info] Alive Servers:
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.6(192.168.16.6:3306)
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.7(192.168.16.7:3306)
Thu Aug 29 16:04:05 2013 – [info] Alive Slaves:
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.6(192.168.16.6:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:05 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:05 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:05 2013 – [info] ** Phase 1: Configuration Check Phase completed.
Thu Aug 29 16:04:05 2013 – [info]
Configuration check complete
Step 2: Shut down the dead master node
Thu Aug 29 16:04:05 2013 – [info] * Phase 2: Dead Master Shutdown Phase..
Thu Aug 29 16:04:05 2013 – [info]
Thu Aug 29 16:04:05 2013 – [info] Forcing shutdown so that applications never connect to the current master..
Thu Aug 29 16:04:05 2013 – [warning] master_ip_failover_script is not set. Skipping invalidating dead master ip address.
Thu Aug 29 16:04:05 2013 – [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Thu Aug 29 16:04:05 2013 – [info] * Phase 2: Dead Master Shutdown Phase completed.
Thu Aug 29 16:04:05 2013 – [info]
master_ip_failover_script is not configured; Step 2 complete
Thu Aug 29 16:04:05 2013 – [info] * Phase 3: Master Recovery Phase..
Thu Aug 29 16:04:05 2013 – [info]
Thu Aug 29 16:04:05 2013 – [info] * Phase 3.1: Getting Latest Slaves Phase..
Selecting the most up-to-date slave
Thu Aug 29 16:04:05 2013 – [info]
Thu Aug 29 16:04:05 2013 – [info] The latest binary log file/position on all slaves is mysql-bin.000005:106
Thu Aug 29 16:04:05 2013 – [info] Latest slaves (Slaves that received relay log files to the latest):
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.6(192.168.16.6:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:05 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:05 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:05 2013 – [info] The oldest binary log file/position on all slaves is mysql-bin.000005:106
Thu Aug 29 16:04:05 2013 – [info] Oldest slaves:
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.6(192.168.16.6:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:05 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:05 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:05 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:05 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:05 2013 – [info]
Thu Aug 29 16:04:05 2013 – [info] * Phase 3.2: Saving Dead Master's Binlog Phase..
Saving binary logs from the original master node
Thu Aug 29 16:04:05 2013 – [info]
Thu Aug 29 16:04:06 2013 – [info] Fetching dead master's binary logs..
Thu Aug 29 16:04:06 2013 – [info] Executing command on the dead master 192.168.16.5(192.168.16.5:3306): save_binary_logs –command=save –start_file=mysql-bin.000005 –start_pos=106 –binlog_dir=/usr/local/mysql/mysql_data/ –output_file=/var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.55
Creating /var/tmp if not exists.. ok.
Concat binary/relay logs from mysql-bin.000005 pos 106 to mysql-bin.000005 EOF into /var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog ..
Dumping binlog format description event, from position 0 to 106.. ok.
Dumping effective binlog data from /usr/local/mysql/mysql_data//mysql-bin.000005 position 106 to tail(125).. ok.
Concat succeeded.
Thu Aug 29 16:04:07 2013 – [info] scp from [email protected]:/var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog to local:/masterha/app1/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog succeeded.
Thu Aug 29 16:04:08 2013 – [info] HealthCheck: SSH to 192.168.16.6 is reachable.
Thu Aug 29 16:04:09 2013 – [info] HealthCheck: SSH to 192.168.16.7 is reachable.
Thu Aug 29 16:04:09 2013 – [info]
Thu Aug 29 16:04:09 2013 – [info] * Phase 3.3: Determining New Master Phase..
Thu Aug 29 16:04:09 2013 – [info]
Thu Aug 29 16:04:09 2013 – [info] Finding the latest slave that has all relay logs for recovering other slaves..
Thu Aug 29 16:04:09 2013 – [info] All slaves received relay logs to the same position. No need to resync each other.
Thu Aug 29 16:04:09 2013 – [info] Searching new master from slaves..
Thu Aug 29 16:04:09 2013 – [info] Candidate masters from the configuration file:
Thu Aug 29 16:04:09 2013 – [info] 192.168.16.6(192.168.16.6:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:09 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:09 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:09 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Thu Aug 29 16:04:09 2013 – [info] Replicating from 192.168.16.5(192.168.16.5:3306)
Thu Aug 29 16:04:09 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Thu Aug 29 16:04:09 2013 – [info] Non-candidate masters:
Thu Aug 29 16:04:09 2013 – [info] Searching from candidate_master slaves which have received the latest relay log events..
Thu Aug 29 16:04:09 2013 – [info] New master is 192.168.16.6(192.168.16.6:3306)
Selected 16.6 as the new master node
Thu Aug 29 16:04:09 2013 – [info] Starting master failover..
Thu Aug 29 16:04:09 2013 – [info]
From:
192.168.16.5 (current master)
+–192.168.16.6
+–192.168.16.7
To:
192.168.16.6 (new master)
+–192.168.16.7
The master-slave structure willcurrent master->new master结构
Thu Aug 29 16:04:09 2013 – [info]
Thu Aug 29 16:04:09 2013 – [info] * Phase 3.3: New Master Diff Log Generation Phase..
Thu Aug 29 16:04:09 2013 – [info]
Thu Aug 29 16:04:09 2013 – [info] This server has all relay logs. No need to generate diff files from the latest slave.
Thu Aug 29 16:04:09 2013 – [info] Sending binlog..
Thu Aug 29 16:04:10 2013 – [info] scp from local:/masterha/app1/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog to [email protected]:/var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog succeeded.
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] * Phase 3.4: Master Log Apply Phase..
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] *NOTICE: If any error happens from this phase, manual recovery is needed.
Thu Aug 29 16:04:10 2013 – [info] Starting recovery on 192.168.16.6(192.168.16.6:3306)..
Thu Aug 29 16:04:10 2013 – [info] Generating diffs succeeded.
Thu Aug 29 16:04:10 2013 – [info] Waiting until all relay logs are applied.
Thu Aug 29 16:04:10 2013 – [info] done.
Thu Aug 29 16:04:10 2013 – [info] Getting slave status..
Thu Aug 29 16:04:10 2013 – [info] This slave(192.168.16.6)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000005:106). No need to recover from Exec_Master_Log_Pos.
Thu Aug 29 16:04:10 2013 – [info] Connecting to the target slave host 192.168.16.6, running recover script..
Thu Aug 29 16:04:10 2013 – [info] Executing command: apply_diff_relay_logs –command=apply –slave_user='root' –slave_host=192.168.16.6 –slave_ip=192.168.16.6 –slave_port=3306 –apply_files=/var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog –workdir=/var/tmp –target_version=5.1.48-log –timestamp=20130829160405 –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.55 –slave_pass=xxx
Thu Aug 29 16:04:10 2013 – [info]
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = "zh_CN:zh",
LC_ALL = (unset),
LC_PAPER = "zh_CN",
LC_ADDRESS = "zh_CN",
LC_MONETARY = "zh_CN",
LC_NUMERIC = "zh_CN",
LC_TELEPHONE = "zh_CN",
LC_IDENTIFICATION = "zh_CN",
LC_MEASUREMENT = "zh_CN",
LC_TIME = "zh_CN",
LC_NAME = "zh_CN",
LANG = "zh_CN.GBK"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Applying differential binary/relay log files /var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog on 192.168.16.6:3306. This may take long time…
Applying log files succeeded.
Thu Aug 29 16:04:10 2013 – [info] All relay logs were successfully applied.
Thu Aug 29 16:04:10 2013 – [info] Getting new master's binlog name and position..
Thu Aug 29 16:04:10 2013 – [info] mysql-bin.000011:106
Thu Aug 29 16:04:10 2013 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.16.6', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=106, MASTER_USER='replicationuser', MASTER_PASSWORD='xxx';
Thu Aug 29 16:04:10 2013 – [warning] master_ip_failover_script is not set. Skipping taking over new master ip address.
Thu Aug 29 16:04:10 2013 – [info] ** Finished master recovery successfully.
Thu Aug 29 16:04:10 2013 – [info] * Phase 3: Master Recovery Phase completed.
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] * Phase 4: Slaves Recovery Phase..
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] * Phase 4.1: Starting Parallel Slave Diff Log Generation Phase..
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] — Slave diff file generation on host 192.168.16.7(192.168.16.7:3306) started, pid: 5323. Check tmp log /masterha/app1/192.168.16.7_3306_20130829160405.log if it takes time..
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] Log messages from 192.168.16.7 …
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] This server has all relay logs. No need to generate diff files from the latest slave.
Thu Aug 29 16:04:10 2013 – [info] End of log messages from 192.168.16.7.
Thu Aug 29 16:04:10 2013 – [info] — 192.168.16.7(192.168.16.7:3306) has the latest relay log events.
Thu Aug 29 16:04:10 2013 – [info] Generating relay diff files from the latest slave succeeded.
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] * Phase 4.2: Starting Parallel Slave Log Apply Phase..
Thu Aug 29 16:04:10 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] — Slave recovery on host 192.168.16.7(192.168.16.7:3306) started, pid: 5325. Check tmp log /masterha/app1/192.168.16.7_3306_20130829160405.log if it takes time..
Thu Aug 29 16:04:12 2013 – [info]
Thu Aug 29 16:04:12 2013 – [info] Log messages from 192.168.16.7 …
Thu Aug 29 16:04:12 2013 – [info]
Thu Aug 29 16:04:10 2013 – [info] Sending binlog..
Thu Aug 29 16:04:11 2013 – [info] scp from local:/masterha/app1/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog to [email protected]:/var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog succeeded.
Thu Aug 29 16:04:11 2013 – [info] Starting recovery on 192.168.16.7(192.168.16.7:3306)..
Thu Aug 29 16:04:11 2013 – [info] Generating diffs succeeded.
Thu Aug 29 16:04:11 2013 – [info] Waiting until all relay logs are applied.
Thu Aug 29 16:04:11 2013 – [info] done.
Thu Aug 29 16:04:11 2013 – [info] Getting slave status..
Thu Aug 29 16:04:11 2013 – [info] This slave(192.168.16.7)'s Exec_Master_Log_Pos equals to Read_Master_Log_Pos(mysql-bin.000005:106). No need to recover from Exec_Master_Log_Pos.
Thu Aug 29 16:04:11 2013 – [info] Connecting to the target slave host 192.168.16.7, running recover script..
Thu Aug 29 16:04:11 2013 – [info] Executing command: apply_diff_relay_logs –command=apply –slave_user='root' –slave_host=192.168.16.7 –slave_ip=192.168.16.7 –slave_port=3306 –apply_files=/var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog –workdir=/var/tmp –target_version=5.1.48-log –timestamp=20130829160405 –handle_raw_binlog=1 –disable_log_bin=0 –manager_version=0.55 –slave_pass=xxx
Thu Aug 29 16:04:12 2013 – [info]
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
LANGUAGE = "zh_CN:zh",
LC_ALL = (unset),
LC_PAPER = "zh_CN",
LC_ADDRESS = "zh_CN",
LC_MONETARY = "zh_CN",
LC_NUMERIC = "zh_CN",
LC_TELEPHONE = "zh_CN",
LC_IDENTIFICATION = "zh_CN",
LC_MEASUREMENT = "zh_CN",
LC_TIME = "zh_CN",
LC_NAME = "zh_CN",
LANG = "zh_CN.GBK"
are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Applying differential binary/relay log files /var/tmp/saved_master_binlog_from_192.168.16.5_3306_20130829160405.binlog on 192.168.16.7:3306. This may take long time…
Applying log files succeeded.
Thu Aug 29 16:04:12 2013 – [info] All relay logs were successfully applied.
Thu Aug 29 16:04:12 2013 – [info] Resetting slave 192.168.16.7(192.168.16.7:3306) and starting replication from the new master 192.168.16.6(192.168.16.6:3306)..
Thu Aug 29 16:04:12 2013 – [info] Executed CHANGE MASTER.
Thu Aug 29 16:04:12 2013 – [info] Slave started.
Thu Aug 29 16:04:12 2013 – [info] End of log messages from 192.168.16.7.
Thu Aug 29 16:04:12 2013 – [info] — Slave recovery on host 192.168.16.7(192.168.16.7:3306) succeeded.
Thu Aug 29 16:04:12 2013 – [info] All new slave servers recovered successfully.
Thu Aug 29 16:04:12 2013 – [info]
Thu Aug 29 16:04:12 2013 – [info] * Phase 5: New master cleanup phase..
Thu Aug 29 16:04:12 2013 – [info]
Thu Aug 29 16:04:12 2013 – [info] Resetting slave info on the new master..
Thu Aug 29 16:04:12 2013 – [info] 192.168.16.6: Resetting slave info succeeded.
Thu Aug 29 16:04:12 2013 – [info] Master failover to 192.168.16.6(192.168.16.6:3306) completed successfully.
Thu Aug 29 16:04:12 2013 – [info]
—– Failover Report —–
app1: MySQL Master failover 192.168.16.5 to 192.168.16.6 succeeded
Master 192.168.16.5 is down!
Check MHA Manager logs at ubuntu:/masterha/app1/manager.log for details.
Started automated(non-interactive) failover.
The latest slave 192.168.16.6(192.168.16.6:3306) has all relay logs for recovery.
Selected 192.168.16.6 as a new master.
192.168.16.6: OK: Applying all logs succeeded.
192.168.16.7: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.16.7: OK: Applying all logs succeeded. Slave started, replicating from 192.168.16.6.
192.168.16.6: Resetting slave info succeeded.
Master failover to 192.168.16.6(192.168.16.6:3306) completed successfully.
The logs are very detailed and conclude with a failover report:
Selected 192.168.16.6 as a new master.
Master failover to 192.168.16.6(192.168.16.6:3306) completed successfully.
The entire process took from 16:03:47 to 16:04:12 — less than 1 minute!
4.2 Data Integrity and Availability Testing
Simple testing confirmed data integrity and availability. Further testing will be conducted during the business testing phase.
4.3 IP Address Takeover
Modify the configuration file to addadd master_ip_failover_script脚本
vi /etc/masterha/app1.cnf"
add master_ip_failover_script="/masterha/scripts/master_ip_failover"
Error when starting the management node process:
Thu Aug 29 17:08:17 2013 – [info] /masterha/scripts/master_ip_failover –command=status –ssh_user=root –orig_master_host=192.168.16.6 –orig_master_ip=192.168.16.6 –orig_master_port=3306
Bareword "FIXME_xxx" not allowed while "strict subs" in use at /masterha/scripts/master_ip_failover line 93.
Execution of /masterha/scripts/master_ip_failover aborted due to compilation errors.
Thu Aug 29 17:08:17 2013 – [error][/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm, ln216] Failed to get master_ip_failover_script status with return code 255:0.
Need to modify the /masterha/scripts/master_ip_failover template as follows:
Write the IP address switchover script:
root@ubuntu:/masterha/app1# more /masterha/scripts/master_ip_failover
#!/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.16.9/24’; # Virtual IP
my $key = “1”;
my $ssh_start_vip = “/sbin/ifconfig eth0:$key $vip”;
my $ssh_stop_vip = “/sbin/ifconfig eth0:$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” ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
My $exit_code = 1;
eval {
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” ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
My $exit_code = 10;
eval {
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/@$orig_master_host /” $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() {
`ssh $ssh_user/@$orig_master_host /” $ssh_stop_vip /”`;
}
sub usage {
“Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=po
rt –new_master_host=host –new_master_ip=ip –new_master_port=port/n”;
}
root@ubuntu:/masterha/app1#
Log analysis after switchover:
n:enabled
Fri Aug 30 08:50:44 2013 – [info] Replicating from 192.168.16.6(192.168.16.6:3306)
Fri Aug 30 08:50:44 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Fri Aug 30 08:50:44 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Fri Aug 30 08:50:44 2013 – [info] Replicating from 192.168.16.6(192.168.16.6:3306)
Fri Aug 30 08:50:44 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Fri Aug 30 08:50:44 2013 – [info] ** Phase 1: Configuration Check Phase completed.
Fri Aug 30 08:50:44 2013 – [info]
Fri Aug 30 08:50:44 2013 – [info] * Phase 2: Dead Master Shutdown Phase..
Fri Aug 30 08:50:44 2013 – [info]
Fri Aug 30 08:50:44 2013 – [info] Forcing shutdown so that applications never connect to the current master..
Fri Aug 30 08:50:44 2013 – [info] Executing master IP deactivatation script:
Fri Aug 30 08:50:44 2013 – [info] /masterha/scripts/master_ip_failover –orig_master_host=192.168.16.6 –orig_master_ip=192.168.16.6 –orig_master_port=3306 –command=stopssh –ssh_user=root
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.16.9/24===
Disabling the VIP on old master: 192.168.16.6
Fri Aug 30 08:50:44 2013 – [info] done.
Fri Aug 30 08:50:44 2013 – [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Fri Aug 30 08:50:44 2013 – [info] * Phase 2: Dead Master Shutdown Phase completed.
Fri Aug 30 08:50:44 2013 – [info]
Fri Aug 30 08:50:44 2013 – [info] * Phase 3: Master Recovery Phase..
Fri Aug 30 08:50:44 2013 – [info]
Fri Aug 30 08:50:44 2013 – [info] * Phase 3.1: Getting Latest Slaves Phase..
Fri Aug 30 08:50:44 2013 – [info]
Fri Aug 30 08:50:44 2013 – [info] The latest binary log file/position on all slaves is mysql-bin.000011:106
Fri Aug 30 08:50:44 2013 – [info] Latest slaves (Slaves that received relay log files to the latest):
Fri Aug 30 08:50:44 2013 – [info] 192.168.16.5(192.168.16.5:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Stop the floating IP 16.9 on the original master node
Applying differential binary/relay log files /var/tmp/saved_master_binlog_from_192.168.16.6_3306_20130830085043.binlog on 192.168.16.5:3306. This may take long time…
Applying log files succeeded.
Fri Aug 30 08:50:49 2013 – [info] All relay logs were successfully applied.
Fri Aug 30 08:50:49 2013 – [info] Getting new master’s binlog name and position..
Fri Aug 30 08:50:49 2013 – [info] mysql-bin.000006:106
Fri Aug 30 08:50:49 2013 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.16.5’, MASTER_PORT=3306, MASTER_LOG_FILE=’mysql-bin.000006’, MASTER_LOG_POS=106, MASTER_USER=’replicationuser’, MASTER_PASSWORD=’xxx’;
Fri Aug 30 08:50:49 2013 – [info] Executing master IP activate script:
Fri Aug 30 08:50:49 2013 – [info] /masterha/scripts/master_ip_failover –command=start –ssh_user=root –orig_master_host=192.168.16.6 –orig_master_ip=192.168.16.6 –orig_master_port=3306 –new_master_host=192.168.16.5 –new_master_ip=192.168.16.5 –new_master_port=3306 –new_master_user=’root’–new_master_password=’sztf@yunwei’
Unknown option: new_master_user
Unknown option: new_master_password
IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 192.168.16.9/24===
Enabling the VIP – 192.168.16.9/24 on the new master – 192.168.16.5
Fri Aug 30 08:50:49 2013 – [info] OK.
Fri Aug 30 08:50:49 2013 – [info] ** Finished master recovery successfully.
Fri Aug 30 08:50:49 2013 – [info] * Phase 3: Master Recovery Phase completed.
Fri Aug 30 08:50:49 2013 – [info]
Fri Aug 30 08:50:49 2013 – [info] * Phase 4: Slaves Recovery Phase..
Start VIP 16.9 on the new master node
Check the IP address on the new node:
root@ubuntu:~# ifconfig -a
eth0 Link encap:Ethernet HWaddr 94:de:80:18:11:82
inet addr:192.168.16.5 Bcast:192.168.16.255 Mask:255.255.255.0
inet6 addr: fe80::96de:80ff:fe18:1182/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:19122846 errors:0 dropped:58 overruns:0 frame:0
TX packets:241270 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:1644186729 (1.6 GB) TX bytes:40646216 (40.6 MB)
Interrupt:40 基本addr:0x8000
eth0:1 Link encap:Ethernet HWaddr 94:de:80:18:11:82
inet addr:192.168.16.9 Bcast:192.168.16.255 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
Interrupt:40 基本addr:0x8000
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:16436 Metric:1
RX packets:3169361 errors:0 dropped:0 overruns:0 frame:0
TX packets:3169361 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:158507432 (158.5 MB) TX bytes:158507432 (158.5 MB)
root@ubuntu:~#
vipAlready switched normally
4.4Manual switchover test
Switchover command:
masterha_master_switch –conf=/etc/masterha/app1.cnf –master_state=alive
Output as follows:
root@ubuntu:~# masterha_master_switch –conf=/etc/masterha/app1.cnf –master_state=alive
Fri Aug 30 10:21:29 2013 – [info] MHA::MasterRotate version 0.55.
Fri Aug 30 10:21:29 2013 – [info] Starting online master switch..
Fri Aug 30 10:21:29 2013 – [info]
Fri Aug 30 10:21:29 2013 – [info] * Phase 1: Configuration Check Phase..
Fri Aug 30 10:21:29 2013 – [info]
Fri Aug 30 10:21:29 2013 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Aug 30 10:21:29 2013 – [info] Reading application default configurations from /etc/masterha/app1.cnf..
Fri Aug 30 10:21:29 2013 – [info] Reading server configurations from /etc/masterha/app1.cnf..
Fri Aug 30 10:21:29 2013 – [info] Current Alive Master: 192.168.16.6(192.168.16.6:3306)
Fri Aug 30 10:21:29 2013 – [info] Alive Slaves:
Fri Aug 30 10:21:29 2013 – [info] 192.168.16.5(192.168.16.5:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Fri Aug 30 10:21:29 2013 – [info] Replicating from 192.168.16.6(192.168.16.6:3306)
Fri Aug 30 10:21:29 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Fri Aug 30 10:21:29 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Fri Aug 30 10:21:29 2013 – [info] Replicating from 192.168.16.6(192.168.16.6:3306)
Fri Aug 30 10:21:29 2013 – [info] Primary candidate for the new Master (candidate_master is set)
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 192.168.16.6(192.168.16.6:3306)? (YES/no): yes
Fri Aug 30 10:21:44 2013 – [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Fri Aug 30 10:21:44 2013 – [info] ok.
Fri Aug 30 10:21:44 2013 – [info] Checking MHA is not monitoring or doing failover..
Fri Aug 30 10:21:44 2013 – [info] Checking replication health on 192.168.16.5..
Fri Aug 30 10:21:44 2013 – [info] ok.
Fri Aug 30 10:21:44 2013 – [info] Checking replication health on 192.168.16.7..
Fri Aug 30 10:21:44 2013 – [info] ok.
Fri Aug 30 10:21:44 2013 – [info] Searching new master from slaves..
Fri Aug 30 10:21:44 2013 – [info] Candidate masters from the configuration file:
Fri Aug 30 10:21:44 2013 – [info] 192.168.16.5(192.168.16.5:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Fri Aug 30 10:21:44 2013 – [info] Replicating from 192.168.16.6(192.168.16.6:3306)
Fri Aug 30 10:21:44 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Fri Aug 30 10:21:44 2013 – [info] 192.168.16.6(192.168.16.6:3306) Version=5.1.48-log log-bin:enabled
Fri Aug 30 10:21:44 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bin:enabled
Fri Aug 30 10:21:44 2013 – [info] Replicating from 192.168.16.6(192.168.16.6:3306)
Fri Aug 30 10:21:44 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Fri Aug 30 10:21:44 2013 – [info] Non-candidate masters:
Fri Aug 30 10:21:44 2013 – [info] Searching from candidate_master slaves which have received the latest relay log events..
Fri Aug 30 10:21:44 2013 – [info]
From:
192.168.16.6 (current master)
+–192.168.16.5
+–192.168.16.7
To:
192.168.16.5 (new master)
+–192.168.16.7
Starting master switch from 192.168.16.6(192.168.16.6:3306) to 192.168.16.5(192.168.16.5:3306)? (yes/NO): yes
Fri Aug 30 10:21:50 2013 – [info] Checking whether 192.168.16.5(192.168.16.5:3306) is ok for the new master..
Fri Aug 30 10:21:50 2013 – [info] ok.
Fri Aug 30 10:21:50 2013 – [info] ** Phase 1: Configuration Check Phase completed.
Fri Aug 30 10:21:50 2013 – [info]
Fri Aug 30 10:21:50 2013 – [info] * Phase 2: Rejecting updates Phase..
Fri Aug 30 10:21:50 2013 – [info]
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Fri Aug 30 10:22:15 2013 – [info] Locking all tables on the orig master to reject updates from everybody (including root):
Fri Aug 30 10:22:15 2013 – [info] Executing FLUSH TABLES WITH READ LOCK..
Fri Aug 30 10:22:15 2013 – [info] ok.
Fri Aug 30 10:22:15 2013 – [info] Orig master binlog:pos is mysql-bin.000012:106.
Fri Aug 30 10:22:15 2013 – [info] Waiting to execute all relay logs on 192.168.16.5(192.168.16.5:3306)..
Fri Aug 30 10:22:15 2013 – [info] master_pos_wait(mysql-bin.000012:106) completed on 192.168.16.5(192.168.16.5:3306). Executed 0 events.
Fri Aug 30 10:22:15 2013 – [info] done.
Fri Aug 30 10:22:15 2013 – [info] Getting new master's binlog name and position..
Fri Aug 30 10:22:15 2013 – [info] mysql-bin.000004:106
Fri Aug 30 10:22:15 2013 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.16.5', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=106, MASTER_USER='replicationuser', MASTER_PASSWORD='xxx';
Fri Aug 30 10:22:15 2013 – [info]
Fri Aug 30 10:22:15 2013 – [info] * Switching slaves in parallel..
Fri Aug 30 10:22:15 2013 – [info]
Fri Aug 30 10:22:15 2013 – [info] — Slave switch on host 192.168.16.7(192.168.16.7:3306) started, pid: 10166
Fri Aug 30 10:22:15 2013 – [info]
Fri Aug 30 10:22:15 2013 – [info] Log messages from 192.168.16.7 …
Fri Aug 30 10:22:15 2013 – [info]
Fri Aug 30 10:22:15 2013 – [info] Waiting to execute all relay logs on 192.168.16.7(192.168.16.7:3306)..
Fri Aug 30 10:22:15 2013 – [info] master_pos_wait(mysql-bin.000012:106) completed on 192.168.16.7(192.168.16.7:3306). Executed 0 events.
Fri Aug 30 10:22:15 2013 – [info] done.
Fri Aug 30 10:22:15 2013 – [info] Resetting slave 192.168.16.7(192.168.16.7:3306) and starting replication from the new master 192.168.16.5(192.168.16.5:3306)..
Fri Aug 30 10:22:15 2013 – [info] Executed CHANGE MASTER.
Fri Aug 30 10:22:15 2013 – [info] Slave started.
Fri Aug 30 10:22:15 2013 – [info] End of log messages from 192.168.16.7 …
Fri Aug 30 10:22:15 2013 – [info]
Fri Aug 30 10:22:15 2013 – [info] — Slave switch on host 192.168.16.7(192.168.16.7:3306) succeeded.
Fri Aug 30 10:22:15 2013 – [info] Unlocking all tables on the orig master:
Fri Aug 30 10:22:15 2013 – [info] Executing UNLOCK TABLES..
Fri Aug 30 10:22:15 2013 – [info] ok.
Fri Aug 30 10:22:15 2013 – [info] All new slave servers switched successfully.
Fri Aug 30 10:22:15 2013 – [info]
Fri Aug 30 10:22:15 2013 – [info] * Phase 5: New master cleanup phase..
Fri Aug 30 10:22:15 2013 – [info]
Fri Aug 30 10:22:16 2013 – [info] 192.168.16.5: Resetting slave info succeeded.
Fri Aug 30 10:22:16 2013 – [info] Switching master to 192.168.16.5(192.168.16.5:3306) completed successfully.
Key points:
From:
192.168.16.6 (current master)
+–192.168.16.5
+–192.168.16.7
To:
192.168.16.5 (new master)
+–192.168.16.7
After switchover, the database on 16.6 will be shut down
4.5 Shutdown Test (Directly Shut Down the Host Running the Master Database)
Shutdown command:
root@ubuntu:~# reboot
Broadcast message from root@ubuntu
(/dev/pts/2) 于 18:25 …
The system is going down for reboot now!
root@ubuntu:~#
Test results:
After the master server shuts down, the management program will promote one of the slave nodes to master and bind the IP address to the new master node.
4.6 About Priority Weight Settings
Current status:
192.168.16.6 (current master)
+–192.168.16.5
+–192.168.16.7
We expect the switchover to go to 16.7 (by default, if all weights are equal, it should switch to 16.5 because server id 5 comes first). Modify the configuration as follows:
[server default]
manager_workdir=/masterha/app1
manager_log=/masterha/app1/manager.log
user=root
password=sztf@yunwei
ssh_user=root
repl_user=replicationuser
repl_password=replicationuser
ping_interval=1
shutdown_script=""
#master_ip_failover_script=''
master_ip_failover_script="/masterha/scripts/master_ip_failover"
master_ip_online_change_script=""
report_script=""
[server1]
hostname=192.168.16.5
master_binlog_dir="/usr/local/mysql/mysql_data/"
[server2]
hostname=192.168.16.6
master_binlog_dir="/usr/local/mysql/mysql_data/"
[server3]
hostname=192.168.16.7
candidate_master=1
master_binlog_dir="/usr/local/mysql/mysql_data/"
~
Set the weight of 16.7 to 1, remove the weights of 16.5 and 16.6, clear the app1.failover.complete file, and stop the 16.6 database. Commands are as follows:
root@ubuntu:~# /usr/local/mysql/bin/mysql.server.sh stop
Shutting down MySQL…… *
root@ubuntu:~#
Excerpt from log analysis:
Fri Aug 30 19:18:32 2013 – [info] * Phase 3.3: Determining New Master Phase..
Fri Aug 30 19:18:32 2013 – [info]
Fri Aug 30 19:18:32 2013 – [info] Finding the latest slave that has all relay logs for recovering other slaves..
Fri Aug 30 19:18:32 2013 – [info] All slaves received relay logs to the same position. No need to resync each other.
Fri Aug 30 19:18:32 2013 – [info] Searching new master from slaves..
Fri Aug 30 19:18:32 2013 – [info] Candidate masters from the configuration file:
Fri Aug 30 19:18:32 2013 – [info] 192.168.16.7(192.168.16.7:3306) Version=5.1.48-log (oldest major version between slaves) log-bi
n:enabled
Fri Aug 30 19:18:32 2013 – [info] Replicating from 192.168.16.6(192.168.16.6:3306)
Fri Aug 30 19:18:32 2013 – [info] Primary candidate for the new Master (candidate_master is set)
Fri Aug 30 19:18:32 2013 – [info] Non-candidate masters:
Fri Aug 30 19:18:32 2013 – [info] Searching from candidate_master slaves which have received the latest relay log events..
Fri Aug 30 19:18:32 2013 – [info] New master is 192.168.16.7(192.168.16.7:3306)
Fri Aug 30 19:18:32 2013 – [info] Starting master failover..
Fri Aug 30 19:18:32 2013 – [info]
From:
192.168.16.6 (current master)
+–192.168.16.5
+–192.168.16.7
To:
192.168.16.7 (new master)
+–192.168.16.5
16.7 was directly selected from the configuration file. Continue checking the subsequent logs to see if the latest relay logs were received.
The official documentation explains these two parameters as follows:
You might use different kinds of machines between slaves, and want to promote the most reliable machine to the new master (i.e. promoting a RAID1+0 slave rather than RAID0 slaves).
By setting candidate_master to 1, the server is prioritized to the new master, as long as it meets conditions to be the new master (i.e. binary log is enabled, it does not delay replication significantly, etc). So candidate_master=1 does not mean that the specified host always becomes new master when the current master crashes, but is helpful to set priority.
If you set candidate_master=1 on multiple servers, priority is decided by sort order by block name ([server_xxx]). [server_1] will have higher priority than [server_2].
By setting no_master=1 on the target server, the server never becomes the new master. This is useful if you have some servers that should not become the new master. For example, you may want to set no_master=1 when you run slaves on unreliable (RAID0) machine, or when you run a slave at a remote data center. Note that if none of the slaves can be new master, MHA aborts and does not start monitoring/failover.
That is, by setting candidate_master=1, the server gets priority to become the new master, provided it meets the conditions (i.e., binary logging is enabled and there is no significant replication lag). So candidate_master=1 does not mean the designated host will always become the new master when the master crashes, but setting the priority is helpful.
If you do not want a machine to become the new master, you can achieve this by setting no_master=1.
4.7 About MHA’s Detection Mechanism
I did not find much valuable information in the official documentation; only the following descriptions were found:
This parameter states how often MHA Manager pings(executes ping SQL statement) the master. After missing three connection intervals in a row, MHA Manager decides that the MySQL master is dead. Thus, the maximum time for discovering a failure through the ping mechanism is four times the ping interval. The default is 3 (3 seconds).
If MHA Manager fails to connect by too many connections or authentication errors, it doesn't count that the master is dead.
(Supported from 0.53) By default, MHA establishes a persistent connection to a master and checks master's availability by executing "SELECT 1" (ping_type=SELECT). But in some cases, it is better to check by connecting/disconnecting every time, because it's more strict and it can detect TCP connection level failure more quickly. Setting ping_type=CONNECT makes it possible.
When MHA Manager is successfully monitoring the MySQL master, status code (exit code) 0 should be returned like above.
All status codes and descriptions are listed below.
|
Status Code(Exit code) |
Status String |
Description |
|
0 |
PING_OK |
Master is running and MHA Manager is monitoring. Master state is alive. |
|
1 |
— |
Unexpected error happened. For example, config file does not exist. If this error happens, check arguments are valid or not. |
|
2 |
NOT_RUNNING |
MHA Manager is not running. Master state is unknown. |
|
3 |
PARTIALLY_RUNNING |
MHA Manager main process is not running, but child processes are running. This should not happen and should be investigated. Master state is unknown. |
|
10 |
INITIALIZING_MONITOR |
MHA Manager is just after startup and initializing. Wait for a while and see how the status changes. Master state is unknown. |
|
20 |
PING_FAILING |
MHA Manager detects ping to master is failing. Master state is maybe down. |
|
21 |
PING_FAILED |
MHA Manager detects either a) ping to master failed three times, b) preparing for starting master failover. Master state is maybe down. |
|
30 |
RETRYING_MONITOR |
MHA Manager internal health check program detected that master was not reachable from manager, but after double check MHA Manager verified the master is alive, and currently waiting for retry. Master state is very likely alive. |
|
31 |
CONFIG_ERROR |
There are some configuration problems and MHA Manager can't monitor the target master. Check a logfile for detail. Master state is unknown. |
|
32 |
TIMESTAMP_OLD |
MHA Manager detects that ping to master is ok but status file is not updated for a long time. Check whether MHA Manager itself hangs or not. Master state is unknown. |
|
50 |
FAILOVER_RUNNING |
MHA Manager confirms that master is down and running failover. Master state is dead. |
|
51 |
FAILOVER_ERROR |
MHA Manager confirms that master is down and running failover, but failed during failover. Master state is dead. |
Based on the above evidence, MHA likely establishes a persistent connection with the master node and executes the following SQL statements to check database status including process status, and uses ping commands to check host status. This is just my personal speculation.
Answers to several questions
Q1: For automatic switchover, what method did you use to make the original master fail?
Shut down the database and power off the host. Commands are as follows:
/usr/local/mysql/bin/mysql.server.sh stop
Mysqladmin –uroot –p shutdown
reboot
2 : manual switchover, Can you specify which server becomes the new master?master ?
You can give a server higher priority by setting its weight, but it is not always guaranteed.
3: I see you mentioned keepalived , Whether it was ultimately not used, But instead used MHA built-in vipmanagement features ?
usedMHAPerfected using the built-in template.
Q4: For modifying weights — you just modify the priority level for slave-to-master promotion. Where is this modified? Can you completely prevent a slave from being promoted to master?
Modify the configuration file on the management node and set no_master=1
问题5: 7.Manual switchover requires defining firstmaster_ip_online_change_script脚本 — If not defined, What are the consequences ?
If not defined, only the database switchover occurs, whileipaddrWill not be taken over
Q6: What are the detection points for master failure detection?
No definitive answer was found in the official documentation. From code analysis, due to the complexity of the code, I have not been able to find many clues for now.
root@ubuntu:/masterha/app1#vi /opt/mha4mysql-manager-0.55/bin/masterha_master_monitor
use strict;
use warnings FATAL => 'all';
use English qw(-no_match_vars);
use Getopt::Long qw(:config pass_through);
use Pod::Usage;
use MHA::MasterMonitor;
use MHA::ManagerConst;
my $help;
my $version;
$| = 1;
GetOptions(
'help' => /$help,
'version' => /$version,
);
if ($version) {
print "masterha_master_monitor version $MHA::ManagerConst::VERSION./n";
exit 0;
}
if ($help) {
pod2usage(0);
}
my ( $exit_code, $dead_master, $ssh_reachable ) =
MHA::MasterMonitor::main( "–monitor_only", @ARGV );
if ( $dead_master->{hostname} ) {
print "Master $dead_master->{hostname} is dead!/n";
print "IP Address: $dead_master->{ip} ";
print "Port: $dead_master->{port}/n";
}
if ( $exit_code && $exit_code eq "0" ) {
if ($ssh_reachable) {
print "SSH: reachable/n";
}
else {
print "SSH: NOT reachable/n";
}
exit 0;
}
exit 1 if ( !defined($exit_code) );
exit $exit_code;
Search for the MasterMonitor package file; it is defined in the following file:
/usr/local/share/perl/5.14.2/MHA/MasterMonitor.pm
Which in turn calls
use MHA::ServerManager;
use MHA::HealthCheck;
use MHA::FileStatus;
use MHA::SSHCheck;
View the /usr/local/share/perl/5.14.2/MHA/HealthCheck.pm file
The code is quite long, but you can see some MySQL login and SSH information:
my $log = $self->{logger};
$self->{dbh} = DBI->connect(
"DBI:mysql:;host=$self->{ip};"
. "port=$self->{port};mysql_connect_timeout=$connect_timeout",
$self->{user},
$self->{password},
{ PrintError => 0, RaiseError => $raise_error }
);
sub invoke_ssh_check {
my $self = shift;
my $log = $self->{logger};
if ( !$self->{_ssh_check_invoked} ) {
if ( $self->{_ssh_check_pid} = fork ) {
$self->{_ssh_check_invoked} = 1;
}
elsif ( defined $self->{_ssh_check_pid} ) {
$SIG{INT} = $SIG{HUP} = $SIG{QUIT} = $SIG{TERM} = "DEFAULT";
$log->info("Executing SSH check script: $self->{ssh_check_command}");
#child ssh check process
exit ssh_check(
$self->{ssh_user}, $self->{ssh_host},
$self->{ssh_ip}, $self->{ssh_port},
$self->{logger}, $self->{ssh_connection_timeout},
$self->{ssh_check_command}
);
}
else {
croak "Forking SSH check process failed. Can't continue operation./n";
}
}
}