Category: recovery

MYSQL Backup using mysqldump

  1. Create a script “mysql_backup.sh” and set in cron to run the jobs.

0 3 * * * /var/lib/mysql/mysql_backup.sh >> /var/log/mysql_backup/mysql_backup.log 2>> /var/log/mysql_backup/mysql_backup.err

2. SCRIPT:

#!/bin/bash
#Script to run on cron for taking mysql db backups
#Author: rajukv
#Date:
Host=slavemysqlserver.mydomain.com
BKPDir=/logs/mysql_backup
USER=mysql_bkpuser
PASS=’*******’
LOG=/var/log/mysql_backup/mysql_backup.log
LOG_ERROR=/var/log/mysql_backup/mysql_backup.log

#Set MYSQL Dump Command
Dump=”/usr/bin/mysqldump -u $USER -p$PASS -h $Host –skip-extended-insert –force”
#Set MYSQL DUMP COMMAND with –skip-lock-tables options
Dump_skip_lock_tables=”/usr/bin/mysqldump -u $USER -p$PASS -h $Host –skip-extended-insert –force –skip-lock-tables”
MySQL=/usr/bin/mysql

Today=$(date “+%a”)
echo ‘>>>Starting mysql backup at `date`’

# Get a list of all databases
Databases=$(echo “SHOW DATABASES” | $MySQL -u $USER -p$PASS -h $Host|grep -v Database)

for db in $Databases; do
date=`date`
file=”$BKPDir/$Host-$db-$Today.sql.gz”
echo “Backing up ‘$db’ from ‘$Host’ on ‘$Today’ to: ”
echo ” $file”
if [ “$db” == “information_schema” ] || [ “$db” == “performance_schema” ]; then
$Dump_skip_lock_tables -h $Host $db | gzip > $file
else
$Dump -h $Host $db | gzip > $file
fi
done
echo ‘>>>End of mysql backup at `date`’

3. Backup file verification

-rw-r–r– 1 mysql mysql    6424169 Sep 18 03:00 slavemysqlserver.mydomain.com-information_schema-Sun.sql.gz

-rw-r–r– 1 mysql mysql 546263 Sep 18 03:02 slavemysqlserver.mydomain.com-performance_schema-Sun.sql.gz

-rw-r–r– 1 mysql mysql      28365 Sep 18 03:04 slavemysqlserver.mydomain.com-sys-Sun.sql.gz