Wildsky's Blog

升級 Mysql Database 所遇到的那些坑

Posted on

因為一些原因，需要在 CentOS 6 上使用 mysql 5.7。看了一下才發現這台 server 上的 Mysql Database 是 5.1 版，找了一些方法裝完後，才發現是悲劇的開始⋯

這邊先來個 tl;dr

cd /tmp
wget http://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm
sudo yum localinstall mysql57-community-release-el6-9.noarch.rpm
sudo yum remove -y mysql*
sudo yum install -y mysql-community-server

sudo -i
mysqld_safe --skip-grant-tables &

# press enter
mysql -uroot
use mysql;
update user set authentication_string=password('PASSWORD_YOU_WANT') where user='root';
flush privileges;
quit

servie mysqld restart
mysql -uroot -p
# enter password

set global validate_password_length=3;
set global validate_password_policy=0;
set password = password('PASSWORD_YOU_WANT');

# here you go!

照著上面走應該就可以正常使用 mysql 了，如果還是不行，那我這篇文章應該就沒法幫 上忙了。

在 CentOS 6 上安裝 mysql 5.7

其實只要 google 一下就找得到安裝方法，如下

cd /tmp
wget http://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm # download package from official site
sudo yum localinstall mysql57-community-release-el6-9.noarch.rpm
sudo yum remove -y mysql* # remove all package about old version mysql
sudo yum install -y mysql-community-server

裝完後讓 service 重新啟動

sudo service mysqld restart

結果 stop 正常，start 就壞掉了⋯ 不死心地直接在 shell 下 mysql 試試看，結果就 給我跳這個訊息：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

⋯沒辦法，只好認命地捲起袖子來解決，應該也不太難吧？

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

CentOS 上 mysql 的 config 檔是放在 /etc/mmy.cnf，找一下就可以看到 log 是放在/var/log/mysqld.log 裡面，進去後就會看到這樣的錯誤訊息：

2018-09-23T10:03:45.472407Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-23T10:03:45.472465Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2018-09-23T10:03:45.472486Z 0 [ERROR] InnoDB: os_file_readdir_next_file() returned -1 in directory ./, crash recovery may have failed for some .ibd files!
2018-09-23T10:03:45.472661Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-23T10:03:45.472685Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2018-09-23T10:03:45.472700Z 0 [ERROR] InnoDB: os_file_readdir_next_file() returned -1 in directory ./, crash recovery may have failed for some .ibd files!
2018-09-23T10:03:45.472917Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-23T10:03:45.472942Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2018-09-23T10:03:45.472958Z 0 [ERROR] InnoDB: os_file_readdir_next_file() returned -1 in directory ./, crash recovery may have failed for some .ibd files!
2018-09-23T10:03:45.473241Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-23T10:03:45.473269Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2018-09-23T10:03:45.473285Z 0 [ERROR] InnoDB: os_file_readdir_next_file() returned -1 in directory ./, crash recovery may have failed for some .ibd files!
2018-09-23T10:03:45.473998Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-23T10:03:45.474230Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2018-09-23T10:03:45.474286Z 0 [ERROR] InnoDB: os_file_readdir_next_file() returned -1 in directory ./, crash recovery may have failed for some .ibd files!
2018-09-23T10:03:45.475060Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-23T10:03:45.477289Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2018-09-23T10:03:45.477317Z 0 [ERROR] InnoDB: os_file_readdir_next_file() returned -1 in directory ./, crash recovery may have failed for some .ibd files!
2018-09-23T10:03:45.477413Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-23T10:03:45.477434Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2018-09-23T10:03:45.477449Z 0 [ERROR] InnoDB: os_file_readdir_next_file() returned -1 in directory ./, crash recovery may have failed for some .ibd files!
2018-09-23T10:03:45.477530Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2018-09-23T10:03:45.477550Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2018-09-23T10:03:45.477592Z 0 [ERROR] InnoDB: os_file_readdir_next_file() returned -1 in directory ./, crash recovery may have failed for some .ibd files!
2018-09-23T10:03:45.477909Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2018-09-23T10:03:46.079143Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2018-09-23T10:03:46.079218Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-09-23T10:03:46.079232Z 0 [ERROR] Failed to initialize builtin plugins.
2018-09-23T10:03:46.079242Z 0 [ERROR] Aborting

2018-09-23T10:03:46.079294Z 0 [Note] Binlog end
2018-09-23T10:03:46.079394Z 0 [Note] Shutting down plugin 'MyISAM'
2018-09-23T10:03:46.079452Z 0 [Note] Shutting down plugin 'CSV'
2018-09-23T10:03:46.080034Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

查了一下，好像是權限問題，用了 chmod 之類的指令也依然無解。翻了一陣子終於發現 原來可以直接用 root 開看看！

sudo -i
service mysqld start

要求不存在的密碼

才終於跑起來了！不過我下 mysql -uroot 後他就跑來跟我要密碼，但我根本沒設定過 啊，怎麼可能會有密碼呢？只好繼續查要怎麼在忘記密碼的情況下重設密碼⋯⋯

mysqld_safe --skip-grant-tables &
# press enter to find bash back
mysql -uroot

發現用這個方法可以免密碼直接進去 mysql，然後輸入

use mysql;
update user set authentication_string=password('你要設的密碼') where user='root';
flush privileges;
quit
servie mysqld restart
mysql -uroot -p
# enter the password you just gave to it

「終於可以了吧！」看著終於進去的 mysql prompt 不禁有些感動⋯⋯ 結果輸入 show database; 又跳了錯誤訊息

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

好，這個錯誤訊息夠明顯，一樣繼續查，可以直接這樣（密碼和剛剛一樣也ＯＫ）

set password = password('你要設的密碼');

結果又出現

ERROR 1819 (HY000) Your password does not satisfy the current policy requirements

ERROR 1819 (HY000) Your password does not satisfy the current policy requirements

只好⋯⋯

set global validate_password_length=3;
set global validate_password_policy=0;

再重下一次

set password = password('你要設的密碼');

才終於改成功了！離開再重新進來、再用 show databases; 測試一次，就能正常使用 Mysql Database 了。

參考資料

更多我過去所踩的雷，可以見這裡： http://blog.wildsky.cc/tags/踩雷人生