詳細検索
↑↓移動
↵選択
ESC閉じる

Tried mysqlfailover after becoming a daemon

Avatar
by komi04/01/2014
Tags failover ha mysqlutility
Tried mysqlfailover after becoming a daemon
Translated from 日本語 • View original
  • Please note **this is an outdated article. **

Hello. This is Komiya.

I'm not sure if anyone still wants to use it, but I've tested it and will share it here.
This is a long article, so please read it when you have time.

Start mysqlfailover without using --force and --daemon=start

When I tested it before,
　--force didn't work unless you installed it,
　There was no option to activate it with a demon
So, let me confirm that once again.

・Structure: 

192.168.1.133   komiya-test-mysql01 my1
192.168.1.155   komiya-test-mysql02 my2
192.168.1.150   komiya-test-mysql03 my3
192.168.1.241   komiya-test-mysql04 my4 manager
192.168.1.222   vip

・Installation:
You can download the package from the official site or somewhere like this.
For now, I installed MySQL 5.6 and utilities in Chef.
ssh-copy-id and knife solo preparation
Specify the db role in the node file's runlist,
Just by doing knife solo cooking, the following and necessary configuration files are placed,
I made server_id and report_host enter automatically.
Here is the recipe I referenced.

Below are the related packages.
mysql-utilities is a tool written in Python, so mysql-connector-python is required. 

$ rpm -qa|grep -i mysql
MySQL-shared-compat-5.6.15-1.linux_glibc2.5.x86_64
MySQL-test-5.6.15-1.linux_glibc2.5.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
mysql-utilities-1.4.1-1.el6.noarch
mysqltuner-1.1.1-1.el6.noarch
MySQL-client-5.6.15-1.linux_glibc2.5.x86_64
MySQL-server-5.6.15-1.linux_glibc2.5.x86_64
MySQL-devel-5.6.15-1.linux_glibc2.5.x86_64
mysql-connector-python-1.1.4-1.el6.noarch
mysqlreport-3.5-4.el6.noarch

・Build replicaiton
Since server_id is set to the fourth octet of the IP address, there shouldn't be duplication.
The report _host should also be automatically set to your own IP.

The settings for replicaiton are as follows 

## replication (master/slave)
log-bin=mysql-bin
log-bin-index=mysql-bin.index
binlog_format=mixed
server-id = 133
relay-log=mysqld-relay-bin
relay-log-index=mysql-relay-bin.index
log_slave_updates=1
replicate-ignore-db=mysql,information_schema,performance_schema
binlog-ignore-db=mysql,information_schema,performance_schema
skip_slave_start
read_only
#slave_net_timeout=120

## replication (for 5.6)
gtid-mode = OFF
enforce_gtid_consistency=false
master-info-repository=TABLE
relay-log-info-repository=TABLE
relay_log_recovery=ON
#sync-master-info=1
slave-parallel-workers=0
binlog-checksum=CRC32
#master-verify-checksum=1
#slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
#log_bin_use_v1_row_events=ON
#sync_binlog=1
report-port=3306
report-host = 192.168.1.133

*Parameter adjustments were required. 

gtid-mode = ON
enforce_gtid_consistency=true

Otherwise, mysqlfailover will not work. I'm pretty sure.
By the way, if GTID is ON, you won't be able to process transactions that are not transaction-safe.
(MyISAM storage engine cannot be used, Create... Unable to select, etc.) 

sed -i 's/gtid-mode = OFF/gtid-mode = ON/g' /etc/my.cnf
sed -i 's/enforce_gtid_consistency=false/enforce_gtid_consistency=true/g' /etc/my.cnf
service mysql restart

Make 1 master and the others slave.

The necessary GRANT statements for additional accounts like repl are included in the recipe, so just check them. 

mysql> select user,host,password from mysql.user;
+------+---------------------+-------------------------------------------+
| user | host                | password                                  |
+------+---------------------+-------------------------------------------+
| root | localhost           | *E8DD65E018E30F27D962FB9BFA2F4E8206DC3AF8 |
| root | komiya-test-mysql01 | *6A60A70C59535B75A79FDE4C7C55FDA55FC40A55 |
| root | 127.0.0.1           | *E8DD65E018E30F27D962FB9BFA2F4E8206DC3AF8 |
| root | ::1                 | *E8DD65E018E30F27D962FB9BFA2F4E8206DC3AF8 |
| root | 192.168.%           | *E8DD65E018E30F27D962FB9BFA2F4E8206DC3AF8 |
| repl | 192.168.%           | *43E209EED080057E35C2630AC06D32960A46D120 |
+------+---------------------+-------------------------------------------+

Reset master with 1~3; to initialize the position (this was done because data was not available). Implementation in certain conditions is prohibited. Be careful of inconsistencies)

In 2 and 3 

mysql> CHANGE MASTER TO 
MASTER_HOST='192.168.1.133',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='re*****',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;

mysql> start slave;
mysql> show slave status\G
mysql> set global read_only=1;
mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

When to enable the auto position 

stop slave;
mysql> CHANGE MASTER TO 
MASTER_HOST='192.168.1.133',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='re*****',
MASTER_AUTO_POSITION = 1;
start slave;
  • If GTID is enabled, you can automatically specify your position,
    　It seems like you can easily write recipes without making replicaiton complicated with chef.

Replicaiton confirmed in 1 

mysql> show slave hosts;
+-----------+---------------+------+-----------+--------------------------------------+
| Server_id | Host          | Port | Master_id | Slave_UUID                           |
+-----------+---------------+------+-----------+--------------------------------------+
|       150 | 192.168.1.150 | 3306 |       133 | afee6fde-978f-11e3-9f2a-02883e765295 |
|       155 | 192.168.1.155 | 3306 |       133 | 896d4156-9846-11e3-a3d2-02619050bb48 |
+-----------+---------------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

Replicaiton Verification Using Utility in 4
For now, each host needs to be accessible via SSH key authentication
(It seems you need permission to set replicaiton, so I set it as the root key.) 

# mysqlrpladmin --master=root:`cat /path_to_file`@192.168.1.133:3306 \
> --slaves=root:`cat /path_to_file`@192.168.1.155:3306,root:`cat /path_to_file`@192.168.1.150:3306 health
# Checking privileges.
#
# Replication Topology Health:
+----------------+-------+---------+--------+------------+---------+
| host           | port  | role    | state  | gtid_mode  | health  |
+----------------+-------+---------+--------+------------+---------+
| 192.168.1.133  | 3306  | MASTER  | UP     | ON         | OK      |
| 192.168.1.150  | 3306  | SLAVE   | UP     | ON         | OK      |
| 192.168.1.155  | 3306  | SLAVE   | UP     | ON         | OK      |
+----------------+-------+---------+--------+------------+---------+
# ... done.

# mysqlrplcheck --master=root:`cat /path_to_file`@192.168.1.133:3306 --slave=root:`cat /path_to_file`@192.168.1.155:3306
# master on 192.168.1.133: ... connected.
# slave on 192.168.1.155: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [WARN]

+---------+--------+----------------------------------------------+
| server  | do_db  | ignore_db                                    |
+---------+--------+----------------------------------------------+
| master  |        | mysql,information_schema,performance_schema  |
| slave   |        | mysql,information_schema,performance_schema  |
+---------+--------+----------------------------------------------+

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.

