MariaDB Backup Script Mail Notifications Alert

Try following script , This script will take mysql database backup automatically  and remove the old backup file which is over 3 days and once backup is completed means it will send mail confirmation

If you know the mysql root password you can use following script , if you don’t know the mysql root password you can use the second script

Before running this script please create a directory for backup location as ” backup ”

First script with ROOT password

#!/bin/bash
# Shell script to backup MySQL database

# Set these variables
MyUSER="root"	# DB_USERNAME
MyPASS="12345"	# DB_PASSWORD
MyHOST="localhost"	# DB_HOSTNAME

# Backup Dest directory
DEST="/root/backup" # 

# Email for notifications
EMAIL="nijamutheen060@gmail.com"

# How many days old files must be to be removed
DAYS=3

# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y_%s")"

# Create Backup sub-directories
MBD="$DEST/$NOW/mysql"
install -d $MBD

# DB skip list
SKIP="information_schema
another_one_db"

# Get all databases
DBS="$($MYSQL -h $MyHOST -u $MyUSER -p$MyPASS -Bse 'show databases')"

# Archive database dumps
for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
		for i in $SKIP
		do
			[ "$db" == "$i" ] && skipdb=1 || :
		done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
    	FILE="$MBD/$db.sql"
	$MYSQLDUMP -h $MyHOST -u $MyUSER -p$MyPASS $db > $FILE
    fi
done

# Archive the directory, send mail and cleanup
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar

echo "MySQL backup is completed! Backup name is $NOW.tar.gz" | mail -s "MySQL backup" $EMAIL
rm -rf $NOW

# Remove old files
find $DEST -mtime +$DAYS -exec rm -f {} \;

Without MySQL ROOT Password :

#!/bin/bash
# Shell script to backup MySQL database

# Set these variables
MyUSER="root"	   # DB_USERNAME
MyHOST="localhost" # DB_HOSTNAME

# Backup Dest directory
DEST="/root/backup" # 

# Email for notifications
EMAIL="nijamutheen060@gmail.com"

# How many days old files must be to be removed
DAYS=3

# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y_%s")"

# Create Backup sub-directories
MBD="$DEST/$NOW/mysql"
install -d $MBD

# DB skip list
SKIP="information_schema
another_one_db"

# Get all databases
DBS="$($MYSQL -h $MyHOST -u $MyUSER  -Bse 'show databases')"

# Archive database dumps
for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
		for i in $SKIP
		do
			[ "$db" == "$i" ] && skipdb=1 || :
		done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
    	FILE="$MBD/$db.sql"
	$MYSQLDUMP -h $MyHOST -u $MyUSER  $db > $FILE
    fi
done

# Archive the directory, send mail and cleanup
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar

echo "MySQL backup is completed! Backup name is $NOW.tar.gz" | mail -s "MySQL backup" $EMAIL
rm -rf $NOW

# Remove old files
find $DEST -mtime +$DAYS -exec rm -f {} \;

 

Note : while running this script may  get below error

mysqldump: Got error: 1142: "SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts'" when using LOCK TABLES

Solution :

Do not dump any tables of performance_schema, information_schema or mysql. The first two are not ‘real’ tables, and they cannot be reloaded anyway.

95% of Production servers are not helped by, or even hurt by, turning on the Query cache. Please elaborate on why you are even attempting to use the QC.

so you have add it those databases in skipping list , so script should be below shape

#!/bin/bash
# Shell script to backup MySQL database

# Set these variables
MyUSER="root"      # DB_USERNAME
MyHOST="localhost" # DB_HOSTNAME

# Backup Dest directory
DEST="/root/backup" #

# Email for notifications
EMAIL="nijamutheen060@gmail.com"

# How many days old files must be to be removed
DAYS=3

# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Get date in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y_%s")"

# Create Backup sub-directories
MBD="$DEST/$NOW/mysql"
install -d $MBD

# DB skip list
SKIP="information_schema
performance_schema"

# Get all databases
DBS="$($MYSQL -h $MyHOST -u $MyUSER  -Bse 'show databases')"

# Archive database dumps
for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
           for i in $SKIP
           do
                [ "$db" == "$i" ] && skipdb=1 || :
           done
    fi

    if [ "$skipdb" == "-1" ] ; then
        FILE="$MBD/$db.sql"
        $MYSQLDUMP -h $MyHOST -u $MyUSER  $db > $FILE
    fi
done

# Archive the directory, send mail and cleanup
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar

echo "MySQL backup is completed! Backup name is $NOW.tar.gz" | mail -s "MySQL backup" $EMAIL
rm -rf $NOW

# Remove old files
find $DEST -mtime +$DAYS -exec rm -f {} \;