Mysql – (re)creating empty tables in partially restored thesql database fails with “table exists”

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.

The solution requires running mysql_upgrade on the server that has been restored. As per the documentation on https://dev.mysql.com/doc/refman/5.5/en/performance-schema-build-configuration.html

If you install MySQL over a previous installation that was configured without the Performance Schema (or with an older version of the Performance Schema that may not have all the current tables), run mysql_upgrade after starting the server to ensure that the performance_schema database exists with all current tables. Then restart the server.