February 4, 2010

Backing up MySQL via phpMyAdmin

Movable Type 3.2 User Manual: Backup/restore your MySQL database

Backing up via phpMyAdmin

phpMyAdmin is the most popular web-based interface to MySQL and is usually preinstalled in most shared hosting accounts. Through it, you can perform any action on the database including backups and restoration.

To back up your database, open your phpMyAdmin page in your web browser and select your database from the dropdown on the left. From the the main database page, click on "Export" in the top button bar.

Set up the options as follows:
Export section

This section determines from which tables data should be exported and the format of the file itself. You should "Select All" tables and choose SQL as the data type.
SQL section

This section gives you various SQL syntax options that you can choose for your export. You should check both the Structure and Data checkboxes.

* Structure - Check the following SQL structure options:
o Enclose table and fieldnames with backquotes
* Data - Do not check any of SQL insert syntax options (e.g. Complete, Extended, Delayed) but make sure that the "Export type" is set to "INSERT".

"Save as file" section

This section lets you choose options about the exported file itself. If you want to save your export to a file (always helpful when you want to actually back up your data), check the "Save as file" button.

In Filename template, you can set the name of the file to be saved to your desktop. The default is DB which yields the database name plus .sql file extension. This is fine for a one time backup, but I tend to prefer %Y%m%d%H%M%S-DB which yields a time-stamped filename (e.g. 20040510120000-mt.sql).

By default, the export will be downloaded in a plain text file format. If, however, your database is very large, you may want to choose some form of compression to shorten your download and save on bandwidth. Note that due to a bug in the software at the time of writing, if you choose a compression format, the .gz and .zip extensions may no be added on and so you may want to add them yourself after the download is complete.

Once you hit the "Go" button, the download of your export file will proceed and phpMyAdmin will remember your settings for the next time.
Restoring via phpMyAdmin

To restore from a backup, click on the SQL tab in the top menu bar. You will see a button labelled "Choose file". Click it and select the MySQL export file from your computer in the resulting dialog box. Leave the "Compression" option at "Autodetect" unless you experience problems. Click on Go and your database will be restored!

Posted by prettyposies at February 4, 2010 11:24 AM