Blog post has been done using MySQL 5.6.11 (binary release i.e. Linux – Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive), Oracle Enterprise Linux 6.4 64 bits. In below server1.domain.com is my master server and server2.domain.com the slave one. They are both virtual machine using a non routable IP.
MySQL Utilities
Installation
First start by downloading MySQL Utilities, I have taken the source code then Generic Linux (Architecture Independent), Compressed TAR Archive edition. Then installation is as simple as:
You obviously need Python on your server (python-2.6.6-36.el6.x86_64 in mycase) and the Python MySQL library (mysql-connector-python-1.0.8-1.el6.noarch) as I got the below error:
ImportError: No module named connector
Usage
Checks replication prerequisites:
[mysql@server1 ~]$ mysqlrplcheck --master=yjaquier:secure_password@server1.domain.com:3326--slave=yjaquier:secure_password@server2.domain.com:3326# master on server1.domain.com: ... connected.# slave on server2.domain.com: ... connected.
Test Description Status
---------------------------------------------------------------------------
Checking for binary logging on master [pass]
Are there binlog exceptions? [pass]
Replication user exists? [pass]
Checking server_id values [pass]
Checking server_uuid values [pass]
Is slave connected to master? [pass]
Check master information file[pass]
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Checking slave delay (seconds behind master)[pass]# ...done.
[mysql@server1 ~]$ mysqlrplcheck --master=yjaquier:secure_password@server1.domain.com:3326 --slave=yjaquier:secure_password@server2.domain.com:3326
# master on server1.domain.com: ... connected.
# slave on server2.domain.com: ... connected.
Test Description Status
---------------------------------------------------------------------------
Checking for binary logging on master [pass]
Are there binlog exceptions? [pass]
Replication user exists? [pass]
Checking server_id values [pass]
Checking server_uuid values [pass]
Is slave connected to master? [pass]
Check master information file [pass]
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Checking slave delay (seconds behind master) [pass]
# ...done.
[mysql@server1 ~]$ mysqlrpladmin --master=yjaquier:secure_password@server1.domain.com:3326--slaves=yjaquier:secure_password@server2.domain.com:3326 health
# Checking privileges.## Replication Topology Health:
+---------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+---------+
| server1.domain.com |3326| MASTER | UP | ON | OK || server2.domain.com |3326| SLAVE | UP | ON | OK |
+---------------------+-------+---------+--------+------------+---------+
# ...done.
[mysql@server1 ~]$ mysqlrpladmin --master=yjaquier:secure_password@server1.domain.com:3326 --slaves=yjaquier:secure_password@server2.domain.com:3326 health
# Checking privileges.
#
# Replication Topology Health:
+---------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+---------+
| server1.domain.com | 3326 | MASTER | UP | ON | OK |
| server2.domain.com | 3326 | SLAVE | UP | ON | OK |
+---------------------+-------+---------+--------+------------+---------+
# ...done.
Interactive health monitoring and failover:
[mysql@server1 ~]$ mysqlfailover --master=yjaquier:secure_password@server1.domain.com:3326--discover-slaves-login=yjaquier:secure_password --rediscover
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Fri Jun 717:26:372013
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 396
GTID Executed Set
770d3753-c6e4-11e2-8e78-080027d93e15:1
Replication Health Status
+---------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+---------+
| server1.domain.com |3326| MASTER | UP | ON | OK || server2.domain.com |3326| SLAVE | UP | ON | OK |
+---------------------+-------+---------+--------+------------+---------+
Q-quit R-refresh H-health G-GTID Lists U-UUIDs
[mysql@server1 ~]$ mysqlfailover --master=yjaquier:secure_password@server1.domain.com:3326 --discover-slaves-login=yjaquier:secure_password --rediscover
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Fri Jun 7 17:26:37 2013Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 396GTID Executed Set
770d3753-c6e4-11e2-8e78-080027d93e15:1Replication Health Status
+---------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+---------+
| server1.domain.com | 3326 | MASTER | UP | ON | OK |
| server2.domain.com | 3326 | SLAVE | UP | ON | OK |
+---------------------+-------+---------+--------+------------+---------+Q-quit R-refresh H-health G-GTID Lists U-UUIDs
Remark: In this release (1.3.1) mysqlfailover tool is really CPU intensive so better not to forget it running in a terminal…
As a crash test if you stop master instance (clean shutdown or not), you get:
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Fri Jun 717:34:492013
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 396
GTID Executed Set
770d3753-c6e4-11e2-8e78-080027d93e15:1
Replication Health Status
+---------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+---------+
| server2.domain.com |3326| MASTER | UP | ON | OK |
+---------------------+-------+---------+--------+------------+---------+
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Fri Jun 7 17:34:49 2013Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 396GTID Executed Set
770d3753-c6e4-11e2-8e78-080027d93e15:1Replication Health Status
+---------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+---------+
| server2.domain.com | 3326 | MASTER | UP | ON | OK |
+---------------------+-------+---------+--------+------------+---------+
Then add it back with (you can also use CHANGE MASTER SQL command):
[mysql@server1 ~]$ mysqlreplicate --master=yjaquier:secure_password@server2.domain.com:3326--slave=yjaquier:secure_password@server1.domain.com:3326--rpl-user=repadmin:secure_password
# master on server2.domain.com: ... connected.# slave on server1.domain.com: ... connected.# Checking for binary logging on master...# Setting up replication...# ...done.
[mysql@server1 ~]$ mysqlreplicate --master=yjaquier:secure_password@server2.domain.com:3326 --slave=yjaquier:secure_password@server1.domain.com:3326 --rpl-user=repadmin:secure_password
# master on server2.domain.com: ... connected.
# slave on server1.domain.com: ... connected.
# Checking for binary logging on master...
# Setting up replication...
# ...done.
You now have:
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Fri Jun 717:37:142013
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 396
GTID Executed Set
770d3753-c6e4-11e2-8e78-080027d93e15:1
Replication Health Status
+---------------------+-------+---------+--------+------------+-----------------------------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+-----------------------------+
| server2.domain.com |3326| MASTER | UP | ON | OK || server1.domain.com |3326| SLAVE | UP | ON | SQL thread is not running. |
+---------------------+-------+---------+--------+------------+-----------------------------+
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Fri Jun 7 17:37:14 2013Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 396GTID Executed Set
770d3753-c6e4-11e2-8e78-080027d93e15:1Replication Health Status
+---------------------+-------+---------+--------+------------+-----------------------------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+-----------------------------+
| server2.domain.com | 3326 | MASTER | UP | ON | OK |
| server1.domain.com | 3326 | SLAVE | UP | ON | SQL thread is not running. |
+---------------------+-------+---------+--------+------------+-----------------------------+
So, strangely mysqlreplicate does not automatically start the slave SQL thread. So tried another MySQL Utilities tool:
[mysql@server1 ~]$ mysqlrpladmin --master=yjaquier:secure_password@server2.domain.com:3326--slaves=yjaquier:secure_password@server1.domain.com:3326 start
# Checking privileges.# Performing START on all slaves.# Executing start on slave server1.domain.com:3326 Ok# ...done.
[mysql@server1 ~]$ mysqlrpladmin --master=yjaquier:secure_password@server2.domain.com:3326 --slaves=yjaquier:secure_password@server1.domain.com:3326 start
# Checking privileges.
# Performing START on all slaves.
# Executing start on slave server1.domain.com:3326 Ok
# ...done.
But not working 🙁 so basic START SLAVE SQL command… I’m wondering if mysqlreplicate use mysqlrpladmin in the background, that could explain why both tools fail to start slave SQL thread…
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Fri Jun 717:49:372013
Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 396
GTID Executed Set
770d3753-c6e4-11e2-8e78-080027d93e15:1
Replication Health Status
+---------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+---------+
| server2.domain.com |3326| MASTER | UP | ON | OK || server1.domain.com |3326| SLAVE | UP | ON | OK |
+---------------------+-------+---------+--------+------------+---------+
MySQL Replication Failover Utility
Failover Mode = auto Next Interval = Fri Jun 7 17:49:37 2013Master Information
------------------
Binary Log File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000001 396GTID Executed Set
770d3753-c6e4-11e2-8e78-080027d93e15:1Replication Health Status
+---------------------+-------+---------+--------+------------+---------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+---------+
| server2.domain.com | 3326 | MASTER | UP | ON | OK |
| server1.domain.com | 3326 | SLAVE | UP | ON | OK |
+---------------------+-------+---------+--------+------------+---------+
To perform a switchover to be back to initial configuration use:
[mysql@server1 ~]$ mysqlrpladmin --master=yjaquier:secure_password@server2.domain.com:3326--slaves=yjaquier:secure_password@server1.domain.com:3326 \
>--new-master=yjaquier:secure_password@server1.domain.com:3326--demote-master switchover
# Checking privileges.# Performing switchover from master at server2.domain.com:3326 to slave at server1.domain.com:3326.# Checking candidate slave prerequisites.# Checking slaves configuration to master.# Waiting for slaves to catch up to old master.# Stopping slaves.# Performing STOP on all slaves.# Demoting old master to be a slave to the new master.# Switching slaves to new master.# Starting all slaves.# Performing START on all slaves.# Checking slaves for errors.# Switchover complete.## Replication Topology Health:
+---------------------+-------+---------+--------+------------+-----------------------------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+-----------------------------+
| server1.domain.com |3326| MASTER | UP | ON | OK || server2.domain.com |3326| SLAVE | UP | ON | SQL thread is not running. |
+---------------------+-------+---------+--------+------------+-----------------------------+
# ...done.
[mysql@server1 ~]$ mysqlrpladmin --master=yjaquier:secure_password@server2.domain.com:3326 --slaves=yjaquier:secure_password@server1.domain.com:3326 \
> --new-master=yjaquier:secure_password@server1.domain.com:3326 --demote-master switchover
# Checking privileges.
# Performing switchover from master at server2.domain.com:3326 to slave at server1.domain.com:3326.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+---------------------+-------+---------+--------+------------+-----------------------------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+-----------------------------+
| server1.domain.com | 3326 | MASTER | UP | ON | OK |
| server2.domain.com | 3326 | SLAVE | UP | ON | SQL thread is not running. |
+---------------------+-------+---------+--------+------------+-----------------------------+
# ...done.
START SLAVE SQL command has to be issued manually on server2.domain.com even if command output clearly mention that start slave has been successfully done…
Please note it is very easy to do mistake if messing-up master and slave server names. For example imagine that after a failover or a switchover server2.domain.com is the new master and you have completely forgotten or not followed it carefully. Then if you issue mysqlrpladmin command with wrong server name you may imagine MySQL Replication completely broken, while in fact you have just exchange server names::
[mysql@server1 ~]$ mysqlrpladmin --master=yjaquier:secure_password@server1.domain.com:3326--slaves=yjaquier:secure_password@server2.domain.com:3326 health
# Checking privileges.## Replication Topology Health:
+---------------------+-------+---------+--------+------------+------------------------------------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+------------------------------------+
| server1.domain.com |3326| MASTER | UP | ON | OK || server2.domain.com |3326| SLAVE | WARN || Slave is not connected to master. |
+---------------------+-------+---------+--------+------------+------------------------------------+
# ...done.
[mysql@server1 ~]$ mysqlrpladmin --master=yjaquier:secure_password@server1.domain.com:3326 --slaves=yjaquier:secure_password@server2.domain.com:3326 health
# Checking privileges.
#
# Replication Topology Health:
+---------------------+-------+---------+--------+------------+------------------------------------+
| host | port | role | state | gtid_mode | health |
+---------------------+-------+---------+--------+------------+------------------------------------+
| server1.domain.com | 3326 | MASTER | UP | ON | OK |
| server2.domain.com | 3326 | SLAVE | WARN | | Slave is not connected to master. |
+---------------------+-------+---------+--------+------------+------------------------------------+
# ...done.
MySQL Utilities errors you may encounter if not sing GTID feature. mysqlfailover works only with GTID mode on:
Topology must support global transaction ids and have GTID_MODE=ON.
2013-06-19 17:08:19 PM CRITICAL Topology must support global transaction ids and have GTID_MODE=ON.
ERROR: Topology must support global transaction ids and have GTID_MODE=ON.
mysqlrpladmin works without –rel-user option specified only if master_info_repository and relay_log_info_repository are set to table value:
ERROR: You must specify either the --rpl-user or set all slaves to use --master-info-repository=TABLE.
To solve it I had to add –rpl-user=repadmin:secure_password –force to switchover command, –force due to:
# When the master_info_repository variable is set to FILE, the --rpl-user option may be used only if the user specified matches what is shown in the SLAVE STATUS output unless the --force option is used.
ERROR: The specified replication user is using a different password that the one specified.
Use the --force option to force the use of the user specified with --rpl-user and new password.
Master High Availability Manager and tools for MySQL (MHA)
Installation
MHA is made of two packages one called MHA Node and MHA Manager. MHA Node must be installed on all MySQL servers part of your architecture (masters and slaves) while MHA Manager should be installed on a separated server (quorum server), if you don’t want to do this for cost reason then:
If you have only one (master, slaves) pair, you may not like allocating dedicated hardware for MHA Manager because it adds relatively high costs. In such cases, running MHA Manager on one of slaves makes sense. Note that current version of MHA Manager connects to MySQL slave server via SSH even though the MySQL server is located on the same host as MHA Manager, so you need to enable SSH public key authentication from the same host.
If installing from source package installation (MHA Node and MHA Manager) is as simple as (I apparently had all needed libraries installed i.e. DBI & DBD::mysql):
Automatic download did not worked, so had to manually install (yum):
perl-Config-Tiny.noarch
perl-Log-Dispatch.noarch (with lots of dependencies, thanks yum)
perl-Parallel-ForkManager.noarch
Then you have to configure a password less access between your server, root account preferred one. Start by generating a public (I chose DSA) on all servers:
[root@server2 .ssh]# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter fileinwhich to save the key (/root/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in/root/.ssh/id_dsa.
Your public key has been saved in/root/.ssh/id_dsa.pub.
The key fingerprint is:
a1:98:4d:f5:57:91:28:a0:75:20:4c:fb:05:fd:2b:d5 root@server2.domain.com
The key's randomart image is:
+--[ DSA 1024]----+
| oo *+. .oo |
| .* +o ... |
| + . oo.. |
| = o o .o E |
| o o S . . |
| . . |
| . |
| |
| |
+-----------------+
[root@server2 .ssh]# pwd
/root/.ssh
[root@server2 .ssh]# ll -a
total 28
drwx------. 2 root root 4096 Jun 17 12:50 .
dr-xr-x---. 22 root root 4096 Jun 17 10:50 ..
-rw------- 1 root root 668 Jun 17 12:50 id_dsa
-rw-r--r-- 1 root root 613 Jun 17 12:50 id_dsa.pub
-rw-r--r--. 1 root root 2321 Jun 17 12:45 known_hosts
[root@server2 .ssh]# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
a1:98:4d:f5:57:91:28:a0:75:20:4c:fb:05:fd:2b:d5 root@server2.domain.com
The key's randomart image is:
+--[ DSA 1024]----+
| oo *+. .oo |
| .* +o ... |
| + . oo.. |
| = o o .o E |
| o o S . . |
| . . |
| . |
| |
| |
+-----------------+
[root@server2 .ssh]# pwd
/root/.ssh
[root@server2 .ssh]# ll -a
total 28
drwx------. 2 root root 4096 Jun 17 12:50 .
dr-xr-x---. 22 root root 4096 Jun 17 10:50 ..
-rw------- 1 root root 668 Jun 17 12:50 id_dsa
-rw-r--r-- 1 root root 613 Jun 17 12:50 id_dsa.pub
-rw-r--r--. 1 root root 2321 Jun 17 12:45 known_hosts
Make sure to uncomment or add below line in /etc/ssh/sshd_config and restart sshd daemon on all servers:
AuthorizedKeysFile .ssh/authorized_keys
Club all public key files in a common authorized_keys file that you will distribute to all servers (in /root/.ssh directory).
Usage
First thing to do is create an application local configuration file (I have only one application to monitor so global configuration not needed):
[server default]
# mysql user and password
user=yjaquier
password=secure_password
port=3326
# working directory on the manager
manager_workdir=/tmp
# manager log file
manager_log=/tmp/app1.log
# working directory on MySQL servers
remote_workdir=/tmp
[server1]
hostname=server1.domain.com
master_binlog_dir=/mysql/logs/5.6.11
[server2]
hostname=server2.domain.com
master_binlog_dir=/mysql/logs/5.6.11
Then check ssh access for MHA between your servers:
[root@server2 ~]# masterha_check_ssh --conf=/etc/app1.cnf
Mon Jun 1712:56:192013 - [warning] Global configuration file/etc/masterha_default.cnf not found. Skipping.
Mon Jun 1712:56:192013 - [info] Reading application default configurations from /etc/app1.cnf..
Mon Jun 1712:56:192013 - [info] Reading server configurations from /etc/app1.cnf..
Mon Jun 1712:56:192013 - [info] Starting SSH connection tests..
Mon Jun 1712:56:192013 - [debug]
Mon Jun 1712:56:192013 - [debug] Connecting via SSH from root@server1.domain.com(192.168.56.101:22) to root@server2.domain.com(192.168.56.102:22)..
Mon Jun 1712:56:192013 - [debug] ok.
Mon Jun 1712:56:202013 - [debug]
Mon Jun 1712:56:192013 - [debug] Connecting via SSH from root@server2.domain.com(192.168.56.102:22) to root@server1.domain.com(192.168.56.101:22)..
Mon Jun 1712:56:192013 - [debug] ok.
Mon Jun 1712:56:202013 - [info] All SSH connection tests passed successfully.
[root@server2 ~]# masterha_check_ssh --conf=/etc/app1.cnf
Mon Jun 17 12:56:19 2013 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 17 12:56:19 2013 - [info] Reading application default configurations from /etc/app1.cnf..
Mon Jun 17 12:56:19 2013 - [info] Reading server configurations from /etc/app1.cnf..
Mon Jun 17 12:56:19 2013 - [info] Starting SSH connection tests..
Mon Jun 17 12:56:19 2013 - [debug]
Mon Jun 17 12:56:19 2013 - [debug] Connecting via SSH from root@server1.domain.com(192.168.56.101:22) to root@server2.domain.com(192.168.56.102:22)..
Mon Jun 17 12:56:19 2013 - [debug] ok.
Mon Jun 17 12:56:20 2013 - [debug]
Mon Jun 17 12:56:19 2013 - [debug] Connecting via SSH from root@server2.domain.com(192.168.56.102:22) to root@server1.domain.com(192.168.56.101:22)..
Mon Jun 17 12:56:19 2013 - [debug] ok.
Mon Jun 17 12:56:20 2013 - [info] All SSH connection tests passed successfully.
When issuing below command to check replication status:
I then had another error (I’m using root Linux account):
Can't exec "mysqlbinlog": No such file or directory at /usr/local/share/perl5/MHA/BinlogManager.pm line 99.
mysqlbinlog version not found!
I did not see any option to specify MySQL directory (I’m not using default filesystems) but looks like people are waiting for it. Changing root profile to add MySQL binary directory in path did not help… So created a symbolic link in /usr/bin directory on all servers:
Testing mysql connection and privileges..sh: mysql: command not found
Testing mysql connection and privileges..sh: mysql: command not found
So also created a symbolic link for mysql binary.
To run MHA Manager use:
[root@server2 ~]# masterha_manager --conf=/etc/app1.cnf
Wed Jun 1910:50:06 2013 - [warning] Global configuration file/etc/masterha_default.cnf not found. Skipping.
Wed Jun 1910:50:06 2013 - [info] Reading application default configurations from /etc/app1.cnf..
Wed Jun 1910:50:06 2013 - [info] Reading server configurations from /etc/app1.cnf..
[root@server2 ~]# masterha_manager --conf=/etc/app1.cnf
Wed Jun 19 10:50:06 2013 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Jun 19 10:50:06 2013 - [info] Reading application default configurations from /etc/app1.cnf..
Wed Jun 19 10:50:06 2013 - [info] Reading server configurations from /etc/app1.cnf..
Then you can check your MySQL Replication status with:
[root@server2 ~]# masterha_check_status --conf=/etc/app1.cnf
app1 (pid:28816) is running(0:PING_OK), master:server1.domain.com
[root@server2 ~]# masterha_check_status --conf=/etc/app1.cnf
app1 (pid:28816) is running(0:PING_OK), master:server1.domain.com
When trying to perform a master switch I can see one first limitation of running MHA manager on slave server:
[root@server2 ~]# masterha_master_switch --master_state=alive --conf=/etc/app1.cnf --new_master_host=server2.domain.com
.
Wed Jun 1914:27:362013 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln142] Getting advisory lock failed on the current master. MHA Monitor runs on the current master. Stop MHA Manager/Monitor and try again.
.
[root@server2 ~]# masterha_master_switch --master_state=alive --conf=/etc/app1.cnf --new_master_host=server2.domain.com
.
Wed Jun 19 14:27:36 2013 - [error][/usr/local/share/perl5/MHA/MasterRotate.pm, ln142] Getting advisory lock failed on the current master. MHA Monitor runs on the current master. Stop MHA Manager/Monitor and try again.
.
So decided to test a failover, kill mysql processes on master server (server1.domain.com in my case). After a while you may interactively see (that would not occur on a dedicated MHA manager host):
And see an app1.failover.complete empty file in working directory you configured (/tmp for me) and found in application log (/tmp/app1.log for me), output obviously truncated:
.
.
----- Failover Report -----
app1: MySQL Master failover server1.domain.com to server2.domain.com succeeded
Master server1.domain.com is down!
Check MHA Manager logs at server2.domain.com:/tmp/app1.log for details.
Started automated(non-interactive) failover.
The latest slave server2.domain.com(192.168.56.102:3326) has all relay logs for recovery.
Selected server2.domain.com as a new master.
server2.domain.com: OK: Applying all logs succeeded.
Generating relay diff files from the latest slave succeeded.
server2.domain.com: Resetting slave info succeeded.
Master failover to server2.domain.com(192.168.56.102:3326) completed successfully.
How to move MySQL Replication from GTID to standard mode
mysql>change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password', \
master_log_file='mysql-bin.000001', master_log_pos=120;
ERROR 1776(HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be setwhen MASTER_AUTO_POSITION is active.
mysql> change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password', \
master_log_file='mysql-bin.000001', master_log_pos=120;
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
I changed gtid_mode value to off (stop/start of both MySQL instances as a static parameter) and tried to recreate replication, but still failing for same error (!!):
mysql>show variables like'%gtid%';+--------------------------+-----------+| Variable_name |Value|+--------------------------+-----------+| enforce_gtid_consistency |ON|| gtid_executed ||| gtid_mode | OFF || gtid_next | AUTOMATIC || gtid_owned ||| gtid_purged ||+--------------------------+-----------+6 rows inset(0.01 sec)
mysql>change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password', \
master_log_file='mysql-bin.000001', master_log_pos=120;
ERROR 1777(HY000): CHANGE MASTER TO MASTER_AUTO_POSITION =1 can only be executed when @@GLOBAL.GTID_MODE =ON.
mysql>change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password', \
master_log_file='mysql-bin.000001', master_log_pos=120, master_auto_position=0;
ERROR 1777(HY000): CHANGE MASTER TO MASTER_AUTO_POSITION =1 can only be executed when @@GLOBAL.GTID_MODE =ON.
mysql> show variables like '%gtid%';
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-----------+
6 rows in set (0.01 sec)mysql> change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password', \
master_log_file='mysql-bin.000001', master_log_pos=120;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @@GLOBAL.GTID_MODE = ON.mysql> change master to master_host='server1.domain.com', master_port=3326, master_user='repadmin', master_password='secure_password', \
master_log_file='mysql-bin.000001', master_log_pos=120, master_auto_position=0;
ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @@GLOBAL.GTID_MODE = ON.
While I expected to compare the two products I reached the conclusion that they are not usable on same perimeter:
MySQL Utilities still beta (as of writing of this post) and clearly few things are missing. Its main target is MySQL 5.6 as all is around new GTID feature, many of the tools are working with standard replication implementation but I have found few errors.
MHA has not yet been released for GTID but coming soon (surely lots of people waiting for it). Few annoying things still in loop (path to MySQL binaries for example). Support from SkySQL for MHA if platinum contract is clearly a plus. On standard MySQL Replication implementation MHA is much in advanced and has proven to work in a real life big example (DeNA company claims 700+ MySQL servers with more than 150 {master, slaves} pairs, mainly MySQL 5.0 and 5.1).
tomcooper says:
nice blog and information about mysql manager and sql tools.