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. )
Best Answer
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
rsyncon 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 thersyncone 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!