0

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 .

6
  • 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.
    – Randy
    Mar 8, 2012 at 3:36
  • Ok.Thanks a lot. I think I have no choice but to do so .
    – xuqin1019
    Mar 8, 2012 at 5:39
  • 1
    15gb is hugh? Time machine? THe las time I considered that hugh sis about 15 years ago. 15gb is tiny today.
    – TomTom
    Mar 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. Mar 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.
    – TomTom
    Mar 8, 2012 at 10:00

2 Answers 2

1

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.

1

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:

  1. 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.
  2. Shut down mysql on old machine
  3. Run rsync again. This time it will only transfer files that are changed since previous rsync run, so it should complete faster.
  4. 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.

You must log in to answer this question.