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 .
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.