MySQL Backup

Create a cron.daily job to unload all the MySQL tables into flat file.

Below is the script.

mkdir -p /data/backups/mysql
chmod o-rwx /data/backups/mysql/

• MySQL Root Password
• Backup location and MySQL Backup location includes the mysql directory
• Create the mysql backup directory

• Backups up data
• Backups up users
• Deletes backup older than 7 days

File location: /etc/cron.daily/mysql_backup

Script for debian systems - MariaDB is configured to try to use the unix_socket authentication plugin - so no password is required for Linux root user and the /etc/mysql/debian.cnf is no longer required.

mysql_backup

#!/bin/sh
#
# MySQL backup script
#
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
#
# Set MySQL backup location
BACKUPLOC="/data/backups"
MYSQLBACKUPLOC="$BACKUPLOC/mysql"
#mkdir -p $MYSQLBACKUPLOC
BACKUPFILE1="$MYSQLBACKUPLOC/backup-data-${NOW}.sql.gz"
BACKUPFILE2="$MYSQLBACKUPLOC/backup-user-${NOW}.sql.gz"
SQLFILE="/tmp/eu_${NOW}.sql"
INFILE="/tmp/in_${NOW}.sql"
RUNFILE="/tmp/run_${NOW}.sql"
#
# Backup Data
#/usr/bin/mysqldump --defaults-extra-file=/etc/mysql/debian.cnf --events --ignore-table=mysql.event --all-databases | gzip > $BACKUPFILE1
/usr/bin/mysqldump --events --ignore-table=mysql.event --all-databases | gzip > $BACKUPFILE1
chmod 600 $BACKUPFILE1
#
# Backup Users
echo "SELECT DISTINCT CONCAT(\"show grants for '\", user, \"'@'\", host, \"';\") AS query FROM mysql.user where user<>'root' ORDER BY 1;" > $SQLFILE
/usr/bin/mysql --skip-column-names mysql < $SQLFILE  > $INFILE
cat $INFILE | grep show > $RUNFILE
/usr/bin/mysql --skip-column-names mysql < $RUNFILE | gzip > $BACKUPFILE2
chmod 600 $BACKUPFILE2
rm $SQLFILE $INFILE $RUNFILE
#
/usr/bin/find $MYSQLBACKUPLOC/backup*.sql.gz -mtime +7 -exec rm {} \;
#
exit 0

Script for non-debian systems

mysql_backup

#!/bin/sh
#
# MySQL backup script
#
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
#
# Set MySQL root password, backup location and remove exit statement
MYSQLPASS="myjinga11sql"
BACKUPLOC="/data/backups"
MYSQLBACKUPLOC="$BACKUPLOC/mysql"
#mkdir -p $MYSQLBACKUPLOC
BACKUPFILE1="$MYSQLBACKUPLOC/backup-data-${NOW}.sql.gz"
BACKUPFILE2="$MYSQLBACKUPLOC/backup-user-${NOW}.sql.gz"
SQLFILE="/tmp/eu_${NOW}.sql"
INFILE="/tmp/in_${NOW}.sql"
RUNFILE="/tmp/run_${NOW}.sql"
#
# Backup Data
/usr/bin/mysqldump -uroot --password=${MYSQLPASS} --events --ignore-table=mysql.event --all-databases | gzip > $BACKUPFILE1
chmod 600 $BACKUPFILE1
#
# Backup Users
echo "SELECT DISTINCT CONCAT(\"show grants for '\", user, \"'@'\", host, \"';\") AS query FROM mysql.user where user<>'root' ORDER BY 1;" > $SQLFILE
/usr/bin/mysql -B -N -u root --password=${MYSQLPASS} --skip-column-names mysql < $SQLFILE  > $INFILE
cat $INFILE | grep show > $RUNFILE
/usr/bin/mysql -B -N -u root --password=${MYSQLPASS} --skip-column-names mysql < $RUNFILE | gzip > $BACKUPFILE2
chmod 600 $BACKUPFILE2
rm $SQLFILE $INFILE $RUNFILE
#
/usr/bin/find $MYSQLBACKUPLOC/backup*.sql.gz -mtime +7 -exec rm {} \;
#
exit 0

chmod 700 /etc/cron.daily/mysql_backup