MySQL 5.5 Rescue

php mysql 資料管理 drupal

icekimo 2019-11-15 15:24:03 ‧ 1493 瀏覽

分享至

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

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

MySQL有一種神奇的特性，就像政客一樣，頭與屁股可以互換然後還是講屁話
這次要救的資料庫名稱是dtcewebdb

至於為什麼為是這路徑，全憑my.ini一句話

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

首先我嘗試把my.ini加點料

然後用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出去

真是幸運，又順利的匯出當備份