Creating backups to increase your site’s redundancy is a smart move in the face of a disaster. This is why most website admins build and download an offline version of their website data every single day for recovery. However, this traditional method of doing things has two major drawbacks. For one, it’s effort-intensive (i.e., not lazy-proof), and two, you lose all your recent data if your website crashes before you have a chance to create your daily backup!
In this article, we’ll look at how to back up your website’s backend MySQL database in Linux or Ubuntu. But for those of you who are looking for a more elegant solution, we will also cover automated backups. With automation, once you set it up, you don’t need to worry about that one time you forgot to create a backup or pushing all changes made to your website onto the recovery copy. It takes care of it for you!
Backup MySQL Database on Linux/Ubuntu
MySQL provides a command-line utility, mysqldump, that can be utilized to create backups by exporting databases as raw SQL files. You can run the command manually every day or install an automation script, like automysql backup, that will do it for you on a daily basis.
Use the mysqldump Command:
From your terminal, without logging into your MySQL console, execute the command using the basic syntax:
mysqldump -u [username] -p[password] [database_name] > [backup_file_name].sql
Note that there is no space between -p and the password you input.
Back-Up a Single Table
You can also back up the data of an individual table directly onto a text file using the general syntax mentioned below:
mysqldump -u admin_user -p testDB > backup.sql
If you want to create a database dump of all databases on your server use the following command using the MySQL root user credentials:
mysqldump --all-databases -u root -p > AllDatabaseBackup.sql
Restore MySQL Database:
To restore your database from the backup you created in the previous step, log in to MySQL console by entering your credentials:
mysql -u [username] -p[password] [database_name] < [backup_name].sql
Conclusion:
This blog explains how to backup and restore the MySQL database on ubuntu Linux.