# mysqlrplcheck --master=root:`cat /path_to_file`@192.168.1.133:3306 --slave=root:`cat /path_to_file`@192.168.1.150:3306
# master on 192.168.1.133: ... connected.
# slave on 192.168.1.150: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [WARN]

+---------+--------+----------------------------------------------+
| server  | do_db  | ignore_db                                    |
+---------+--------+----------------------------------------------+
| master  |        | mysql,information_schema,performance_schema  |
| slave   |        | mysql,information_schema,performance_schema  |
+---------+--------+----------------------------------------------+

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.

# mysqlrplshow \
> --master=root:`cat /path_to_file`@192.168.1.133:3306 \
> --discover-slaves-login=root:`cat /path_to_file`
# master on 192.168.1.133: ... connected.
# Finding slaves for master: 192.168.1.133:3306

# Replication Topology Graph
192.168.1.133:3306 (MASTER)
   |
   +--- 192.168.1.150:3306 - (SLAVE)
   |
   +--- 192.168.1.155:3306 - (SLAVE)

・Check the help for the mysqlfailover command 

# mysqlfailover --help
------------------------------------------------
MySQL Utilities mysqlfailover version 1.4.1 (part of MySQL Workbench Distribution 6.0.0)
License type: GPLv2
Usage: mysqlfailover --master=root@localhost --discover-slaves-login=root --candidates=root@host123:3306,root@host456:3306

mysqlfailover - automatic replication health monitoring and failover

Options:
  --version             show program's version number and exit
  --help                display this help message and exit
  --license             display program's license and exit
  --candidates=CANDIDATES
                        connection information for candidate slave servers for
                        failover in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>]. Valid only with
                        failover command. List multiple slaves in comma-
                        separated list.
  --discover-slaves-login=DISCOVER
                        at startup, query master for all registered slaves and
                        use the user name and password specified to connect.
                        Supply the user and password in the form
                        <user>[:<password>] or <login-path>. For example,
                        --discover-slaves-login=joe:secret will use 'joe' as
                        the user and 'secret' as the password for each
                        discovered slave.
~Omitted~

If you specify as --daemon=DAEMON, you are told to choose from 'start', 'stop', 'restart', or 'nodetach'. 

mysqlfailover: error: option --daemon: invalid choice: 'DAEMON' (choose from 'start', 'stop', 'restart', 'nodetach')

Try the following in step 4. 

mysqlfailover \
--master=root:`cat /path_to_file`@192.168.1.133:3306 \
--candidate=root:`cat /path_to_file`@192.168.1.155:3306,root:`cat /path_to_file`@192.168.1.150:3306 \
--discover-slaves-login=root:`cat /path_to_file` \
--log=/tmp/failover.log \
--rpl-user=repl:re***** \
--rediscover \
--failover-mode=auto \
--daemon=start \
-v
# mysqlfailover \
> --master=root:`cat /path_to_file`@192.168.1.133:3306 \
> --candidate=root:`cat /path_to_file`@192.168.1.155:3306,root:`cat /path_to_file`@192.168.1.150:3306 \
> --discover-slaves-login=root:`cat /path_to_file` \
> --log=/tmp/failover.log \
> --rpl-user=repl:re***** \
> --rediscover \
> --failover-mode=auto \
> --daemon=start \
> -v
NOTE: Log file '/tmp/failover.log' does not exist. Will be created.
Starting failover daemon...

Since the standard output shows no status, check the logs 

# tail /tmp/failover.log
2014-02-17 23:34:12 PM INFO Unregistering existing instances from slaves.
2014-02-17 23:34:12 PM INFO Registering instance on master.
2014-02-17 23:34:12 PM INFO Checking privileges.
2014-02-17 23:34:12 PM INFO Checking privileges on candidates.
2014-02-17 23:34:12 PM CRITICAL User root on 192.168.1.133 does not have sufficient privileges to execute the failover command.
2014-02-17 23:34:12 PM CRITICAL User root on 192.168.1.150 does not have sufficient privileges to execute the failover command.
2014-02-17 23:34:12 PM CRITICAL User root on 192.168.1.155 does not have sufficient privileges to execute the failover command.
2014-02-17 23:34:12 PM CRITICAL User root on 192.168.1.155 does not have sufficient privileges to execute the failover command.
2014-02-17 23:34:12 PM CRITICAL User root on 192.168.1.150 does not have sufficient privileges to execute the failover command.
2014-02-17 23:34:12 PM INFO Unregistering instance on master.

It seems you don't have the right to failover the specified root.
I didn't really understand, so I googled and looked at the ORACLE manual (in English)
MySQL Utilities (PDF manual)
3.4.3 Setup Automatic Failover (P28) is written around the same time.
Japanese doesn't seem to exist, but HTML appears to exist. (But it seems the PDF is more accurate.)
MySQL Utilities (HTML manual)

There was a page explaining the authority. 

3.4.3.4 Permissions Required

The user must have permissions to configure replication.
Users must have permission to set replication. 

Well, but root is all for all. Are you talking about 'with grantoption' or something?
Below is an explanation of the permissions required for commands to set replication (such as mysqlrpladmin).

3.4.2.4 Permissions Required

The m_account user needs the following privileges for the mysqlreplicate: SELECT and INSERT privileges on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION. As for the slave_acc users, they need the SUPER privilege. The repl user, used as the argument for the --rpl-user option, is either created automatically or if it exists, it needs the REPLICATION SLAVE privilege.

To run the mysqlrpladmin utility with the health command, the m_account used on the master needs an extra SUPER privilege.

As for the switchover command all the users need the following privileges: SUPER, GRANT OPTION, SELECT, RELOAD, DROP, CREATE and REPLICATION SLAVE

★ When translating the required permission statement,
m_account (the user connecting to the master) requires the following permissions:
    SELECT and INSERT on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION.
slave_acc (the user connecting to the slave) requires the following permissions:
    SUPER privilege.
repl (replication user) requires REPLICATION SLAVE privileges. If you use the --rpl-user option, it will be automatically generated (or already generated)
All users using the toggle command require the following privileges:
    SUPER, GRANT OPTION, SELECT, RELOAD, DROP, CREATE and REPLICATION SLAVE

Here are some other tips

3.4.3.5 Tips and Tricks
The console mode I introduced earlier is the mode. However, you can also have them run as demons.
To do this、-- you need to use daemon, specifically starting it with '--daemon=start'.
At this time, mysqlfailover executes as a daemon and does not output anything to the console, instead recording it to the specified file.
To stop the mysqlfailover daemon, simply use '--daemon=stop'.
 Unless you specify the --pidfile option at startup, no other options are needed; if specified, the same option is required.

Another useful feature is the ability to customize the environment by specifying extension scripts at runtime.
--exec-fail-check Specify a script to run regularly at predefined intervals for each default check.
--exec-before Specify the script to run before starting failover
--exec-after specifies the script to run when the failover process ends
--exec-post-failover: Specifies the script to run after failover (such as a health report)

Anyway, let's check the permissions of my current account. 

