Eric Bergen

Copy and paste is normally a great idea. It saves time. Who wants to retype things over and over again? Not me. There are times when copy and paste can get you in trouble. This has bit me a few times in the past as well as other people. When this happens it usually causes and “oh shit! CTRL+c” to kill the query. The query gets cancelled on the master but still propagates to the slave. The master will happily continue executing while replication silently stays stopped on the slave.

The solution? Stop writes on the master. It really depends on the situation whether you want reads to continue or not. If not shutdown mysql (this will stop your clients from hanging) If reads can continue a simple flush tables with read lock; will be fine. Once the slave has caught up flush tables on the slave and copy the table to the master. Once the table is in place on the master start mysql or flush tables and unlock tables if you left it running. The table on the master is now how it would be had the bad query never been ran. Magic! Replication should continue and everything will be happy (including whom ever ran the query ). If the slave was caught up when the table was copied to the master no queries or data will be lost.

Those steps again are.
* Stop writes on the master.
* Let the slave catch up.
* Copy table from slave to master.
* Continue writes on the master.

Now for some notes on how to prevent this from happening at all. In the case above an update query had been copied without the where clause which caused MySQL to update the entire table instead of just one row. Instead of using copy/paste to edit queries then pasting them into the mysql client write them in a .sql text file and then use the mysql cli client to execute them. I realize one should never execute queries by hand on a live system but sometimes it has to be done. The mysql client has two options for executing sql files. First being to use the shell to pipe the file in to the client such as mysql < file.sql or cat file.sql | mysql. The other is to us \. (backslash dot) file.sql from the mysql prompt.

This entry was posted on Tuesday, August 30th, 2005 at 10:39 pm and is filed under Geek, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.