mySQL Master not getting data from slave

Question

I have followed the instructions on the Web on how to set up master-slave mySQL replication and I have spent a day and half trying to get it to work.

The master is simply not getting new data at all. I looked at phpMyAdmin and I see "replication" enabled on both systems - slave/master. But nothing shows up. The DB structure is identical on both systems.

It has never generated any errors. The slave is able to connect to the master as shown in show slave status command. The PROCEESSLIST showed no errors. I used the replicate_do_db set on slave. If I unplug the ethernet cable, it will generate error. Plug it back in, and it just does nothing (except that the error went way). I changed the password and it generated error as expected but put it back to right password and it just went silent with no errors.

It's so aggravating as it doesn't tell me WHY it's not updating at all or why it's not pushing data to master. I am under the impression that any new data that's entered in slave DB should automatically be replicated to the master and the master should be showing data. Is this correct?

Is there any way that I can find what went wrong considering that the replication setup tells no errors whatsoever? How do I force the master to get data from the slave? Is there a way to do it?

Answer 1

I think you what you are describing is a replication topology called a Star Topology

I wrote about that before

• Apr 18, 2013 : Single slave - multiple master MySQL replication (Approach #1)
• Apr 28, 2011 : MySQL in star topology (ServerFault)
• Apr 26, 2011 : MySQL in star topology (Stack Overflow)

In that event, the Central Server must serve as a Slave in MySQL Replication paradigm until all changes in the binary logs of the Master (outside server) are pulled and executed in the Central Server.

If you pull the plug, replication breaks. It will lose its place.

MySQL does have a setting to control this in the CHANGE MASTER TO command. By default, the retry interval for MySQL 5.6 is 86400 seconds. That's 24 hours. Before MySQL 5.6, it was 60 seconds. To change the retry interval simply run,

STOP SLAVE;
CHANGE MASTER TO MASTER_CONNECT_RETRY = 60;
START SLAVE:

CAVEAT

Even if you pull the plug, it is possible for replication to lose its place and not reconnect because of incomplete transmission of binlog events. When that happens, you must reset the replication coordinates manually.

Here is how to fix replication manually in this regard:

Step 1: STOP SLAVE;

Step 2: SHOW SLAVE STATUS\G

Suppose the display looks like this

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000255
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           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: 858190247
            Relay_Log_Space: 873772
            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
1 row in set (0.00 sec)

Step 4: Record Relay_Master_Log_File and Exec_Master_Log_Pos

In this case, 'mysql-bin.000254' and 858190247

Step 5: Use Replication Coordinates from Step 4

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000254',MASTER_LOG_POS=858190247;

Step 6: Start Replication

START SLAVE;

Give it a Try !!!