【MySQL】MySQL之备份自动化脚本

库级备份

[root@strong backup]# more mysql_full_backup.sh 
#!/bin/sh

source /usr/local/mysql/backup/mysql_env.ini

DATA_PATH=/usr/local/mysql/backup
DATA_FILE=${DATA_PATH}/whole_db_bak_`date +%F`.sql.gz
LOG_FILE=${DATA_PATH}/whole_db_bak_`date +%F`.log
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${USER} -p${PASSWD} -A -R -x --default-character-set=utf8"

echo > $LOG_FILE

echo -e "======= Jobs started at `date +%F' '%T' '%w`=====\n">> $LOG_FILE
echo -e "***** Executed command:${MYSQL_DUMP} | gzip > ${DATA_FILE}" >> $LOG_FILE

${MYSQL_DUMP} | gzip > ${DATA_FILE}

echo -e "***** Executed Finished at `date +%F' '%T' '%w` =====" >> $LOG_FILE
echo -e "***** Backup file size :`du -sh ${DATA_FILE}` =====\n" >> $LOG_FILE

echo -e "---------- Find expired backup and delete those files ----------" >> $LOG_FILE

for dfile in $(/usr/bin/find $DATA_PATH/ -mtime +6)
do
    if [ -d $dfile ] ; then
             rmdir $dfile
    elif [ -f $dfile ] ; then
             rm -f $dfile
    fi
    echo -e "------- Delete file :$dfile ----------" >> $LOG_FILE
done

#echo -e "\n ===== Job ended at `date +%F' '%T' '%w`===== \n" >> $LOG_FILE

分表备份

[root@strong backup]# more mysql_full_backup_by_table.sh 
#!/bin/sh

source /usr/local/mysql/backup/mysql_env.ini

DATA_PATH=/usr/local/mysql/backup/mysql_full_tables
DATA_FILE=${DATA_PATH}/whole_db_bak_by_table_`date +%F`.sql.gz
LOG_FILE=${DATA_PATH}/whole_db_bak_`date +%F`.log
MYSQL_PATH=/usr/local/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${USER} -p${PASSWD}"

MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${USER} -p${PASSWD} --single-transaction -l "

echo > $LOG_FILE

echo -e "======= Jobs started at `date +%F' '%T' '%w`=====\n">> $LOG_FILE

for dbs in `${MYSQL_CMD} -e "show databases" | sed '1d' | egrep -v "information_schema|mysql|performance_schema|sys"`
do
    mkdir -p ${DATA_PATH}/${dbs}
    echo -e "***** Database :${dbs} Backup Start Time :`date +%F' '%T' '%w` *****\n" >> $LOG_FILE
    
    for tbs in `${MYSQL_CMD} -D $dbs -e "show tables" | sed '1d'`
    do 
       echo -e "##### Begin $dbs.$tbs Dump ! Start Time :`date +%F' '%T' '%w`" >>$LOG_FILE
       echo -e "     Execute Command : ${MYSQL_DUMP} --tables $dbs $tbs | gzip > ${DATA_PATH}/$dbs/$dbs_$tbs.sql.gz" >> $LOG_FILE
       ${MYSQL_DUMP} --tables $dbs $tbs | gzip > ${DATA_PATH}/$dbs/$dbs_$tbs.sql.gz
       echo -e "##### End $dbs.$tbs Dump ! Stop Time :`date +%F' '%T' '%w`\n" >> $LOG_FILE
       echo >> $LOG_FILE
    done

    echo -e "***** Database :$dbs Backup Stop Time :`date +%F' '%T' '%w` *****" >> $LOG_FILE
    echo -e "***** Backup file size :`du -sh ${DATA_PATH}/$dbs` *****\n" >> $LOG_FILE
done

echo -e "---------- Find expired backup and delete those files ----------" >> $LOG_FILE

for dfile in $(/usr/bin/find $DATA_PATH/ -mtime +6)
do
    if [ -d $dfile ] ; then
             rmdir $dfile
    elif [ -f $dfile ] ; then
             rm -f $dfile
    fi
    echo -e "------- Delete file :$dfile ----------" >> $LOG_FILE
done

#echo -e "\n ===== Job ended at `date +%F' '%T' '%w`===== \n" >> $LOG_FILE

参考：涂抹MySQL