0

Shell Script To Back Up All MySQL Databases

Introduction

In this article, I will help you to write a shell script to backup all databases one by one and zip it with gzip

Here’s the source code for my MySQL database backup shell script. I will explain each steps one by one

#!/bin/bash
 
# Add your backup dir location, password, mysql location and mysqldump location
DATE=$(date +%d-%m-%Y)
BACKUP_DIR="/backups/"
MYSQL_USER="root"
MYSQL_PASSWORD=""
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
 
# To create a new directory into backup directory location
mkdir -p $BACKUP_DIR/$DATE
 
# get a list of databases
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
 
# dump each database in separate name
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done
 
# Delete files older than 10 days
find $BACKUP_DIR/* -mtime +10 -exec rm {} \;

Inthe first section, we are declaring some variables to use in our script.

DATE : – Get the current date

BACKUP_DIR : – Backup location

MYSQL_USER : – User name for conneting the DB

MYSQL_PASSWORD :- Mysql user password to connect to DB – Make sure this user has sufficient prvilages to get all db.

MYSQL: – binary location of mysql command

MYSQLDUMP: – binary location of mysqldump command

We have declared the variables, now the script will create a database dump location using mkdir -p $BACKUP_DIR/$DATE. the folder name will be like /backups/<date>

Now the script will get the database list and store it into databases variable as a list

databases=$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW \

DATABASES;" | grep -Ev "(Database|information_schema)"

Now we have the list of databases in databases variable, let’s run the mysqldump command in a for loop and zip it using gzip and save to backup directory

for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER - \

p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

The last section is to clean up the old dump. here i am deleting files more than 10 days old

find $BACKUP_DIR/* -mtime +10 -exec rm {} \;

Setting the Cron Job

As a quick note, if you are familiar with crontab, you know you’ll want to create an entry that looks something like this to run your MySQL database backup script:

0 5 * * * /home/jjames/mysql-db-backup.sh 

Conclusion

I hope you guys understand this. If you have any suggestions, please comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *