MySQL Back Up and Restore using phpMyAdmin GUI Method - MySQL

Using the phpMyAdmin GUI Method

For those that are not comfortable with the command line method and do not have SSH access, you can still use phpMyAdmin to do backups and database restoration. This is the GUI method and is done using a web browser.

Take note that it has some limitations if you have a very large database; you might have import and export issues because it might timeout. Also, it is recommended that you check if your phpMyAdmin session in the browser is encrypted. Make sure it uses HTTPS in the browser address bar.

Backup Databases

1.) Log-in to your hosting control panel and click phpMyAdmin.
2.) On the left, you can see a list of databases in your phpMyAdmin. If you have more than one database, you can see them all. Click the database name that you would like to backup.
3.) You will then see the actual database tables and details about the database. Now click “Export”.
4.) Under “Export”, click “Select all”.
5.) Select the “SQL” radio button.
6.) Scroll down and make sure “Save as file” is checked.

The following are the recommended settings for phpMyAdmin when doing backups: http://www.php-developer.org/screenshot/backupphpmyadminoptions.jpg

7.) Click “Go” to download the backup to your computer.

Restoring Databases

Supposing you will use the same restoration example as the command line method, but want to restore the database using phpMyAdmin, these are the steps to follow:

1.) Follow the Step 1 procedure discussed in the “Restoring Database - command line method” example (re-creating the database and re-assigning the users).

2.) Once the database has been re-created, it can be accessed using phpMyAdmin. Click phpMyAdmin in the hosting control panel.

3.) Click the database on the left. This database does not yet contain any tables since it is empty. Click “Import”.

4.) Under “File to Import”, browse to the clean backup database (done in the backup section) and open it. Leave the other settings at their default value. Make sure the format of the imported file is SQL.

This is the recommended settings for phpMyAdmin when restoring a database: http://www.php-developer.org/screenshot/restortedatabasephpmyadmin.jpg

5.) Finally click “Go”. The clean backup will be uploaded to your server and phpMyAdmin will restore it. If you have a large database (2MB to 50MB), it might timeout (varies depending on web host).

6.) If you see “Import has been successfully finished”. The restoration process is complete.

Tip: If you have a corrupted database that still exists, you cannot overwrite it with a clean database using the above procedure (starting from Step 2 to Step 6) while there are still tables in the database because it will result in the following error:

MySQL said: Documentation
#1062 - Duplicate entry

You need to drop all of the database tables first (not the database itself). This is done by going to “Structure”, and then if you see the entire list of tables, scroll down and check “Check All”. Once all tables are selected, beside it you will see “With selected”, select “Drop”. Then click "Yes". Once it is empty, execute Step3 to Step 6 again.