You can back up and restore a MySQL database in one of two ways:
- Via phpMyAdmin
- Via the command line
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:
- Add DROP TABLE
- Add AUTO_INCREMENT value
- 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.
Go!
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!
Backing up via the command line
To backup from the command line of your shell account, log in and type the following at the prompt replacing USERNAME and DATABASE as described previously:
mysqldump -a -u USERNAME -p DATABASE > FILENAME.mysql
You will be prompted for your database password and then the DATABASE will be dumped to a plain-text file called FILENAME.mysql.
The resulting file, FILENAME.mysql, is a full backup with which you can fully restore your database in case of problems.
Restoring via the command line
Restoring from FILENAME.mysql is a three step process:
Drop the database
mysqladmin -u USERNAME -p drop DATABASERecreate the database
mysqladmin -u USERNAME -p create DATABASEImport the backup data
mysql -u USERNAME -p DATABASE <>
EmoticonEmoticon