題 mysql如何修復用戶'root'拒絕訪問'@ localhost'


在我搞砸之前,當我登錄使用時 $ mysql -u root -p,並顯示數據庫:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| game_data          |
| test               |
+--------------------+

然後我嘗試創建一個新用戶,並註意到PRIVILEGES有問題。

所以我刪除了新用戶,我想我意外刪除了“root”和“Admin”。

然後我嘗試再次創建“root”,但在授予所有權限時獲取Access denied錯誤。

mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY 'password';
mysql> grant all privileges on *.* to 'root'@'localhost' identified by 'password' with grant option;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

如果我再次使用登錄MySQL $ mysql -u root -p,並顯示數據庫,

+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

所有其他數據庫都不見了。

我如何修復MySQL?

我找不到數據庫'mysql',無法創建數據庫,創建用戶,我嘗試做的任何事都會收到錯誤。

錯誤1045(28000):用戶'root'@'localhost'拒絕訪問(使用密碼:YES)。

我應該使用MacPorts重新安裝MySQL嗎?如果重新安裝,我將丟失數據庫 game_data, 對?


108
2018-06-03 07:32


起源


嘗試使用'root'@'127.0.0.1'訪問服務器,這與'root'@'localhost'不同。然後發出命令以創建用戶'root'@'localhost'並授予其所有權限。 - Gimmy
另見 如何讓MySQL授權root用戶? 在超級用戶。它試圖避免重置密碼。 - jww
我之間只有一個空間 -p 和密碼。我知道它很傻但可能會幫助別人。 - Vinay Wadhwa


答案:


請按照以下步驟操作。

  1. 用。啟動MySQL服務器實例或守護進程 --skip-grant-tables 選項(安全設置)。

    $ mysqld --skip-grant-tables
    
  2. 執行這些語句。

    $ mysql -u root mysql
    $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root';
    $mysql> FLUSH PRIVILEGES;
    

如果您面對上述未知字段密碼錯誤,請使用:

update user set authentication_string=password('my_password') where user='root';
  1. 最後,重新啟動實例/守護進程而不使用 --skip-grant-tables 選項。

    $ /etc/init.d/mysql restart
    

您現在應該可以使用新密碼進行連接。

$ mysql -u root -p

輸入密碼: my_password

修復MySQL“無法鎖定ibdata1”錯誤

sudo mv /usr/local/mysql/data/ibdata1 /usr/local/mysql/data/ibdata1.bak
sudo mv /usr/local/mysql/data/ib_logfile0 /usr/local/mysql/data/ib_logfile0.bak
sudo mv /usr/local/mysql/data/ib_logfile1 /usr/local/mysql/data/ib_logfile1.bak
sudo cp -a /usr/local/mysql/data/ibdata1.bak /usr/local/mysql/data/ibdata1
sudo cp -a /usr/local/mysql/data/ib_logfile0.bak /usr/local/mysql/data/ib_logfile0
sudo cp -a /usr/local/mysql/data/ib_logfile1.bak /usr/local/mysql/data/ib_logfile1
sudo /etc/init.d/mysql restart

116
2018-06-03 07:35



“$ mysqld --skip-grant-tables”和“InnoDB:無法鎖定./ibdata1,錯誤:35 InnoDB:檢查您是否還有另一個mysqld進程InnoDB:使用相同的InnoDB數據或日誌文件。”?
看我上面的更新 - Yogus
我發現在發出第一個命令之前停止mysql會停止發生鎖定錯誤 - 服務mysql停止 - Frank Tzanabetis
顯然'密碼'列不再存在......它已被'authentication_string'取代。所以密碼更新字符串現在是:UPDATE用戶SET authentication_string = PASSWORD('my_password')其中USER ='root'; - jdmcnair
謝謝!而現在我很好奇:可能是什麼導致了這個?在Windows上? - Ilia Andrienko


以上都不是對我有幫助的。我發現我需要清除插件方法。在5.6中,我可以這樣做:

sudo mysql -u root
use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;

在5.7中,我發現我需要:

sudo mysql -u root
use mysql;
[mysql] update user set plugin='mysql_native_password' where User='root';
[mysql] flush privileges;

根據文檔,將插件設置為空字符串,它應該有效地默認為mysql_native_password,但可能會被空密碼哈希混淆。有關更多細微差別,您可以在此處閱讀文檔: https://dev.mysql.com/doc/refman/5.7/en/native-authentication-plugin.html 


43
2017-09-28 15:01



這解決了我。當我查看mysql守護程序的狀態時,我看到了這個警告:“[警告]'用戶'條目'root @ localhost'同時指定了密碼和身份驗證插件。密碼將被忽略。”將插件設置為''允許我再次登錄。謝謝@Mario Flores - SpacePope
我的插件是空白的。有趣的是它自動變為空白。請注意,我正在談論在我的個人計算機上使用dist-upgrades的安裝,這可能是root用戶破壞的原因。 - Friend of Kim
set plugin='mysql_native_password' 為我做了,謝謝!以供參考: dev.mysql.com/doc/refman/5.7/en/... - Koen.
為什麼這對於進入2018年的全面封裝安裝仍然是一個問題? - mckenzm
ubuntu 18,它有效 - Rahal Kanishka


還要確保表中需要的記錄 user 空了 plugin 字段(例如,可以 "unix_socket")。

從版本5.5.7開始,mysql具有各種auth插件支持 https://dev.mysql.com/doc/refman/5.6/en/authentication-plugins.html

所以,如果你有非空 plugin 字段然後密碼將被忽略,並在mysql錯誤日誌會有警告(對我而言) /var/log/mysql/error.log):

[Warning] 'user' entry 'root@localhost' has both a password and an authentication plugin specified. The password will be ignored.


8
2018-04-28 21:27



對於運行這個的其他人來說,當運行mysql server 5.7.15時,如果你不提供插件,這實際上會鎖定你的用戶。可能你正在尋找的是一個 plugin 的 mysql_native_password。 - Jonathan Cantrell


grep 'temporary password' /var/log/mysqld.log
Sort date (newest date)

你可能會看到這樣的東西;

[root@SERVER ~]# grep 'temporary password' /var/log/mysqld.log
2016-01-16T18:07:29.688164Z 1 [Note] A temporary password is generated for root@localhost: O,k5.marHfFu
2016-01-22T13:14:17.974391Z 1 [Note] A temporary password is generated for root@localhost: b5nvIu!jh6ql
2016-01-22T15:35:48.496812Z 1 [Note] A temporary password is generated for root@localhost: (B*=T!uWJ7ws
2016-01-22T15:52:21.088610Z 1 [Note] A temporary password is generated for root@localhost: %tJXK7sytMJV
2016-01-22T16:24:41.384205Z 1 [Note] A temporary password is generated for root@localhost: lslQDvgwr3/S
2016-01-22T22:11:24.772275Z 1 [Note] A temporary password is generated for root@localhost: S4u+J,Rce_0t
[root@SERVER ~]# mysql_secure_installation

保護MySQL服務器部署。

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password:

如果你看到它說

... Failed! Error: Your password does not satisfy the current policy requirements
That means your password needs to have a character such as ! . # - etc...
mix characters well, upper case, lower case, ! . , # etc...

New password: 

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : Y

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done! 
[root@SERVER ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.10 MySQL Community Server (GPL)

觀看最後10分鐘 這個視頻,它教你如何做到這一點。


7
2018-01-22 23:08



雖然這可能會回答這個問題,但您應該對鏈接的內容進行更詳細的描述,並解釋它與問題的關係。這有助於確保在刪除或脫機鏈接頁面時此答案仍然有用。有關更多信息,請參閱 這個Meta Stack Exchange帖子。 - bwDraco
這是我看到的用例,我在AWS中使用Ansible在RHEL7上安裝mysql,並且在重新配置和重新啟動數據庫之後我得到了相同的行為。你知道為什麼會發生這種情況,阻止Ansible訪問本地mysqld並進行配置。這僅用於測試目的。 - einarc
顯然,這取決於你如何初始化服務器,請看這裡的第4步: dev.mysql.com/doc/refman/5.7/en/... - einarc


試試吧:

mysql --no-defaults --force --user=root --host=localhost --database=mysql 
UPDATE user SET Password=PASSWORD('NEWPASSWORD') where USER='root';
FLUSH PRIVILEGES;

3
2018-02-21 17:05



你能描述一下嗎? --no-defaults --force 開關呢?否則,它是以上接受的答案的VLQ副本。 - Canadian Luke
@CanadianLuke沒有默認值會跳過它所包含的任何配置文件,即使任何已發出的SQL命令失敗,強制繼續操作。如有疑問,請檢查 手冊 - deucalion
需要將其編輯到答案中 - Canadian Luke
我明白了 ERROR 1054 (42S22): Unknown column 'Password' in 'field list' - Jonathan
@Jonathan領導看: stackoverflow.com/questions/30692812/... - Gustavo Duarte


在我的情況下,我有一個數據庫損壞,在Debian上重啟mysql後,root登錄沒有密碼。 解決方案是這樣的:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

其他一些答案也提到了native_password插件,但這就是你如何做到這一點而不需要復雜的擺弄。 這就是改變它的方式。


0
2017-11-10 20:13