# pt-show-grants -u root -p`cat /path_to_file`
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.6.15-log at 2014-02-18 15:32:11
-- Grants for 'repl'@'192.168.%'
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY PASSWORD '*43E209EED080057E35C2630AC06D32960A46D120';
-- Grants for 'root'@'127.0.0.1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*E8DD65E018E30F27D962FB9BFA2F4E8206DC3AF8' WITH GRANT OPTION;
-- Grants for 'root'@'192.168.%'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.%' IDENTIFIED BY PASSWORD '*E8DD65E018E30F27D962FB9BFA2F4E8206DC3AF8';
-- Grants for 'root'@'::1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY PASSWORD '*E8DD65E018E30F27D962FB9BFA2F4E8206DC3AF8' WITH GRANT OPTION;
-- Grants for 'root'@'komiya-test-mysql01'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'komiya-test-mysql01' IDENTIFIED BY PASSWORD '*6A60A70C59535B75A79FDE4C7C55FDA55FC40A55' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'komiya-test-mysql01' WITH GRANT OPTION;
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*E8DD65E018E30F27D962FB9BFA2F4E8206DC3AF8' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

Since you have to CHANGE MASTER when changing topology,
It seems you need the permissions required for mysqlreplicate or mysqladmin switchover.

When I tried translating the previous chapter, I found that the following permissions were required. 

m_account (the user connecting to the master) requires the following permissions:
    SELECT and INSERT on mysql database, REPLICATION SLAVE, REPLICATION CLIENT and GRANT OPTION.
slave_acc (the user connecting to the slave) requires the following permissions:
    SUPER privilege.
Repl (replication user) requires the following permissions:
    REPLICATION SLAVE。 If you use the --rpl-user option, it will be automatically generated (or already generated)
All users using the toggle command require the following privileges:
    SUPER, GRANT OPTION, SELECT, RELOAD, DROP, CREATE and REPLICATION SLAVE

From what I see, the master grant option is lacking. I'll try creating a failover user instead of root.
I don't want to add a grant option to an overly straightforward account like root, which allows execution over the network.
Until now, the grant option had only ever been attached to local users.
If I had an account connected remotely, I recognized it as an operation error.

Add this to the master. But since the master might also switch, I think it's fine to add them all. 

GRANT all on *.* to failover@'192.168.%' identified by "xxxxxxxx" WITH GRANT OPTION;

If you want to be strict, it would look like this. 

GRANT SELECT, INSERT, REPLICATION SLAVE, REPLICATION CLIENT on *.* to m_failover@'192.168.%' identified by "xxxxxxxx" WITH GRANT OPTION;
GRANT SUPER on *.* to s_failover@'192.168.%' identified by "xxxxxxxx";
GRANT REPLICATION SLAVE on *.* to repl@'192.168.%' identified by "xxxxxxxx";

I'll try launching it. 

mysqlfailover \
--master=failover:xxxxxxxx@192.168.1.133:3306 \
--candidate=failover:xxxxxxxx@192.168.1.155:3306 \
--discover-slaves-login=failover:xxxxxxxx \
--log=/tmp/failover.log \
--rpl-user=repl:re***** \
--rediscover \
--failover-mode=auto \
--daemon=start \
-v
Starting failover daemon...
Multiple instances of failover daemon found for master 192.168.1.133:3306.
If this is an error, restart the daemon with --force.
Failover mode changed to 'FAIL' for this instance.
Daemon will start in 10 seconds.
......... starting Daemon.

Let's look at the logs 

# tail /tmp/failover.log
2014-03-27 02:06:32 AM INFO Discovering slave at 192.168.1.150:3306
2014-03-27 02:06:32 AM INFO Discovering slave at 192.168.1.155:3306
2014-03-27 02:06:32 AM INFO Master Information
2014-03-27 02:06:32 AM INFO Binary Log File: mysql-bin.000008, Position: 191, Binlog_Do_DB: N/A, Binlog_Ignore_DB: mysql,information_schema,performance_schema
2014-03-27 02:06:32 AM INFO GTID Executed Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
2014-03-27 02:06:32 AM INFO Getting health for master: 192.168.1.133:3306.
2014-03-27 02:06:32 AM INFO Health Status:
2014-03-27 02:06:32 AM INFO host: 192.168.1.133, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000008, master_log_pos: 191, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2014-03-27 02:06:32 AM INFO host: 192.168.1.150, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000008, master_log_pos: 191 , IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2014-03-27 02:06:32 AM INFO host: 192.168.1.155, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000008, master_log_pos: 191 , IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0

There was a log showing the wind that had actually moved.

Looking at the process, it looks like this 

# ps -ef|grep failover
root      1091     1  0 02:06 ?        00:00:00 /usr/bin/python /usr/bin/mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306,failover: xxxxxxxx@192.168.1.150:3306 --discover-slaves-login=failover:xxxxxxxx --log=/tmp/failover.log --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=start -v

Looking closely at the logs, 

2014-03-27 02:16:11 AM INFO Failover mode = fail.

I'm curious about how things like that are shown. I wonder why.
It seems that failing means it won't failover, which is a problem.
After searching, I found the following page.
Tried mysqlfailover with MySQL 5.6-rc - hiroi10's diary 

mysql> select * from mysql.failover_console;
+---------------+------+
| host          | port |
+---------------+------+
| 192.168.1.133 | 3306 |
+---------------+------+

If you delete this and then restart mysqlfailover, it seems the Failover mode will not fail.
It's kind of like a lock file in MHA.
It seems that log monitoring for "Failover mode = fail" is necessary.
From the name alone, monitoring logs with fail seems a bit problematic, so you really need to choose keywords carefully.

For now, I'll stop 

# mysqlfailover \
> --master=failover:xxxxxxxx@192.168.1.133:3306 \
> --candidate=failover:xxxxxxxx@192.168.1.155:3306 \
> --discover-slaves-login=failover:xxxxxxxx \
> --log=/tmp/failover.log \
> --rpl-user=repl:re***** \
> --rediscover \
> --failover-mode=auto \
> --daemon=stop \
> -v
Stopping failover daemon...
# ps -ef|grep failover

At the master
mysql> delete from mysql.failover_console;
Query OK, 1 row affected (0.02 sec)

mysql> select * from mysql.failover_console;
Empty set (0.00 sec)

At this time, there is no change to the existing replication configuration.
mysql> show slave hosts;
+-----------+---------------+------+-----------+--------------------------------------+
| Server_id | Host          | Port | Master_id | Slave_UUID                           |
+-----------+---------------+------+-----------+--------------------------------------+
|       150 | 192.168.1.150 | 3306 |       133 | afee6fde-978f-11e3-9f2a-02883e765295 |
|       155 | 192.168.1.155 | 3306 |       133 | 896d4156-9846-11e3-a3d2-02619050bb48 |
+-----------+---------------+------+-----------+--------------------------------------+

Launching 

# mysqlfailover \
> --master=failover:xxxxxxxx@192.168.1.133:3306 \
> --candidate=failover:xxxxxxxx@192.168.1.155:3306 \
> --discover-slaves-login=failover:xxxxxxxx \
> --log=/tmp/failover.log \
> --rpl-user=repl:re***** \
> --rediscover \
> --failover-mode=auto \
> --daemon=start \
> -v
Starting failover daemon...

It might be better to add --pidfile=.

When I checked the logs, it was successfully set to auto as shown below. 

# view /tmp/failover.log
2014-03-27 03:00:21 AM INFO Failover mode = auto.

Let's review the remaining issues here.
Other items to check:
　--daemon=restart and other checks
　Switch Test
　VIP Migration Try adding other external scripts
　Try making a startup script

