定時對大資料量的表進行分表對資料備份

工作中遇到資料庫中一個表的資料量比較大，屬於日誌表。正常情況下是不會有查詢操作的，但如果不進行分表資料太多，執行一條簡單sql語句要等好幾分鐘。。

分表工具：linux的shell + mysql自身提供的管理命令

原理：使用一個和原表資料結構一樣的表，替換原表。

Linux Shell內容如下：

=======================開始

DATE=`date +%Y%m%d`   #當前日期備份

BACKUP_DIRECTORY="/var/db_backup"  #備份的目錄，主要存放備份中需要的暫存資料表

 

DB_USER="root"               #資料庫使用者

DB_PWD="123456"         #資料庫密碼

 

WSM_APPENTRYREQLOG_SHELL="$BACKUP_DIRECTORY/db_appentryreqlog_shell.sql"                     #替換表db_appentryreqlog時執行的sql命令檔案存放位置

WSM_ADENTRYSHOWRECORD_SHELL="$BACKUP_DIRECTORY/db_adentryshowrecord_shell.sql"      #替換表 db_adentryshowrecord時執行的sql命令檔案存放位置

 

WSM_APPENTRYREQLOG_FILE="$BACKUP_DIRECTORY/db_appentryreqlog_nodata.sql"                    #匯出表db_appentryreqlog結構時的檔案存放位置

WSM_ADENTRYSHOWRECORD_FILE="$BACKUP_DIRECTORY/db_adentryshowrecord_nodata.sql"     #匯出表db_adentryshowrecord結構時的檔案存放位置

 

rm -f $WSM_APPENTRYREQLOG_FILE                                                                                                   #如果已經存在檔案，則刪除

rm -f $WSM_ADENTRYSHOWRECORD_FILE                                                                                           #如果已經存在檔案，則刪除

 

mysqldump -u$DB_USER -p$DB_PWD -d db db_appentryreqlog > $WSM_APPENTRYREQLOG_FILE    #匯出表結構

mysqldump -u$DB_USER -p$DB_PWD -d db db_adentryshowrecord > $WSM_ADENTRYSHOWRECORD_FILE  #匯出表結構

 

sed -i "s/wsm_appentryreqlog/db_appentryreqlog_new/" $WSM_APPENTRYREQLOG_FILE                         #將匯出的表結構中表名稱wsm_appentryreqlog替換為臨時名稱db_appentryreqlog_new

sed -i "s/wsm_adentryshowrecord/db_adentryshowrecord_new/" $WSM_ADENTRYSHOWRECORD_FILE   #同上

 

sed -i 's/AUTO_INCREMENT=[0-9]\+/AUTO_INCREMENT=1/' $WSM_APPENTRYREQLOG_FILE                     #新表結構，ID自增值重設為1

sed -i 's/AUTO_INCREMENT=[0-9]\+/AUTO_INCREMENT=1/' $WSM_ADENTRYSHOWRECORD_FILE

 

sed -i "s/db_appentryreqlog_bak/db_appentryreqlog_$DATE/" $WSM_APPENTRYREQLOG_SHELL                            #將db_appentryreqlog_shell.sql檔案中的備份表名稱根據日期動態替換

sed -i "s/db_adentryshowrecord_bak/db_adentryshowrecord_$DATE/" $WSM_ADENTRYSHOWRECORD_SHELL      #同上

 

#cat $WSM_APPENTRYREQLOG_FILE

#echo '---------------------------------------------------------------------------------1'

#cat $WSM_ADENTRYSHOWRECORD_FILE

#echo '---------------------------------------------------------------------------------2'

#cat $WSM_APPENTRYREQLOG_SHELL

#echo '---------------------------------------------------------------------------------3'

#cat $WSM_ADENTRYSHOWRECORD_SHELL

#echo '---------------------------------------------------------------------------------4'

 

#以上準備工作完成，開始替換表

mysql -u$DB_USER -p$DB_PWD db < $WSM_APPENTRYREQLOG_FILE      #先把新的表結構匯入進去

mysql -u$DB_USER -p$DB_PWD db < $WSM_APPENTRYREQLOG_SHELL 

 

mysql -u$DB_USER -p$DB_PWD db < $WSM_ADENTRYSHOWRECORD_FILE   #執行替換表命令

mysql -u$DB_USER -p$DB_PWD db < $WSM_ADENTRYSHOWRECORD_SHELL

 

#恢複檔案db_appentryreqlog_shell.sql和db_adentryshowrecord_shell.sql的內容為修改前

sed -i "s/db_appentryreqlog_$DATE/db_appentryreqlog_bak/" $WSM_APPENTRYREQLOG_SHELL

sed -i "s/db_adentryshowrecord_$DATE/db_adentryshowrecord_bak/" $WSM_ADENTRYSHOWRECORD_SHELL

 

#執行完畢。

=======================結束 

其中db_appentryreqlog_shell.sq檔案的內容為

RENAME TABLE db_appentryreqlog TO db_appentryreqlog_bak,db_appentryreqlog_new TO db_appentryreqlog;

db_adentryshowrecord_shell.sql檔案的內容為

RENAME TABLE db_adentryshowrecord TO db_adentryshowrecord_bak,db_adentryshowrecord_new TO db_adentryshowrecord;   #先把舊錶改名備份，然後把新的表改成舊錶的名字

將shell命令檔案 以及db_appentryreqlog_shell.sq和db_adentryshowrecord_shell.sql檔案都放置到BACKUP_DIRECTORY="/var/db_backup"目錄下

然後把shell命令配置到定時任務cron裡面，OK了。