Home / server / Questions / 177910
In Process
nos
Asked: 2010-09-04 12:42:40 +0800 CST

dumping/restoring a large mysql database, best practice

  • 772

We're planning on moving a large MySQL database from one server to another - moving from 32 bit to 64 bit, so copying the database files seems not to be an option.

Large here means around 30 tables, and 60Gb space on the file system. Having had a bit problems(being incredibly slow for one) with dumping databases only a tenth this size before:

Does anyone have any tips on how to best transfer this between servers ? (Anything "better" than mysqldump ? Any particular command line switches that should be on ? Dump/reload from files, or pipe directly to the other db, do gzip compression etc. )

mysql

2 Answers

  1. First off, going from 32bit to 64bit should not have any correlation to your filesystem, so copying your DB files directly should not be a concern. Dumping a large DB could be slow, so copying the raw files is probably the best option. Are you using MyISAM, or are all your tables in InnoDB? If you are using InnoDB, you could try using xtrabackup from Percona to do a "live" backup of your database without downtime:

    https://launchpad.net/percona-xtrabackup

    If you're using MyISAM and downtime is a concern, what you can do is perform an rsync on the data files directly while the server is running. Run it multiple times in a row until the "changed" tables are few and rsync is quick to finish. Then, you can perform a quick shutdown of MySQL, run the rsync one last time to grab the files in a consistent state and start MySQL again. You can then copy them to your new server, start MySQL and go from there.

    Hope this helps!

    • 2

  2. mysqldump has the invaluable advantage of producing text data that can be, for instance, processed with a script or re-arranged, unlike a binary format that is structure/program dependent.

    I would take the added time to perform the mysqldump text backup. However, there are two things to consider

    • there was a bug in mysqldump earlier versions that caused slow performances
    • add the --opt option to mysqldump, it is the default in newer releases (it does --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset) and makes the process faster (it adds also instructions to accelerate the loading (see the MySQL page to assess which option fits your needs).
    • 0