・Check if reboots and other functions are possible

Other options available in Deamon mode are 'start', 'stop', 'restart', and 'nodetach', so I'll try them all
I also found a manual here.
MySQL Utility:: 5.9.1 mysqlfailover - Automatic replication health monitoring and failover
From what I saw in the manual, 'nodetach' seems to display the console screen as well. 

# ps -ef|grep failover
root      1343     1  0 03:00 ?        00:00:08 /usr/bin/python /usr/bin/mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306 --discover-slaves-login= failover:xxxxxxxx --log=/tmp/failover.log --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=start -v

# mysqlfailover \
> --master=failover:xxxxxxxx@192.168.1.133:3306 \
> --candidate=failover:xxxxxxxx@192.168.1.155:3306 \
> --discover-slaves-login=failover:xxxxxxxx \
> --log=/tmp/failover.log \
> --rpl-user=repl:re***** \
> --rediscover \
> --failover-mode=auto \
> --daemon=restart \
> -v
Restarting failover daemon...
Multiple instances of failover daemon found for master 192.168.1.133:3306.
If this is an error, restart the daemon with --force.
Failover mode changed to 'FAIL' for this instance.
Daemon will start in 10 seconds.
......... starting Daemon.
# ps -ef|grep failover
root      1500     1  0 03:30 ?        00:00:00 /usr/bin/python /usr/bin/mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306 --discover-slaves-login= failover:xxxxxxxx --log=/tmp/failover.log --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=restart -v

# view /tmp/failover.log
2014-03-27 03:30:09 AM INFO Failover mode = fail.

So, it seems best not to use restart in general,
If you use it, it might be about adding --force.

I tried adding --force 

# mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306 --discover-slaves-login=failover:xxxxxxxx --log=/tmp/ failover.log --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=restart --force -v
Restarting failover daemon...

# ps -ef|grep failover
root      1523     1  0 03:33 ?        00:00:00 /usr/bin/python /usr/bin/mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306 --discover-slaves-login= failover:xxxxxxxx --log=/tmp/failover.log --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=restart --force -v

# view /tmp/failover.log
2014-03-27 03:33:52 AM INFO Failover mode = auto.

Well, it seems to be fine. When I use Photoshop, the --force output somehow feels like a loss.

I'll try no-detach after stopping once. 

# mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306 --discover-slaves-login=failover:xxxxxxxx --log=/tmp/ failover.log --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=stop -v
Stopping failover daemon...

# ps -ef|grep failover

Don't forget to delete the console on the master either. 

mysql> select * from mysql.failover_console;
+---------------+------+
| host          | port |
+---------------+------+
| 192.168.1.133 | 3306 |
+---------------+------+
1 row in set (0.00 sec)

mysql> delete from mysql.failover_console;
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql.failover_console;
Empty set (0.00 sec)

Launch with nodetach 

# mysqlfailover \
> --master=failover:xxxxxxxx@192.168.1.133:3306 \
> --candidate=failover:xxxxxxxx@192.168.1.155:3306 \
> --discover-slaves-login=failover:xxxxxxxx \
> --log=/tmp/failover.log \
> --rpl-user=repl:re***** \
> --rediscover \
> --failover-mode=auto \
> --daemon=nodetach \
> -v
Starting failover daemon...
# Discovering slaves for master at 192.168.1.133:3306
# Discovering slave at 192.168.1.150:3306
# Found slave: 192.168.1.150:3306
# Discovering slave at 192.168.1.155:3306
# Found slave: 192.168.1.155:3306
# Checking privileges.
# Checking privileges on candidates.
# Discovering slaves for master at 192.168.1.133:3306
# Attempting to contact 192.168.1.133 ... Success
# Attempting to contact 192.168.1.150 ... Success
# Attempting to contact 192.168.1.155 ... Success
# Discovering slaves for master at 192.168.1.133:3306
# Attempting to contact 192.168.1.133 ... Success
# Attempting to contact 192.168.1.150 ... Success
# Attempting to contact 192.168.1.155 ... Success
# Discovering slaves for master at 192.168.1.133:3306
# Attempting to contact 192.168.1.133 ... Success
# Attempting to contact 192.168.1.150 ... Success
# Attempting to contact 192.168.1.155 ... Success

It seems like the log keeps flowing endlessly on the console.
If you press Ctl+C to exit, the process seems to crash.

ps -ef|grep failover

There is nothing

・Switch test

For now, I won't handle VIP or anything like that; just install the master MySQL process.
Let's check if the replication master switches.

db1: 

mysql> select * from mysql.failover_console;
mysql> delete from mysql.failover_console;
mysql> select * from mysql.failover_console;

db4: 

ps -ef |grep failover
tail -f /tmp/failover.log

mysqlfailover \
--master=failover:xxxxxxxx@192.168.1.133:3306 \
--candidate=failover:xxxxxxxx@192.168.1.155:3306 \
--discover-slaves-login=failover:xxxxxxxx \
--log=/tmp/failover.log \
--rpl-user=repl:re***** \
--rediscover \
--failover-mode=auto \
--daemon=start \
-v

db1: 

service mysql stop

db4: 

