• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to prevent a MySQL slave accepting queries?

 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)?
 
Lasse Koskela
author
Sheriff
Posts: 11962
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic