對於MySQL遠端連線中出現的一個問題總結

余月七發表於2021-09-03
MySql

2021年9月3日更新補充

（真的心累，本來是個小問題，但是網上帖子都基本差不多，基本都是相同的操作，導致搜了半個多小時才解決）
一、首先為什麼要重新發一次呢，因為我發現上次寫的這個記錄是不完善甚至是錯的，因為我忽略了一個操作，其次在今天再次去解決這個問題的時候，發現了一個比較正確的解決方法。下面我基本以上一次的內容為基礎，因為上一次的內容僅僅完成了一部分的操作，並未解決真正的問題。也慶幸上次寫的那篇隨筆沒有很多人去看，不然和網上的帖子一樣會誤導很多人，而且也成為了一篇沒有用的帖子。

二、我會在每一個步驟上打上補充，避免誤操作。而且希望一步步往下看，因為最後還會有一個根據這些網上普遍的操作總結出來的問題。

以下是上次文章內容，文章標題：記錄"ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES/NO) ”錯誤

前言

前段時間MySQL莫名的掛了，導致我資料都被清了，資料庫也連不上；具體原因我也沒去多看，今天順便去搜了一下解決方法，沒想到重置了一下密碼竟然好了，但是資料還是被清了，有時間再去看看具體原因吧。
下面是我偶然搜到的一篇文章還是第一次這麼順利的解決了。

錯誤描述

首先這個問題我是在我的雲伺服器上遇見的，以前其實也遇到過，但經常是當時解決了，並未去記錄，時間一長一些操作就忘了，但個人記得這個錯誤不管在Linux和Windows上都出現過，但是基本上相似，此篇記錄主要針對Linux上的錯誤，在其中我也會提一些Windows上出現的問題。

解決方式

一、先停止mysql

/etc/init.d/mysqld stop

(如果已經檢視了MySQL服務程式的話可以直接 kill -9 [PID] 殺死程式！）

二、執行以下命令，可以跳過登入驗證

補充點

（補充一種方式，還是按照這個新方式來吧，也可以按照我上次寫的方法來都行，就是第二種方式）：

可能有的人對下面的命令不理解，所以也可以採用這種方式

vim /etc/my.cnf                #輸入這條命令去編輯這個檔案

然後在檔案中新增下面這段語句

[mysqld]
skip-grant-tables

要注意的點來了，就是所有操作完畢後，記得把這句話給註釋了（儘量是刪除的時候給“skip-grant-tables”這句話之前新增一個“#”最為方便，不然每次都得重新輸一遍這句話），不然你以為成功了，而我就是最傻的那個，原本以為成功了，今天才發現是這句話沒有註釋，直接讓mysql跳過了驗證，成功登入了，這種方式是極其不正確的！！！

第二種方式：

mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

注意：如果在window上的話，我看很多文章去說找到“my.ini”檔案，但對第一次使用mysql的人來說是大可能找不見的，如果可以的話可以去網上搜一下這個檔案在哪。因為我當時是使用的解壓縮的方式進行的安裝，直接在資料夾中新建的這個檔案，然後在當中去編輯以下內容,可以參考一下我的這篇記錄：MySQL鞏固學習記錄（一）
這是window上的安裝配置檔案內容：

[mysqld]
basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables #跳過登入驗證

三、開始登入資料庫(以root身份)

mysql -u root -p

四、更新root使用者密碼

MySQL5.7之前

UPDATE user SET Password=PASSWORD('你的密碼') where USER='root';

MySQL5.7之後（包括5.7）

UPDATE user SET authentication_string=PASSWORD('你的密碼') where USER='root';

五、重新整理許可權

FLUSH PRIVILEGES;

六、退出並重啟

mysql> exit

mysql> /etc/init.d/mysqld restart

七、重新登入

mysql -uroot -p Enter password: <輸入你剛才新設的密碼>

八、結束

如果以上步驟如果不成功，多試一次，如果不行的話再換別的辦法；至於window的話可能上面就跳過登入驗證那塊有點小問題，其他的話，我感覺window上解決問題比較容易一點，可以去部落格園中搜搜看看，或者騰訊雲社群搜搜看看，感覺這兩個地方的解決方法稍微靠譜點。
本文參考於：https://cloud.tencent.com/developer/article/1188636

以上就是上次原文，重點來了

問題一：

你以為這就結束了，不不不，通常第一遍是忘了註釋掉檔案中的“skip-grant-tables”這句話，誤以為成功了。

問題二：

當再次把這句話註釋掉時，問題出來了，還是會和以前一樣，出現“ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES/NO)”錯誤，當然在這裡我也不知道和我情況一樣的兄弟們有多少，所以下面我說說我的情況：問題再次出現，我去網上找解決方法，偶然看到有人說應該是mysql資料庫中user表中缺資料著，也就是許可權不夠。所以我按著這條路下去，發現問題解決了。

