How To Backup MariaDB Or MySQL Using Bash Script

The article explains how to backup all databases MySQL or MariaDB using bash script on CentOS. The script will backup all databases and will display the size of the backup file and also displays the backup time of a database. The script using bzip2 compression so if we don’t have the package, install it using the command:

Here is the script:

#!/bin/bash

date=`date -d "-1 day" +%Y%m%d`
backupdir="/root/backup-$date"
echo "Start to backup => `date`"

if [ ! -d $backupdir ]; then
echo "$backupdir not existing, create it now automatically"
mkdir -p $backupdir
fi

echo

cd $backupdir
index=1
nomor=1

dbase=`mysql -h localhost -uroot -pqwerty -e"show databases;"`
echo $dbase | awk '{for (i = NF; i > 1; i--) print $i}' | while read line;
do
echo -n "Start Backup : `date `"
echo
echo -n "[$index]Backup Database : $line ... "
mysqldump --single-transaction -h localhost -uroot -pqwerty $line > $line.sql

if [ $? -eq 0 ]; then
echo "OK [`du -h $backupdir/$line.sql | awk '{print }'`]"
else
echo "Fail"
fi

index=`expr $index + 1`

echo -n "[$nomor]Compress Database : $line.sql ... "
bzip2 $line.sql

if [ $? -eq 0 ]; then
echo "OK [`du -h $backupdir/$line.sql.bz2 | awk '{print }'`]"
else
echo "Fail"
fi

echo "Compress finish => `date`"
echo

nomor=`expr $nomor + 1`
done

echo "Backup finish => `date`"
echo "==============================================================="

The script will backup database in localhost with user root and password qwerty. Please change your user and password of the database on line 18 and 24. If we want to change folder location to save the backup database, change in line 4 and fill it with the location of the folder we want. If we don’t want to backup test and employee databases, please change it on line 19  to be:

Reference:
From many resources

 

 

Leave a Reply

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