Mysql – Both replication masters stop by themselves

MySQLreplication

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 for 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 doesn't work.

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

When I go to MySQL and 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? The second server does nothing on the database, so normally, there is no problem with the autoincrement, right?

Best Answer

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.

OPTION 1

Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

When you view the Slave Status, here is what to expect

If Seconds_Behind_Master is NULL
- Replication is Broken : Look for Tell-Tale Signs
- If Error Number is 1062 again, Repeat the 5 steps for Skipping an Error
If Seconds_Behind_Master is a Number
- Replication is running
- When Seconds_Behind_Master > 0, Replication is Catching Up.
- When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

OPTION 2

Remove the row to allow replication to continue

Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:

STOP SLAVE;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G

At the risk of sounding redundant...

When you view the Slave Status, here is what to expect

If Seconds_Behind_Master is NULL
- Replication is Broken : Look for Tell-Tale Signs
- If Error Number is 1062 again, delete the row Repeat the 4 steps for Skipping an Error
If Seconds_Behind_Master is a Number
- Replication is running
- When Seconds_Behind_Master > 0, Replication is Catching Up.
- When Seconds_Behind_Master = 0, Replication is Fully Caught Up.

What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync:

Mysql – Master – Master replication Duplicate entry error

The tutorial you linked to is, I'm afraid, very optimistic. It forgets to note that it only covers the case of an auto generated AUTO_INCREMENT PRIMARY KEY.

The methods it suggests do not cover the general case for a UNIQUE KEY, including, of course, a general PRIMARY KEY.

The article does mention that "The most common problem with replication is primary key collision". It then completely fails to mention all the other problems...

So, anyway, any UNIQUE KEY collision will render your replication broken, possibly in both directions.

Which table was it that failed your replication? Was that a table with an AUTO_INCREMENT PRIMARY KEY or a "general" PRIMARY KEY (ie on non-AUTO_ICNREMENT columns)?

To solve the general case of unique keys, your application must be very aware of its location (Netherlands or Washington DC), and must refrain from writing data that may cause collision. Alternatively it might want to use some synchronizing/locking mechanism; that would of course induce latency as data must cross the Atlantic.

Might I also suggest that the use of binlog_do_db is also naive and dangerous. Are you sure you don't want to replicate the mysql schema? What happens when you add new schemas? The general correct solution is to have full replication without filtering, or otherwise have a very good explanation why not to do so.

Finally, I highly discourage the use of active-active master-master replication. I had customers using it in the past. The amount of trouble they went into because of that setup, and in spite of my counter-recommendations, led to many ruined weekends and holidays.

You might want to look at Galera replication. It's a synchronized master replication solution, and I hear it works over WAN. This will allow your application to remain ignorant. Watch out for network failures, as one part of your cluster will turn immutable.

Best Answer

Related Solutions

MySQL Replication – Fixing Replication Errors

OPTION 1

OPTION 2

Mysql – Master – Master replication Duplicate entry error

Related Question