Mar 9, 2018

Sample working backup script for MySQL


#!/bin/bash

#echo "analyzing all tables";
#mysqlcheck -Aa 

BACKUPFILE=$(date +"i88-%m-%d-%y-%H%M.sql");

echo "starting sql dump";
date;
#using bzip2 use up less space, but slower
#mysqldump --max_allowed_packet=1G  --compress  --quick --lock-tables --add-locks --comments --compact --dump-date  --routines --triggers  i88 | bzip2 >  /home/ruz/database/$BACKUPFILE.bz2

mysqldump --login-path=local  --max_allowed_packet=1G  --comments  --dump-date  --routines --triggers --events --lock-all-tables i88  >  /var/lib/mysql/$BACKUPFILE

echo "backup complete"
date;
#delete backup files older than 2 weeks.
#/usr/bin/find /path/to/database/ -name "i88*.sql" -mtime +14 -delete