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

Below is the script.

===== Prerequisites =====
<code>
mkdir -p /data/backups/mysql
chmod o-rwx /data/backups/mysql/
</code>

===== 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

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.
<file bash 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
</file>

Script for non-debian systems
<file bash 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
</file>

===== Post Install =====
<code>
chmod 700 /etc/cron.daily/mysql_backup
</code>