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>CREATE DATABASE NEW_DBNAME;
mysql> \q
Bye
$ 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.
Thursday, May 15, 2008
Backup and restore a MySQL database using mysqldump
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment