Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 4 '09 at 22:10

This question came from our site for professional and enthusiast programmers.

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

3 Answers 3

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

share|improve this answer

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.

share|improve this answer

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.

  1. Stop replication.
  2. Stop all operations onto the databases (no more access, read or write)
  3. Shutdown both databases fully.
  4. 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
  5. Restart the databases.
  6. Restart your replication.
share|improve this answer
    
He's tagged it mysql, so I assume that's a pretty good bet. –  Matthew Scharley Dec 4 '09 at 20:27
    
crap! I did not see! :( –  Terry Felkrow Dec 4 '09 at 20:33

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.