How to backup/restore your MySQL database?

Posted by Bounceweb in Web Hosting Tips

1 Star2 Stars (No Ratings Yet)

There are many ways to do this task, and i’ll explain two ways that you can use to restore mysql backups or backup your mysql databases.

Backup of MySQL Database using PHPMyAdmin

– Open your PHPMyAdmin, clicking on the corresponding icon in cPanel. On the left menu, click on the database you wish to backup;

– Click on the Export tab on the right side;

– Make sure you selected to export your entire database, not just one table. All the tables should appear in the export list, so you can pick the ones you want to backup;

– Select “SQL” for output format, check “Structure” and “Add AUTO_INCREMENT” value. Check “Enclose table and field name with backquotes”. Check “DATA”, check use “hexadecimal for binary field”. Export type set to “INSERT”.

– Check “Save as file”, do not change the file name, use compression if you want. Then click “GO” to download the backup file.

Restoring a backup of a MySql database using PHPMyAdmin

–  To restore a database, you click the SQL tab;
– On the “SQL” page , unclick the show query here again;
– Paste the code you wish to restore;
– Click Go;

Alternatively to this method you can click on the Import tab and choose the filename (with sql extension) that you wish to restore and click “Go”.

Creating a backup of your MySQL database using SSH

– If you have shell access on your hosting account, you can easily backup your database using the following command:

# mysql -u username -p database_name > filename.sql

Where:

username = root or database user with enough privileges to create this backup;

database_name = the name of the database you want to backup;

filename = name of the output file with sql extension;

Restoring a MySQL backup using SSH

The restore procedure is almost like the above, you just have to change a symbol. Easy, don’t you think?

# mysql -u username -p database_name < filename.sql

Where:

username = root or database user with enough privileges to restore this backup;

database_name = the name of the database you want to restore data into;

filename = name of the input file with sql extension;

Note: when doing backup/restore using SSH, you have to create the database and user before executing the command, or else it will not work!

Be Sociable, Share!

Leave a Reply

You must be logged in to post a comment.