I have a massive MySQL database (around 10 GB), and I need to copy it to a different server (slicehost). I don't want to do a DB dump and reimport b/c I think that would take forever. Is it possible to just move the raw SQL files from one machine to the next, setup an identical mysql server, and flip the switch?
A:
It should work.
This is the principle that the mysqlhotcopy tool uses, although this tool is meant to be run while the server is operating.
R. Bemrose
2010-05-18 18:05:37
Hmm, you could probably copy the files over to an ssh mounted drive on the other machine and it would still be a 'local' copy. What do you think?
tesmar
2010-05-18 20:46:17
Even if it didn't, mysqlhotcopy has the option to use scp. I see an article over on Debian Administration on how to use it to backup to a remote server: http://www.debian-administration.org/articles/311
R. Bemrose
2010-05-18 20:52:42
+1
A:
Generally, yes. It's preferable to have the same underlying architecture and server version, but those aren't critically necessary. Make sure you stop the source server so that the raw files are a consistent copy.
I do this all the time when overwriting my dev database. We have backups on a replica that are made from tarring up /var/mysql when the server is stopped. I move those to another machine, overwrite iblog and ibdata, then overwrite all the directories in data except for mysql and test.
David M
2010-05-18 18:15:29
So let me get this right: You stop the server and copy for consistency's sake? I am thinking this will have a consistency advantage over mysqlhotcopy, right? How long does it take? I am thinking through whether or not stopping the server is worth it or not.
tesmar
2010-05-19 12:25:25
It won't necessarily have a consistency advantage over mysqlhotcopy, but it's fast. If you are copying to a remote server, you can copy the files while the database is running, stop the database, then rsync to minimize server downtime. Copying files locally is pretty fast, especially if copying from one physical disk to another.
David M
2010-05-19 15:06:49
A:
You don't have a "massive" database, you have a smallish database at 10G. So dump/restore should not be a problem.
Copying the files directly might work in a subset of circumstances, but dump/restore is much better (i.e. less chance of problems).
Clearly, try it on a non-production system with the same version(s) of mysql and data size first to ensure that it's going to work on production.
MarkR
2010-05-18 21:22:28