Mysql

在繁忙的主從複製系統上恢復單個 mysql 數據庫

  • September 7, 2017

尋找一種策略或工具來處理在繁忙的複制系統中將單個數據庫恢復到某個時間點。

我有 12 個數據庫在主從復製配置中的 2 個 MySQL 5.0.77 伺服器上執行。每天對只讀從站進行一次完整轉儲，並且有可用的增量 SQL 轉儲，這些備份是異地備份，並且複制狀態受到監控。

編輯：表是 InnoDB 和 myISAM 的混合體，因此不提供特定於引擎的解決方案。

因此，如果主伺服器完全失敗，我可以中斷複製並提升從伺服器，我還可以選擇重建新伺服器並從越位 FULL 備份進行配置，然後應用每小時從從伺服器獲取的差異。

但是我擔心如何處理部分故障或單個數據庫的故障。我可以想到兩種很可能的情況；

  1. 數據庫 7（例如）已損壞，繼續處理一些請求，直到有人注意到它已損壞，或者來自日誌文件的警報…
  2. 一些查詢，如刪除數據庫、刪除表、“更新位置…”類型的查詢會破壞單個數據庫或其中的某個子集。

目前我有一堆 FULL 轉儲為 FULL- $ DATE-all-databases.sql.gz files , and differentials that can be applied to the FULL dumps as DIFF- $ DATE-all-databases.sql.gz

要將數據庫 7 恢復到某個時間點，需要通過 FULL 和 DIFF 文件執行 grep，並手動應用該 sql。

為了能夠恢復到主數據庫的先前 DIFF 轉儲之一，我應該如何進行？

我是否需要備份到單個數據庫文件，即

mysqldump --databases "database1" | gzip > database1.sql.gz
mysqldump --databases "database2" | gzip > database2.sql.gz
mysqldump --databases "database3" | gzip > database3.sql.gz

而不是..

mysqldump --master-data --lock--all-databases --all-databases | gzip > all-databases.sql.gz

如果我選擇單獨的 mysqldump 文件，主數據二進制日誌會發生什麼情況，我什至應該為主伺服器恢復轉儲設置 –master-data 嗎？

如果你所有的數據庫都只使用 InnoDB，我有一些好消息。

您應該從從屬設備並行轉儲所有數據庫。

事實上，您可以強制所有數據庫進入相同的時間點。

關於從站，首先要記住的是，如果它不是其他從站的主站，則不需要啟用二進制日誌記錄。

您不能--master-data對並行轉儲使用選項，因為每個轉儲將在每個轉儲文件的第 22 行寫入不同的位置。最好記錄下 Master 的最後一個日誌文件，並定位 Slave 執行時使用SHOW SLAVE STATUS\G. 這樣，所有數據庫都具有相同的時間點位置。

您可以收集所有數據庫並編寫所有數據庫的並行轉儲腳本。

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

for DB in `cat ${DBLIST}` 
do 
   mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz & 
done 
wait 

mysql -h... -u... -p... -e"START SLAVE;"

如果數據庫太多，一次轉儲 10 或 20 個，如下所示：

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0    
for DB in `cat ${DBLIST}` 
do 
   mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz & 
   (( COMMIT_COUNT++ ))
   if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
   then
       COMMIT_COUNT=0
       wait
   fi
done 
wait 
if [ ${COMMIT_COUNT} -gt 0 ]
then
   wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

如果需要恢復單個表，可以按大小順序一次並行轉儲 20 個表。

嘗試這個：

TBLIST=/tmp/ListOfTablesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY data_length" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0    
for DBTB in `cat ${TBLIST}` 
do
   DB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $1}'`
   TB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $2}'`
   DUMPFILE=$DB-{DB}-TBL-${TB}.sql.gz
   mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} ${TB} | gzip >  ${DUMPFILE} & 
   (( COMMIT_COUNT++ ))
   if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
   then
       COMMIT_COUNT=0
       wait
   fi
done 
wait 
if [ ${COMMIT_COUNT} -gt 0 ]
then
   wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

現在您有了轉儲數據庫或單個表的腳本，您可以自行決定載入該數據。如果您需要從 master 上的二進制日誌中執行 SQL，您可以使用mysqlbinlog並為其指定 datetime 位置並將 SQL 輸出到其他文本文件。您只需執行盡職調查，即可從 bnary 日誌具有的任何時間戳中找到所需的數據量。請記住，作業系統中每個二進制日誌的時間戳都代表上次寫入的時間。

引用自：https://dba.stackexchange.com/questions/10970

相關問答

Mysql

即使使用 –lock-tables=false 在 mysqldump 期間寫入鎖

  • August 21, 2017
檢視問答
Mysql

使用 mysqldump 設置 MySQL 從站以獲取初始數據

  • December 5, 2011
檢視問答
Mysql

如何使用 MySQL 為數據庫轉儲批處理多個插入語句？

  • January 25, 2021
檢視問答
Mysql

使用 MyISAM 讀取數據，使用 InnoDB 寫入數據

  • February 25, 2020
檢視問答
Mysql

MySQL：將大型 MyISAM 表更改為 InnoDB (17gb+) 的最佳配置/方法

  • September 11, 2019
檢視問答
Mysql

如何將混合了 InnoDB 和 MyISAM 的 MySQL 數據庫複製到同一台伺服器？

  • September 3, 2019
檢視問答