Mysql – Replication not updating all tables

MySQLreplication

  • Master db MySQL db Server 2012
  • Slave db MySQL Win7 XAMPP
  • DB size 500MB
  • Table count 42

I have setup the replication successfully however it stopped last week and my slave was showing the error Slave_SQL_Running No. I realised that it was looking at an incorrect log file (00004 whereas it should have been 00006).

I have since sorted this by;

At the MASTER;

SHOW MASTER STATUS;
Copied the values of MASTER_LOG_FILE and MASTER_LOG_POS.

At the SLAVE;

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98; (<- example values)
START SLAVE;
SHOW SLAVE STATUS \G;

On my master I tested the replication by editing the table members – I edited one of the row values (from 85 to 86 – this successfully replicated in my slave). However I notice that on my master members table there are 70652 members but on my slave there are only 70056.

What could be the problem? Replication seems to be working but totals aren't. New members are added to the members table each day, but the aren't being added to my slave members table.

The results of my slave status table (from phpmyadmin) are;

Slave_IO_State  Waiting for master to send event
Master_Host xxx.xxx.xxx.xxx
Master_User repl
Master_Port 3306
Connect_Retry   60
Master_Log_File mysql-bin.000006
Read_Master_Log_Pos 787956776
Relay_Log_File  mysql-relay-bin.000004
Relay_Log_Pos   624412
Relay_Master_Log_File   mysql-bin.000006
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 787956776
Relay_Log_Space 788197
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

Is there something else that I could check or test?

Best Answer

In the end I had to reset the slave completely and set up replication again. Not sure why but this seems to be a common problem/solution as I can see from other articles online.