Tuesday, 8 April 2008

Bigging up BigDump

Yesterday, I found myself in the position of having to transfer a medium sized (200 ish MB) MySQL database from one server to another. Ordinarily, I would use SSH on the new server and import the database that way. In this instance, I only had web and FTP access to the new site.

The main problem is that phpMyAdmin limits the size of SQL file that you can upload in one go and I was damned if I was going to split the data up in to ~ 100 files to update the new database. Apart form being time consuming, there was plenty of opportunity for error.

So, before I set about writing my own solution, I thought I'd use my google mojo to see if there was a ready made answer. Half an hour later, I found BigDump which fit the bill perfectly.

My implementation was a follows:

Dump source database using phpMyAdmin to local file localhost.sql. Make sure that you don't use extended inserts and, if necessary, don't use database creation.

Having downloaded it, edit bigdump.php. I amended it thus:

$db_server = 'localhost';
$db_name = 'database_name';
$db_username = 'database_user';
$db_password = 'password';

// Other settings (optional)

$filename = '/full/path/to/dump/localhost.sql'; // Specify the dump filename to suppress the file selection dialog

From the web root on the target server, create a directory dump and upload via ftp the files localhost.sql and bigdump.php

Point your browser at the my.domain/dump/bigdump.php and click on the Start Import link. You should see something like this:

Big Dump Screen Shot

When finished, delete your sql file and bigdump.php from the web server.

That's it. It did the job simply and saved me a load of effort. Yay!

No comments: