How to backup/restore your MySQL database?
Posted by swestphal in Web Hosting Tips
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!
Related Posts
Leave a Reply
You must be logged in to post a comment.
Search
Subscribe
Media
Translator
