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

I just spun up two EC2 instances and got an elastic IP for each. I can't seem to get it to connect. Here is the bulk of my config:

auto_increment_offset=2

log-bin=master2-bin
binlog-ignore-db=mysql

server-id = 2

master-host = 10.122.191.151
master-user = slaverep
master-password = *********
master-port = 3306

the config for the other server is very similar(but different IP among a few things).

When I run:

mysql> show slave status\G;

I see the following:

*************************** 1. row ***************************
           Slave_IO_State: Connecting to master
              Master_Host: 107.20.178.31
              Master_User: replication
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysqld-bin.000002
      Read_Master_Log_Pos: 1145380
           Relay_Log_File: mysqld-relay-bin.000005
            Relay_Log_Pos: 618
    Relay_Master_Log_File: mysqld-bin.000002
         Slave_IO_Running: No
        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 '2124' for key 'PRIMARY'' on query. Default database: 'phpmyfaq'. Query: 'INSERT INTO 
                            faqsessions
                        (sid, user_id, ip, time)
                            VALUES
                        (2124, -1, '10.223.61.111', 1314625795)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 472
              Relay_Log_Space: 1148388
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1130
                Last_IO_Error: error connecting to master 'replication@107.20.178.31:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 1062
               Last_SQL_Error: Error 'Duplicate entry '2124' for key 'PRIMARY'' on query. Default database: 'phpmyfaq'. Query: 'INSERT INTO 
                            faqsessions
                        (sid, user_id, ip, time)
                            VALUES
                        (2124, -1, '10.223.61.111', 1314625795)'
1 row in set (0.00 sec)

ERROR: 
No query specified

I don't know what to do at this point...

EDIT:

I've fixed my settings to match the following: MASTER1:

auto_increment_increment = 1
auto_increment_offset = 2

MASTER2:

auto_increment_increment = 2
auto_increment_offset = 2
share|improve this question

1 Answer 1

up vote 2 down vote accepted

Both servers are trying to use the same sets of autoincrement IDs, causing a conflict when replication occurs.

To fix this, you need to adjust the auto_increment_offset (to 1 on the first server, 2 on the second) and auto_increment_increment (to 2). If you eventually add a third server, its auto_increment_offset would need to be 3 and all three servers would need auto_increment_increment of 3.

This'll cause server 1 to use autoincrement IDs of 1, 3, 5, 7, 9... and server 2 will use 2, 4, 6, 8, 10, preventing conflict.

share|improve this answer
    
I modified my settings and added an edit to my post. Is that what you meant? –  Publiccert Aug 30 '11 at 18:02
    
Yes, that looks like it should work. You may need to skip over errors to have replication catch up, or do a fresh database import. –  ceejayoz Aug 30 '11 at 18:24
    
Sorry, I had a typo in my last set of configs. I changed the offsets to 1 & 2, respectively and kept the increment at 2. How do force the slave to resume? It is still showing that long error dump I posted above. –  Publiccert Aug 30 '11 at 18:25

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.