Description:
mysqlrpladmin switchover breaks replication if the password is omitted for the rpl-user and the replication user does not have host-specific grants.
$ mysqlrpladmin --verbose --master=testbeaverlake --new-master=testslab switchover --demote-master --slaves=testslab,testslab2,testslab3,testslab4,testslab6 --rpl-user=Repl
# Checking privileges.
# WARNING: Errant transactions check skipped (GTID not enabled for the whole topology).
# Performing switchover from master at testbeaverlake:3306 to slave at testslab:3306.
# Checking candidate slave prerequisites.
# Checking eligibility of slave testslab:3306 for candidate.
# Slave connected to master ... Ok
# Slave not behind master ... Ok
# Logging filters agree ... Ok
# Binary logging turned on ... Ok
# Replication user exists ... Ok
# Checking slaves configuration to master.
# 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.
# Creating replication user if it does not exist.
# Blocking writes on master.
# LOCK STRING: FLUSH TABLES WITH READ LOCK
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Executing stop on slave testslab:3306 Ok
# Executing stop on slave testslab2:3306 Ok
# Executing stop on slave testslab3:3306 Ok
# Executing stop on slave testslab4:3306 Ok
# Executing stop on slave testslab6:3306 Ok
# UNLOCK STRING: UNLOCK TABLES
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Executing CHANGE MASTER on testslab2:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Executing CHANGE MASTER on testslab3:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Executing CHANGE MASTER on testslab4:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Executing CHANGE MASTER on testslab6:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Executing CHANGE MASTER on testbeaverlake:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Starting all slaves.
# Performing START on all slaves.
# Executing start on slave testslab2:3306 Ok
# Executing start on slave testslab3:3306 Ok
# Executing start on slave testslab4:3306 Ok
# Executing start on slave testslab6:3306 Ok
# Executing start on slave testbeaverlake:3306 Ok
# Checking slaves for errors.
# testslab2:3306 status: Ok
# testslab3:3306 status: Ok
# testslab4:3306 status: Ok
# testslab6:3306 status: Ok
# testbeaverlake:3306 status: Ok
# Switchover complete.
# Attempting to contact testslab ... Success
# Attempting to contact testslab2 ... Success
# Attempting to contact testslab3 ... Success
# Attempting to contact testslab4 ... Success
# Attempting to contact testslab6 ... Success
# Attempting to contact testbeaverlake ... Success
#
# Replication Topology Health:
+-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+
| host | port | role | state | gtid_mode | health | version | master_log_file | master_log_pos | IO_Thread | SQL_Thread | Secs_Behind | Remaining_Delay | IO_Error_Num | IO_Error | SQL_Error_Num | SQL_Error | Trans_Behind |
+-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+
| testslab | 3306 | MASTER | UP | OFF | OK | 5.6.21-70.1-log | bin.002753 | 298995 | | | | | | | | | |
| testbeaverlake | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 60 retries: 1 | 5.6.23-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 60 retries: 1 | 0 | | |
| testslab2 | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 5.6.21-70.1-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 0 | | |
| testslab3 | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 5.6.21-70.1-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 0 | | |
| testslab4 | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 5.6.21-70.1-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 0 | | |
| testslab6 | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 5.6.22-71.0-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 0 | | |
+-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+
# ...done.
How to repeat:
Create a set of replication servers using the replication user Repl@%
GRANT REPLICATION SLAVE ON *.* TO Repl IDENTIFIED BY 'somepassword';
CHANGE MASTER TO MASTER_HOST = 'testbeaverlake', MASTER_USER = 'Repl', MASTER_PASSWORD = 'somepassword', MASTER_LOG_FILE='bin.000004', MASTER_LOG_POS=0, MASTER_CONNECT_RETRY=15, MASTER_HEARTBEAT_PERIOD=1 ;
Add a rpladmin user to the cluster
GRANT SUPER, GRANT OPTION, SELECT, INSERT, RELOAD, DROP, CREATE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to rpladmin IDENTIFIED BY 'someotherpassword';
mysql_config_editor set --login-path=testbeaverlake --host=testbeaverlake --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab --host=testslab --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab1 --host=testslab1 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab2 --host=testslab2 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab3 --host=testslab3 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab4 --host=testslab4 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab5 --host=testslab5 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab6 --host=testslab6 --user=rpladmin --password --port=3306
Execute a command to switch the master to one of the slaves.
mysqlrpladmin --verbose --master=testbeaverlake --new-master=testslab switchover --demote-master --slaves=testslab,testslab2,testslab3,testslab4,testslab6 --rpl-user=Repl
Suggested fix:
Do not set passwd_hash to the empty string when SELECT_RPL_USER_PASS_QUERY fails to find a match since SELECT PASSWORD('') returns the empty string which then matches the empty passwd_hash
Description: mysqlrpladmin switchover breaks replication if the password is omitted for the rpl-user and the replication user does not have host-specific grants. $ mysqlrpladmin --verbose --master=testbeaverlake --new-master=testslab switchover --demote-master --slaves=testslab,testslab2,testslab3,testslab4,testslab6 --rpl-user=Repl # Checking privileges. # WARNING: Errant transactions check skipped (GTID not enabled for the whole topology). # Performing switchover from master at testbeaverlake:3306 to slave at testslab:3306. # Checking candidate slave prerequisites. # Checking eligibility of slave testslab:3306 for candidate. # Slave connected to master ... Ok # Slave not behind master ... Ok # Logging filters agree ... Ok # Binary logging turned on ... Ok # Replication user exists ... Ok # Checking slaves configuration to master. # 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. # Creating replication user if it does not exist. # Blocking writes on master. # LOCK STRING: FLUSH TABLES WITH READ LOCK # Waiting for slaves to catch up to old master. # Stopping slaves. # Performing STOP on all slaves. # Executing stop on slave testslab:3306 Ok # Executing stop on slave testslab2:3306 Ok # Executing stop on slave testslab3:3306 Ok # Executing stop on slave testslab4:3306 Ok # Executing stop on slave testslab6:3306 Ok # UNLOCK STRING: UNLOCK TABLES # Demoting old master to be a slave to the new master. # Switching slaves to new master. # Executing CHANGE MASTER on testslab2:3306. # CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995 # Executing CHANGE MASTER on testslab3:3306. # CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995 # Executing CHANGE MASTER on testslab4:3306. # CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995 # Executing CHANGE MASTER on testslab6:3306. # CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995 # Executing CHANGE MASTER on testbeaverlake:3306. # CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995 # Starting all slaves. # Performing START on all slaves. # Executing start on slave testslab2:3306 Ok # Executing start on slave testslab3:3306 Ok # Executing start on slave testslab4:3306 Ok # Executing start on slave testslab6:3306 Ok # Executing start on slave testbeaverlake:3306 Ok # Checking slaves for errors. # testslab2:3306 status: Ok # testslab3:3306 status: Ok # testslab4:3306 status: Ok # testslab6:3306 status: Ok # testbeaverlake:3306 status: Ok # Switchover complete. # Attempting to contact testslab ... Success # Attempting to contact testslab2 ... Success # Attempting to contact testslab3 ... Success # Attempting to contact testslab4 ... Success # Attempting to contact testslab6 ... Success # Attempting to contact testbeaverlake ... Success # # Replication Topology Health: +-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+ | host | port | role | state | gtid_mode | health | version | master_log_file | master_log_pos | IO_Thread | SQL_Thread | Secs_Behind | Remaining_Delay | IO_Error_Num | IO_Error | SQL_Error_Num | SQL_Error | Trans_Behind | +-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+ | testslab | 3306 | MASTER | UP | OFF | OK | 5.6.21-70.1-log | bin.002753 | 298995 | | | | | | | | | | | testbeaverlake | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 60 retries: 1 | 5.6.23-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 60 retries: 1 | 0 | | | | testslab2 | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 5.6.21-70.1-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 0 | | | | testslab3 | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 5.6.21-70.1-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 0 | | | | testslab4 | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 5.6.21-70.1-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 0 | | | | testslab6 | 3306 | SLAVE | UP | OFF | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 5.6.22-71.0-log | bin.002753 | 298995 | Connecting | Yes | 0 | No | 1045 | error connecting to master 'Repl@testslab:3306' - retry-time: 15 retries: 2 | 0 | | | +-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+ # ...done. How to repeat: Create a set of replication servers using the replication user Repl@% GRANT REPLICATION SLAVE ON *.* TO Repl IDENTIFIED BY 'somepassword'; CHANGE MASTER TO MASTER_HOST = 'testbeaverlake', MASTER_USER = 'Repl', MASTER_PASSWORD = 'somepassword', MASTER_LOG_FILE='bin.000004', MASTER_LOG_POS=0, MASTER_CONNECT_RETRY=15, MASTER_HEARTBEAT_PERIOD=1 ; Add a rpladmin user to the cluster GRANT SUPER, GRANT OPTION, SELECT, INSERT, RELOAD, DROP, CREATE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to rpladmin IDENTIFIED BY 'someotherpassword'; mysql_config_editor set --login-path=testbeaverlake --host=testbeaverlake --user=rpladmin --password --port=3306 mysql_config_editor set --login-path=testslab --host=testslab --user=rpladmin --password --port=3306 mysql_config_editor set --login-path=testslab1 --host=testslab1 --user=rpladmin --password --port=3306 mysql_config_editor set --login-path=testslab2 --host=testslab2 --user=rpladmin --password --port=3306 mysql_config_editor set --login-path=testslab3 --host=testslab3 --user=rpladmin --password --port=3306 mysql_config_editor set --login-path=testslab4 --host=testslab4 --user=rpladmin --password --port=3306 mysql_config_editor set --login-path=testslab5 --host=testslab5 --user=rpladmin --password --port=3306 mysql_config_editor set --login-path=testslab6 --host=testslab6 --user=rpladmin --password --port=3306 Execute a command to switch the master to one of the slaves. mysqlrpladmin --verbose --master=testbeaverlake --new-master=testslab switchover --demote-master --slaves=testslab,testslab2,testslab3,testslab4,testslab6 --rpl-user=Repl Suggested fix: Do not set passwd_hash to the empty string when SELECT_RPL_USER_PASS_QUERY fails to find a match since SELECT PASSWORD('') returns the empty string which then matches the empty passwd_hash