修復錯誤 MySQL: Access denied for user ‘debian-sys-maint’@’localhost’

fix-mysql-access-denied/image/01

這是我之前在轉移網站時發生的問題,當我把備份的資料庫覆蓋回去要重啟時,就跳出了底下這些錯誤訊息:

1
2
3
4
5
pi@octopi:~$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld fail
* Starting MySQL database server mysqld [ OK ]
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

所以,是什麼原因造成了這個錯誤?

安裝MySQL時,安裝程序會在系統裡新增一個名為 debian-sys-maint 的維護用帳戶

由於這個帳戶的密碼是安裝MySQL時隨機生成的,當MySQL重裝或是自行更動時,就會發生密碼對不起來的情況

(順便一提,這 debian-sys-maint 的權限和 root 一樣,但密碼沒有經過加密,所以別隨便就把它丟到網路上)

為了解決這問題,必須先找到debian-sys-maint這個帳戶最新的密碼,這個密碼就存在 debian.cnf 文件裡:

1
sudo nano /etc/mysql/debian.cnf

開啟檔案後,橘色的部分就是該帳戶的密碼,把它複製起來,等下會用到:

1
2
3
4
5
6
7
8
9
10
11
12
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = <span style="color: #ff6600;">[debian-sys-maint password]
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = <span style="color: #ff6600;">[debian-sys-maint password]
socket = /var/run/mysqld/mysqld.sock
basedir = /usr

接著把剛剛複製的密碼更新至系統中:

1
2
mysql -u root -p [mysql root password]
mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '[debian-sys-maint password]'; mysql> exit

最後再重新啟動 MySQL:

1
2
3
4
pi@octopi:~$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.

參考文章

MySQL: “Access denied for user ‘debian-sys-maint’@’localhost’”-RU-FI-OOOOOH!