MySQL备份脚本

1. MySQL dump

vim mysqldump.sh

#!/bin/bash

. /etc/profile
# Shell script to backup MySQL database

# Set these variables
MyUSER="root"   # DB_USERNAME
MyPASS="root"   # DB_PASSWORD
MyHOST="localhost"  # DB_HOSTNAME

# Get date in dd-mm-yyyy format
NOW="$(date +"%F_%s")"

# Backup Dest directory
# You must create individual folder for backup data and log as this script will remove old files in the end!!!
DEST="/data/bak"     # /db/mysql/backup/data

# Email for notifications
# EMAIL=""

# How many days old files must be to be removed
DAYS=7

# Remote server to store backup copy
# SCP_IP=172.16.1.21

# Linux bin paths
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# Create Backup sub-directories
MBD="$DEST/$NOW"
mkdir $MBD

# Get all databases
DBS="$($MYSQL -h $MyHOST -u $MyUSER -p$MyPASS -Bse 'show databases'|grep -v schema)"

# Archive database dumps
for db in $DBS
do
        FILE="$MBD/$db.sql"
        $MYSQLDUMP -h $MyHOST -u $MyUSER -p$MyPASS -R -E --single-transaction --master-data=2  $db > $FILE
        if [ $? -eq 0 ]; then
            echo "Database [${db}] backup successfully completed"
        else
            echo "Database [${db}] backup failed"
        fi
done

# Archive the directory, send mail and cleanup
cd $DEST
tar -cf $NOW.tar $NOW
$GZIP -9 $NOW.tar
rm -rf $NOW

# Remove old files
find $DEST -mtime +$DAYS -exec rm -f {} \;

#find $LOGPATH -mtime +$DAYS -exec rm -f {} \;

# Transfer backup to remote server
# scp $DEST/$NOW.tar.gz root@"$SCP_IP":$DEST/