One of the things we have to do quite frequently is migrate databases from one server to another.  There are lots of reasons we might have to do this: a site going live after being on a development server, wanting to test live data in a development environment (and so bringing the database down locally from the live site), restoring a back-up to fix a broken/corrupted database, etc.

The following is a selection of things we’ve learned to do or, in some cases, things we’ve learned not to do along the way.  May they prove useful to you in your database migrating efforts!

1)      If possible, always use mysqldump via command line to get a copy of the database for use in your migration.  You can use PHPMyAdmin’s ‘Export’ feature, however we’ve noticed that this is not always consistent.  Specifically around ‘funny’ characters the export can run into problems when done through the browser, thus we’ve found it easier to use the command line.

Here’s how to import/export MySQL databases via command line.

2)      Be sure that the database collation is correct on your import and export.  This is fairly self-explanatory, but here’s a useful guide to some commands you can run to ensure that the database collation is preserved correctly.

If you’re confused about what database collation is, you can read more about it here.

3)      If importing the database into (migrating to) a server with a different version of the same database already on it, back that database up before importing.  Human nature tells us that we’ve done everything correctly and so our import will ‘just work’ and will ‘have no problems’, but this is not always the case.