當 MySQL 使用 InnoDB 模式卻又發生 ibdata ib_logfile0 ib_logfile1 誤刪或損毀時該如何資料復原?
如果是 MyISAM 模式的話，只要把DB資料夾複製回去就能還原，但 InnoDB 這樣是沒有效果的，可能會發生看的到資料表，但是 select 時又告訴你 table doesn't exist
首先，先備份mysql資料檔案
#cp -a /var/lib/mysql /var/lib/mysql_backup
通常資料庫發生損毀時會建議移除後重新安裝再進行復原工作
以下是 Ubuntu 的指令
#sudo apt-get purge --auto-remove mysql-common mysql-server mariadb-server
#sudo apt-get autoremove
#sudo apt-get autoclean
#sudo rm -rf /etc/mysql rm -rf /var/lib/mysql
#sudo apt-get install --reinstall mysql-server
#sudo service mysql start
如果移除過程中發生錯誤，可能跟套件關聯有關，可以試著使用下列指令移除
#sudo apt-get remove dbconfig-mysql
模擬情境(假設mydb裡有3個table)
DB Name: mydb
Table Name: customers、orders、news
重新安裝後的mysql路徑為: /var/lib/mysql
備份原mysql資料路徑為: /var/lib/mysql_backup
安裝 MySql 工具套件
#sudo apt update
#sudo apt install mysql-utilities
透過 MySQL 工具 mysalfrm 指令檔將 .frm schema 匯出成檔案
#mysqlfrm --diagnostic /var/lib/mysql_backup/mydb/ > mydb.sql
由於mydb.sql內沒有create database指令，所以可以加上
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `mydb`;
在文件內最上方
將mydb.sql內的 schema 匯入至 mysql
#mysql -u root -p < mydb.sql
進入mysql準備進行資料導入
#mysql -u root -p
輸入下列指令將DB設定為獨立
select concat(concat('alter table ',table_name), ' discard tablespace;') from information_schema.tables where table_schema='mydb' and engine ='InnoDB';
輸入完畢後進入選擇此DB
use `mydb`;
接著輸入剛看到的語法
alter table customers discard tablespace;
alter table orders discard tablespace;
alter table news discard tablespace;
執行完畢後離開mysql
#ls /var/lib/mysql/mydb/
會發現只剩下 .frm 檔案
這時將先前的ibd資料CO過來
#cp -a /var/lib/mysql_backup/mydb/*.ibd /var/lib/mysql/mydb/
copy完idb後，設定權限，導入資料
#chown mysql:mysql /var/lib/mysql/mydb/*.ibd
#chmod 660 /var/lib/mysql/mydb/*.ibd
接著進入mysql進行導回資料工作
#mysql -u root -p
use `mydb`;
輸入下列語法
alter table customers import tablespace;
alter table orders import tablespace;
alter table news import tablespace;
接著就可以select看看資料是不是都回來了
SELECT * FROM `customers`;
SELECT * FROM `orders`;
SELECT * FROM `news`;
關於復原資料，有發現一個問題就是匯入後有的ENUM欄位可能有變動
像我遇到的是 ENUM('Y','N') 確自動變為 ENUM('L','T')
另外也要檢查一下資料表每個欄位跟屬性還有字串長度、預設值
遇到這種情況就是復原後再自己修改調整一下資料表及內容
總之，能回復資料真是太好了!