tail /tmp/failover.log
2014-03-27 03:48:41 AM INFO Failed to reconnect to the master after 3 attemps.
2014-03-27 03:48:41 AM CRITICAL Master is confirmed to be down or unreachable.
2014-03-27 03:48:41 AM INFO Failover starting in 'auto' mode...
2014-03-27 03:48:41 AM INFO Checking eligibility of slave 192.168.1.155:3306 for candidate.
2014-03-27 03:48:41 AM INFO GTID_MODE=ON ... Ok
2014-03-27 03:48:41 AM INFO Replication user exists ... Ok
2014-03-27 03:48:41 AM INFO Candidate slave 192.168.1.155:3306 will become the new master.
2014-03-27 03:48:41 AM INFO Checking slaves status (before failover).
2014-03-27 03:48:41 AM WARNING Problem detected with SQL thread for slave '192.168.1.150'@'3306' that can result on a unstable topology.
2014-03-27 03:48:41 AM WARNING - SQL thread running: No
2014-03-27 03:48:41 AM WARNING - SQL error: 1146 - Worker 0 failed executing transaction 'e97b08ca-6798-11e3-a666-02c0661dc6e6:65' at master log mysql-bin.000008, end_log_pos 485; Error executing row event: 'Table 'mysql.failover_console' doesn't exist'
2014-03-27 03:48:41 AM WARNING Problem detected with SQL thread for slave '192.168.1.155'@'3306' that can result on a unstable topology.
2014-03-27 03:48:41 AM WARNING - SQL thread running: No
2014-03-27 03:48:41 AM WARNING - SQL error: 1146 - Error executing row event: 'Table 'mysql.failover_console' doesn't exist'
2014-03-27 03:48:41 AM INFO Preparing candidate for failover.
2014-03-27 03:48:41 AM INFO Reading events in relay log for slave 192.168.1.150:3306
2014-03-27 03:48:41 AM INFO Missing transactions found on 192.168.1.150:3306. SELECT gtid_subset() = 0
2014-03-27 03:48:41 AM INFO Connecting candidate to 192.168.1.150:3306 as a temporary slave to retrieve unprocessed GTIDs.
2014-03-27 03:48:41 AM INFO Waiting for candidate to catch up to slave 192.168.1.150:3306.
2014-03-27 03:48:42 AM INFO Creating replication user if it does not exist.
2014-03-27 03:48:42 AM INFO Stopping slaves.
2014-03-27 03:48:42 AM INFO Performing STOP on all slaves.
2014-03-27 03:48:42 AM WARNING Executing stop on slave 192.168.1.150:3306 WARN - slave is not configured with this master
2014-03-27 03:48:42 AM INFO Executing stop on slave 192.168.1.150:3306 Ok
2014-03-27 03:48:42 AM WARNING Executing stop on slave 192.168.1.155:3306 WARN - slave is not configured with this master
2014-03-27 03:48:42 AM INFO Executing stop on slave 192.168.1.155:3306 Ok
2014-03-27 03:48:42 AM INFO Switching slaves to new master.
2014-03-27 03:48:42 AM INFO Disconnecting new master as slave.
2014-03-27 03:48:42 AM INFO Execute on 192.168.1.155:3306: RESET SLAVE ALL
2014-03-27 03:48:42 AM INFO Starting slaves.
2014-03-27 03:48:42 AM INFO Performing START on all slaves.
2014-03-27 03:48:42 AM INFO Executing start on slave 192.168.1.150:3306 Ok
2014-03-27 03:48:42 AM INFO Checking slaves for errors.
2014-03-27 03:48:42 AM INFO 192.168.1.150:3306 status: Ok
2014-03-27 03:48:42 AM INFO Failover complete.
2014-03-27 03:48:42 AM INFO Discovering slaves for master at 192.168.1.155:3306
2014-03-27 03:48:42 AM INFO Discovering slave at 192.168.1.150:3306
2014-03-27 03:48:42 AM INFO Found slave: 192.168.1.150:3306
2014-03-27 03:48:47 AM INFO Unregistering existing instances from slaves.
2014-03-27 03:48:47 AM INFO Registering instance on new master 192.168.1.155:3306.
2014-03-27 03:48:47 AM INFO Master Information
2014-03-27 03:48:47 AM INFO Binary Log File: mysql-bin.000004, Position: 547000, Binlog_Do_DB: N/A, Binlog_Ignore_DB: mysql,information_schema,performance_schema
2014-03-27 03:48:47 AM INFO GTID Executed Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
2014-03-27 03:48:47 AM INFO Getting health for master: 192.168.1.155:3306.
2014-03-27 03:48:47 AM INFO Health Status:
2014-03-27 03:48:47 AM INFO host: 192.168.1.155, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2014-03-27 03:48:47 AM INFO host: 192.168.1.150, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2014-03-27 03:49:05 AM INFO Discovering slaves for master at 192.168.1.155:3306
2014-03-27 03:49:05 AM INFO Discovering slave at 192.168.1.150:3306
2014-03-27 03:49:05 AM INFO Master Information
2014-03-27 03:49:05 AM INFO Binary Log File: mysql-bin.000004, Position: 547000, Binlog_Do_DB: N/A, Binlog_Ignore_DB: mysql,information_schema,performance_schema
2014-03-27 03:49:05 AM INFO GTID Executed Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
2014-03-27 03:49:05 AM INFO Getting health for master: 192.168.1.155:3306.
2014-03-27 03:49:05 AM INFO Health Status:
2014-03-27 03:49:05 AM INFO host: 192.168.1.155, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2014-03-27 03:49:05 AM INFO host: 192.168.1.150, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2014-03-27 03:49:23 AM INFO Discovering slaves for master at 192.168.1.155:3306
2014-03-27 03:49:23 AM INFO Discovering slave at 192.168.1.150:3306
2014-03-27 03:49:23 AM INFO Master Information
2014-03-27 03:49:23 AM INFO Binary Log File: mysql-bin.000004, Position: 547000, Binlog_Do_DB: N/A, Binlog_Ignore_DB: mysql,information_schema,performance_schema
2014-03-27 03:49:23 AM INFO GTID Executed Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
2014-03-27 03:49:23 AM INFO Getting health for master: 192.168.1.155:3306.
2014-03-27 03:49:23 AM INFO Health Status:
2014-03-27 03:49:23 AM INFO host: 192.168.1.155, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2014-03-27 03:49:23 AM INFO host: 192.168.1.150, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2014-03-27 03:49:41 AM INFO Discovering slaves for master at 192.168.1.155:3306
2014-03-27 03:49:41 AM INFO Discovering slave at 192.168.1.150:3306
2014-03-27 03:49:41 AM INFO Master Information
2014-03-27 03:49:41 AM INFO Binary Log File: mysql-bin.000004, Position: 547000, Binlog_Do_DB: N/A, Binlog_Ignore_DB: mysql,information_schema,performance_schema
2014-03-27 03:49:41 AM INFO GTID Executed Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
2014-03-27 03:49:41 AM INFO Getting health for master: 192.168.1.155:3306.
2014-03-27 03:49:42 AM INFO Health Status:
2014-03-27 03:49:42 AM INFO host: 192.168.1.155, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2014-03-27 03:49:42 AM INFO host: 192.168.1.150, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2014-03-27 03:50:00 AM INFO Discovering slaves for master at 192.168.1.155:3306
2014-03-27 03:50:00 AM INFO Discovering slave at 192.168.1.150:3306
2014-03-27 03:50:00 AM INFO Master Information
2014-03-27 03:50:00 AM INFO Binary Log File: mysql-bin.000004, Position: 547000, Binlog_Do_DB: N/A, Binlog_Ignore_DB: mysql,information_schema,performance_schema
2014-03-27 03:50:00 AM INFO GTID Executed Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
2014-03-27 03:50:00 AM INFO Getting health for master: 192.168.1.155:3306.
2014-03-27 03:50:00 AM INFO Health Status:
2014-03-27 03:50:00 AM INFO host: 192.168.1.155, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2014-03-27 03:50:00 AM INFO host: 192.168.1.150, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2014-03-27 03:50:18 AM INFO Discovering slaves for master at 192.168.1.155:3306
2014-03-27 03:50:18 AM INFO Discovering slave at 192.168.1.150:3306
2014-03-27 03:50:18 AM INFO Master Information
2014-03-27 03:50:18 AM INFO Binary Log File: mysql-bin.000004, Position: 547000, Binlog_Do_DB: N/A, Binlog_Ignore_DB: mysql,information_schema,performance_schema
2014-03-27 03:50:18 AM INFO GTID Executed Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
2014-03-27 03:50:18 AM INFO Getting health for master: 192.168.1.155:3306.
2014-03-27 03:50:18 AM INFO Health Status:
2014-03-27 03:50:18 AM INFO host: 192.168.1.155, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2014-03-27 03:50:18 AM INFO host: 192.168.1.150, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.6.15-log, master_log_file: mysql-bin.000004, master_log_pos: 547000, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0

# ps -ef |grep mysql
root      1599     1  0 03:46 ?        00:00:01 /usr/bin/python /usr/bin/mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306 --discover-slaves-login= failover:xxxxxxxx --log=/tmp/failover.log --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=start -v

db3: 

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.155
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 547000
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 408
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,information_schema,performance_schema
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 547000
              Relay_Log_Space: 613
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 155
                  Master_UUID: 896d4156-9846-11e3-a3d2-02619050bb48
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
                Auto_Position: 1
