Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

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. )

share|improve this question

2 Answers

up vote 2 down vote

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!

share|improve this answer
1  
How sure are you going from 32bit to 64 bit is not an issue ? Various posts here and there suggests the on disc format can be different, especially if you have floating point columns. – nos Sep 3 '10 at 20:57
Yep, there really should be no issue. Check out this article from the Percona guys (who are the authority in MySQL): mysqlperformanceblog.com/2010/01/05/upgrading-mysql – vmfarms Sep 4 '10 at 0:12
up vote 0 down vote

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).
share|improve this answer

Your Answer

 
discard

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

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