wood burning stoves 2.0*
The moose likes JDBC and the fly likes How to prevent a MySQL slave accepting queries? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to prevent a MySQL slave accepting queries?" Watch "How to prevent a MySQL slave accepting queries?" New topic
Author

How to prevent a MySQL slave accepting queries?

Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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)?


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
Lasse Koskela
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to prevent a MySQL slave accepting queries?
 
Similar Threads
remote display of file select dialog
How to connect second hard disk?
OneToOne shared PK isn't setting key in related table during insertion
Check status of MySQL replication with JDBC
Failover with MySQL