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: 416
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