MySQL修改用户密码及重置root密码_MySQL

1、密码修改的几种方法

a、可以在创建用户的时候指定密码，以及直接使用grant创建用户的时候指定密码。
   对于已经存在的用户直接使用grant方式也可以修改密码
如下：

--演示版本
root@localhost[(none)]> show variables like &＃39;version%&＃39;;  
+-------------------------+------------------------------+   
| 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 &＃39;fred&＃39;@&＃39;localhost&＃39; identified by &＃39;fred&＃39;;
Query OK, 0 rows affected (0.00 sec)

--查看刚刚创建的账户
root@localhost[(none)]> select host,user,password from mysql.user where user=&＃39;fred&＃39;;
+-----------+------+-------------------------------------------+
| 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 &＃39;fred&＃39;@&＃39;localhost&＃39;=password(&＃39;passwd&＃39;);
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 &＃39;fred&＃39;@&＃39;localhost&＃39; (using password: YES)

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

fred@localhost[(none)]> 

--检索数据库是否存在jack用户，如下密码为null
root@localhost[(none)]> select host,user,password from mysql.user where user=&＃39;jack&＃39;;
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | jack |          |
+-----------+------+----------+

c、加密方式更新系统表user的password列
--我们尝试直接更新密码列(不使用加密函数方式)
root@localhost[(none)]> update mysql.user set password=&＃39;jack&＃39; where user=&＃39;jack&＃39;;
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=&＃39;jack&＃39;;
+-----------+------+----------+
| host      | user | password |
+-----------+------+----------+
| localhost | jack | jack     |
+-----------+------+----------+

--Author : Leshami
--Blog   ：http://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 &＃39;jack&＃39;@&＃39;localhost&＃39; (using password: YES)

--下面我们通过set方式来修改jack的密码，提示找不到jack用户
root@localhost[(none)]> set password for &＃39;jack&＃39;@&＃39;localhost&＃39;=password(&＃39;jack&＃39;);
ERROR 1133 (42000): Can&＃39;t find any matching row in the user table

--我们切换到mysql数据库下尝试，
root@localhost[(none)]> use mysql   

root@localhost[mysql]> set password for &＃39;jack&＃39;@&＃39;localhost&＃39;=password(&＃39;passwd&＃39;);  --在mysql数据库下依旧无法更新用户jack的密码
ERROR 1133 (42000): Can&＃39;t find any matching row in the user table

--下面我们尝试用password函数方式来更新password列
root@localhost[mysql]> update user set password=password(&＃39;passwd&＃39;) where user=&＃39;jack&＃39;; --此方式更新成功
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=&＃39;jack&＃39;;    --可以看到密码已经变成了密文
+-----------+------+-------------------------------------------+
| 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 &＃39;root&＃39;@&＃39;localhost&＃39; (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=&＃39;root&＃39;;
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | %         | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| root  | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
+-------+-----------+-------------------------------------------+

--更新mysql账户密码为NULL或设定为新密码，注设定为空密码时可以直接设置，无须使用加密函数，2者等同
root@localhost[(none)]> update mysql.user set password=&＃39;&＃39; where user=&＃39;root&＃39;;
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=&＃39;root&＃39;;
+------+-----------+----------+
| 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、小结