Backup and Restore database on MySQL using phpMyAdmin

MySQL is one of the most popular DBMS and widely used by many websites as back-end database for storing content. And many people use phpMyAdmin to manage their databases on MySQL because of its friendly user interface, perform many tasks easily, etc. But one of the important thing that most people forgot to do is to backup databases. I heard many people have lost their content on websites because they didn’t backup their databases. When some incident happens to the server (for instance, power failure, hard disk broke, malicious attack, etc.), there may be a chance that your data is damaged. So it is wisely to backup your databases regularly and keep it in a secure place.

This article shows you how to backup and restore database on MySQL using phpMyAdmin. You can choose to backup/restore tables in a database or databases so I have separate steps into different sections as below.

Section

  1. Sample Database
    If you want to learn by follow this article, you can download sample database from this section and follow article in restoration section first.
  2. Backup tables in a database
  3. Backup entire database(s)
  4. Restore tables to a database
  5. Restore entire database(s)

Step-by-step to backup and Restore database on MySQL using phpMyAdmin

Sample Database

  1. You can download sample database for MySQL at http://dev.mysql.com/doc/index-other.html. In this example, I use “world” database.
    Download Sample Database for MySQL
  2. There are 3 tables in world database as you see in the figure below.
    MySQL's Sample Database - World

Back to top

Backup tables in a database

This section shows how to backup tables in a database. You can select which tables that you want to backup or all of them.

  1. Select the database that you want to backup and click on Export. Notice I have selected “world” database on top (localhost -> world).
    Export tables in the database
  2. On Export page, there are many configurations that you can customize for export. By default, all tables in the database are selected for export which you can see that they are all highlighted in export section (city, country, countrylanguage). Also, you can select which file type that you want, for example, SQL, CSV, Excel, etc. In this example, I use default configuration and scroll down to the bottom of the page. Next, click Go button on the right. This will export all tables in “world” database including its records (data).
    Configure option to export tables
  3. The popup window appears. Select Save File and click OK and the exported file will be saved to your computer.
    Save the exported file
  4. Now you have backup the selected tables in “world” database. Here is sample content of the exported file in SQL format.
    Exported file

Back to top

Backup entire database(s)

The previous section backup only tables in a database. This section will shows how to backup entire database. You can select one or more databases for backup.

  1. On phpMyAdmin main’s page, click on Export. Notice that I haven’t select any database yet on top (localhost).
    Export databases
  2. On Export page, there are many configurations that you can customize for export as similar to the previous section. In this example, select the database that you want to backup, “world” database. Next, scroll down to the bottom of the page and click Go button on the right.
    Note: You can select more than one databases.
    Export a database
  3. The popup window appears. Select Save File and click OK and the exported file will be saved to your computer.
    Save the exported file
  4. Now you have backup the selected database. Here is sample content of the exported file in SQL format.
    Exported file

Back to top

Restore tables to a database

This section shows how to restore (import) tables from a backup file into a database. If you have an exported file which derived from section backup tables in a database, you will have to create a database manually and select that database before follow steps below.

  1. Select the database that you want to restore tables to. In this example, I have a “world” database which is an empty database (no tables). Click on Import.
    Blank Database
  2. On Import page, click Browse button and browse to the location where you keep the backup file. Then, click Go on the bottom right.
    Select a backup file
  3. If there is no error while importing tables, you will see the green section display as figure below. Notice that the tables have been imported to the database on the left side.
    Finished Import tables

Back to top

Restore entire database(s)

This section shows how to restore (import) database(s) from a backup file.

  1. On phpMyAdmin main’s page, click on Import. Notice that I haven’t select any database yet on top (localhost).
    Import database(s)
  2. On Import page, click Browse button and browse to the location where you keep the backup file. Then, click Go on the bottom right.
    Select a backup file
  3. If there is no error while importing database(s), you will see the green section display as figure below. Notice that the database(s) have been imported to MySQL on the left side.
    Finished Import database(s)

Back to top

Comments are now closed for this article. If you still have issue and would like to discuss, you can post in the forum or send me a message.