I have a huge mysql database , say about 15G size . Now I want to move the database to another mysql server , the normal way to do this is to export mysql file then import this file to the target server . But I find it is so slow and error occured in this process . Any better way to do this ? Please let me know . Thanks .
-
im pretty sure that would be the preferred method. rolling your own would be worse. or maybe go to a more robust database for something so big.– RandyMar 8, 2012 at 3:36
-
Ok.Thanks a lot. I think I have no choice but to do so .– xuqin1019Mar 8, 2012 at 5:39
-
115gb is hugh? Time machine? THe las time I considered that hugh sis about 15 years ago. 15gb is tiny today.– TomTomMar 8, 2012 at 8:42
-
@TomTom, for some people it's huge, for some it isn't, there's no need to be condescending about it.– ThatGraemeGuyMar 8, 2012 at 9:41
-
For none that is hugh. transferring 15gb data is something yuo do on a cheap usb stick , dsl or soemthing. THis is like someone saying "my uuuugh fuile server of 15gb". Reality checks are not condescending, unless you accept most people live in a dreamworld.– TomTomMar 8, 2012 at 10:00
2 Answers
You can copy the underlying database files to your new server. for example if your database files are in /var/lib/mysql. then
tar -zcvf ~/database.tar.gz /var/lib/mysql/<database-name> # compress the data
then copy the compressed file to your new server's mysql database location. this will be much faster and you don't need to use mysqldump.
I can think about two options for migrating database to new hardware with minimal downtime.
1: using replication: set up replication to new machine, then propagate the machine to master and shut down old database. I have not done this with mysql, so I'm not sure - there may be some problems with it, but it worked for me perfectly with PostgreSQL.
2: using filesystem-based copy with rsync:
- rsync from old machine to new one:
rsync -rav /var/lib/mysql/ newmachine:/var/lib/mysql/
. This will take good amount of time, but you don't need to shut down mysql during first rsync. - Shut down mysql on old machine
- Run rsync again. This time it will only transfer files that are changed since previous rsync run, so it should complete faster.
- Start mysql on new machine.
BUT: you can face some problems if your machines have different OS'es, architectures or mysql versions. E.g. I'm sure it's not possible to do filesystem-based copy for PostgreSQL from 32-bit to 64-bit host. AFAIR mysql allows this, but you can still have some other problems.