mysql 5.7 刪除ibdata1 、ib_logfile 檔案的資料恢復

记忆抹不去發表於2024-09-12
MySql資料恢復

  • 簡介：

　　本文記錄刪除ibdata1 、ib_logfile 檔案被意外刪除且無法還原或損壞的解決方案，當刪除後沒有重啟mysql 可以查詢程序號，找到刪除的檔案可以還原回來。參考其他文章。本文介紹ibdata1 、ib_logfile 檔案無法找到或異常沒有備份的情況處理。

新安裝一臺mysql用作從庫

注意安裝版本與主庫版本一致。

獲取所有建表語句

如果原表所有建表語句都有儲存最方便直接用即可。

如果沒有儲存需要使用工具mysqlfrm。在問題機器主庫安裝下：

#安裝目錄 自己建立
cd /data/      
#下載檔案
wget  https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz
#解壓檔案
tar -xf mysql-utilities-1.6.5.tar.gz
ls
#進入解壓好的資料夾
cd mysql-utilities-1.6.5/
#build構建
python ./setup.py build
#install安裝
python ./setup.py install
 
#安裝成功檢視命令
mysqlfrm --version
  • 模式介紹

　　–basedir：需指定伺服器的基本目錄，相當於再生一個資料庫例項，還需指定prot，user及.frm檔案位置等資訊，指定的prot不能與在執行的資料庫衝突，可在原資料庫無法開啟情況下使用，在讀取.frm檔案後，再生的例項將被關閉，所有的臨時檔案將被刪除。

　　例：mysqlfrm --basedir=/mysql/mysqld/ --port=3333 --user=mysql /mysql/data/test/tables.frm --show-stats

　　–server：需指定資料庫的連線字串，需在原資料庫可以開啟的情況下使用，指定資料庫使用者名稱，密碼，埠號及.frm檔案位置等資訊。

　　例：mysqlfrm --server=root:Root#123@localhost:3306 /mysql/data/test/tables.frm --port=3310 --user=mysql

　　注意：如資料庫還可以正常連線，推薦使用–server模式

  • 獲取建表語句指令碼　
#!/bin/bash

# 定義變數
output_file="Create.sql"                     # 輸出檔名
mysql_root_password="123456"                 # MySQL root 使用者密碼
mysql_host="localhost"                         # MySQL 主機
mysql_port="3306"                             # MySQL 埠
mysql_user="mysql"                             # MySQL 使用者名稱
data_dir="/data/mysql/"                        # MySQL 資料目錄
excluded_dirs=(mysql sys performance_schema)   # 需要排除的目錄列表

# 清空輸出檔案，如果檔案不存在則建立
> $output_file

# 構建排除目錄的 find 過濾條件
exclude_find=""
for dir in "${excluded_dirs[@]}"; do
    exclude_find+=" -path ${data_dir}${dir} -o"
done
exclude_find=${exclude_find::-2}  # 去掉最後一個 "-o"

# 查詢所有的 .frm 檔案，排除指定目錄
find $data_dir -type d \( $exclude_find \) -prune -o -type f -name '*.frm' -print | while read frm_file; do
    # 提取建表語句（去掉註釋）
    mysqlfrm --server=root:"$mysql_root_password"@$mysql_host:"$mysql_port" "$frm_file" --user=$mysql_user \
    | grep -v '^#' >> $output_file
    echo -e "\n" >> $output_file  # 新增換行符便於分隔
done

echo "建表語句已儲存到 $output_file"

建表語句匯入從庫

從庫先建立所有主庫的庫，執行Create.sql語句。

還原資料

修改從庫配置檔案新增：innodb_force_recovery=1，重啟資料庫。

建表語句建立好後，對每個表進行刪除表空間，注意指令碼不要放mysql預設4個庫，指令碼如下：

#!/bin/bash

# MySQL 使用者名稱和密碼
MYSQL_USER="root"
MYSQL_PASS="123456"

# 資料庫列表
databases=(
    "aaa"
    "bbbb"
    "ccc"
    "ddd"
)

# 遍歷資料庫列表
for db in "${databases[@]}"; do
    echo "Processing database: $db"
    
    # 獲取當前資料庫中的所有表
    tables=$(mysql -u $MYSQL_USER -p"$MYSQL_PASS" -e "SHOW TABLES;" $db | awk '{print $1}' | grep -v '^Tables_in')

    # 遍歷表列表並執行 DISCARD TABLESPACE
    for table in $tables; do
        echo "Discarding tablespace for table: $table in database: $db"
        mysql -u $MYSQL_USER -p$MYSQL_PASS -e "ALTER TABLE $table DISCARD TABLESPACE;" $db
    done
done

echo "Tablespace discard operation completed."

執行完刪除表空間後匯入主庫的ibd檔案。庫很多可以使用指令碼主庫打包mysql目錄放到從庫中，使用指令碼複製：

#!/bin/bash

# 源目錄和目標目錄
src_dir="/home/mysqlbak"
dest_dir="/home/mysql"

# 目錄列表
dirs=(
    "aaa"
    "bbb"
)

# 遍歷目錄列表並複製檔案
for dir in "${dirs[@]}"; do
    src_path="$src_dir/$dir"
    dest_path="$dest_dir/$dir"
    
    # 檢查源目錄是否存在
    if [ -d "$src_path" ]; then
        # 複製 .ibd 檔案
        if [ "$(ls -A $src_path/*.ibd 2>/dev/null)" ]; then
            echo "Copying .ibd files from $src_path to $dest_path"
            # 建立目標目錄（如果存在）
            if [ ! -d "$dest_path" ]; then
                echo "Directory $dest_path does not exist. Skipping copy."
                continue
            fi
            cp -a  "$src_path"/*.ibd "$dest_path/"
        else
            echo "No .ibd files found in $src_path"
        fi
    else
        echo "Source directory $src_path does not exist"
    fi
done

echo "File copy operation completed."

再次執行匯入表空間命令，指令碼和刪除表空間基本一樣，複製一份修改一個地方即可：

mysql -u $MYSQL_USER -p$MYSQL_PASS -e "ALTER TABLE $table DISCARD TABLESPACE;" $db

##改為

mysql -u $MYSQL_USER -p$MYSQL_PASS -e "ALTER TABLE $table IMPORT TABLESPACE;" $db

大功告成！檢測從庫資料是否恢復。
資料恢復後注意修改配置檔案：innodb_force_recovery=1 刪除，重啟資料庫。

相關文章