MySQL Forums
Forum List  »  Utilities

Connection problem with mysqlrpladmin (on MySQL 5.6.5)
Posted by: T. Gip
Date: May 23, 2012 09:33AM

Hello there!

This is my first post in this forum and I hope I found the right place. If not, a hint to where I might get help is greatly appreciated.


The problem in short:
=====================
I'm trying out MySQL 5.6.5 to use automatic failover switching. So far I set up two servers (one Master, one Slave) on a Windows 7 machine; they use GTIDs and replication works like a charm. The problem arises when I try to use mysqlrplcheck or mysqlrpladmin from the MySQL Utilities to check replication health or do switchovers. For some reason the utilities claim that they cannot connect to the slave even though the slave's log clearly states the opposite.
This means that switchover also fails and I really wanna make that work! :)


Details:
========

SETUP:

I set up two servers on localhost with ports 33560 (master) and 33561 (slave). Both have an account named "slave" which have the privileges REPLICATION SLAVE and REPLICATION CLIENT. The options regarding replication are set as follows:

server-id=1 on master, 2 on slave
log-bin=mysql-bin
sync_binlog=1
report-host=localhost
report-port=33560 on master, 33561 on slave
report-user=slave
log-slave-updates
disable-gtid-unsafe-statements
gtid-mode=ON

On the slave, SHOW SLAVE STATUS returns the following:

************************* 1. row *************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave
Master_Port: 33560
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1106
Relay_Log_File: LAP1140-relay-bin.000037
Relay_Log_Pos: 346
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 1106
Relay_Log_Space: 517
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: 1
Master_UUID: c2e9938d-a40a-11e1-a5e2-f0def186cda4
Master_Info_File: D:\Datenbanken\MySQL V5.6 DB1\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: C2E9938D-A40A-11E1-A5E2-F0DEF186CDA4:1-8
Executed_Gtid_Set: 3496862D-A4B2-11E1-AA26-F0DEF186CDA4:1,
C2E9938D-A40A-11E1-A5E2-F0DEF186CDA4:1-8

Also:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON |
+---------------+-------+

So, clearly, the slave is running and replication indeed works which I tested by creating tables and inserting rows.
Furthermore mysqlrplshow displays this grid:

>mysqlrplshow --master=root@localhost:33560
# master on localhost: ... connected.
# Finding slaves for master: localhost:33560

# Replication Topology Graph
localhost:33560 (MASTER)
|
+--- localhost:33561 - (SLAVE)

So far so good...

PROBLEM:

Then I ran mysqlrplcheck just to try it out and this happens:

>mysqlrplcheck.exe --master=root@localhost:33560 --slave=root@localhost:33561 --verbose
# master on localhost: ... connected.
# slave on localhost: ... 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]

master id = 1
slave id = 2

Is slave connected to master? [WARN]
Check master information file [pass]

#
# Master information file:
#
Master_Log_File : mysql-bin.000003
Read_Master_Log_Pos : 1448
Master_Host : 127.0.0.1
Master_User : slave
Master_Password : slave
Master_Port : 33560
Connect_Retry : 60
Master_SSL_Allowed : 0
Master_SSL_CA_File :
Master_SSL_CA_Path :
Master_SSL_Cert :
Master_SSL_Cipher :
Master_SSL_Key :

Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]

Master lower_case_table_names: 1
Slave lower_case_table_names: 1

Checking slave delay (seconds behind master) [pass]
# ...done.

A warning about the connection... the slave's log shows this:

3 Connect root@localhost on
3 Query SET SESSION autocommit='OFF'
3 Query SHOW VARIABLES LIKE 'READ_ONLY'
3 Query COMMIT
3 Query SHOW VARIABLES LIKE 'VERSION'
3 Query COMMIT
3 Query SHOW SLAVE STATUS
3 Query COMMIT
3 Query SHOW SLAVE STATUS
3 Query COMMIT
3 Query SHOW VARIABLES LIKE 'server_id'
3 Query COMMIT
3 Query SHOW VARIABLES LIKE 'server_id'
3 Query COMMIT
3 Query SHOW SLAVE STATUS
3 Query COMMIT
3 Query SHOW SLAVE STATUS
3 Query COMMIT
3 Query SHOW VARIABLES LIKE 'datadir'
3 Query COMMIT
3 Query SHOW SLAVE STATUS
3 Query COMMIT
3 Query SHOW VARIABLES LIKE 'datadir'
3 Query COMMIT
3 Query SELECT (support='YES' OR support='DEFAULT' OR support='ENABLED') AS `exists` FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'innodb'
3 Query COMMIT
3 Query SELECT (plugin_library LIKE 'ha_innodb_plugin%') AS `exists` FROM INFORMATION_SCHEMA.PLUGINS WHERE LOWER(plugin_name) = 'innodb' AND LOWER(plugin_status) = 'active'
3 Query COMMIT
3 Query SELECT plugin_version, plugin_type_version FROM INFORMATION_SCHEMA.PLUGINS WHERE LOWER(plugin_name) = 'innodb'
3 Query COMMIT
3 Query SHOW VARIABLES LIKE 'have_innodb'
3 Query COMMIT
3 Query SELECT UPPER(engine), UPPER(support) FROM INFORMATION_SCHEMA.ENGINES ORDER BY engine
3 Query COMMIT
3 Query SHOW VARIABLES LIKE 'lower_case_table_names'
3 Query COMMIT
3 Query SHOW VARIABLES LIKE 'lower_case_table_names'
3 Query COMMIT
3 Query SHOW SLAVE STATUS
3 Query COMMIT

I could not make sense of this and decided to proceed with mysqlrpladmin to check replication health:

>mysqlrpladmin.exe --master=root@localhost:33560 --slave=root@localhost:33561 --verbose --format=vertical health
# Checking privileges.
# Attempting to contact localhost ... Success (this localhost is the master)
# Attempting to contact localhost ... Server is reachable (this localhost is the slave)
#
# Replication Topology Health:
************************* 1. row *************************
host: localhost
port: 33560
role: MASTER
state: UP
gtid_mode: ON
health: OK
version: 5.6.5-m8-log
master_log_file: mysql-bin.000003
master_log_pos: 1448
IO_Thread:
SQL_Thread:
Secs_Behind:
Remaining_Delay:
IO_Error_Num:
IO_Error:
SQL_Error_Num:
SQL_Error:
Trans_Behind:
************************* 2. row *************************
host: localhost
port: 33561
role: SLAVE
state: WARN
gtid_mode:
health: Cannot connect to slave.
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:
2 rows.
# ...done.

Again, the log states that connection was successful:

3 Connect root@localhost on
3 Query SET SESSION autocommit='OFF'
3 Query SHOW VARIABLES LIKE 'READ_ONLY'
3 Query COMMIT
3 Query SELECT CURRENT_USER()
3 Query COMMIT
3 Query SHOW GRANTS FOR 'root'@'localhost'
3 Query COMMIT
3 Query SHOW GRANTS FOR 'root'@'%'
3 Query SHOW DATABASES
3 Query COMMIT
3 Query SHOW SLAVE STATUS
3 Query COMMIT

All the while these commands work fine from the mysql console and SHOW SLAVE STATUS returns what was shown above.
In conclusion: I'm out of ideas and will appreciate any help! Thanks in advance.

gip

Options: ReplyQuote


Subject
Views
Written By
Posted
Connection problem with mysqlrpladmin (on MySQL 5.6.5)
3195
May 23, 2012 09:33AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.