I wrote an app to replace a table on a live server without rebooting it.

[RichardBronosky]

I have used this method a few times, so I scripted it in python.
http://code.google.com/p/mysql-replace-tables-live/

Let me know what you think. I have ran it many times while "super-smack"ing my dev servers. It seems to work pretty darn well. Not a single query on the live server receives an error. They just pause for a few milliseconds (depending on the size of the table, ~8ms in most cases for me) while the write locks are held.

Please poke at it and let me know if I am missing anything!

[sterin]

I read the code and only thing that I saw was a common mistake with a trailing semicolon in one of the sql strings:

self.sql_lock = “LOCK TABLES %s WRITE;”

Usually MySQL client lib allows it so it probably works for anyway.

Out of curiosity why do you need to replace the tables during runtime?

[RichardBronosky]

[B]Quote:[/B]

Out of curiosity why do you need to replace the tables during runtime?

I work for a news agency and we have a lot of data that is read only. We are constantly upgrading both the quality of the data and the interface the user uses to search the data. In some situations, this method is the best way to make a seamless change. It all has to do with our replication scheme, and such. I don’t use it for everything. But it’s nice to have.