I am switching VPS providers and I want to get my MySQL table data in one server over to another. I currently have a daily mysqldump of all the tables and the biggest tables have in excess of 10M rows and the dump file is approx 4-5GB in size. The import of this is taking many many hours and I am wondering if there is a faster way to do this - something that takes 10 or 15 mins rather than hours and hours. Thanks!

Edit: all my tables are InnoDB

  • Have you already tried doing your dump with --disable-keys? – Zoredache Dec 6 '11 at 6:28
  • And have you copied the dump to the new VPS? Loading it in with the -h option from the first VPS will usually be slower. Copying the dump files across will usually go faster if you use a compression option (scp -C or rsync -z or you could just use gzip before copying and gunzip after.) – Ladadadada Dec 6 '11 at 7:44
  • --disable-keys is a part of --opt, which is set by default. – thinice Dec 6 '11 at 17:47
  • @Zoredache: Yes, I have the dump which first disables the keys, does the insert and then enables the keys – bachposer Dec 6 '11 at 17:50
  • @Ladadadada: yes I have rcp-ed the gzipped files over to the new VPS. That file copy speed is not a big issue. It's the slurping into the table on VPS2 that is dog-slow – bachposer Dec 6 '11 at 17:52

Why not use xtrabackup? Your edit says that your tables are InnoDB. xtrabackup works well for your needs as you describe them.

  • I would either use Percona XtraBackup, or an LVM or other filesystem snapshot. If you can shut the server down, you can also do a raw file copy with scp or rsync; previous comments seem to (mistakenly) flag this as scary or prone to failure, but it isn't. – Baron Schwartz Dec 6 '11 at 20:30
  • @BaronSchwartz - I can shut the DB down for 1-2 hrs on a weekend if required. What files/directories do I rcp over? Any links to articles/tutorials/write-ups that show how exactly to do this raw file copy would be most useful. Thanks! – bachposer Dec 6 '11 at 20:44
  • Percona has a considerable amount of documentation for xtrabackup on their website. percona.com/doc/percona-xtrabackup/?id=percona-xtrabackup:start – navaho Dec 6 '11 at 22:34
  • 1
    and this site has a lot of good info too. serverfault.com/search?q=xtrabackup – navaho Dec 6 '11 at 22:36

This way does NOT work in case of Innodb. If Mysql versions are the same, you can try to move files from /var/lib/mysql (depends on your distributive) from one vps to another.
For example in Debian GNU/Linux this will work, but you'll also need to change debian-sys-maint mysql user's password. you can do it this way:
mysql -uroot -p -e "GRANT ALL PRIVILEGES ON . TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '$(sed -n '/password/p;' /etc/mysql/debian.cnf | sed '1d;s/.* //')' WITH GRANT OPTION;"
In case you have different minor mysql version it might work, but theoretically you can have some bugs.

  • Copying files like this will not work with InnoDB tables. It can also cause (recoverable) errors on MyISAM tables if a table is being written to at the time you copy the table. – Ladadadada Dec 6 '11 at 7:41
  • It will work with InnoDB in case Mysql is down. In official Mysql manual there is info about copying Innodb files dev.mysql.com/doc/refman/4.1/en/innodb-backup.html . – rush Dec 6 '11 at 7:49
  • That page is about making a backup that will be restored on the same machine. Trying to restore a backup made like this onto a different machine will fail. Xtrabackup has a tool that handles this for you. – Ladadadada Dec 6 '11 at 7:54
  • 1
    Oh, ok. So my way is not good idea. I think i should mark it as non workable with Innodb. Thank you for explanation. – rush Dec 6 '11 at 7:56
  • 1
    Baron's comment on another answer made me doubt my own memory so I went off to Google to refresh my knowledge. It turns out that with InnoDB, you can safely copy your entire MySQL data directory to another empty machine when the server is not running. You run into problems when you copy single .ibd files or if you make the copy while the server is running. Even then, the problems are not insurmountable. – Ladadadada Dec 7 '11 at 22:45

Use MySQL replication here. You should not have problems with versions - as you have VPS not just hosting, as I correctly understand. If performance of second machine would not be much worse than another - Slave should be just seconds behind the master.

Don't forget to stop repication when resigning from the first VPS (someone may issue DROP on Master which would replicate to Slave).

Try to plan for the future - maybe some MyISAM tables won't be enough with bigger traffic - you can have MySQL Slave with some tables on different engines than Master.

  • Arek - how much time will replication take to catch up with tens of millions of rows? Will it be much faster than mysqldump + import? All my tables are InnoDB, no MyISAM at all – bachposer Dec 6 '11 at 17:38
  • 1
    You will have to do the dump and import once but after that the slave will only ever be a second or two behind the master. – Ladadadada Dec 6 '11 at 18:17
  • As Ladadadada said - dump and restore once. Then wait for Slave to catch up master, what will be delay behind depends on CHANGES not SIZE. How long it would take? The same answer - changes, not size. Replication brings simple complexity to the structure - bur a few more serious administration tasks if you want constantly rely on replication. but for your task - it's just great! If dump last too long freezing your App... well - you may dump with no locks and then try to sync tables - e.g. with MAATKIT set of tools. But most convenient would be mysqldump with master data (read about it) – Arek B. Dec 6 '11 at 18:28
  • 1
    By the time you establish replication you could have completed the task using other means. – navaho Dec 6 '11 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.