Why backup a database?
It is highly recommended to have a plan to take a backup of your database which is acting as a data repository for your application. If you do not do it, please have one and keep it in place.
This will help you to recover your database while anything goes wrong during upgrading the version of the database or any corruption found in any object of the database or any other system failure.
This should be a part of your business continuity plan even after natural calamity or network breakdown or hardware malfunction. This will also help to recover your database from any kind of disaster.
It is assumed that phpMyAdmin is installed in your server or service provider.
Step 1: Open phpMyAdmin.
Step 2: Select the database you want to backup.
Step 3: Go to ‘Export’ menu. This will lead you to “Exporting tables from "<DatabaseName>" database” screen.
Step 4: Select the Export method, “Custom - display all possible options”.
Step 5: Select All Table, just like it is selected by default.
Step 6: Select an appropriate “Output” options whether you want to view it or save to it to your computer. You can provide the file name template value.
Ex. “@DATABASE@” - .
Ex. “@DATABASE@” - .
Select character set of the file. It is used be ‘utf-8’. I have used no compression but you can use the compression ‘zipped’ or ‘gzipped’.
Step 7: Select the format as per your requirement. I have left it with ‘SQL’.
Step 8: Fill up ‘Format-specific options’. Check “Display comments”, Dump table as “structure and data” this is most important part.
Under sub-heading “Object creation options” check the followings:
Add [CREATE PROCEDURE] / FUNCTION / EVENT statement
[CREATE TABLE] options:
- IF NOT EXISTS
AUTO_Enclose table and column names with backquotes
Under sub-heading “Data dump options” check the followings:
Function to use when dumping data: INSERT
Syntax to use when inserting data: both of the above
Maximal length of created query: 50000
- Dump binary columns in hexadecimal notation
- Dump TIMESTAMP columns in UTC
Step 9: Go and save the file in your intended location.