這是一個 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
//Featured Free Photos,連結:Pixabay