Backup and restore a MySQL database using mysqldump - Adventures in Switching to Linux

Thursday, May 15, 2008

Backup and restore a MySQL database using mysqldump

Today I am working on migrating some web apps to a new server. The old server was running Fedora Core 1 (yeah, really old) and the new box is running Ubuntu 8.04 server edition. (This is the first time I have ever used the server version. A coworker chose the distribution. I think I would have gone with CentOS (a RHEL clone) but it will be nice to play with Ubuntu server too. I am not some super Ubuntu fanboy!)

So I am moving a MediaWiki 1.5 installation (you know, the software that runs Wikipedia) and upgrading it to 1.12. Copying the files and upgrading proved to be pretty simple and painless so far as I can tell but backing up and restoring the MySQL DB between version 3.23.58 and 5.0.51a got me a little concerned. I wanted to copy over an archive of the /var/lib/mysql/mediawiki db data directory and restart MySQL but that didn't work. It has in the past when going from a 3.x to a 4.x but I may have been pushing my luck even then too.

The best way I found to do this though is to use mysqldump. I have used this before but I always forget all the syntax (which is why I am writing this post). mysqldump will spit out SQL statements to recreate your database and reinsert all the data. The problem with that is you then have a gigantic .sql file that you have to import into your new database. That feels a lot less foolproof to me than if just copying the data directory would (had it worked).

Anyway, to backup your database with mysqldump, do this:

$ mysqldump -u root -p DBNAME > DBNAME.sql

And then to restore that, do this:

$ mysql -u root -p
mysql> \q
$ mysql -u root -p NEW_DBNAME <>

There you have it. There is also the option to use msqlhotcopy but I didn't bother with it.

Other tutorials and thoughts:

No comments: