MySQL force upgrade

奇怪我的 MySQL 很容易當掉,然後我的 http://Benjr.tw 就會直接在網頁上顯示資料庫連線錯誤,檢查 /var/log/mysql 裡面的 log 會顯示 table 錯誤.

root@benjr:~# cat /var/log/mysql.log
root@benjr:~# cat /var/log/mysql.err
root@benjr:~# cat /var/log//mysql/error.log
160323  5:24:20 [ERROR] /usr/sbin/mysqld: Table './benjr/wp_commentmeta' is marked as crashed and should be repaired
160323  5:24:20 [Warning] Checking table:   './benjr/wp_commentmeta'
160323  5:24:20 [ERROR] /usr/sbin/mysqld: Table './benjr/wp_comments' is marked as crashed and should be repaired
160323  5:24:20 [Warning] Checking table:   './benjr/wp_comments'
160323  5:24:20 [ERROR] /usr/sbin/mysqld: Table './benjr/wp_options' is marked as crashed and should be repaired
160323  5:24:20 [Warning] Checking table:   './benjr/wp_options'
160323  5:24:20 [ERROR] /usr/sbin/mysqld: Table './benjr/wp_postmeta' is marked as crashed and should be repaired
160323  5:24:20 [Warning] Checking table:   './benjr/wp_postmeta'
160323  5:24:20 [ERROR] /usr/sbin/mysqld: Table './benjr/wp_posts' is marked as crashed and should be repaired
160323  5:24:20 [Warning] Checking table:   './benjr/wp_posts'
160323  5:24:20 [ERROR] /usr/sbin/mysqld: Table './benjr/wp_usermeta' is marked as crashed and should be repaired
160323  5:24:20 [Warning] Checking table:   './benjr/wp_usermeta'
160323  5:24:20 [ERROR] /usr/sbin/mysqld: Table './radytobe/wp_options' is marked as crashed and should be repaired
160323  5:24:20 [Warning] Checking table:   './radytobe/wp_options'
160323  5:24:20 [ERROR] /usr/sbin/mysqld: Table './radytobe/wp_postmeta' is marked as crashed and should be repaired

用下面的指令就可以修復.

root@benjr:~# initctl stop mysqld
root@benjr:~# cd /var/lib/mysql/drupal
root@benjr:~# myisamchk –r *.MYI
root@benjr:~# initctl start mysqld 

整理完之後就可以,但過一段時間 MySQL 又會有錯誤產生.後來檢查到 /var/log/syslog 裡面有一段關於 MySQL 的錯誤訊息 “Upgrading MySQL tables if necessary.”.

root@benjr.tw:~# cat /var/log/syslog
Mar 23 05:24:19 benjr.tw /etc/mysql/debian-start[427]: Upgrading MySQL tables if necessary.
Mar 23 05:24:19 benjr.tw /etc/mysql/debian-start[430]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Mar 23 05:24:19 benjr.tw /etc/mysql/debian-start[430]: Looking for 'mysql' as: /usr/bin/mysql
Mar 23 05:24:19 benjr.tw /etc/mysql/debian-start[430]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Mar 23 05:24:19 benjr.tw /etc/mysql/debian-start[430]: This installation of MySQL is already upgraded to 5.5.47, use --force if you still need to run mysql_upgrade

似乎需要下下面的指令來更新 MySQL

root@benjr.tw:/var/log# mysql_upgrade -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
This installation of MySQL is already upgraded to 5.5.47, use --force if you still need to run mysql_upgrade

要加上 參數 force 才能使用.更新之後看起來一些怪問題都解決了,但 MYSQL 會不會再發生當掉的問題還需要在觀察一段時間.

root@benjr.tw:/var/log# mysql_upgrade --force -u root -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/var/run/mysqld/mysqld.sock'
benjr.wp_commentmeta                                OK
benjr.wp_comments                                   OK
benjr.wp_links                                      OK
benjr.wp_options                                    OK
benjr.wp_postmeta                                   OK
benjr.wp_posts                                      OK
benjr.wp_term_relationships                         OK
benjr.wp_term_taxonomy                              OK
benjr.wp_termmeta                                   OK
benjr.wp_terms                                      OK
benjr.wp_usermeta                                   OK
benjr.wp_users                                      OK
benjr.wp_wp_rp_tags                                 OK
benjr.wp_commentmeta                               OK
benjr.wp_comments                                  OK
benjr.wp_links                                     OK
benjr.wp_options                                   OK
benjr.wp_postmeta                                  OK
benjr.wp_posts                                     OK
benjr.wp_postviews_plus                            OK
benjr.wp_term_relationships                        OK
benjr.wp_term_taxonomy                             OK
benjr.wp_termmeta                                  OK
benjr.wp_terms                                     OK
benjr.wp_usermeta                                  OK
benjr.wp_users                                     OK
benjr.wp_wp_rp_tags                                OK
Running 'mysql_fix_privilege_tables'...
OK
root@benjr:~# initctl restart mysqld 

冏,上星期還是發生過一次 MySQL 整個當掉的問題!!