一個MySQL資料庫服務不能正常啟動的故障處理過程

NO IMAGE

今天上午進行了一些my.cnf配置引數的修改,修改後,重啟資料庫,結果報錯:
# service mysqld start
Starting MySQL…The server quit without updating PID file (/data/mysql/v02c09.idc.huobi.com.pid).[FAILED]

檢視錯誤日誌檔案:
# tail -50 errors.log 

140823 12:03:16 mysqld_safe mysqld from pid file /data/mysql/v02c09.idc.huobi.com.pid ended
140823 12:06:16 mysqld_safe Starting mysqld daemon with databases from /data/mysql
140823 12:06:16 [Note] Plugin ‘FEDERATED’ is disabled.
140823 12:06:16 InnoDB: The InnoDB memory heap is disabled
140823 12:06:16 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140823 12:06:16 InnoDB: Compressed tables use zlib 1.2.3
140823 12:06:16 InnoDB: Using Linux native AIO
140823 12:06:16 InnoDB: Initializing buffer pool, size = 12.0G
140823 12:06:18 InnoDB: Completed initialization of buffer pool
140823 12:06:18 InnoDB: Error: combined size of log files must be < 4 GB
140823 12:06:18 [ERROR] Plugin ‘InnoDB’ init function returned error.
140823 12:06:18 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
140823 12:06:18 [ERROR] Unknown/unsupported storage engine: InnoDB
140823 12:06:18 [ERROR] Aborting

140823 12:06:18 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

140823 12:06:18 mysqld_safe mysqld from pid file /data/mysql/v02c09.idc.huobi.com.pid ended

通過查詢錯誤日誌中“[ERROR] Plugin ‘InnoDB’ init function returned error.”一部分,在網路上找到一個解決方案:
只要刪除MySQL目錄下的ib_logfile0和ib_logfile1兩個檔案,就可以解決問題了。

於是按照這個步驟刪除檔案:
# pwd
/data/mysql

# ls -l ib*
-rw-rw—- 1 mysql mysql  136314880 Aug 23 12:03 ibdata1
-rw-rw—- 1 mysql mysql 2097152000 Aug 23 12:03 ib_logfile0
-rw-rw—- 1 mysql mysql  134217728 Aug 22 12:11 ib_logfile0.bak
-rw-rw—- 1 mysql mysql 2097152000 Aug 22 12:12 ib_logfile1
-rw-rw—- 1 mysql mysql  134217728 Aug 22 12:01 ib_logfile1.bak

# rm ib_logfile0 ib_logfile1
rm: remove regular file `ib_logfile0′? yes
rm: remove regular file `ib_logfile1′? yes

# ls -l ib*
-rw-rw—- 1 mysql mysql 136314880 Aug 23 12:03 ibdata1
-rw-rw—- 1 mysql mysql 134217728 Aug 22 12:11 ib_logfile0.bak
-rw-rw—- 1 mysql mysql 134217728 Aug 22 12:01 ib_logfile1.bak

日誌檔案正常移除後,再次重啟資料庫:
# service mysqld start
Starting MySQL…The server quit without updating PID file (/data/mysql/v02c09.idc.huobi.com.pid).[FAILED]

仍然有報錯,檢視error.log日誌檔案,內容如下:

140823 12:06:18 mysqld_safe mysqld from pid file /data/mysql/v02c09.idc.huobi.com.pid ended
140823 12:19:19 mysqld_safe Starting mysqld daemon with databases from /data/mysql
140823 12:19:19 [Note] Plugin ‘FEDERATED’ is disabled.
140823 12:19:19 InnoDB: The InnoDB memory heap is disabled
140823 12:19:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140823 12:19:19 InnoDB: Compressed tables use zlib 1.2.3
140823 12:19:19 InnoDB: Using Linux native AIO
140823 12:19:19 InnoDB: Initializing buffer pool, size = 12.0G
140823 12:19:21 InnoDB: Completed initialization of buffer pool
140823 12:19:21 InnoDB: Error: combined size of log files must be < 4 GB
140823 12:19:21 [ERROR] Plugin ‘InnoDB’ init function returned error.
140823 12:19:21 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
140823 12:19:21 [ERROR] Unknown/unsupported storage engine: InnoDB
140823 12:19:21 [ERROR] Aborting

140823 12:19:21 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

140823 12:19:21 mysqld_safe mysqld from pid file /data/mysql/v02c09.idc.huobi.com.pid ended

檢視第一條報錯情況”InnoDB: Error: combined size of log files must be < 4 GB”,說的是MySQL的InnoDB日誌檔案總大小必須小於4GB,一般設定為1300G,組數為3,這樣總大小為3.9G;但該配置檔案中昨天剛設定為2000M,沒有設定組數;今天進行了這樣的設定:
innodb_file_per_table
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_buffer_pool_size = 12G
innodb_log_file_size = 2000M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 3
innodb_file_format = Barracuda

innodb_log的大小為2000M,組數為3,總大小超過4GB,所以會報錯。
將 innodb_log_file_size = 2000M 引數修改為:
innodb_log_file_size = 1300M

重新啟動MySQL資料庫服務:
[[email protected] mysql]# service mysqld start
Starting MySQL……………………………….[  OK  ]

至此,各方面確認正常。

通過這個故障處理,可以得到如下結論:
1.對於MySQL資料庫的innodb_log引數的相關修改,需要重啟MySQL服務,並將原來的ib_logfile*轉移成其他的名稱,才能在新服務啟動後生效;
2.對於出現類似“The server quit without updating PID file (/data/mysql/v02c09.idc.huobi.com.pid).”都是體現的msyql服務沒有啟動成功,一般MySQL不能啟動成功的原因都是my.cnf中的配置存在問題,才導致了服務不能正常啟動,此時需要立即查詢error.log來確認MySQL啟動過程和報錯資訊;
3.在error.log中可能會有多個報錯資訊,一般後面的錯誤就是結果,查詢錯誤原因,要看前面的,尤其要關注第一個出現的錯誤,如本次故障處理過程中“InnoDB: Error: combined size of log files must be < 4 GB”,雖然沒有[ERROR]標記,但是第一個出現故障的問題,也明確了錯誤的原因;
4.明確了錯誤的原因後,有時需要修改my.cnf檔案,或者需要重啟MySQL資料庫服務,這時都要先確認好狀態,然後再進行操作;
5.對於MySQL資料庫的常用引數,有些是有限制條件和引數範圍的,需要確認好後再設定,否則設定也會無效或報錯。