MySQL資料庫修改用戶密碼及重置root密碼

為資料庫用戶修改密碼是DBA比較常見的工作之一。對於MySQL用戶賬戶的密碼修改,有幾種不同的方式,推薦的方式使用加密函數來修改密碼。本文主要描述瞭通過幾種不同的方式來修改用戶密碼以及mysql root賬戶密碼丟失(重置root密碼)的處理方法。

1、密碼修改的幾種方法

a、可以在創建用戶的時候指定密碼,以及直接使用grant創建用戶的時候指定密碼。
   對於已經存在的用戶直接使用grant方式也可以修改密碼
如下:

--演示版本
root@localhost[(none)]> show variables like 'version%';  
+-------------------------+------------------------------+   
| Variable_name           | Value                        |  
+-------------------------+------------------------------+   
| version                 | 5.5.37                       |  
| version_comment         | MySQL Community Server (GPL) |  
| version_compile_machine | x86_64                       |  
| version_compile_os      | Linux                        |  
+-------------------------+------------------------------+   

--下面我們使用grant方式創建一個新帳戶fred,並設定密碼
root@localhost[(none)]> grant usage on *.* to 'fred'@'localhost' identified by 'fred';
Query OK, 0 rows affected (0.00 sec)

--查看剛剛創建的賬戶
root@localhost[(none)]> select host,user,password from mysql.user where user='fred';
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | fred | *6C69D17939B2C1D04E17A96F9B29B284832979B7 |
+-----------+------+-------------------------------------------+

--下面可以成功登陸mysql
SZDB:~ # mysql -ufred -pfred

fred@localhost[(none)]> 

b、使用set password方式來修改賬戶密碼
--下面我們使用set password方式來設定密碼
root@localhost[(none)]> set password for 'fred'@'localhost'=password('passwd');
Query OK, 0 rows affected (0.00 sec)

root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--再次登陸時,之前的密碼已經失效,無法登陸
SZDB:~ # mysql -ufred -pfred
ERROR 1045 (28000): Access denied for user 'fred'@'localhost' (using password: YES)

--下面使用新密碼登陸成功
SZDB:~ # mysql -ufred -ppasswd

fred@localhost[(none)]> 

--檢索資料庫是否存在jack用戶,如下密碼為null
root@localhost[(none)]> select host,user,password from mysql.user where user='jack';
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | jack |          |
+-----------+------+----------+

c、加密方式更新系統表user的password列
--我們嘗試直接更新密碼列(不使用加密函數方式)
root@localhost[(none)]> update mysql.user set password='jack' where user='jack';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--由於直接使用明文,因此系統表user列password顯示為明文
root@localhost[(none)]> select host,user,password from mysql.user where user='jack';
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | jack | jack     |
+-----------+------+----------+

--Author : Leshami
--Blog   :https://blog.csdn.net/leshami

root@localhost[(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)

--此時無法登陸
SZDB:~ # mysql -ujack -pjack -h localhost      
ERROR 1045 (28000): Access denied for user 'jack'@'localhost' (using password: YES)

--下面我們通過set方式來修改jack的密碼,提示找不到jack用戶
root@localhost[(none)]> set password for 'jack'@'localhost'=password('jack');
ERROR 1133 (42000): Can't find any matching row in the user table

--我們切換到mysql資料庫下嘗試,
root@localhost[(none)]> use mysql   

root@localhost[mysql]> set password for 'jack'@'localhost'=password('passwd');  --在mysql資料庫下依舊無法更新用戶jack的密碼
ERROR 1133 (42000): Can't find any matching row in the user table

--下面我們嘗試用password函數方式來更新password列
root@localhost[mysql]> update user set password=password('passwd') where user='jack'; --此方式更新成功
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@localhost[mysql]> select host,user,password from user where user='jack';    --可以看到密碼已經變成瞭密文
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | jack | *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 |
+-----------+------+-------------------------------------------+

root@localhost[mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--此時登陸成功
robin@SZDB:~> mysql -ujack -ppasswd          

jack@localhost[(none)]> 

2、重置root帳戶密碼

--假定此時我們的root帳戶忘記或遺失瞭密碼,如下面的演示,我們給出的是xxx,不能登陸到mysql(真實的密碼為mysql)
SZDB:~ # mysql -uroot -pmysql

root@localhost[(none)]> 

SZDB:~ # mysql -uroot -pxxx       #忘記密碼,此時無法正常登錄  
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

--首先停止mysql伺服器
SZDB:~ # service mysql stop
Shutting down MySQL..                               done

--使用--skip-grant-tables選項跳過授權表驗證,
SZDB:~ # mysqld --help --verbose     #獲取mysqld幫助信息

--skip-grant-tables Start without grant tables. This gives all users FULL
                      ACCESS to all tables.

--使用--skip-grant-tables啟動mysql伺服器
SZDB:~ # mysqld --skip-grant-tables --user=mysql &
[1] 10209
SZDB:~ # ps -ef | grep mysql
mysql    10209 14240  4 13:52 pts/0    00:00:00 mysqld --skip-grant-tables --user=mysql
root     10229 14240  0 13:53 pts/0    00:00:00 grep mysql
SZDB:~ # mysql   

root@localhost[(none)]> select user,host,password from mysql.user where user='root';
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | %         | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| root  | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+-------+-----------+-------------------------------------------+

--更新mysql賬戶密碼為NULL或設定為新密碼,註設定為空密碼時可以直接設置,無須使用加密函數,2者等同
root@localhost[(none)]> update mysql.user set password='' where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

root@localhost[(none)]> select user,host,password from mysql.user where user='root';
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
| root | %         |          |
| root | 127.0.0.1 |          |
+------+-----------+----------+

root@localhost[(none)]> exit
Bye

#再次停止mysql資料庫伺服器
SZDB:~ # service mysql stop
Shutting down MySQL.                                                  done
[1]+  Done                    mysqld --skip-grant-tables --user=mysql
SZDB:~ # service mysql start
Starting MySQL..                                                      done
SZDB:~ # mysql            #重啟後再次登陸,不再需要任何密碼

root@localhost[(none)]>   

3、小結

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *