Bash script backup MySql database

As we all know, databases is one of the most important thing you need to backup before you want to upgrade, move to new server or before you want to do a task but do not really sure about the impact. I have written a article about the command line to MySql database - How to Backup and Restore. In this article, I will show you a little bash script backup MySql database at regular intervals and deleting backups which older than 15 days from current.

This Bash script backup MySql will contain your database information so please make sure you set the right permissions, more over please don’t place the backup folder in public_html folder othervise other people will be able to see the content of your database.

Implement Bash script backup MySql database

#!/bin/bash

# Database information
user="db_username"
password="your_db_password"
host="your_host"
db_name="your_db_name"

The line at the top is the shebang and is used to select the interpreter. Others are the database credentials. In next step we will define the derectory to store the backup files also the date

# Other options
backup_path="/path/to/your/home/_backup/mysql"
date=$(date +"%d-%b-%Y")

Next step, we will create the main backup script to make sql file gzip format and set the permission

# Set default file permissions
umask 177
# Dump database into SQL file
mysqldump --user=$user --password=$password --host=$host $db_name | gzip > $backup_path/$db_name-backup-$date.sql

The last step is to delete files older than 15 days, for this we will use the find utility. First we pass the path to the directory that contains the .sql files and use a wildcard to select all. Then we need to set 2 options, the first is mtime which we use to select files that are older than a given amount of days. The next option exec allows us pass the files that are found into another utility, which I will use rm to delete the files. The last few characters are to end the line.

#Delete files older than 15 days
find $backup_path/* -mtime +15 -exec rm -f {} \;

Finally, I will gather all path of script below:

#!/bin/bash

# Database credentials
user="db_username"
password="your_db_password"
host="your_host"
db_name="your_db_name"

# Other options
backup_path="/path/to/your/backup/folder"
date=$(date +"%d-%b-%Y")

#set Default file permission
umask 177

# Dump database into SQL file
mysqldump --user=$user --password=$password --host=$host $db_name | gzip > $backup_path/$db_name-bak-$date.sql.gz

#Delete files older than 15 days
find $backup_path/* -mtime +15 -exec rm -f {} \;

That’s, you can run this script manually or create a cron job to schedule the time this bash script backup mysql database will be execute. We hope that this article is helpful for you.