MySQL Backup

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

Below is the script.

Prerequisites

mkdir -p /zzz/backups/mysql

Script Updates

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

Features

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

File location: /etc/cron.daily/mysql_backup

mysql_backup
#!/bin/sh
#
# MySQL backup script
#
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
#
MYSQLPASS="<fillinpassword>"
BACKUPLOC="/zzz/backups"
MYSQLBACKUPLOC="$BACKUPLOC/mysql"
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 ORDER BY User;" > $SQLFILE
/usr/bin/mysql -B -N -u root --password=${MYSQLPASS} mysql < $SQLFILE  > $INFILE
cat $INFILE | grep show > $RUNFILE
/usr/bin/mysql -B -N -u root --password=${MYSQLPASS} mysql < $RUNFILE | gzip > $BACKUPFILE2
chmod 600 $BACKUPFILE2
rm $SQLFILE $INFILE $RUNFILE
#ls -l /tmp/*_${NOW}.sql
#echo "BACKUPFILE1 = $BACKUPFILE1"
#echo "BACKUPFILE2 = $BACKUPFILE2"
#
/usr/bin/find $MYSQLBACKUPLOC/backup*.sql.gz -mtime +7 -exec rm {} \;
#
exit 0

Post Install

chmod 700 /etc/cron.daily/mysql_backup

QR Code
QR Code tech:linux:mysql:backups (generated for current page)