How to Automatically Backup MySQL Database Using Bash Script

MySQL is a popular RDBMS that aids in storing and managing relational databases effortlessly. It also provides mysqldump client utility to create a logical backup of MySQL databases. MySQL supports multiple languages and platforms, which means you can use Bash scripting to use the mysqldump utility for backups in Linux.

This guide will demonstrate how to automatically back up MySQL databases using Bash script, step-by-step.

How to Automatically Backup MySQL Database Using Bash Script?

Create a Bash script for the backup of the MySQL database. Firstly, open the terminal, create a directory, and navigate to the directory by typing these commands:

The output displays that you have successfully navigated to the directory:

Create a bash script named “backup.sh” using any editor, for this post nano editor is being used:

The bash script will create:

Provide the MySQL credentials and the name of the database that you want to backup:

Set the Backup directory “BACKUP_DIR” by providing the location where the backup file should save:

Set the date format for the name of backup file:

Use this mysqldump command with MySQL database credentials to create the SQL backup file:

To compress the SQL backup file with the gzip tool, use this command:

To save the disk space, remove the old backup files after a time period, for this period “7” days old backup file will be removed using this command:

Save the file and exit the nano editor by press “CTRL + X” keys:

Change the permissions of the bash script to executable by running this command:

The error-free output means the execution of the command is successful:

Run the script using this bash command:

Type the password of Ubuntu and use the “ls” command to verify whether the backup file is created or not:

The backup file is successfully created using the bash script. Now to automate the process of backup by using the “cron” job scheduler utility. To submit a new cron job use the “-e” option with crontab:

Set the time for automatically running the bash script. For this post, “2 AM” is selected for running the script automatically:

Save and exit the file:

Let’s see another command if you want to backup your database after every “5 Minutes” type this and save the file:

The crontab will take some time to create the job successfully:

Check the directory for backup files that are automatically created after “2 AM”, using the “ls” command:

The MySQL database backup files are created automatically using the bash script and cron utility.

Conclusion

Create a bash script by providing MySQL credentials and the name of the database that you want to back up and the file name format. Use the mysqldump command for creating the backup SQL file, gzip to compress it, and run the script. Use the crontab to make the process of backup automatic. This post discussed how to back up a MySQL database using Bash script automatically.