Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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 .

share|improve this question

migrated from stackoverflow.com Mar 8 '12 at 7:56

This question came from our site for professional and enthusiast programmers.

    
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 '12 at 3:36
    
Ok.Thanks a lot. I think I have no choice but to do so . –  xuqin1019 Mar 8 '12 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 '12 at 8:42
    
@TomTom, for some people it's huge, for some it isn't, there's no need to be condescending about it. –  ThatGraemeGuy Mar 8 '12 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 '12 at 10:00

2 Answers 2

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.