How to Back Up a MySQL Database on Linux / Ubuntu? 


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:

Select * Into Outfile 'table_backup_file' From name_of_table;


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

Once you’re logged in, type the following command to restore the database,
modifying the parameters as required:

$ mysql -u [username] -p[password] [database_name] < [backup_name].sql


Automate the Backup Process:

To automatically back up your database, you will first need to install the
automysqlbackup package. To do this, use the following command:

$ sudo apt-get install automysqlbackup

The script will run automatically on a daily basis, and backups will be
stored under the “/var/lib/automysqlbackup” directory. Additionally, you
can create a crontab entry to run automysqlbackup at any particular time.

To view the contents of the daily backup folder, run the following command:

$ sudo ls -a /var/lib/automysqlbackup/daily

If you’d like to modify any configurations or add customizations to the
utility, you can edit the file located at “/etc/default/automysqlbackup”
by executing the command below:

$ sudo nano /etc/default/automysqlbackup

To create on-demand backups, run the command manually, as shown below:

$ sudo automysqlbackup