Make a snapshot of a live mySQL database with myISAM & innoDB tables without locking

Posted by Artem on Server Fault See other posts from Server Fault or by Artem
Published on 2010-04-12T18:10:39Z Indexed on 2010/04/12 18:13 UTC
Read the original article Hit count: 379

Filed under:
|
|
|

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?

© Server Fault or respective owner

Related posts about mysql

Related posts about myisam