1

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 '[email protected]: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

1 Answer 1

2

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.

3
  • I modified my settings and added an edit to my post. Is that what you meant?
    – Publiccert
    Commented Aug 30, 2011 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
    Commented Aug 30, 2011 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
    Commented Aug 30, 2011 at 18:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .