MySQL備份恢復(fù)基礎(chǔ)

MySQL備份恢復(fù)基礎(chǔ)

一，mysqldump命令

mysqldump這個命令一般可以滿足備份的大部分需要，因為這個工具支持全庫備份，單庫備份，單表備份，甚至邏輯備份，只備份表結(jié)構(gòu)等等功能。

主要命令格式：

shell> mysqldump [options] > dump.sql

主要的一些參數(shù)：

-u ，--user 用戶名

-p , --password 用戶密碼

-S ，--socket 套接字

-h , --host 指定連接的服務(wù)器

-P，--port 指定服務(wù)器的對應(yīng)端口

--default-character-set 設(shè)置備份集字符集

-A ，--all-databases 備份所有數(shù)據(jù)庫

-B ，--databases 導(dǎo)出某個或者某些數(shù)據(jù)庫，數(shù)據(jù)庫名以空格分隔

--tables 導(dǎo)出指定的表，格式為“數(shù)據(jù)庫名 表名”，這個參數(shù)會覆蓋-B參數(shù)

-l ,--lock-tables 鎖定表，默認(rèn)開啟

--single-transaction ,對于支持事物的數(shù)據(jù)庫引擎來說，保證了一致性，通常和-l一起工作，寫作 --single-transaction -l， 其作用是取代其他引擎的 -x 參數(shù)。

-d, --nodata, 只導(dǎo)出對象的結(jié)構(gòu)，不導(dǎo)出數(shù)據(jù)。

-t , --no-create-info , 只導(dǎo)出對象數(shù)據(jù)而不包含對象的結(jié)構(gòu)

-F，--flush-logs ，一般在全庫備份的時候需要先刷新日志，以方便恢復(fù)。

--flush-privileges , 備份前刷新一下權(quán)限。

-R , --routines , 導(dǎo)出存儲過程，函數(shù)等MYSQL程序。

-E， --events ,輸出event

--master-data=1/2   --1,記錄備份后將繼續(xù)回復(fù)的binlog 編號和位置。 2，編號和位置前有注釋

一般以上參數(shù)適應(yīng)了大多數(shù)的應(yīng)用場景

對于myisam,比較常用的全庫備份語句:

mysqldump -uroot -p -A -B -F -R --flush-privileges --master-data=2 -x --events | gzip > /data/backup/all.sql.gz

對于innodb，比較常用的全庫備份語句：

mysqldump -uroot -p -A -B -F -R --flush-privileges --master-data=2 --single-transaction --events | gzip > /data/backup/all.sql.gz

二，備份腳本：

1.全庫備份：

vi /data/mysqldata/scripts/mysql_full_backup.sh 
#!/bin/sh
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=/opt/mysql/bin
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${MYSQL_PORT}/mysql.sock -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 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}

2. 分庫備份：

vi mysql_full_backup_by_dbs.sh
#!/bin/sh
source /data/mysqldata/scripts/mysql_env.ini
DATA_PATH=/data/mysqldata/backup/mysql_full_by_dbs
if [ ! -d ${DATA_PATH}/${MYSQL_PORT} ] ; then
        mkdir -p ${DATA_PATH}/${MYSQL_PORT}
fi
LOG_FILE=${DATA_PATH}/${MYSQL_PORT}/dbfullbak_`date +%F`.log
MYSQL_PATH=/opt/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${MYSQL_PORT}/mysql.sock"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${MYSQL_PORT}/mysql.sock --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"`
do
        echo -e "*** Database: ${dbs} Backup start at `date +%F''%T''%w` ***" >> ${LOG_FILE}
        ${MYSQL_DUMP} -B ${dbs} | gzip > ${DATA_PATH}/${MYSQL_PORT}/${dbs}_`date +%F''%T''%w`.sql.gz
echo -e "*** Executed finished at at `date +%F''%T''%w` ===" >> ${LOG_FILE}
echo -e "*** Backup file size: `du -sh ${DATA_FILE}` ===\n" >> ${LOG_FILE}
done
echo -e "--- Find expired backup and delete those files ---" >> ${LOG_FILE}
for tfile in $(/usr/bin/find ${DATA_PATH}/${MYSQL_PORT} -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}

3. 分表備份

vi mysql_full_backup_by_tbs.sh
#!/bin/sh
source /data/mysqldata/scripts/mysql_env.ini
DATA_PATH=/data/mysqldata/backup/mysql_full_by_dbs
if [ ! -d ${DATA_PATH}/${MYSQL_PORT} ] ; then
        mkdir -p ${DATA_PATH}/${MYSQL_PORT}
fi
LOG_FILE=${DATA_PATH}/${MYSQL_PORT}/dbfullbak_`date +%F`.log
MYSQL_PATH=/opt/mysql/bin
MYSQL_CMD="${MYSQL_PATH}/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${MYSQL_PORT}/mysql.sock"
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} -S /data/mysqldata/${MYSQL_PORT}/mysql.sock --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"`
do
        echo -e "*** Database: ${dbs} Backup start at `date +%F''%T''%w` ***" >> ${LOG_FILE}
        ##${MYSQL_DUMP} -B ${dbs} | gzip > ${DATA_PATH}/${MYSQL_PORT}/${dbs}_`date +%F''%T''%w`.sql.gz
        if [ ! -d ${DATA_PATH}/${MYSQL_PORT}/${dbs} ] ; then
                mkdir -p ${DATA_PATH}/${MYSQL_PORT}/${dbs}
        fi
        for tbls in `${MYSQL_CMD} -D ${dbs} -e "show tables" | sed '1d'`
        do
                echo -e "*** Tables: ${dbs}.${tbls} start dump at `date +%F''%T''%w` ***" >> ${LOG_FILE}
                echo -e " Execute Command: ${MYSQL_DUMP} --tables  ${dbs} ${tbls} | gzip > ${DATA_PATH}/${MYSQL_PORT}/${dbs}/${dbs}.${tbls}.sql.gz" >> ${LOG_FILE}
                ${MYSQL_DUMP} --tables  ${dbs} ${tbls} | gzip > ${DATA_PATH}/${MYSQL_PORT}/${dbs}/${dbs}.${tbls}.sql.gz
                echo -e " ### End ${dbs}.${tbls} Dump! Stop_time:`date +%F''%T''%w`  ### " >> ${LOG_FILE}
        done
echo -e "*** Executed finished  at `date +%F''%T''%w` ===" >> ${LOG_FILE}
echo -e "*** Backup file size: `du -sh ${DATA_FILE}` ===\n" >> ${LOG_FILE}
done
echo -e "--- Find expired backup and delete those files ---" >> ${LOG_FILE}
for tfile in $(/usr/bin/find ${DATA_PATH}/${MYSQL_PORT}/${dbs} -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}
eco -e "--- Find expired logs and delete those files ---" >> ${LOG_FILE}
for tfile in $(/usr/bin/find ${DATA_PATH}/${MYSQL_PORT}/${dbs} -mtime +6)
do
        if [ -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}