Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I did a master/master replication with MySQL on a Gentoo OVH Release 2, all work fine in my test phase, i put it in production, the replication works fine in 1 or 2 days but this morning, i don't know why, my slave stops running !

So now the log position is bad => i can't just restart slave And my replication did'nt works :/

I want a master/master replication to make a backup server with an IP Fail Over, so only one server is writing/reading in the database at same time.

When i go to MySql and i click on "Show slave status", i see an error like : "Error 'Duplicate entry '411465' for key 1' on query. Default database .... etc"

Did this error stop my replication ? If yes, why do i get this error ? Because the second server does nothing on the database, so normally, there is no problem with the auto increment, right ?

If someone have an idea on how i can fix it, he's welcome :)

PS: Sorry if i made some grammatical mistakes in my question.

share|improve this question
    
Please post output of SHOW SLAVE STATUS\G from both DB Servers. –  RolandoMySQLDBA Jul 25 '13 at 16:13
    
Further to Derek's answer, have a look at the binary log and filter out anything that isn't from the –  James Jul 25 '13 at 21:58

1 Answer 1

up vote 3 down vote accepted

The provided error Error 'Duplicate entry '411465' for key 1' on query means that the slave read and attempted to execute a binary log event to insert a row that already existed, ie. the same value 411465 for your primary key.

The most likely cause of this is the the insert was executed on the slave. To diagnose the query, you would use mysqlbinlog and use the binary log coordinates from SHOW SLAVE STATUS. This will give you the server-id that the query originated from, which will match either your main master or 'passive' master.

Once you determine the query, you can identify the row on the server that is throwing the slave error to determine next steps. You can choose to :

  • skip the entry using SET GLOBAL sql_slave_skip_counter=1 to proceed to the next binary log statement
  • Delete the specific row on the server and start slave to have the statement run from replication.

However, you need to take steps to understand how the mismatch occurred, or you're going to run into this again. This will require some more detective work from your end using mysqlbinlog.

If, as you say, only one master is writeable at a time, you should ensure the following:

  • passive master is read_only=1 and your failover solution is able to modify read_only.
  • the user that your application runs as (or any other non-trusted user) does not have the SUPER privilege. Any user with SUPER privilege can execute statements even on read_only=1 servers.
  • setup pt-table-checksum to ensure the data is in sync on both servers.
share|improve this answer
1  
Thanks for your answer, i will follow your advices. By the way i find the source of my problem, when i duplicated my servers i duplicated cron tasks too, and this is why i've got records on my slave... I think if i fix this, and put the read only settings, my replication will be better. –  Tarkaros Jul 26 '13 at 10:12

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.