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 .
migrated from stackoverflow.com Mar 8 '12 at 7:56
This question came from our site for professional and enthusiast programmers.
You can copy the underlying database files to your new server. for example if your database files are in /var/lib/mysql. then
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:
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.