We have a live database in production where we are running out of space on the server. So I would like to transfer to a new server without any downtime (or as little downtime as possible). In general, I would also like to have a hot failover copy of the database available.

I would like to use replication to get all of the data copied to the new machine, and then at some point flip a switch and have that new machine become the master (normal failover scenario). My problem is that I am not sure how to initialize replication without locking the db to make the initial snapshot I will use? Is there any way to do this? I know I could do it using single-transaction if I was using innoDB, but very unfortunately we have some myISAM tables in there (in fact the largest 150GB table is myISAM and I want to switch it to InnoDB but I can't do it until I have more space & a hot copy to switch to).

Any ideas? Is there some way to make such a snapshot? Or is there alternatively a way to get replication to "catch up" without an snapshot for initialization?

  • @Artem - i'm afraid you cannot convert whatever you have to lvm on the fly. one update or insert / 10sec means you cannot use write lock. i have some suicidal ideas about ... turning on replication and while it's already running copying data in small chunks until it all gets synchronized. but that is really really bad hackery. so better schedule downtime and make proper dump. go for innodb and take something better [ like sphins or lucene ] for text search. – pQd Apr 12 '10 at 21:07
  • Thanks pQd. A small follow up -- if this one table is the bulk of the work, would it be faster to take down the server fully and just copy the binary data files over to the new machine? – Artem Apr 12 '10 at 22:50
  • @Artek - yest, it will be faster to take server down and use file-level copy. think about having lvm on a new machine - just in case. – pQd Apr 13 '10 at 6:06

file-system consistent snapshot probably is out of question. right?

snapshot on lvm would be reasonable starting point...

what is the characteristics of your data - especially one in myisam - does it change a lot?

  • Thanks for the answer. I was just looking at the LVM stuff as a nice alternative. If the current drive where the DB is on does not have LVM, is it possible to somehow create an LVM snapshot? Sorry, I don't know anything about LVM. The data on the myISAM gets about one INSERT/UPDATE every ~10 seconds, does not get many deletes. It has a very large TEXT column (it's not designed very well). – Artem Apr 12 '10 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.