Home arrow MySQL arrow Page 2 - Backup and Restore a MySQL Database

MySQL Back Up and Restore using phpMyAdmin GUI Method - MySQL

This is a beginner tutorial that will show you how to perform MySQL backups and restoration.

TABLE OF CONTENTS:
  1. Backup and Restore a MySQL Database
  2. MySQL Back Up and Restore using phpMyAdmin GUI Method
  3. Automating MySQL Backups with Cron and PHP
By: Codex-M
Rating: starstarstarstarstar / 0
July 12, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Codex-M
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: