MySQL 按天备份表数据

MySQL 按天备份表数据。

备份脚本

• 按天备份
• 自定义备份表
• 备份数据保留 15 天

#!/bin/bash
#############################################
#    author wyq
#############################################

#############################################
#    define paras
#############################################
cd $(dirname $0)
workDir=`pwd`
backupDir=$workDir/backup
currentDate=`date "+%Y-%m-%d"`

#jue mirror
jueHost=127.0.0.1
juePort=3306
jueDatabase=test
jueTables=( mysqlTable1 mysqlTable2 mysqlTable3 mysqlTable4 mysqlTable5 )

#mysqldump --default-character-set=gbk --skip-add-drop-table --quick --single-transaction --lock-tables=false --host=127.0.0.1 --port=3306 -uusername -ppassword test mysqlTable1 --result-file=/tmp/mysqlTable1.sql
ddbBackup()
{
    host=$1
    port=$2
    database=$3
    table=$4
    name=$5

    mysqldump --default-character-set=gbk --skip-add-drop-table --quick --single-transaction --lock-tables=false --host=$host --port=$port -ugarbage -pgarbage $database $table \
--result-file=$backupDir/$currentDate/$name-$database-$table.sql
    if [ $? -ne 0 ]
    then
        echo "Can't mysqldump $database.$table from $host !!!"
    else
        echo "mysqldump $database.$table from $host successfully."
    fi

}

##################################################
#       start
##################################################
echo "Start at :"`date`

cd $workDir
if [ -d $backupDir ]
then
    echo "backup directory is ok."
else
    echo "no backup directory,make one."
    mkdir $backupDir
fi

mkdir $backupDir/$currentDate


#backup host  database table info
echo ""
echo "start to backup jue's mailgasp.tables"
for((i=0;i<${#jueTables[@]};i++))
do
    echo "table is : ${jueTables[$i]}"
    ddbBackup $jueHost $juePort $jueDatabase ${jueTables[$i]} jue
done


echo "start delete old data"

find $backupDir -ctime +15 -type d | xargs rm -rf
echo "end to delete old data"
exit 0

crontab 配置

01 05 * * * /tmp/mysqlBackup.sh >> /tmp/mysqlBackup.out 2>&1

zip 加密

zip -rP abc#123 $backupDir/$currentDate/$currentDate.zip $backupDir/$currentDate/*
rm $backupDir/$currentDate/*.sql

zip 解密

unzip -P abc#123 2021-01-15.zip