Bash: Backup MySQL

README.md

Backup mysql

Usage:

Create a cron job

crontab -e

Add following lines

0  0  *  *  *  curl -s https://gist.githubusercontent.com/trongnghia203/3f62313f08359fd2dd9b289d856270f7/raw/backup_mysql_v2.sh | bash

backup_mysql.sh

#!/bin/bash
#
# Discussion:
#   - https://serverfault.com/questions/554103/how-to-monitor-regular-mysql-backups
# Simple script to create logical backups of all MySQL databases on
# a server. by http://serverfault.com/users/37681/hbruijn
# Free to use and modify as neeeded.

#======================================================================
# Define paths to system binaries
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
MAIL="/bin/mailx"

# MySQL credentials used for reading the databases.
# either the MySQL DBA account "root"
# or alternatively create a dedicated read-only backup user
# with the following GRANT statement:
# mysql>  GRANT SELECT,RELOAD,SUPER,REPLICATION CLIENT ON *.* TO \
#  backupuser@<this IP or localhost>  identified by 'Very_s3cr3t_passW0rd';
MYHOST="localhost"  # localhost or remote ip-address
MYUSER="backupuser"
MYPASS="Very_s3cr3t_passW0rd"

# Local filesystem or network share to dump back-ups
# Good practice to have file back-ups on their own filesystem
# and not on the root filesystem.
MYBAKDIR="/backups"

# Keep 1 week worth of MySQL backups under $MYBAKDIR
MYDIR=$(date +MySQL/%A)

# Mail errors to somebody in charge
ERROR_RCPT=nghiale@infotechviet.com

# The rest shouldn't need much tuning
#=====================================================================

errormail(){
cat << EOF | $MAIL -s "MySQL back-up failed !" $ERROR_RCPT
    This is an automatic warning message.

    The MySQL back-up on server: $(hostname) has failed with the following
    errors:

    $1

    Please take appropiate action.

    Thanks in advance.
EOF
exit 1 ;
}

if ! test -d $MYBAKDIR ; then
 mkdir -p $MYBAKDIR || errormail "Backup directory $MYBAKDIR does not exist and could not be created."
fi

if test -d "$MYBAKDIR/$MYDIR" ; then
  rm -rf "$MYBAKDIR/$MYDIR" || errormail "Expired backups from $MYBAKDIR/$MYDIR could not be removed."
fi

mkdir -p "$MYBAKDIR/$MYDIR" || errormail "Todays backup directory $MYBAKDIR/$MYDIR could not be created."

# Generate list with all databases
DATABASES=$(echo "show databases" | $MYSQL -h $MYHOST -u $MYUSER -p$MYPASS |grep -v ^Database$) || errormail "Unable to connect to MySQL database server on $MYHOST please check the supplied credentials"

# Make a logical backup of each database
for DB in $DATABASES
do
  $MYSQLDUMP -h $MYHOST -u $MYUSER -p$MYPASS --opt --single-transaction $DB > $MYBAKDIR/$MYDIR/$DB.sql  || errormail "Unable to create backup from $DB "
  $GZIP $MYBAKDIR/$MYDIR/$DB.sql  || errormail "Unable to compress $MYBAKDIR/$MYDIR/$DB.sql "
done

backup_mysql_v2.sh

#!/bin/bash
#
# Idealy:
#   - From the version v1, but without define mysql credential into this script
# Simple script to create logical backups of all MySQL databases on
# a server with credential file ~/.my.cnf or /root/.my.cnf
# Free to use and modify as neeeded.

#======================================================================
# Define paths to system binaries
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
GZIP="/bin/gzip"
MAIL="/bin/mailx"

# MySQL credentials used for reading the databases.
# either the MySQL DBA account "root"
# or alternatively create a dedicated read-only backup user
# with the following GRANT statement:
# mysql>  GRANT SELECT,RELOAD,SUPER,REPLICATION CLIENT ON *.* TO \
#  backupuser@<this IP or localhost>  identified by 'Very_s3cr3t_passW0rd';
# Then, create a mysql credential file:
# touch /root/.my.cnf
#  [client] # or [mysql] or assign to [mysqldump] only
#  host=localhost
#  user=backup_user
#  password=Very_s3cr3t_passW0rd

# Local filesystem or network share to dump back-ups
# Good practice to have file back-ups on their own filesystem
# and not on the root filesystem.
MYBAKDIR="/backups"

# Keep 1 week worth of MySQL backups under $MYBAKDIR
MYDIR=$(date +MySQL/%A)

# Mail errors to somebody in charge
ERROR_RCPT=nghiale@infotechviet.com

# The rest shouldn't need much tuning
#=====================================================================

errormail(){
cat << EOF | $MAIL -s "MySQL back-up failed !" $ERROR_RCPT
    This is an automatic warning message.

    The MySQL back-up on server: $(hostname) has failed with the following
    errors:

    $1

    Please take appropiate action.

    Thanks in advance.
EOF
exit 1 ;
}

if ! test -d $MYBAKDIR ; then
 mkdir -p $MYBAKDIR || errormail "Backup directory $MYBAKDIR does not exist and could not be created."
fi

if test -d "$MYBAKDIR/$MYDIR" ; then
  rm -rf "$MYBAKDIR/$MYDIR" || errormail "Expired backups from $MYBAKDIR/$MYDIR could not be removed."
fi

mkdir -p "$MYBAKDIR/$MYDIR" || errormail "Todays backup directory $MYBAKDIR/$MYDIR could not be created."

# Generate list with all databases
DATABASES=$(echo "show databases" | $MYSQL |grep -v ^Database$) || errormail "Unable to connect to MySQL database server on $MYHOST please check the supplied credentials"

# Make a logical backup of each database
for DB in $DATABASES
do
  $MYSQLDUMP --opt --single-transaction $DB > $MYBAKDIR/$MYDIR/$DB.sql  || errormail "Unable to create backup from $DB "
  $GZIP $MYBAKDIR/$MYDIR/$DB.sql  || errormail "Unable to compress $MYBAKDIR/$MYDIR/$DB.sql "
done