Many web applications and CMSs use MySQL databases to store contents. If you also use MySQL databases for your CMS, Blog, or website, then it is useful to learn an easy method to create database backups regularly. PhpMyAdmin is an application written in PHP. It is used to administer MySQL databases from a web browser.
In the following steps, you will see how to easily create a MySQL database backup using phpMyAdmin.
1) Open a web browser and visit the phpMyAdmin page from where you manage your MySQL databases. (You can access phpMyAdmin from cPanel if you use cPanel to manage your website and database. If you are on localhost, you are probably using XAMPP, WAMP, etc. PhpMyAdmin comes bundled with both XAMPP and WAMP.)
In the phpMyAdmin page, click on “Databases”. A list of databases will be displayed as shown below in the web browser.
Click on the database which you want to backup. In this tutorial, we are making a backup of the database named “testdb”.
2) You will see the list of tables in the selected database as shown below.
Click on the “Export” tab.
3) You can choose an Export Method. In this tutorial we are choosing the Custom option to finely control the backup properties.
4) Choosing "Custom" displays many options as shown below.
In the Table(s) section, select all the tables in the database.
5) In the Output section, choose “Save output to a file”
The value “@DATABASE@” means the backup file will be named as the database name.
In the field “Character set of the file”, leave the default value utf-8.
In the field “Compression”, if your database is small you can choose “None” or you can choose other compression options. In this tutorial we are choosing “gzipped” which generates a small backup file.
6) In the Format section select “SQL”.
7) In the Format-specific options section, do as shown in the image below:
Checking “Display comments” will add extra information inside SQL comments in the backup file. If you don't want SQL comments in the backup file, you can uncheck “Display comments”.
You can add additional comments in the field “Additional custom header comment”.
You can leave the default value(unchecked) for the other checkboxes:
If "Enclose export in a transaction" is checked, backup import will be done in one transaction.)
You can choose to disable foreign key checks. If checked, a table can be dropped even if it is referenced in another table using a foreign key.
8) In this tutorial, the backup file we are creating should be imported in a blank database while restoring from the backup. So, it is not necessary to check "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement". (If it is checked, the tables in the target database having the same name as the tables in the backup will be dropped before importing from the backup.)
Check other checkboxes as shown in the image below:
9) In the Data dump options section, do as shown below:
Leave the default value for “Maximal length of created query”. (If the INSERT statement is longer than this value, multiple INSERT statements will be created.)
Click Go.
A backup of the database will be created and downloaded to your computer through the web browser.
Related:
