
MySQL ibdata?件刪除故障分析
1. 故障復現
1.1 mv ibdata1 ibdata1_bak
1.1.1 查看狀態(tài)
1.1.2 mv ?件
1.1.3 查看句柄狀態(tài)
1.1.4 重啟服務
1.2 先cp ibdata1備份 然后rm ibdata1
1.2.1 先查看狀態(tài)
1.2.2 刪除ibdata?件
2 問題修復
2.1 MySQL進程還在
2.1.1 查看實例狀態(tài)
2.1.2 停?復制
2.1.3 刷新臟?
2.1.4 查找MySQL進程句柄?件
2.2 MySQL進程不存在,原ibdata保留
2.2.1 先備份業(yè)務庫表數據?件
2.2.2 修改my.cnf參數
2.2.3 dump表結構
2.2.4 創(chuàng)建表/導?表結構
2.2.5 刪除業(yè)務表表空間
2.2.6 將備份業(yè)務庫表數據?件拷?回數據?錄
2.2.7 將恢復后的新實例作為業(yè)務庫對外提供服務
2.3 MySQL進程不存在,原ibdata被刪除
2.3.1 在新實例創(chuàng)建表
2.3.2 沒有建表語句
2.3.3 將建表語句導?新實例
3 附錄
3.1 ibdata 不同mv?式驗證
3.1.1 mv到其他分區(qū)
3.2 innodb_force_recovery參數說明
3.3 拷?句柄?件恢復步驟
3.4 從frm?件獲取建表語句
3.4.1 dbsake使?說明
3.4.1 dbsake 測試過程
MySQL ibdata?件刪除故障分析
1. 故障復現
1.1 mv ibdata1 ibdata1_bak
1.1.1 查看狀態(tài)
實例狀態(tài)
?件句柄狀態(tài)
1.1.2 mv ?件
mv ibdata
mv ibdata1 ibdata1_bak
1.1.3 查看句柄狀態(tài)
句柄?件指向的?錄?件發(fā)?了改變,猜測:inode未改變,所以句柄?件指向了新的?件;
已驗證:在同分區(qū),mv ibdata1到同分區(qū)其他?錄下,?件句柄?件會刷新為的mv后的?件,并不會變?yōu)?span id="rfpddht" class="ff3">delete狀態(tài)
mv 到其他分區(qū) 請參考:附錄3.1.1 mv到其他分區(qū)
1.1.4 重啟服務
重啟后MySQL服務并不會讀取ibdata_bak 的備份?件
## 關閉mysql
mysql> shutdown;
## 重啟 mysql
[root@centos-linux fd]# /data/mysql/base/base_5.7.27/bin/mysqld_safe --defaults-?le=/data/mysql/base/base_5.7.27/etc/3301/my.cnf &
mysql-error.log
ibdata1不存在然后創(chuàng)建新ibdata1,但新ibdata1與已經存在的undolog不匹配導致報錯,?法啟動;
mv ibdata1后再刪除undo redolog;可以正常啟動,但表?法正常讀取
1.2 先cp ibdata1備份 然后rm ibdata1
1.2.1 先查看狀態(tài)
使?cp ibdata1 ibdata1_bak 備份?份?件
使?stat命令查看ibdata1及ibdata1_bak?件inode
進?/proc/2821/fd ?錄,使?ls 命令查看ibdata1操作系統(tǒng)句柄狀態(tài)
1.2.2 刪除ibdata?件
使?rm 命令刪除ibdata1?件,保留ibdata1_bak?件
進?/proc/2821/fd ?錄,使?ls 命令查看ibdata1操作系統(tǒng)句柄狀態(tài)
復現現場故障現象
2 問題修復
2.1 MySQL進程還在
MySQL進程存在的情況下,操作系統(tǒng)還保留進程打開的?件句柄,可以進?/proc/$進程pid/fd ?錄下查到?件對應的句柄?件,通過cp 拷?到原路徑
下;
2.1.1 查看實例狀態(tài)
##############for mgr
select * from performance_schema.replication_group_members;
SELECT ta.* ,tb.MEMBER_HOST,tb.MEMBER_PORT,tb.MEMBER_STATE FROM performance_schema.global_status ta,performance_schema.replication_
group_members tb WHERE ta.VARIABLE_NAME='group_replication_primary_member' and ta.VARIABLE_VALUE=tb.MEMBER_ID;
##################for slave
show slave status\G;
##############for mgr
stop group_replication;
################for slave
stop slave;
###################################
##### 添加全局讀鎖同時開啟super_read_only
FLUSH TABLES WITH READ LOCK;
set global super_read_only=1;
##### 修改強制臟?刷新,調整innodb_io_capacity 增加IO刷新參數
set global innodb_max_dirty_pages_pct = 0;
set global innodb_io_capacity=2000;
##### 查找鏈接并組成kill 語句
select concat('kill ',id,';') from information_schema.processlist where db is not null;
##### 確認臟?是否刷新完成
show engine innodb status\G;
------------
TRANSACTIONS
------------
Trx id counter 6435
Purge done for trx's n:o < 6435 undo n:o < 0 state: running but idle
## 確保事務號?致
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
## 確保insert bu?er 合并插?緩存等于1
---
LOG
---
Log sequence number 1721029
Log ?ushed up to 1721029
Last checkpoint at 1721029
## 確保這三個值不變
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 274726912; in additional pool allocated 0
Dictionary memory allocated 76753
Bu?er pool size 16383
Free bu?ers 16181
Database pages 202
Old database pages 0
Modi?ed db pages 0
## 確保臟?數量為0
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 3415, id 140701545637632, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 5
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
## 確保插?、更新、刪除為0
## 查找MySQL進程
ps -ef |grep 端?號
## 根據進程號查找到對應的?件句柄
cd /proc/進程號/fd
ls -lrt|grep delete|grep innodb
## 備份源?錄:
mv /ssddata/innodb/13390 /ssddata/innodb/13390_bak
mkdir /ssddata/innodb/13390
## 拷??件句柄回去
cp 9 /ssddata/innodb/13770/ib_log?le1
cp 4 /ssddata/innodb/13770/ib_log?le0
cp 11 /ssddata/innodb/13770/ibtmp1
cp 10 /ssddata/innodb/13770/ibdata1
## ?件修改權限:
chown mysql:mysql -R /ssddata/innodb/13390
## 重啟MySQL服務
mysql -h127.1 -P3390 -uroot -p'passwd' -A
shutdown;
##########For docker
docker restart container_id ####重啟docker并啟動MySQL
##########For 服務器
/$mysql_path/bin/mysqld_safe --defaults-?le=/$path/my.cnf &
建議:實例重啟后,使?mysqldump 導出?份表結構,然后備份所有的表數據?件;在新的實例導?表結構,然后使?表空間傳輸?式將數據恢復到
新實例;或者dump?份數據在新實例恢復
注意:1. 如果句柄最后變更時間停留在某個時間,會導致后來的DDL操作丟失
2.2 MySQL進程不存在,原ibdata保留
如果有最新的備份及binlog,可以使?備份+binlog的?式恢復;
ibdata1備份到其分區(qū)?錄導致故障,因在刪除后有數據寫?,將原ibdata1?件拷?回原路徑下,因LSN差距過?導致MySQL?法啟動;
這種情況可以使?force recovery?式強制啟動MySQL服務,導出建表語句在新實例重新創(chuàng)建表然后使?可傳輸表空間?式進?恢復;
已知表數據?件沒有損壞;如果是MySQL Crash后force recovery啟動進?數據恢復,建議先dump出表數據的?式進?數據恢復,因為在
MySQLCrash情況下?法確認表數據?件是否損壞
2.2.1 先備份業(yè)務庫表數據?件
cp *.ibd /backup/*.ibd
2.2.2 修改my.cnf參數
在my.cnf?件添加innodb_force_recovery參數,建議從1 增加到6逐?進?嘗試;數據越?對MySQL影響越?
innodb_force_recovery = [1-6]
innodb_force_recovery 參數說明請參考 附錄 3.1 innodb_force_recovery參數說明
2.2.3 dump表結構
使?mysqldump 把mysql.user表數據
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=o? --hex-blob --no-cre
ate-info
--tables mysql user > mysql_user.sql
使?mysqldump 導出業(yè)務庫的表結構,如果ibdata?件不是最新可能會丟失部分表的表結構:
mysqldump -h127.0.0.1 -uroot -P3306 -p --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=o? --hex-blob --no-data
employees > employees_schema.sql
2.2.4 創(chuàng)建表/導?表結構
在新的空實例中導?mysql.user表數據,及業(yè)務表;
source mysql_user.sql
source employees_schema.sql
alter table tablename discard tablespace;
2.2.6 將備份業(yè)務庫表數據?件拷?回數據?錄
1. 拷?數據?件備份到數據?錄
cp *ibd /data/mysql/data/3301/data/*.idb
2. 修改權限
chown mysql:mysql *
3. 導?表空間
組裝sql語句,將業(yè)務表空間導?新實例
alter table tablename import tablespace;
2.2.7 將恢復后的新實例作為業(yè)務庫對外提供服務
2.3 MySQL進程不存在,原ibdata被刪除
2.3.1 在新實例創(chuàng)建表
如果可以獲取到表建表語句,在新實例先創(chuàng)建所有的表,然后使?可傳輸表空間?法恢復表數據
可傳輸表空間請參考:2.2 MySQL進程不存在,原ibdata保留 ?式恢復
2.3.2 沒有建表語句
如果沒有建表語句,但表frm?件保存;可以使??具解析frm獲取建表語句
從frm?件獲取建表語句請參考:3.4 從frm?件獲取建表語句
2.3.3 將建表語句導?新實例
將使?dbsake獲得的建表sql導?新實例,然后使?可傳輸表空間恢復表數據
3 附錄
3.1 ibdata 不同mv?式驗證
3.1.1 mv到其他分區(qū)
– 和直接刪除相同,因為每個分區(qū)都是有??的inode區(qū),mv到其他分區(qū)相當于從當前分區(qū)刪除該?件
3.2 innodb_force_recovery參數說明
當數據庫崩潰時,可以嘗試使? SELECT … INTO OUTFILE 來導出數據,通常情況下,?這種?式導出來的數據是完整的。對于導不出來的
表,可以加上 order by PK 來導出部分數據。
崩潰?較嚴重時,這種?式可能因后臺 innodb 相應線程中斷?失效,甚? innodb 會回滾數據來恢復崩潰。這種情況下,則可以在 [mysqld] 下
設置 innodb_force_recovery 來強制啟動 innodb,同時避免后臺操作,以?便導出數據。
該參數默認為 0,范圍 1~6,每?級的功能不?樣,較?的值包含較?值的功能。
僅在緊急情況下將 innodb_force_recovery 設置為?于 0 的值,以便可以啟動 InnoDB 并轉儲表。在此之前,請確保有全量備份。
如果能夠以 3 或者以下的值轉儲數據,則數據是相對安全的,只有損壞的單個?上的數據會丟失。對于?于 3 的數據轉儲,數據則就危險了。
做為?種安全措施,對于 0< x <=3,innodb 禁? insert update delete(即此時可能會導致復制相關信息出錯,因為?些復制信息是放在表中
的),但可以 drop create;對于 3< x <=6,innodb 則處于只讀狀態(tài),在 5.7.17 及之前,此時也可以 drop,?于該版本后則不能。之所以有
drop 權限,可能是因為某張表損壞?導致的崩潰,刪除后就好了。
設置為 3/4 后,重啟依舊費時,此時有極?的可能是在處理 undo log、redo log,時間應該耗在這了。
?于等于 4 的值可能會永久地破壞數據?件,所以嘗試使?該參數來啟動數據庫時,盡可能的從 1 開始逐漸往上升。
如果明確是哪張表引起的崩潰,則可以直接刪除再啟動。如果是因批量操作但回滾?引起的,則可以設置為 3 及以上,禁?事務的回滾,然后
再刪除相應的表。
1. 忽略檢測到的損壞?,嘗試使? select 去導出數據。
2. 禁? master purge 線程運?。?如在 purge 期間可能導致崩潰,則會禁? purge 線程。
3. 在崩潰恢復時禁?事務的回滾。
如果 change buffer 可能導致崩潰,則禁? change buffer 功能;
該值可能會導致數據?件被破壞;
使?該值后,最好重建所有的?級索引。
設置 innodb 為只讀。
啟動的時候忽略 undo log,這必然會導致 innodb 將未 commit 的事物視為已 commit。
該值可能會導致數據?件被破壞;
設置 innodb 為只讀。
不執(zhí)?與恢復相關的 redo log 的前滾。
該值可能會導致數據?件被破壞;
使數據庫??處于過時狀態(tài),可能會導致其它未損壞的?也連帶著損壞。此時多表關聯查詢也可能?不了。
設置 innodb 為只讀。
3.3 拷?句柄?件恢復步驟
3.4 從frm?件獲取建表語句
3.4.1 dbsake使?說明
[root@centos-linux dbsake]# ./dbsake –help
Usage: dbsake [options]
Options:
-d, –debug
-q, –quiet
-V, –version Show the version and exit.
-?, –help Show this message and exit.
Commands:
decode-tablename Decode a MySQL ?lename.
encode-tablename Encode a MySQL table identi?er.
?ncore Report cached pages for a ?le.
frmdump Dump schema from MySQL frm ?les.
help Show help for a command.
sandbox Create a sandboxed MySQL instance.
sieve Filter and transform mysqldump output.
uncache Uncache ?le(s) from the OS page cache.
unpack Unpack a MySQL backup archive.
upgrade-mycnf Upgrade a MySQL option ?le
3.4.1 dbsake 測試過程
## 下載dbsake?具
[root@centos-linux dbsake]# curl -s get.dbsake.net > dbsake
[root@centos-linux dbsake]# ll
total 436
-rw-r--r--. 1 root root 445959 May 29 10:27 dbsake
[root@centos-linux dbsake]# ll
total 436
-rw-r--r--. 1 root root 445959 May 29 10:27 dbsake
## 添加權限
[root@centos-linux dbsake]# chmod u+x dbsake
You have new mail in /var/spool/mail/root
[root@centos-linux dbsake]# ll
total 436
-rwxr--r--. 1 root root 445959 May 29 10:27 dbsake
## 解析frm?件
[root@centos-linux dbsake]# ./dbsake frmdump /data/mysql/data/3301/test/sbtest5.frm > sbtest5.sql
[root@centos-linux dbsake]# ll
total 440
-rwxr--r--. 1 root root 445959 May 29 10:27 dbsake
-rw-r--r--. 1 root root 386 May 29 10:28 sbtest5.sql
## 查看解析后的建表語句
[root@centos-linux dbsake]# cat sbtest5.sql
--
-- Table structure for table `sbtest5`
-- Created with MySQL Version 5.7.27
--
CREATE TABLE `sbtest5` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
文檔被以下合輯收錄
評論