iT邦幫忙

0

MySQL 5.5 Rescue

  • 分享至 

  • xImage
  •  

Durpal 7 網站死了,吐出錯誤訊息是
"PDOException: in lock_may_be_available() "

實情是,該網站因為硬碟毀損,連帶導致資料庫毀損
因此drupal下面連接的mysql無法起動
當然最好的方法就是拯救資料庫,理論上就可以把一切恢復正常
不過最好的辦法不見得最好做,就像64事件最好的辦法就是進行民主改革
但是李鵬鄧小平就是要硬幹,大概中國人就是會殺中國人,而且額度還沒滿

MySQL有一種神奇的特性,就像政客一樣,頭與屁股可以互換然後還是講屁話
這次要救的資料庫名稱是dtcewebdb
https://ithelp.ithome.com.tw/upload/images/20191115/200944032ooCyfua6g.png

至於為什麼為是這路徑,全憑my.ini一句話
https://ithelp.ithome.com.tw/upload/images/20191115/20094403pdDXSFdCoj.png

很奇怪的是因為Windows系統一些權限關係,所以放進這裡可以省掉一些搶救的時間
然後啟動MYSQL服務,服務就死了。嗯,這完美的複製了問題,reproduce是偵錯重要的步驟
現在我確定我複製了有問題的資料庫,那就可以進手術房,開心囉~

首先我嘗試把my.ini加點料
https://ithelp.ithome.com.tw/upload/images/20191115/200944036o0px0skV6.png
然後用MySQL WorkBench來處理一下,可以看到Server Log是

Page checksum 811565033, prior-to-4.0.14-form checksum 2111588714
InnoDB: stored checksum 4283310164, prior-to-4.0.14-form stored checksum 542585683
InnoDB: Page lsn 1 1901289074, low 4 bytes of lsn at page end 1447244064
InnoDB: Page number (if stored to page already) 1026,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 1026.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
Waiting for the background threads to start
5.5.62 started; log sequence number 6197058060
!!! innodb_force_recovery is set to 3 !!!
Server hostname (bind-address): '0.0.0.0'; port: 3306
- '0.0.0.0' resolves to '0.0.0.0';
Server socket created on IP: '0.0.0.0'.
Event Scheduler: Loaded 0 events
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: '5.5.62'  socket: ''  port: 3306  MySQL Community Server (GPL)
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

資料庫可以開起來了,但是Drupal還是無法運作
這是廢話,上面都說"do not allow database modifications by the user"
那該怎麼辦?先檢查那個資料表的問題

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqlcheck.exe -u root -p --all-databases
Enter password: ********
dtcewebdb.actions                                  OK
dtcewebdb.book                                     OK
dtcewebdb.cache                                    OK
---節略--
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.user                                         OK

C:\Program Files\MySQL\MySQL Server 5.5\bin>START "Starting PHP FastCGI" php-cgi.exe -b 127.0.0.1:9000

居然沒有資料表損壞,算是不幸中的大幸,趕快把資料庫export出去
https://ithelp.ithome.com.tw/upload/images/20191115/20094403QwVjXiB1YD.png
真是幸運,又順利的匯出當備份

那再來我的作法就是把舊資料庫清掉,註解掉原來my.ini裡面那兩行,重啟MYSQL後餵回去
https://ithelp.ithome.com.tw/upload/images/20191115/20094403DVrxoIdvXk.png
神奇的事情發生了,Drupal網站就復活了。

因此這樣推論剛剛已經把InnoDB修好了,因此把原來複製的那個data資料夾內檔案蓋回去原伺服器上
不過要確定Drupal沒有再踹MYSQL的狀態
是的,你沒看錯,我的作法真的是剪下貼上...然後進入服務,把MYSQL服務啟動起來
結束這回合的搶救。

PS. 我女兒喜歡粉紅,但因為我個人不喜歡小粉紅,所以往後撰寫文章一定故意會扯特定族群理智線的字句。

--
REF:

MySQL – Corrupted InnoDB tables recovery – Step by step guide


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言