A Simple Shell Script For MySQL/MariaDB Database Backup

A simple shell script can automate Linux admin job, which will reduce the human effort and save lots of time.

As a server administrator you should take a regular backups of your databases.

It can help you to restore the database if something goes wrong like database corrupted, etc,.

We have wrote a small shell script to achieve this.

There are three script are available in this article and each was created for different purpose and it’s doing their job nicely.

We are taking a database backups with gunzip format for size constraint.

We are keeping only ten days backup to reduce the disk space utilization on the server.

1) How To Backup Each Databases To A Separate File?

This shell script allows you to backup each database to a separate file.

It will help you to restore the particular database if you need.

To do so, add the following shell script into the file. In this example, i have added the script in the mysql_backup.sh file.

# vi /opt/shell-scripts/mysql_backup.sh

#!/bin/bash
# Add the backup dir location, MySQL root password, MySQL and mysqldump location
DATE=$(date +%d-%m-%Y)
BACKUP_DIR="/backup/db-backup"
MYSQL_USER="root"
MYSQL_PASSWORD="***"
MYSQL="$(which mysql)"

# To create a new directory in the backup directory location based on the date
mkdir -p $BACKUP_DIR/$DATE

# To get a list of databases
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

# To dump each database in a separate file
for db in $databases; do
echo $db
mysqldump --force --opt --skip-lock-tables --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

# Delete the files older than 10 days
find $BACKUP_DIR/* -type d -mtime +10 -exec rm {} \;

Set an executable permission to mysql_backup.sh file.

# chmod +x mysql_backup.sh

Finally run the script to achieve this.

# sh mysql_backup.sh 
mysql
performance_schema
testdb
testdb1

Check the output now.

# ls -lh /backup/db-backup/06-04-2019/
total 11M
-rw-r--r-- 1 root root 134K Apr  6 10:14 mysql.sql.gz
-rw-r--r-- 1 root root 4.6K Apr  6 10:14 performance_schema.sql.gz
-rw-r--r-- 1 root root 266K Apr  6 10:14 testdb1.sql.gz
-rw-r--r-- 1 root root  11M Apr  6 10:14 testdb.sql.gz

Cronjob will make our life easier in executing programs on scheduled time without fail. The below cronjob is scheduled to run @ 10AM everyday.

0 10 * * * /opt/shell-scripts/mysql_backup.sh

2) How To Backup A Single Database Using Shell Script?

This shell script allows you to backup the given database to a file.

To do so, add the following shell script into the file. In this example, i have added the script in the mysql_backup_1.sh file.

# vi /opt/shell-scripts/mysql_backup_1.sh

#!/bin/bash
MYSQL_PASSWORD="*****"
DATE=$(date +%d-%m-%Y)
BACKUP_DIR="/backup/db-backup"
DB=testdb
echo $DB
mysqldump -u root -p$MYSQL_PASSWORD $DB | gzip -9 > "$BACKUP_DIR/testdb-$DATE.sql.gz"
find $BACKUP_DIR/* -mtime +5 -exec rm {} \;

Set an executable permission to mysql_backup_1.sh file.

# chmod +x mysql_backup_1.sh

Finally run the script to achieve this.

# sh mysql_backup_1.sh 
testdb

Check the output now.

# ls -lh /backup/db-backup
total 11M
drwxr-xr-x 2 root root 4.0K Apr  6 10:14 06-04-2019
-rw-r--r-- 1 root root  11M Apr  6 10:40 testdb-06-04-2019.sql.gz

3) How To Backup All Databases Into Single Backup File?

This shell script allows you to backup all databases into single backup file.

To do so, add the following shell script into the file. In this example, i have added the script in the mysql_backup_2.sh file.

# vi /opt/shell-scripts/mysql_backup_2.sh

#!/bin/bash
MYSQL_PASSWORD="*****"
DATE=$(date +%d-%m-%Y)
BACKUP_DIR="/backup/db-backup"
DB=testdb
mysqldump -u root -p$MYSQL_PASSWORD --all-databases | gzip -9 > "$BACKUP_DIR/all-dbs-$DATE.sql.gz"
find $BACKUP_DIR/* -mtime +5 -exec rm {} \;

Set an executable permission to mysql_backup_2.sh file.

# chmod +x mysql_backup_2.sh

Finally run the script to achieve this.

# sh mysql_backup_2.sh 

Check the output now.

# ls -lh /backup/db-backup
total 21M
drwxr-xr-x 2 root root 4.0K Apr  6 10:14 06-04-2019
-rw-r--r-- 1 root root  11M Apr  6 10:49 all-dbs-06-04-2019.sql.gz
-rw-r--r-- 1 root root  11M Apr  6 10:40 testdb-06-04-2019.sql.gz