Moving/copying large MySQL databases between servers with or without phpMyAdmin

I don’t suppose I’m alone in restricting the majority of my interactions with MySQL to what you can do through the wonderful phpMyAdmin. However, one place where it really lets you down is when you are trying to move large databases between servers. Exporting doesn’t seem to cause any problems (with the 100MB or so that I’ve tried so far anyway) but importing is where things start to go wrong.

phpMyAdmin seems to default to a maximum file upload size of 8MB but this, and other settings, are actually set in php.ini. You can zip (or gzip, etc.) your exported SQL file to bring down its size and/or change:

post_max_size = 8M
upload_max_filesize = 2M

in php.ini (just make sure you’re editing the right one – you will often find more than one) to values appropriate for your file.

However, you may still run into problems with execution timeouts and out of memory errors. You can fiddle around trying to change these settings in php.ini:

max_execution_time = 30 (in seconds)
memory_limit = 128M

If this fails, or if you’re just more of a GUI kind of person, and before you take the desperate step of trying to import table by table (if you do go down this route, make sure you have exported with ‘Disable foreign key checks’ ticked with InnoDB tables, to prevent missing foreign key errors), take a look at the free, cross-platform MySQL Workbench (http://www.mysql.com/products/workbench/). We already use this as a great visual DB modelling tool which takes care of all the complexities of foreign key constraints but I have only just realised today that it’s also a great way to import large databases. Simply connect to your target MySQL DB and:

1. Click ‘Data Import/Restore’ under ‘Management’ in the ‘Navigator’ pane;
2. Choose ‘Import from Self-Contained File’ and browse to your sql file;
3. Choose the pre-existing database name (from ‘Default Target Schema’) if you have already created it or allow it to create it for your if you have exported with the DB creation code in there.

It takes a while, and the progress bar doesn’t move ’til it’s finished but it does the job beautifully.