1 row in set (0.00 sec)

The master switches automatically.

db2: 

mysql> show slave status\G
Empty set (0.00 sec)

mysql> show slave hosts;
+-----------+---------------+------+-----------+--------------------------------------+
| Server_id | Host          | Port | Master_id | Slave_UUID                           |
+-----------+---------------+------+-----------+--------------------------------------+
|       150 | 192.168.1.150 | 3306 |       155 | afee6fde-978f-11e3-9f2a-02883e765295 |
+-----------+---------------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000004
         Position: 547000
     Binlog_Do_DB:
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set: e97b08ca-6798-11e3-a666-02c0661dc6e6:1-64
1 row in set (0.00 sec)

mysql> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

The master switched properly.
However, it doesn't seem to automatically turn off the new master's read/_only.
From what I see about help, such options don't exist.
It seems that external scripts need to be used to handle failover operations.

・Try restructuring the structure.

Assuming the data is absolutely not updated and the hosts running replication are aligned,
RESET MASTER; and RESET SLAVE ALL; So I'll reply again and revert it.

For now, there's no need to switch, so I'll stop the MySQL Failover process 

# mysqlfailover --master=failover:xxxxxxxx@192.168.1.155:3306 --candidate=failover:xxxxxxxx@192.168.1.150:3306 --discover-slaves-login=failover:xxxxxxxx --log=/tmp/ failover.log --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=stop -v
Stopping failover daemon...

Somehow, when stopping after switching, you have to adjust the values of --master and --candidate=failover to crash.
But then it seems that if you add the --pidfile option, it seems to crash even without adding --master when specifying stop.

I will restore the rep.
db1： 

netstat -tanp
service mysql start
mysql -u root -p
show master status\G
show slave status\G
show slave hosts;
show global variables like 'read_only';
select * from mysql.failover_console;
delete from mysql.failover_console;
select * from mysql.failover_console;
RESET MASTER;

db2: 

mysql -u root -p
show master status\G
show slave status\G
stop slave;
RESET SLAVE ALL;
CHANGE MASTER TO 
MASTER_HOST='192.168.1.133',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='re*****',
MASTER_AUTO_POSITION = 1;
start slave;
show slave status\G
show slave hosts;
show global variables like 'read_only';
set global read_only=1;

