Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » I wrote an app to replace a table on a live server without rebooting it.
I wrote an app to replace a table on a live server without rebooting it. [message #2027] Mon, 08 October 2007 01:25 Go to next message
RichardBronosky is currently offline RichardBronosky
Messages: 20
Registered: June 2007
Junior Member
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!


.+# Richard Bronosky #+.
MySQL DBA, MythTV User
Report message to a moderator
Re: I wrote an app to replace a table on a live server without rebooting it. [message #2043 is a reply to message #2027 ] Tue, 09 October 2007 18:39 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
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[b];[/b]"

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?
Report message to a moderator
Re: I wrote an app to replace a table on a live server without rebooting it. [message #2060 is a reply to message #2043 ] Thu, 11 October 2007 22:09 Go to previous message
RichardBronosky is currently offline RichardBronosky
Messages: 20
Registered: June 2007
Junior Member
Quote:

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.


.+# Richard Bronosky #+.
MySQL DBA, MythTV User
Report message to a moderator
Previous Topic:Tag schema
Next Topic:120million records - scaling up/out advice?
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 05:10:44 EDT 2009

Total time taken to generate the page: 0.01069 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.