具體解決方式如下：

一、去檢視mysql資料庫中user表

這是我安裝的新的本地資料庫中user的原始資訊列之一也就是root使用者

那遠端伺服器上的怎麼看？
第一種方法：把“skip-grant-tables”這句跳過許可權驗證在上面我提到的那個檔案中加上，然後使用sql語句去檢視。
第二種方法：依舊把上面這個語句加上，只不過這次我們在遠端去看，也就是像Navicat這種圖形化介面去看，看看到底自己表中資訊是否缺失root使用者資訊。

二、把使用者資訊新增上

這裡我直接使用的是我本地新裝資料庫中user表中的資料，root使用者密碼暫且設定為123456
sql檔案直接放這了：https://files.cnblogs.com/files/yuyueq/user.zip
下面是原始檔內容：

/*
 Navicat Premium Data Transfer

 Source Server         : wenxin.du
 Source Server Type    : MySQL
 Source Server Version : 50735
 Source Host           : localhost:3306
 Source Schema         : mysql

 Target Server Type    : MySQL
 Target Server Version : 50735
 File Encoding         : 65001

 Date: 03/09/2021 14:20:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `Host` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `max_updates` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `max_connections` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `max_user_connections` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `plugin` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
  `authentication_string` text CHARACTER SET utf8 COLLATE utf8_bin NULL,
  `password_expired` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) UNSIGNED NULL DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`, `User`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = 'Users and global privileges' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('localhost', 'root', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9', 'N', '2021-08-30 11:57:05', NULL, 'N');
INSERT INTO `user` VALUES ('localhost', 'mysql.session', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE', 'N', '2021-08-30 11:57:05', NULL, 'Y');
INSERT INTO `user` VALUES ('localhost', 'mysql.sys', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE', 'N', '2021-08-30 11:57:05', NULL, 'Y');

SET FOREIGN_KEY_CHECKS = 1;

三、解決最終問題

執行之後還是會發現一個問題，就是當把跳過許可權驗證語句註釋掉之後，雖然遠端伺服器通過ssh連線操作時，mysql可以連線上不再出現那個問題，但是當遠端連線伺服器上的mysql時問題出現了，它沒有遠端連線的許可權，這是因為我們剛才的檔案中也是設定了。所以解決方法我找到了一篇博文：

解決Navicat 出錯:1130-host . is not allowed to connect to this MySql server,MySQL

  1. 改表法。
    可能是你的帳號不允許從遠端登陸，只能在localhost。這個時候只要在localhost的那臺電腦，登入mysql後，更改 "mysql" 資料庫裡的 "user" 表裡的 "host" 項，從"localhost"改稱"%"
    image
    mysql -u root -p
    mysql>use mysql;
    mysql>update user set host = '%' where user = 'root';
    mysql>select host, user from user;
    注：個人覺得不太適用！
  1. 授權法。

i.你想myuser使用mypassword從任何主機連線到mysql伺服器的話。
GRANT ALL PRIVILEGES ON . TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

ii.如果你想允許使用者myuser從ip為192.168.1.6的主機連線到mysql伺服器，並使用mypassword作為密碼
GRANT ALL PRIVILEGES ON . TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

iii.如果你想允許使用者myuser從ip為192.168.1.6的主機連線到mysql伺服器的dk資料庫，並使用mypassword作為密碼
GRANT ALL PRIVILEGES ON dk.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

我用的 i 方法,最後執行一個語句 mysql>FLUSH RIVILEGES 使修改生效.就可以了
另外一種方法,不過我沒有親自試過的,在csdn.net上找的,可以看一下.
在安裝mysql的機器上執行：
1、d:/mysql/bin/>mysql -h localhost -u root //這樣應該可以進入MySQL伺服器
2、mysql>GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION //賦予任何主機訪問資料的許可權
3、mysql>FLUSH PRIVILEGES //修改生效
4、mysql>EXIT //退出MySQL伺服器
這樣就可以在其它任何的主機上以root身份登入啦！

四、方法總結

最簡單的錯誤就是，資料都在，然後單純連線不上，通過修改密碼可以連線。其次就是和我一樣的錯誤，需要先跳過驗證，然後重新修改表中缺失資料，然後給遠端資料庫授權可以遠端連線，最後註釋掉那跳語句，然後以後就可以通過正常的方式連線資料庫了。

最後

篇幅也不算長，都是些個人話語，其實整體步驟很簡單，只不過我想說的是，粗心大意有時候就是問題的所在，因為有時候原本一個很簡單的問題，由於自己的疏忽導致遲遲未解決。還有就是CSDN中的解決錯誤的文章真的太水了，大多都是搬運，不加以修改，都是一模一樣的，很難找到有效資訊，所以還是建議像Stack Overflow、思否、部落格園、這些論壇上去搜尋問題，相對來說稍微比CSDN靠譜點。

相關文章