Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Standard replication breaks for no apparent reason.

mysql> SELECT @@version, @@version_comment;
+---------------+----------------------------------------------------------------------------+
| @@version | @@version_comment |
+---------------+----------------------------------------------------------------------------+
| 5.6.15-56-log | Percona XtraDB Cluster (GPL), Release 25.5, Revision 759, wsrep_25.5.r4061 |
+---------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | OFF |
+---------------+-------+
1 row in set (0.00 sec)

Crash safe replications enabled:

master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1

Slave is running fine:

# mysql -e "SHOW SLAVE STATUS\G" | grep "Slave"
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

but no connected slaves on MASTER and IO thread disappears from MASTER after some time:

mysql> SELECT * FROM information_schema.processlist WHERE command = 'Binlog Dump';
Empty set (0.10 sec)

mysql> SHOW SLAVE HOSTS;
Empty set (0.00 sec)

Master:

mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 568210 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Slave:

# mysql -e "SHOW SLAVE STATUS\G" | grep "Master_Log"
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 568210
        Relay_Master_Log_File: mysql-bin.000003
          Exec_Master_Log_Pos: 568210

Master:

mysql> CREATE DATABASE IF NOT EXISTS repl_test; SHOW MASTER STATUS;
Query OK, 1 row affected (0.00 sec)

+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 568333 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

no changes on slave:

# mysql -e "SHOW SLAVE STATUS\G" | grep "Master_Log"
    Master_Log_File: mysql-bin.000003
    Read_Master_Log_Pos: 568210
    Relay_Master_Log_File: mysql-bin.000003
    Exec_Master_Log_Pos: 568210

slave picks up the changes after IO_THREAD restart:

# mysql -e "STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;"
# mysql -e "SHOW SLAVE STATUS\G" | grep "Master_Log"
    Master_Log_File: mysql-bin.000003
    Read_Master_Log_Pos: 568333
    Relay_Master_Log_File: mysql-bin.000003
    Exec_Master_Log_Pos: 568333

Update: Fri Jul 18 14:15:59 BST 2014

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: $IP
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 433
               Relay_Log_File: mysql-relay.000265
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             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: 433
              Relay_Log_Space: 710
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ca-cert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/mysql/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/mysql/client-key.pem
        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: 1124732721
                  Master_UUID: 4412a455-e1d0-11e3-835a-5254007fe78d
             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: /etc/mysql/ca-cert.pem
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
share|improve this question

2 Answers 2

up vote 4 down vote accepted

What you're experiencing can easily occur in a low traffic situation, particularly if the two servers are separated by a firewall or other device that implements stateful packet inspection. (This is a situation that can occur, for example, within Amazon EC2/VPC). The intermediate networking hardware can "forget" about the TCP connection between the servers, because when there's no data being replicated, the connection is left idle.

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.09 sec)

mysql> CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 60;
Query OK, 0 rows affected (0.13 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

When the slave connects to the master, it will request that the master inject a heartbeat message into the binlog stream every 60 seconds, but only when there have been no replication events for that amount of time -- so it has no impact when there's a lot of traffic, but when traffic is light, the heartbeat events will be sent, and the connection will stay alive.

Note that CHANGE MASTER TO is typically a disruptive command that can reset your replication configuration. In this case, if MASTER_HEARTBEAT_PERIOD is the only argument provided, the slave configuration does not get reset.

http://dev.mysql.com/doc/refman/5.6/en/change-master-to.html

Also consider setting the global variable slave_net_timeout to a value shorter than the default, but not less than twice the value you use for the master heartbeat period. This will cause the slave to drop and retry the connection to the master if nothing happens on the replication stream within the configured period of time.

share|improve this answer
    
Thanks for your help, I couldn't figure it out what's going on. I enabled general log on slave and query arrived around one hour later comparing to binary log however changing MASTER_HEARTBEAT_PERIOD solved this problem. –  HTF Jul 19 at 10:31

Edit: After my first assumptions, my next bet was on network problems. Use Michael advice, they have my bet and vote.

Probably your problems is the combination of wsrep_on = OFF + DDL. Strange things may happen in that case with the binlog (a.k.a. they may not be logged correctly). Make sure to enable it even if you only have one node or you isolate it using galera-aware commands.

Make sure you also activate log_slave_updates.

Are master and slave part of the same Galera cluster?

share|improve this answer
    
Hi, thanks for your reply, I don't use Galera feature at the moment but I faced the same problem with standalone MySQL server. This is just a standard MySQL replication setup and with wsrep enabled I hit the following bug - bugs.launchpad.net/percona-xtradb-cluster/+bug/1313756 –  HTF Jul 18 at 12:46
    
I would recommend you then to use a standard version of Percona Server. Cluster versions have a penalty on latency and, as you can see, more prone to errors (Galera 2 is relatively recent). –  jynus Jul 18 at 12:52
    
I tested this with standard Percona server and I had the same problem. –  HTF Jul 18 at 12:53
    
Are you using GTID anywhere? Do you get something on your error log? Can you show the rest of the show slave status after the problem? –  jynus Jul 18 at 13:02
    
No GTID in use, please see my update: Update: Fri Jul 18 14:15:59 BST 2014 with slave status output, log positions are different because I've tried to reset both, master and slave but this is status after the problem. –  HTF Jul 18 at 13:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.