How to auto backups MySQL databases with Linux crontab

It is very useful having automatic backups from your MySQL databases to avoid data loss from any of our projects. With a simple command line in our crontab, we can do it easily.

First of all, we must login as root user to our server

ssh root@server_ip_or_hostname

We are going to use the command ‘myslqdump‘, which is used to create a MySQL backup from a specified database.

mysqldump -u database_username -pdatabase_password database_name | gzip > /var/www/db_backups/mysite.com/$(date +%d-%m-%Y).gz

We must replace the following vars:

– database_username: username to login to database
– database_password: password to login to database
– database_name: name of the database to connect

When we are inserting your database password, the ‘p’ character goes together with the password string.

For example, this are my credentials:

– Database user: root
– Database password: 1234
– Database name: my_project_db

Our command will be this:

mysqldump -u root -p1234 my_project_db | gzip > /var/www/db_backups/mysite.com/$(date +%d-%m-%Y).gz

Gzip parameter says that our sql file must be compressed with gzip. And the last part of the command, indicates where will be created our backup, with the next format name for the file: DAY-MONTH-YEAR.gz

With your ‘mysqldump‘ command generated with the credentials, we are going to create a cron job in our linux crontab for beign executed and create a MySQL backup for your project every day.

First of all, open your crontab:

crontab -e

Note: You must escape the ‘%’ character with ‘\’ from your ‘mysqldump’ command to be executed by the crontab.

$(date +\%d-\%m-\%Y).gz

Example with the full command:

mysqldump -u database_username -pdatabase_password database_name | gzip > /var/www/db_backups/mysite.com/$(date +\%d-\%m-\%Y).gz

Create the cron tab:

30 4 * * * HERE_YOUR_MYSQLDUMP_COMMAND

For example:

30 4 * * * mysqldump -u database_username -pdatabase_password database_name | gzip > /var/www/db_backups/mysite.com/$(date +\%d-\%m-\%Y).gz

This crontab will be executed every day at 4:30.

Save your crontab, and enjoy your daily MySQL backups.