IIS Aid

In previous guides I've discussed how to create MySQL users, create MySQL databases and even how to assign MySQL priviledges via the command line. One thing I have not covered is how to backup and restore your database via the command line. In this guide we'll use the tried and tested SQL dump method to create a backup of your databases which we'll then use to restore them back to their original state. The methods used here are quite basic, but a knowledge of how to use the command line are necessary.

Before we begin let me point out that the backup method in this guide is rather simple, and only really intended for when you need to make ad hoc backups. If you are looking for a method in which to create regular and consistent backups of all your databases then I strongly recommend you have a look at backing up MySQL automatically using a batch file.

Creating an SQL dump backup is done using a utility called mysqldump.exe which is packaged with the MySQL distribution. To backup your database, type in the following at the command prompt;

mysqldump.exe --user=username --password=secret --databases database1 --allow-keywords --complete-insert > database1.sql

• The "--username" and "--password" arguments should be fairly self explanatory. Just substitute the MySQL user and password for one on your server with sufficient privileges to your databases.
• The "--databases" argument specifies which databases are to be backed up. Here again just substitute the database you want to backup, and if you want to backup several at once you can by entering each database name separated with a space. I typically don't advise this though as your backup SQL file will have multiple databases in it making the restoration of individual databases a lot harder.
• The "--allow-keywords" argument allows the creation of column names that are keywords, and is needed for such things as phpMyAdmin's PDF and MIME type features.
• The "--complete-insert" argument simply uses complete INSERT statements that include column names for use when restoring.
• Finally, the "> database1sql" argument simply redirects all the output from the mysqldump utility into a file called database1.sql, which is your backup.

Restoring you database from the command line is even easier. Just type in;

mysql --user=username --password=secret --database=database1 < database1.sql

Restoring from command line can sometimes be especially useful when trying to restore really large databases as other methods can become a touch sketchy. Anyway, I hope this guide has been of some use to you. As always, if you have any comments, questions or suggestions please feel free to comment here or post a thread in the forums.