4

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

6
  • Have you already tried doing your dump with --disable-keys?
    – Zoredache
    Dec 6, 2011 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, 2011 at 7:44
  • --disable-keys is a part of --opt, which is set by default.
    – thinice
    Dec 6, 2011 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, 2011 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, 2011 at 17:52

3 Answers 3

3

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

4
  • 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.
    – user48802
    Dec 6, 2011 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, 2011 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, 2011 at 22:34
  • 1
    and this site has a lot of good info too. serverfault.com/search?q=xtrabackup
    – navaho
    Dec 6, 2011 at 22:36
2

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.

5
  • 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, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 22:45
1

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.

4
  • 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, 2011 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, 2011 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, 2011 at 18:28
  • 1
    By the time you establish replication you could have completed the task using other means.
    – navaho
    Dec 6, 2011 at 18:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .