MYSQL 各版本密碼相關操作

目錄導讀

• MYSQL 各版本密碼相關操作
• 一.MySQL修改密碼和遠程登錄
• 1.1 各版本修改密碼方法
• 8.0 以下版本修改密碼和允許遠程登陸
• 8.0 以上修改密碼和允許遠程登陸
• 二. 忘記MySQL的root密碼后如何登陸數據庫
• 方法一：使用--init-file選項
• 2.1 具體修改密碼的步驟
• 方法二：使用--skip-grant-tables選項
• 2.2 具體修改密碼的步驟
• 三.去掉密碼安全策略
• 四. 密碼含特殊符合

一.MySQL修改密碼和遠程登錄

1.1 各版本修改密碼方法

8.0 以下版本修改密碼和允許遠程登陸

? 5.5. 5.6. 5.7版本修改密碼和允許遠程登陸

-- 修改密碼
update mysql.user set password=password('test') where user='root';                -- 適用于5.5~5.6
update mysql.user set authentication_string=password('test') where user='root';   -- 適用于 >= 5.7 <8.0
set password for root@'%'=password('test');     -- 適用于 <= 5.7
set password=password('test');                  -- 修改當前用戶的密碼
flush privileges;

-- 創建新用戶，允許遠程登錄
grant all on *.* to root@'localhost' identified by 'test'  with grant option;
grant all on *.* to root@'%' identified by 'test'  with grant option;
flush privileges;
select user,host,grant_priv,super_priv,password from mysql.user;  -- 5.5~5.6
select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user; -- >= 5.7

8.0 以上修改密碼和允許遠程登陸

-- 修改密碼
alter user root@'localhost' identified with mysql_native_password by 'test';     -- 5.7也支持該命令
flush privileges;

-- 允許遠程登錄
grant all on *.* to root@'localhost' with grant option;
create user root@'%' identified with mysql_native_password by 'test';
grant all on *.* to root@'%' with grant option;
flush privileges;
select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;

注：mysql 8.0遠程連接，在參數文件的[mysqld]下添加：
default_authentication_plugin=mysql_native_password

-- 刪除用戶：
mysql> drop user yww@'localhost';
Query OK, 0 rows affected (0.01 sec)

測試5.7版本，以上修改密碼操作，主從同步中，slave端也會同步修改。

二. 忘記MySQL的root密碼后如何登陸數據庫

-- 在MySQL中，若密碼輸入錯誤，則會返回以下信息：
[root@mysql57 ~]#  mysql -uroot -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

? 在MySQL中，若密碼丟失則無法直接找回，只能通過特殊方式來修改密碼。
但是，首先需要確認是“root@localhost”還是“root@%”密碼丟失，因為這是2個不同的用戶，若其中一個丟失，那么可以使用另一個用戶登錄，然后修改密碼。

方法一：使用–init-file選項

2.1 具體修改密碼的步驟

1.登錄MySQL數據庫所在的服務器，停止MySQL服務。
  對于Linux服務器，使用 ps -ef|grep mysql 來查找MySQL服務的進程號，然后手工kill掉MySQL進程。
  對于Windows服務器，在cmd里輸入services.msc打開 服務 ，使用service來停止MySQL服務，或在cmd里使用 net stop mysql 停止。

2.創建一個文本文件mysql-init.sql，文件內容寫入密碼修改語句。
  -- MySQL 5.5和5.6版本使用以下語句：
set password FOR 'root'@'%' =password('test');
set password FOR 'root'@'localhost' =password('test');
  -- 從MySQL 5.7版本開始使用：
alter user 'root'@'%' identified by 'test';
alter user 'root'@'localhost' identified by 'test';

3.使用--init-file參數，啟動MySQL實例：
  -- linux
mysqld_safe --defaults-file=/etc/my.cnf --init-file=/tmp/mysql-init.sql &
  -- 若是Windows服務，則可以通過如下命令啟動：
D:\MySQL\mysql-8.0.15-win64\bin\mysqld --defaults-file=D:\MySQL\mysql-8.0.15-win64\data803314\mysql803314.ini --init-file=d:\mysql-init.sql --console

實例啟動成功后，密碼也就修改完畢，最后再刪除mysql-init.sql文件。

4.重新以正常方式啟動MySQL服務并驗證新密碼登錄。

方法二：使用–skip-grant-tables選項

   在啟動MySQL數據庫時使用--skip-grant-tables選項，表示啟動MySQL服務時跳過權限表認證。通過這種方式啟動后，在使用root用戶連接到MySQL時將不需要密碼。

-- 在使用skip-grant-tables時需要注意以下內容：

1.如果在my.cnf參數文件中只添加了--skip-grant-tables，那么在修改完密碼后，刪除該參數。其實無需重啟MySQL服務，只需要執行flush privileges即可，但是8.0版本為了遠程登錄，還是需要重啟mysql服務。
  
2.從MySQL 8.0開始，必須去掉--skip-grant-tables才能遠程登陸數據庫。否則拋出如下報錯：
[root@mysql8 data]# mysql -uroot -p -h 172.17.0.3   
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '172.17.0.3' (111)'
   
2.從安全角度出發，建議加上--skip-networking，但是因為其是靜態參數，所以將其剔除掉需要重啟實例。在MySQL 8.0版本里，如果你啟用–skip-grant-tables參數，MySQL會默認把 --skip-networking參數打開，表示這時候數據庫只能被本地的客戶端連接。

3.雖然加上--skip-networking參數可以屏蔽掉TCP連接，但對于本地其它用戶，只要有Socket文件的可讀權限，都能無密碼登錄，存在安全隱患。

2.2 具體修改密碼的步驟

-- 具體修改密碼的步驟如下所示：
1.登錄MySQL數據庫所在的服務器，停止MySQL服務。

2.在參數文件的[mysqld]項下添加skip-grant-tables語句，或使用--skip-grant-tables選項重啟MySQL服務：
  -- Linux啟動MySQL服務：
  /var/lib/mysql57/mysql5719/bin/mysqld_safe  --defaults-file=/etc/my.cnf --skip-grant-tables  &

  -- Windows啟動MySQL服務可以用命令行也可以用“服務”來啟停，cmd命令行如下：
  D:\MySQL\mysql-5.7.19-win32\bin\mysqld --defaults-file=D:\MySQL\mysql-5.7.19-win32\mysql553308.ini --skip-grant-tables --console

  -- 注意，若MySQL是8.0且安裝在Windows上，則需要加上--shared-memory參數：
  D:\MySQL\mysql-8.0.15-win64\bin\mysqld --defaults-file=D:\MySQL\mysql-8.0.15-win64\mysql803313.ini  --skip-grant-tables --shared-memory --console 

3.使用空密碼的root用戶連接到MySQL，并且修改root密碼。注意，此時可以以任意一個密碼登陸，也可以以一個空密碼登陸MySQL：
[root@mysql57 ~]# mysql -uroot
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> update mysql.user set authentication_string=password('test') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 1
mysql> alter user 'root'@'localhost' IDENTIFIED BY 'test';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user root@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@'localhost' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
+-----------+-----------+------------+------------+-------------------------------------------+-----------------------+
| user      | host      | grant_priv | super_priv | authentication_string                     | password_last_changed |
+-----------+-----------+------------+------------+-------------------------------------------+-----------------------+
| root      | %         | Y          | Y          | *827F67F8037D3F8F076544B6FFC6D56058166D8B | 2020-02-04 11:28:56   |
| mysql.sys | %         | N          | N          | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | 2019-07-18 10:52:13   |
| root      | localhost  | Y          | Y         | *827F67F8037D3F8F076544B6FFC6D56058166D8B | 2020-02-04 11:28:58   |
+-----------+-----------+------------+------------+-------------------------------------------+-----------------------+
3 rows in set (0.00 sec)

4.刷新權限表，使得權限認證重新生效。刷新權限表的語句必須執行。
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

5.重新使用新密碼來登錄MySQL。使用新密碼成功登錄后，再將MySQL服務器去掉--skip-grant-tables選項重啟即可。

6.重啟后驗證新密碼是否可用。

需要注意的是：

1.在MySQL 5.7以下版本中修改密碼時，需要更新mysql.user表的Password列，盡管有authentication_string列，但是密碼保存在Password列；而從MySQL 5.7開始，去掉了Password列，需要修改mysql.user表的authentication_string列。
  -- 所以，在MySQL 5.7以下版本修改密碼應該使用如下SQL：
update mysql.user set password=password('test') where user='root';
  -- 從MySQL 5.7開始可以使用：
update mysql.user set authentication_string=password('test') where user='root';

注意：從MySQL 8.0開始，password函數已經棄用。

2.從MySQL 5.7開始，不建議通過UPDATE的方式修改密碼，更通用的其實是ALTER USER。但是，需要先通過flush privileges操作觸發權限表的加載，然后才能使用alter user的方式來修改密碼。
alter user root@'localhost' identified by 'test';
alter user root@'%' identified by 'test';

3.可以使用如下命令查詢密碼：
  -- 查詢密碼，5.7以下
select user,host,grant_priv,super_priv,password,authentication_string from mysql.user;
  -- 查詢密碼，5.7以上
select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;

注意：從MySQL 5.7開始，在mysql.user表中新增了password_last_changed列，但是該列只有使用ALTER USER語句后才會更新password_last_changed列。

三.去掉密碼安全策略

? 一般yum方式安裝的mysql，可能需要去掉密碼驗證策略

-- validate_password 是 mysql5.6以后可以引入的一個新密碼校驗插件, 管理用戶密碼長度. 強度等。

show variables like 'validate_password%';
show status like 'validate_password%';
set global validate_password_policy=0;   #這個參數用于控制validate_password的驗證策略 0–>low 1–>MEDIUM 2–>strong
set global validate_password_policy=LOW;
set global validate_password_length=1;

show plugins;
uninstall plugin validate_password;
show variables like 'plugin_dir';

cd /usr/lib64/mysql/plugin
mv validate_password.so validate_password.so_bk
mv /component_validate_password.so component_validate_password.so_bk

四. 密碼含特殊符合

-- 修改密碼含特殊符合
update mysql.user set authentication_string=password('test!@#') where user='root'; 
flush privileges;

-- 登錄報錯
[root@mysql57 ~]# mysql -uroot -ptest!@#
-bash: !@#: event not found

-- 解決辦法：
1.將密碼用單引號引起來
[root@mysql57 ~]# mysql -uroot -p'test!@#'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2.在特殊字符&前面加上'\'來進行登錄
[root@mysql57 ~]# mysql -uroot -ptest\!\@\# 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.27-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

3.手動輸入密碼也可以，無需轉義
[root@mysql57 ~]# mysql -uroot -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password: 
test!@#

4.navicat 工具登錄時候，直接輸入密碼也可以登錄，無需轉義。