MySQL Backup using mysqldump

Create file

mkdir /usr/serverok/
vi /usr/serverok/mysqldump-backup.sh

Add

#!/bin/bash
# Author: Yujin Boby
# Email: [email protected]
# Web: https://serverok.in/mysqldump-backup

DB_BACKUP="/home/serverok-mysql-backup/`date +%Y-%m-%d`"
mkdir -p $DB_BACKUP

find /home/serverok-mysql-backup/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \;

for db in $(mysql -e 'show databases' -s --skip-column-names);
do 
    if [ $db == "performance_schema" ] || \
       [ $db == "information_schema" ] || \
       [ $db == "phpmyadmin" ]; then
	continue
    fi
    echo "Backing up $db"
    /usr/bin/mysqldump --opt --events --routines --triggers $db | gzip -9 > "$DB_BACKUP/$db.sql.gz";
    #/usr/bin/mysqldump --opt --events --routines --triggers $db > "$DB_BACKUP/$db.sql";
done

# To back to Amazon S3
# /usr/local/bin/aws s3 cp --recursive /home/serverok-mysql-backup/ s3://BUCKET_NAME/mysql/

Make it executable

chmod 755 /usr/serverok/mysqldump-backup.sh

Set cronjob

30 4 * * * /usr/serverok/mysqldump-backup.sh  2>&1>> /dev/null

Backing up to Google Cloud Storage

Add following line to end of the script. Make sure you have Installed Google Cloud SDK and configured gsutil.

/usr/bin/gsutil -m rsync /home/serverok-mysql-backup/ gs://BUCKET_NAME/

Create a Googe Cloud Storage bucket of type Nearline. Set life cycle policy to delete files after 30 days.

Store Backups on Same directory

#!/bin/bash
# Author: Yujin Boby
# Email: [email protected]
# Web: https://serverok.in/mysqldump-backup

BACKUP_DIR="/backups/"
BACKUP_DATE=$(date +%Y-%m-%d-%H%M)
mkdir -p $BACKUP_DIR

for db in $(mysql -e 'show databases' -s --skip-column-names);
do 
    if [ $db == "performance_schema" ] || \
       [ $db == "information_schema" ] || \
       [ $db == "mysql" ] || \
       [ $db == "phpmyadmin" ]; then
	continue
    fi
    echo "Backing up $db"
    /usr/bin/mysqldump --opt --events --routines --triggers $db | gzip -9 > "${BACKUP_DIR}/${db}-${BACKUP_DATE}.sql.gz";
done

# Delete backups older than 20 days
find /backups/  -type f -name "*.sql.gz" -mtime +20 -delete

backup