db3:``` mysql -u root -p show slave status\G stop slave; RESET SLAVE ALL; show slave status\G CHANGE MASTER TO MASTER_HOST='192.168.1.133', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='re*****', MASTER_AUTO_POSITION = 1; start slave; show slave status\G show global variables like 'read_only'; set global read_only=1; show global variables like 'read_only';


db1：

show slave hosts; +-----------+---------------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+---------------+------+-----------+--------------------------------------+ | 155 | 192.168.1.155 | 3306 | 133 | 896d4156-9846-11e3-a3d2-02619050bb48 | | 150 | 192.168.1.150 | 3306 | 133 | afee6fde-978f-11e3-9f2a-02883e765295 | +-----------+---------------+------+-----------+--------------------------------------+


With this, the replication configuration has been restored.  
MASTER\_AUTO\_POSITION = 1; It's really convenient.  
If you delete mysql.failover\_console you have to skip it,  
Reset MASTER with db1; I decided to do so.  
  
・Consider using an external script to replace the VIP  

First, about the option to recognize scripts.

--exec-fail-check Specify a script to run regularly at predefined intervals for each default check. --exec-before Specify the script to run before starting failover --exec-after specifies the script to run when the failover process ends --exec-post-failover: Specifies the script to run after failover (such as a health report)


Maybe I should make about four of the following  

１． I perform an F/O check and go download the master MySQL (maybe a mon-like role). Maybe you don't need it? ）  
　　If you do it, the main system checks the connection to MySQL, so it seems better to drop it when the VIP ping isn't going through.  
２． Before F/O begins, remove the VIP from the old master,  
３． At the end of the F/O process, add VIP to the new master and turn off read\_only  
４． After F/O is completed and the status can be confirmed, report (if you can monitor the situation with Zabbix, you might not need it).  
  
At least two or three are needed.  
Also, depending on the environment, you might need scripts that change the load of the slave when it's load-distributed.  
If manual work after detection causes a tough new master load and secondary faults are likely to occur, this is necessary.  
  
・Try creating a startup script  
  
Since specifying various things is a hassle, it seems better to have one, so I decided to make one  
It seems like you could reuse the Redis one I made the other day.

cat /etc/init.d/mysqlfailover

#!/bin/sh

Simple mysqlfailover init.d script conceived to work on Linux systems

as it does use of the /proc filesystem.

chkconfig: - 85 15

description: mysqlfailover

processname: mysqlfailover

. /etc/rc.d/init.d/functions

EXEC=/usr/bin/mysqlfailover prog=$(basename $EXEC)

PIDFILE=/var/run/mysqld/failover.pid LOGFILE=/tmp/failover.log PORT=3306 fouser=failover fopass=xxxxxxxx rpluser=repl rplpass=re***** old_master=192.168.1.133 new_master=192.168.1.155 intervalsec=15 exec_failchk=/usr/local/bin/failchk.sh exec_before=/usr/local/bin/before_failover.sh exec_after=/usr/local/bin/after_failover.sh exec_postfail=/usr/local/bin/post_failover.sh

start() { if [ -f $PIDFILE ] then echo "$PIDFILE exists, process is already running or crashed" else $EXEC --master=${fouser}:${fopass}@${old_master}:${PORT}
--candidate=${fouser}:${fopass}@${new_master}:${PORT}
--discover-slaves-login=${fouser}:${fopass}
--log=${LOGFILE} --pidfile=${PIDFILE} -i ${intervalsec}
--rpl-user=${rpluser}:${rplpass} --rediscover
--failover-mode=auto --daemon=start -vv --force ##--exec-after=${exec_after} --exec-before=${exec_before}
##--exec-post-failover=${exec_postfail}
##--exec-fail-check=${exec_failchk}
fi } stop() { if [ ! -f $PIDFILE ] then echo "$PIDFILE does not exist, process is not running" else PID=$(cat $PIDFILE) $EXEC --log=${LOGFILE} --pidfile=${PIDFILE}
--daemon=stop -vv while [ -x /proc/${PID} ] do echo "Waiting for mysqlfailover to shutdown ... " sleep 1 done echo "mysqlfailover stopped" fi }

rh_status() { status $prog }

case "$1" in start) start ;; stop) stop ;; restart) stop start ;; status) rh_status ;; *) echo "Please use start or stop as first argument" ;;

esac

chmod +x mysqlfailover

chkconfig --add mysqlfailover


I tried adding just in case, but it seems like it won't start unless the replication setup is properly set.  
Since it seems to be going to be manual anyway, I decided to turn off automatic startup.

/etc/init.d/mysqlfailover start

Starting failover daemon...

ps -ef|grep fail

root 1095 1 1 00:43 ? 00:00:00 /usr/bin/python /usr/bin/mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306 --discover-slaves-login= failover:xxxxxxxx --log=/tmp/failover.log --pidfile=/var/run/mysqld/failover.pid -i 15 --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=start -vv

/etc/init.d/mysqlfailover status

mysqlfailover (pid 1095) is running...

/etc/init.d/mysqlfailover stop

Stopping failover daemon... mysqlfailover stopped

ps -ef|grep fail


Successfully started, stopped, and obtained status normally.  

Removing mysql.failover\_console and reconfiguring replication is a bit of a hassle,  
It might be better to add --force when starting after start.  
If you don't delete it, Failover mode will fail.

/etc/init.d/mysqlfailover start

Starting failover daemon... Multiple instances of failover daemon found for master 192.168.1.133:3306. If this is an error, restart the daemon with --force. Failover mode changed to 'FAIL' for this instance. Daemon will start in 10 seconds. ......... starting Daemon.


When I installed \--force, Failover mode worked fine as auto.  
  
You might need to check whether multiple processes can start for each different replication configuration,  
This time, since I don't have much time, I'll try it another time.  
  
・Create a script to manage VIP and read\_only  
  
I defined the following in the startup script.

exec_failchk=/usr/local/bin/failchk.sh exec_before=/usr/local/bin/before_failover.sh exec_after=/usr/local/bin/after_failover.sh exec_postfail=/usr/local/bin/post_failover.sh


The minimum you should make is as follows.  
２． Before starting F/O, remove the VIP from the old master and download MySQL from the old master  
３． At the end of the F/O process, add VIP to the new master and turn off read\_only  
It seems it doesn't have to be a shell script.  
  
Since I'm testing on AWS, I sometimes have to communicate with the API to reinstall VIP.  
  
Command for manually re-attaching VIP:

ip addr del 10.35.31.202/23 brd 10.35.31.255 dev eth0 ip addr add 10.35.31.202/23 brd 10.35.31.255 dev eth0


Reference part of the sytem command called by an external script switching VIP in MHA:

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 \"; system("ssh $ssh_user@$orig_master_host " $ssh_stop_mysqld ""); } my $ssh_start_vip = "sudo /sbin/ip addr add $vip brd $brd dev $nic"; my $ssh_stop_vip = "sudo /sbin/ip addr del $vip brd $brd dev $nic"; my $ssh_stop_mysqld = "sudo /sbin/service mysql stop";


I guess I need to make SSH key authentication possible for VIP replacement.  
I wonder if I need sudo permissions.  
  
For now, I use SSH key authentication (here, root) from the management server 04 to each DB  
db4：

ssh-copy-id -i ~/.ssh/id_rsa komiya-test-mysql01

ssh-copy-id -i ~/.ssh/id_rsa komiya-test-mysql02

ssh-copy-id -i ~/.ssh/id_rsa komiya-test-mysql03

visudo

#Defaults requiretty


I'll try creating a script based on the HA that switches private-IP on AWS.  
Since the host running the run is not a manager, adjustments in that area are likely necessary.  
It may be necessary to predefine the ENI of both the old and new masters.  
  
For now, I added VIP to the old master with commands and checked.  

aws ec2 assign-private-ip-addresses \
--network-interface-id eni-27a2a945 \
--private-ip-addresses 192.168.1.222 --allow-reassignment

ip addr add 192.168.1.222/24 brd 192.168.1.255 dev eth0


Confirmation by ping from another server (AWS management also requires VIP access to ping)

ping 192.168.1.222

vi /usr/local/bin/before_failover.sh

#!/bin/bash

before_failover.sh: Before starting F/O, remove the VIP from the old master and install MySQL from the old master

Dependency: mysqlfailover,after_failover.sh

Update History: 20140331 - create komiyay

export PATH=$PATH:/usr/local/bin export AWS_CONFIG_FILE=/root/.ec2/aws.config datetime=date +%Y%m%d_%H%M%S mailto=< recipient email address> oldmaster=192.168.1.133 newmaster=192.168.1.155 vip=192.168.1.222 subnetmask=24 brd=192.168.1.255 nic=eth0 ssh_user=root oldmaster_eni=aws ec2 describe-network-interfaces --filters Name=addresses.private-ip-address,Values=${oldmaster} --query 'NetworkInterfaces[]. [NetworkInterfaceId]' --output text

ssh_stop_vip="sudo /sbin/ip addr del ${vip}/${subnetmask} brd ${brd} dev ${nic}" ssh_stop_mysqld="sudo /sbin/service mysql stop"

stop_vip() { echo "Disabling the VIP on old master" ssh ${ssh_user}@${oldmaster} "${ssh_stop_vip}" }

stop_mysql() { echo "Stop mysql on old master" ssh ${ssh_user}@${oldmaster} "${ssh_stop_mysqld}" }

aws_pip_unassign() { echo "Disabling the aws's virtual private ip addres" aws ec2 unassign-private-ip-addresses
--network-interface-id ${oldmaster_eni}
--private-ip-addresses ${vip}|tee /tmp/res.txt }

main

#さきにsshの接続性を確認してダメならawsの処理だけする ssh ${ssh_user}@${oldmaster} ls /etc/hosts result_ssh=echo $? if [ $result_ssh -eq 0 ]; then aws_pip_unassign grep true /tmp/res.txt res_unassign=echo $? if [ ${res_unassign} -ne 0 ]; then printf "Error: aws privateip unassign fail.\nfailover NG."
|mail -s "mysqlfailover-err_${datetime}" ${mailto} exit 1 fi stop_vip result_vip=echo $? if [ ${result_vip} -ne 0 ]; then printf "Error: stop vip is fail.\nfailover NG."
|mail -s "mysqlfailover-err_${datetime}" ${mailto} exit 1 fi stop_mysql result_mysql=echo $? if [ ${result_mysql} -ne 0 ]; then printf "Error: stop mysql is fail.\nfailover NG."
|mail -s "mysqlfailover-err_${datetime}" ${mailto} exit 1 fi else aws_pip_unassign grep true /tmp/res.txt res_unassign=echo $? if [ ${res_unassign} -ne 0 ]; then printf "Error: aws privateip unassign fail.\nfailover NG."
|mail -s "mysqlfailover-err_${datetime}" ${mailto} exit 1 fi printf "Warning: old master is down.\nssh NG."
|mail -s "mysqlfailover-info_${datetime}" ${mailto} fi

exit 0

chmod +x /usr/local/bin/before_failover.sh

vi /usr/local/bin/after_failover.sh

#!/bin/bash

after_failover.sh: Attach VIP to the new master at the end of the F/O process and turn off the read_only

Dependency: mysqlfailover,before_failover.sh

Update History: 20140331 - create komiyay

export PATH=$PATH:/usr/local/bin export AWS_CONFIG_FILE=/root/.ec2/aws.config datetime=date +%Y%m%d_%H%M%S mailto=< recipient email address> oldmaster=192.168.1.133 newmaster=192.168.1.155 vip=192.168.1.222 subnetmask=24 brd=192.168.1.255 nic=eth0 ssh_user=root mysql_user=root mysql_pass=/path_to_file newmaster_eni=aws ec2 describe-network-interfaces --filters Name=addresses.private-ip-address,Values=${newmaster} --query 'NetworkInterfaces[]. [NetworkInterfaceId]' --output text

ssh_start_vip="sudo /sbin/ip addr add ${vip}/${subnetmask} brd ${brd} dev ${nic}"

start_vip() { ssh ${ssh_user}@${newmaster} "${ssh_start_vip}" }

set_readonly() { mysql -u ${mysql_user} -p${mysql_pass} -h ${newmaster} -e 'set global read_only=0;' }

aws_pip_assign() { echo "enabling the aws's virtual private ip addres" aws ec2 assign-private-ip-addresses
--network-interface-id ${newmaster_eni}
--private-ip-addresses ${vip} --allow-reassignment|tee /tmp/res.txt }

main

set_readonly result_ro=echo $? if [ ${result_ro} -ne 0 ]; then printf "Error: set read only off is fail.\nfailover NG."
|mail -s "mysqlfailover-err_${datetime}" ${mailto} exit 1 fi

aws_pip_assign grep true /tmp/res.txt result_pip=echo $? if [ ${result_pip} -ne 0 ]; then printf "Error: aws privateip assign fail.\nfailover NG."
|mail -s "mysqlfailover-err_${datetime}" ${mailto} exit 1 fi

start_vip result_vip=echo $? if [ ${result_vip} -ne 0 ]; then printf "Error: start vip is fail.\nfailover NG."
|mail -s "mysqlfailover-err_${datetime}" ${mailto} exit 1 fi

exit 0

chmod +x /usr/local/bin/after_failover.sh


Unit testing here

bash -x /usr/local/bin/before_failover.sh


Mainly checking the old master (whether the IP has been removed and if MySQL is down)

bash -x /usr/local/bin/after_failover.sh


Mainly checking the new master (whether the IP is on or if the read\_only is off)  
  
Modify the startup script

cp -p /etc/init.d/mysqlfailover{,.date %Y%m%d}

vi /etc/init.d/mysqlfailover

diff /etc/init.d/mysqlfailover{,.date %Y%m%d}

date: invalid date `%Y%m%d' 39,40c39,40 < --failover-mode=auto --daemon=start -vv --force
< --exec-before=${exec_before} --exec-after=${exec_after}

            --failover-mode=auto --daemon=start -vv --force
            ##--exec-after=${exec_after} --exec-before=${exec_before} \

