1 前言

一个问题，一篇文章，一出故事。
笔者遇到用户需要单独备份MySQL某库的某表数据的情况，想着长期用方便，于是写了一个通用脚本，现整理此文以便复用。

2 最佳实践

2.1 创建备份脚本

vim ~/scripts/mysqlTablesBackup.sh

创建如下脚本，

#!/bin/bash

tablesBakDIR='/backup/tablesBackup'
logFile='/var/log/mysqlbackup.log'
mysqlUser='root'
mysqlPwd='rootpwd'
mysqlHost='hd04.cmdschool.org'
mysqlDBs='db1 db2'
mysqlTBs='tab1 tab2 tab3'
tablesBakKeeptime='+30'

Today="`date +%a`"

for ((i=1;i<10;i++)); do
        if [ $i -ge 5 ]; then
                echo "`date +'%Y-%m-%d %H:%M:%s'` tablesBackupStorage is unavailable!" | tee -a $logFile
                exit 1
        fi
        cd "$tablesBakDIR"
        if [ $? = 0 ]; then
                break
        fi
        sleep 2
done

#find "$tablesBakDIR" -mtime "$tablesBakKeeptime" -type f -name "tablesBackup-$mysqlHost*.gz" -exec ls -l {} \;
find "$tablesBakDIR" -mtime "$tablesBakKeeptime" -type f -name "tablesBackup-$mysqlHost*.gz" -exec rm -f {} \;

echo "`date +'%Y-%m-%d %H:%M:%s'` tablesBackup start" | tee -a $logFile
for i in $mysqlDBs; do
        dbExist=`mysql -u"$mysqlUser" -p"$mysqlPwd" -h"$mysqlHost" -e "show databases like '"$i"';" 2> /dev/null`
        if [ `echo "$dbExist" | grep "$i" | wc -l` == 0 ]; then
                continue
        fi
        for j in $mysqlTBs; do
                tbExist=`mysql -u"$mysqlUser" -p"$mysqlPwd" -h"$mysqlHost" -e "use $i;show tables like '"$j"';" 2> /dev/null`
                if [ `echo "$tbExist" | grep "$j" | wc -l` == 0 ]; then
                        continue
                fi
                tablesBackup="tablesBackup-$mysqlHost:$i.$j-`date +%Y%m%d`.sql.gz"
                mysqldump -u"$mysqlUser" -p"$mysqlPwd" -h"$mysqlHost" --single-transaction --databases "$i" --tables "$j" 2> /dev/null | gzip > "$tablesBakDIR"'/'"$tablesBackup"
        done
done
echo "`date +'%Y-%m-%d %H:%M:%s'` tablesBackup finished" | tee -a $logFile
exit 0

2.2 创建脚本触发

crontab -e

加入如下配置，

0 4 * * * bash ~/scripts/mysqlTablesBackup.sh