Hi all, If I have a setup with one master and one slave MySQL instance. When the master goes down, the application moves to using the slave instance for its JDBC connections. Now, the problem is that the JDBC connection is quicker than the "stop slave" operation -- that is, after a crash, the application starts pounding the slave server with SQL UPDATEs before the slave has synched itself from the master's transaction log. This is rather unpleasant because the application might update a record, be told it's fine, and then the change would be overridden by an update coming from the synchronization process. This begs the question, is there any way to temporarily prevent the application from executing SQL statements against the slave until it has finished the replication (anything between 1 second and 5 minutes)?
I'll just repeat here a suggestion I got from the official mailing list: only grant the user privileges after STOP SLAVE has been executed and the replica has synched itself from the master's transaction log So obvious... Sure it's a bit ugly, but I guess it's better than nothing. Further suggestions still welcome
subject: How to prevent a MySQL slave accepting queries?