MySQL管理之二進位記錄檔的管理

Mysql的二進位日誌記錄的是所有mysql資料庫發生變更的資訊，因此對資料庫非常重要，二進位日誌的作用我概括了一下主要包含如下幾個方面：

1、可以用於資料庫基於時間點的還原。

2、可以用來分析資料庫發生更改的情況，比如程式的bug導致資料庫資料更改等等。

3、二進位日誌還可以用來重建資料庫。

從上面的幾點可以看出mysql的二進位日誌還是非常重要的，在日常工作中，二進位日誌又往往非常占空間，尤其是寫入比較頻繁的資料庫，二進位日誌更是增長迅猛。那麼在DBA的日常工作中如何來管理二進位日誌呢？接下來就來詳細敘述一下我在日常工作中是如何管理二進位日誌的。

我是通過如下方式來管理二進位日誌的：

1.  通過shell指令碼每小時做一次二進位日誌flush操作

2.  通過shell指令碼每小時進行一次二進位日誌的備份工作，將一小時新產生的二進位日誌遷移到nfs備份伺服器，並壓縮存放，指令碼最後附上！

3.  在my.cnf設定檔中加上expire_logs_days = 5，這樣mysql會自動刪除5天前的日誌。

設定步驟如下：

1、搭建NFS備份伺服器。

搭建NFS非常簡單，如下幾步就可以完成：

yum -y install setup-*  initscripts-*  nfs-utils-*  portmap-*  quota-*

編輯/etc/exports，添加一行二進位日誌的備份條目：

vim /etc/exports

/data/binlog_backup 192.168.3.135(rw,sync,no_root_squash)  *(ro)

讓192.168.3.135這個IP可以讀寫,其他的唯讀。

配置完成之後重新啟動portmap和nfs就可以了：

/etc/rc.d/init.d/portmap restart

/etc/rc.d/init.d/nfs restart

至此伺服器端的NFS就設定完成了。接下來設定用戶端就是我們的mysql伺服器如何掛載：

/etc/rc.d/init.d/portmap start

mkdir /data/binlog_backup

mount -t nfs 192.168.3.92:/data/binlog_backup /data/binlog_backup

如果不啟動用戶端的portmap的話掛載的時候會報：mount.nfs: Input/output error，記得將mount的命令添加到/etc/rc.local中，不然下次重啟伺服器後會導致備份二進位日誌失敗。

2、將指令碼放到mysql資料庫上，並設定計劃任務為每小時運行一次。

指令碼內容如下：

cat bin_log_magment.sh

#!/bin/bash
#Purpose:管理mysql二進位日誌，每小時重新整理二進位日誌，並將日誌複製到nfs伺服器上，方便以後恢複和問題分析！
#Author:carl_zhang
#Date:2012-5-15

 

#some variables 一些參數
#定義資料庫的相關資訊
USERNAME=root
PASSWORD=zhang@123
HOSTIP=localhost
MYSQL=/usr/local/mysql/bin/mysql

#設定二進位日誌的路徑
BIN_LOG_DIR=/data/dbdata
#設定二進位記錄備份的路徑
BIN_LOG_BACKUP_DIR=/data/binlog_backup
#定義一個常量來跳過最後一個記錄檔
COUNT=0
#定義一個記錄檔
LOGFILE=$BIN_LOG_BACKUP_DIR/binlog_backup.log
#找出二進位日誌的名稱首碼
BINLOG_PREFIX=`grep "log-bin=" /etc/my.cnf | awk -F'=' '{print $2}'`
#檢查backup.file檔案是否存在，第一次跑的時候如果沒有這個會報錯

if [ ! -f "$BIN_LOG_BACKUP_DIR/backup.file" ];then
touch $BIN_LOG_BACKUP_DIR/backup.file
fi
#運行檔案比較之前先執行一下flush logs
$MYSQL -u$USERNAME -p$PASSWORD -h$HOSTIP -e "flush logs"
#比較二進位記錄檔列表和已備份的二進位檔案列表
FILE_LIST=`comm -23 $BIN_LOG_DIR/$BINLOG_PREFIX.index $BIN_LOG_BACKUP_DIR/backup.file`
#統計記錄檔的個數
FILE_COUNT=`comm -23 $BIN_LOG_DIR/$BINLOG_PREFIX.index $BIN_LOG_BACKUP_DIR/backup.file|wc -l`

 

 

#程式本文
#開始備份二進位記錄檔
for file in $FILE_LIST
do
BINLOG_NAME=`basename $file`
let COUNT=$COUNT+1
if [ $COUNT -eq $FILE_COUNT ];then
#跳過最新的那個二進位記錄檔
echo "skip the lastest binlog file" >> $LOGFILE
else
cp $BIN_LOG_DIR/$BINLOG_NAME $BIN_LOG_BACKUP_DIR/
if [ $? -eq 0 ];then
echo "`date -d "today" +%Y-%m-%d-%H-%M-%S` $BINLOG_NAME backup success" >> $LOGFILE
else
echo "`date -d "today" +%Y-%m-%d-%H-%M-%S` $BINLOG_NAME backup faild, Please check it out" >> $LOGFILE
exit 5
fi
#對二進位日誌進行壓縮存放
gzip $BIN_LOG_BACKUP_DIR/$BINLOG_NAME
if [ $? -eq 0 ];then
echo "`date -d "today" +%Y-%m-%d-%H-%M-%S` $BINLOG_NAME gzip success" >> $LOGFILE
else
echo "`date -d "today" +%Y-%m-%d-%H-%M-%S` $BINLOG_NAME gzip faild, Please check it out" >> $LOGFILE
exit 5
fi
echo ./$BINLOG_NAME >> $BIN_LOG_BACKUP_DIR/backup.file
fi
done

如果大家需要使用這個指令碼，請自行更改對應的配置資訊，比如IP地址，使用者名稱密碼、以及目錄等等。

注意因為這裡有mysql的使用者名稱和密碼，需要將指令碼的使用權限設定成700，使用命令為：

chmod 700 bin_log_magment.sh

手工運行沒有問題以後，添加到計劃任務中去：

00 */1 * * *  /var/script/bin_log_magment/bin_log_magment.sh >/dev/null 2>&1

3、設定my.cnf設定檔，加上expire_logs_days = 5，並重啟mysql。

這個就不詳細敘述了，是個人都會了！