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
- 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. - Backup tables in a database
- Backup entire database(s)
- Restore tables to a database
- Restore entire database(s)
Step-by-step to backup and Restore database on MySQL using phpMyAdmin
Sample Database
- You can download sample database for MySQL at http://dev.mysql.com/doc/index-other.html. In this example, I use “world” database.
- There are 3 tables in world database as you see in the figure below.
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.
- Select the database that you want to backup and click on Export. Notice I have selected “world” database on top (localhost -> world).
- 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).
- The popup window appears. Select Save File and click OK and the exported file will be saved to your computer.
- Now you have backup the selected tables in “world” database. Here is sample content of the exported file in SQL format.
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.
- On phpMyAdmin main’s page, click on Export. Notice that I haven’t select any database yet on top (localhost).
- 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.
- The popup window appears. Select Save File and click OK and the exported file will be saved to your computer.
- Now you have backup the selected database. Here is sample content of the exported file in SQL format.
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.
- 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.
- On Import page, click Browse button and browse to the location where you keep the backup file. Then, click Go on the bottom right.
- 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.
Restore entire database(s)
This section shows how to restore (import) database(s) from a backup file.
- On phpMyAdmin main’s page, click on Import. Notice that I haven’t select any database yet on top (localhost).
- On Import page, click Browse button and browse to the location where you keep the backup file. Then, click Go on the bottom right.
- 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.
