mysqldump自动化备份脚本

在my.cnf文件中添加

[mysqldump]

user=system

password=123456

[client]

user=system

password=123456

库级备份

[mysql@mysql1 scripts]$ more /data/mysqldata/scripts/mysql_full_backup.sh

#!/bin/bash

#create by allen

source /data/mysqldata/scripts/mysql_env.ini

DATA_PATH=/data/mysqldata/backup/mysql_full

DATA_FILE=${DATA_PATH}/dbfullbak_`date +%F`.sql.gz

LOG_FILE=${DATA_PATH}/dbfullbak_`date +%F`.log

MYSQL_PATH=/usr/local/mysql/bin

MYSQL_DUMP="${MYSQL_PATH}/mysqldump -S /data/mysqldata/${HOST_PORT}/mysql.sock -A -R -x --default-character-set=utf8"

#A导出所有数据库，R导出存储过程、函数等定义好的MySQL程序，x在导出任务执行期间锁定所有表

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 tfile in $(/usr/bin/find $DATA_PATH -mtime +6)

do

        if [ -d $tfile ] ; then

                rmdir $tfile

        elif [ -f $tfile ] ; then

                rm -f $tfile

        fi

        echo -e "--- Delete file:$tfile ---" >> ${LOG_FILE}

done

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

分表备份

备份时，直接按表为单位进行备份

[mysql@mysql1 scripts]$ more mysql_full_backup_by_table.sh

#!/bin/bash

#create by allen

source /data/mysqldata/scripts/mysql_env.ini

DATA_PATH=/data/mysqldata/backup/mysql_full_bytables

DATA_FILE=${DATA_PATH}/dbfullbak_by_tables_`date +%F`.sql.gz

LOG_FILE=${DATA_PATH}/dbfullbak_by_tables_`date +%F`.log

MYSQL_PATH=/usr/local/mysql/bin

MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${HOST_PORT}/mysql.sock"

MYSQL_DUMP="${MYSQL_PATH}/mysqldump -S /data/mysqldata/${HOST_PORT}/mysql.sock --single-transaction -l"

# single-transaction -l对于支持多版本的存储引擎InnoDB，在导出时会建立一致性的快照

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"`

do

         mkdir -p ${DATA_PATH}/${dbs}

         echo -e "*** Database:${dbs} Backup Start_Time:`date +%F' '%T' '%w` ***\n" >> ${LOG_FILE}

         for tbls in `${MYSQL_CMD} -D ${dbs} -e "show tables" | sed '1d'`

         do

                   echo -e "   ### Begin ${dbs}.${tbls} Dump! Start_Time:`date +%F' '%T`" >> ${LOG_FILE}

                   echo -e "   Execute Command:${MYSQL_DUMP} --tables ${dbs} ${tbls} | gzip > ${DATA_PATH}/${dbs}/${db

s}_${tbls}.sql.gz" >> ${LOG_FILE}

                   ${MYSQL_DUMP} --tables ${dbs} ${tbls} | gzip > ${DATA_PATH}/${dbs}/${dbs}_${tbls}.sql.gz

                   echo -e "   ### End ${dbs}.${tbls} Dump! Stop_Time:`date +%F' '%T` \n" >> ${LOG_FILE}

                   echo >> ${LOG_FILE}

         done

         echo -e "*** Database:${dbs} Backup Stop_Time:`date +%F' '%T' '%w` ***\n" >> ${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 tfile in $(/usr/bin/find $DATA_PATH -mtime +6)

do

         if [ -d $tfile ] ; then

                   rmdir $tfile

         elif [ -f $tfile ] ; then

                   rm -f $tfile

         fi

         echo -e "--- Delete file:$tfile ---" >> ${LOG_FILE}

done

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

脚本目前设定了自动过滤mysql、information_shema、performance_schema三个库中的表对象，这3个库都属于系统库，其中的对象通常不需要单独处理。可根据实际情况，对脚本进行调整，更好地支持自己的备份和恢复策略。