My system administrator set up 2 databases that sync. Master-Master. However, these two databases are not syncing

Question

DB1 and DB2.

I made changes to DB1, and it does not seem to be on DB2. When I do "SHOW SLAVE STATUS\G" on DB2, there seems to be an error:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 
                Master_User: 
                Master_Port: 
              Connect_Retry: 60
            Master_Log_File: mysql-bin.0005496
        Read_Master_Log_Pos: 5445649315
             Relay_Log_File: mysqld-relay-bin.0041705
              Relay_Log_Pos: 1624302119
      Relay_Master_Log_File: mysql-bin.0004461
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1062
                 Last_Error: Error 'Duplicate entry '4779' for key 1' on query. Default database: 'falc'. Query: 'INSERT INTO `log` (`anon_id`, `created_at`, `query`, `episode_url`, `detail_id`, `ip`) VALUES ('fdzn1d45kMavF4qbyePv', '2009-11-19 04:19:13', 'amazon', '', '', '130.126.40.57')'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 162301982
            Relay_Log_Space: 136505187184
            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: NULL
1 row in set (0.00 sec)

Then, I did show tables, and it seems like DB2 is lacking a table that I created on DB1...that means that for some reason, DB2 stopped syncing with DB1.

How can I simply allow them to be in full synchronization again?

All I want is DB2 to be exactly the same as DB1!

Why aren't you asking your sysadmin this? – womble Dec 04 '09 at 23:29 — womble, Dec 04 '09 at 23:29

score 1 · Answer 1 · answered Dec 05 '09 at 00:32

Primary key conflicts can be avoided by setting the auto_increment_increment and auto_increment_offset values on both servers.

Set auto_increment_increment to the same value on both servers (for example, 4) Set auto_increment_offset to DIFFERENT values on both servers (for example, 1 on server A, 2 on server B).

This will help avoid conflicting auto increment primary keys in the future. Both mysql servers need to be restarted for this scheme to take effect, and they should ideally be in sync when you do restart them.

Your sysadmin has probably already done all this, but it does look like an auto_increment key conflict.

As for your current predicament, inspect the row with id 4779 in the log table on both servers. If the rows are exactly the same, then you can safely run SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; to get replication running again.

If they are different, you're going to need to decide which one to keep.

Keep in mind that any SQL insert/update/delete statement that you issue to fix this will be run on both servers eventually, so think very carefully.

You can use the SQL_LOG_BIN=0; instruction to run a statement only on one box to fix a replication fault.

Good luck!

If an auto-increment is involved, you need to set the [skip counter](http://dev.mysql.com/doc/refman/5.0/en/replication-problems.html) to 2. — Insyte, Dec 05 '09 at 01:35

score 0 · Answer 2 · answered Dec 04 '09 at 22:38

0

MySQL replication is A->B not A<->B, so make sure that you're updating the master database(A), which is the one that replicates down to the slave database(B). If you're not, then that's probably your problem.

answered Dec 04 '09 at 22:38

Satanicpuppy

5,946
1
17
18

Well, my system administrator told me that I can make changes to either DB1 or DB2, and they will sync automatically. – Alex Dec 04 '09 at 22:46
Master-Master replication... – Alex Dec 04 '09 at 22:46

score 0 · Answer 3 · answered Dec 05 '09 at 00:13

0

I would guess thta you have a bad table setup somewhere. Replication stopped because a replicating query failed:

Duplicate entry '4779' for key 1' on query

you can set the skip query count to 1, then continue processing so that the replicant will skip the bad statement and continue replicating. Your table will then (probably) show up shortly after replication resumes. However, you have to ask yourself, "Why did this replication query fail?". And then you have to ask yourself, "Do you feel lucky?" punk. Well, do ya?

answered Dec 05 '09 at 00:13

Zak

1,032
2
15
25

I say bad table setup, because inserts/updates are what usually causes this problem, and that would mean conflicting keys. It's not 100% though, two users could have tried entering identical information into uniqued columns at the same time on the 2 different DB's. In that case, resolev the conflict, skip the query, and continue... – Zak Dec 05 '09 at 00:15
Yes, it is the conflicting auto-increment keys. Consider this solution: removing that table from BOTH databases. Then, resuming the replication (start slave). Do you think it would sync back up? – Alex Dec 05 '09 at 00:23

My system administrator set up 2 databases that sync. Master-Master. However, these two databases are not syncing

3 Answers3