My previous team member "replicated" a database on a server onto another server. But now it's not in sync anymore

Question

Database 1 is a regular database server. He replicated it to Database 2...a direct clone. Master-Master. They are in "sync" all the time. They each have 40 million rows.

Today, Database 2 is "broken" and seems to return different results than Database 1. That's not normal because they're supposed to be identical.

What suggestion would you make to fix this?

Should I remove Database 2, and make Database 1 replicate to Database 2 all over again? If so, how do I do that?

-I need to know what changes he made and what commands he typed so that I can reverse the replication and create a new replication.

How are we supposed to know what commands your previous team member ran? — womble, Dec 04 '09 at 23:32

score 2 · Answer 1 · answered Dec 04 '09 at 21:15

Before you try anything else, check if DB 2 is a replication slave of DB 1. Execute "SHOW SLAVE STATUS" on DB 2. If it gives you a result, it is likely, that an error occured, that stopped the replication.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
          Master_Host: localhost
          Master_User: root
          Master_Port: 3306
        Connect_Retry: 3
      Master_Log_File: gbichot-bin.005
  Read_Master_Log_Pos: 79
       Relay_Log_File: gbichot-relay-bin.005
        Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB:
  Replicate_Ignore_DB:
           Last_Errno: 0
           Last_Error:
         Skip_Counter: 0
  Exec_Master_Log_Pos: 79
      Relay_Log_Space: 552
      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: 8

Check for the columns named

Slave_IO_Running
Slave_SQL_Running   and
Seconds_behind_master

If the "running" columns show "No" and "seconds_behind_master" is greater than just a few seconds, check for value in "last_error". It should give you an idea of where the error occured. Try to fix it (by modifying the data on DB 2) and give it a new shot by running "START SLAVE" on DB 2. Check "SHOW SLAVE STATUS" immediately afterwards.

To dig deeper, I recommend reading the MySQL manual, especially the chapter about replication setups: MySQL 5.0 Replication

score 0 · Answer 2 · answered Dec 04 '09 at 20:24

You need to first confirm if DB 2 only contains a subset of the DB 1 data, or whether both server contains records that the other doesn't (i.e. DB 1 is missing data also). If this is the case, then remove the replication, wipe DB 2, and start again, doing it properly this time...

If it's not the case, then you've got a lot of SQL to write.

score 0 · Answer 3 · answered Dec 04 '09 at 20:25

It's possible that your replication has been done programmatically, read "manually", as in code. You have no choice but to manually re-sync everything.

From official documentation:

If your database is particularly large, copying the raw data files may be more efficient than using mysqldump and importing the file on each slave.

Stop replication.
Stop all operations onto the databases (no more access, read or write)
Shutdown both databases fully.
RISKY but if you have large data sets, you could copy the "data" directory to Master 2 after stopping replication and flushing binlogs. This will be fast and will 100% replicate your Master. RISKY
Restart the databases.
Restart your replication.

He's tagged it mysql, so I assume that's a pretty good bet. – Matthew Scharley Dec 04 '09 at 20:27 — Matthew Scharley, Dec 04 '09 at 20:27

My previous team member "replicated" a database on a server onto another server. But now it's not in sync anymore

3 Answers3