VPS 不正常重啟，造成 MariaDB 啟動失敗

Photo by Everyday basics on Unsplash

這是一個 VPS 主機上 MariaDB 不正常關閉， VPS 重啟後資料庫 MariaDB server 啟動失敗之復原程序紀錄。

原因是一台 UpCloud VPS 主機更換了新加坡機房的節點，網路延遲突然拉高，ping 值到 175ms 左右，這比在美西機房的網路延遲還高，所以與 UpCloud 的客服聯絡，請他們檢查問題，過程中需要重啟 VPS 主機數次，最後一次重啟主機時，MariaDB server 就掛了。

處理網路延遲

清除 DNS 快取：

# ip -s -s neigh flush all
$ sudo /etc/init.d/dns-clean start

另一個方法，通過以下命令將其用於刷新快取：

$ sudo systemd-resolve --flush-caches

查看統計信息：

$ sudo systemd-resolve --statistics

最後要重啟一次主機。

MariaDB server 啟動失敗

查看 log 文件：

Nov 25 04:58:13 ubuntu-2cpu-4gb-sg-sin1 mysqld[5033]: 2019-11-25  4:58:13 139980579280192 [Note] InnoDB: Starting recovery for XA transactions...
Nov 25 04:58:13 ubuntu-2cpu-4gb-sg-sin1 mysqld[5033]: 2019-11-25  4:58:13 139980579280192 [Note] InnoDB: Transaction 10176096 in prepared state after recovery
Nov 25 04:58:13 ubuntu-2cpu-4gb-sg-sin1 mysqld[5033]: 2019-11-25  4:58:13 139980579280192 [Note] InnoDB: Transaction contains changes to 1 rows
Nov 25 04:58:13 ubuntu-2cpu-4gb-sg-sin1 mysqld[5033]: 2019-11-25  4:58:13 139980579280192 [Note] InnoDB: 1 transactions in prepared state after recovery
Nov 25 04:58:13 ubuntu-2cpu-4gb-sg-sin1 mysqld[5033]: 2019-11-25  4:58:13 139980579280192 [Note] Found 1 prepared transaction(s) in InnoDB
Nov 25 04:58:13 ubuntu-2cpu-4gb-sg-sin1 mysqld[5033]: 2019-11-25  4:58:13 139980579280192 [ERROR] Found 1 prepared transactions! It means that mysqld was not shut down properly last time and critical recovery information (last binlog or tc.log file) was manually deleted after a crash. You have to start mysqld with --tc-heuristic-recover switch to commit or rollback pending transactions.
Nov 25 04:58:13 ubuntu-2cpu-4gb-sg-sin1 mysqld[5033]: 2019-11-25  4:58:13 139980579280192 [ERROR] Aborting
Nov 25 04:58:15 ubuntu-2cpu-4gb-sg-sin1 systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAIL
Nov 25 04:58:15 ubuntu-2cpu-4gb-sg-sin1 systemd[1]: mariadb.service: Failed with result 'exit-code'.
Nov 25 04:58:15 ubuntu-2cpu-4gb-sg-sin1 systemd[1]: Failed to start MariaDB 10.2.29 database server.

這一長串的意思，說明 MariaDB 沒有正常的關閉，造成資料庫崩潰，需要用 –tc-heuristic-recover 方式，提交回滾訊息後，再啟動 MariaDB server。

處理方法：

$ mysqld --tc-heuristic-recover=ROLLBACK

啟動 MariaDB server

$ service mysql start