service mysqlfailover start

Starting failover daemon...

ps -ef|grep fail

root 1229 1 2 14:56 ? 00:00:00 /usr/bin/python /usr/bin/mysqlfailover --master=failover:xxxxxxxx@192.168.1.133:3306 --candidate=failover:xxxxxxxx@192.168.1.155:3306 --discover-slaves-login= failover:xxxxxxxx --log=/tmp/failover.log --pidfile=/var/run/mysqld/failover.pid -i 15 --rpl-user=repl:re***** --rediscover --failover-mode=auto --daemon=start -vv --force --exec-before=/usr/local/bin/before_failover.sh --exec-after=/usr/local/bin/after_failover.sh

view /tmp/failover.log


Here, let's try downloading MySQL on DB1.  
  
Check in advance  

db1,2:

ip addr show mysql> show slave hosts;


db2,3:

show slave status\G show global vairables like 'read_only';


db3:

ping 192.168.1.222


db4:

aws ec2 describe-network-interfaces
--filters Name=addresses.private-ip-address,Values=192.168.1.222
--query 'NetworkInterfaces[]. [NetworkInterfaceId]' --output text

aws ec2 describe-network-interfaces \

--filters Name=addresses.private-ip-address,Values=192.168.1.133
--query 'NetworkInterfaces[]. [NetworkInterfaceId]' --output text eni-27a2a945

aws ec2 describe-network-interfaces \

--filters Name=addresses.private-ip-address,Values=192.168.1.155
--query 'NetworkInterfaces[]. [NetworkInterfaceId]' --output text eni-6cb3b90e

tail -f /tmp/failover.log


Try dropping the master  
db1:

service mysql stop


Double-check the previously confirmed IP address and others  
  
db2:  
DB2 comes with VIP

ip addr show eth0|grep sec

inet 192.168.1.222/24 brd 192.168.1.255 scope global secondary eth0


Even AWS-style private IPs have been moved to DB2's NIC.

aws ec2 describe-network-interfaces \

--filters Name=addresses.private-ip-address,Values=192.168.1.222
--query 'NetworkInterfaces[]. [NetworkInterfaceId]' --output text eni-6cb3b90e


The read\_only on db2 is turned off,

mysql> show global variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | OFF | +---------------+-------+


Watching db3 as the master of db2

mysql> show slave hosts; +-----------+---------------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+---------------+------+-----------+--------------------------------------+ | 150 | 192.168.1.150 | 3306 | 155 | afee6fde-978f-11e3-9f2a-02883e765295 | +-----------+---------------+------+-----------+--------------------------------------+


db2 slave information has been reset

mysql> show slave status\G Empty set (0.00 sec)


Just to be sure, I checked in db1 that the same command didn't have a VIP appended.

ip addr show eth0|grep sec


So, since the expected operation was done, the verification is complete.  
I'll try downloading the OS and skipping report scripts depending on circumstances.  

・Reconfigure  

db4:

aws ec2 unassign-private-ip-addresses
--network-interface-id eni-6cb3b90e
--private-ip-addresses 192.168.1.222|tee /tmp/res.txt

aws ec2 assign-private-ip-addresses
--network-interface-id eni-27a2a945
--private-ip-addresses 192.168.1.222 --allow-reassignment

aws ec2 describe-network-interfaces
--filters Name=addresses.private-ip-address,Values=192.168.1.222
--query 'NetworkInterfaces[]. [NetworkInterfaceId]' --output text


db2:

ip addr del 192.168.1.222/24 brd 192.168.1.255 dev eth0 ip addr show db1： ip addr add 192.168.1.222/24 brd 192.168.1.255 dev eth0 ip addr show


*The rep was mentioned earlier but is being reposted  
db1：

netstat -tanp service mysql start mysql -u root -p show slave hosts; show global variables like 'read_only'; select * from mysql.failover_console; delete from mysql.failover_console; select * from mysql.failover_console; RESET MASTER;


db2:

mysql -u root -p show master status\G show slave status\G stop slave; RESET SLAVE ALL; CHANGE MASTER TO MASTER_HOST='192.168.1.133', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='re*****', MASTER_AUTO_POSITION = 1; start slave; show slave status\G show slave hosts; show global variables like 'read_only'; set global read_only=1;


db3:

mysql -u root -p show slave status\G stop slave; RESET SLAVE ALL; show slave status\G CHANGE MASTER TO MASTER_HOST='192.168.1.133', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='re*****', MASTER_AUTO_POSITION = 1; start slave; show slave status\G show global variables like 'read_only'; set global read_only=1; show global variables like 'read_only';

service mysqlfailover start ps -ef|grep fail


By the way, the switching speed was about the same as in MHA, and it switched smoothly and quickly.  
However, there was a log showing three times waiting for interbal after detecting that the master might have crashed, so by default, the wait time is probably about 45 seconds.  
Compared to MHA, I think it's that you don't need to keep a relay log of the slave for a certain period, and you can run it in demon mode.  
There is a limitation that 5.6 requires GTID to be ON, but if GTID is on in 5.6, the only option for HA for now might be mysqlfailover.  
But it turns out [MHA 0.56 (GTID-compatible 0.56](https://code.google.com/p/mysql-master-ha/wiki/ReleaseNotes#Changes_in_Manager_0.56_\(Apr_1_2014\)) has appeared (2014/4)!  
What an incredible timing.  
  
The reason the logs are at midnight isn't fixed and it's EDT.  
Thank you very much for reading at length.

See more

Related Articles