MySQL如何修改用户密码以及如何重置root密码

<span style="font-size: 14px; font-family: 微软雅黑, "Microsoft YaHei";">a、可以在创建用户的时候指定密码，以及直接使用grant创建用户的时候指定密码。<br/>   对于已经存在的用户直接使用grant方式也可以修改密码<br/>如下：<br/><br/>--演示版本<br/>root@localhost[(none)]> show variables like 'version%';  <br/>+-------------------------+------------------------------+   <br/>| Variable_name           | Value                        |  <br/>+-------------------------+------------------------------+   <br/>| version                 | 5.5.37                       |  <br/>| version_comment         | MySQL Community Server (GPL) |  <br/>| version_compile_machine | x86_64                       |  <br/>| version_compile_os      | Linux                        |  <br/>+-------------------------+------------------------------+   <br/><br/>--下面我们使用grant方式创建一个新帐户fred，并设定密码<br/>root@localhost[(none)]> grant usage on *.* to 'fred'@'localhost' identified by 'fred';<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>--查看刚刚创建的账户<br/>root@localhost[(none)]> select host,user,password from mysql.user where user='fred';<br/>+-----------+------+-------------------------------------------+<br/>| host      | user | password                                  |<br/>+-----------+------+-------------------------------------------+<br/>| localhost | fred | *6C69D17939B2C1D04E17A96F9B29B284832979B7 |<br/>+-----------+------+-------------------------------------------+<br/><br/>--下面可以成功登陆mysql<br/>SZDB:~ # mysql -ufred -pfred<br/><br/>fred@localhost[(none)]> <br/><br/>b、使用set password方式来修改账户密码<br/>--下面我们使用set password方式来设定密码<br/>root@localhost[(none)]> set password for 'fred'@'localhost'=password('passwd');<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>root@localhost[(none)]> flush privileges;<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>--再次登陆时，之前的密码已经失效，无法登陆<br/>SZDB:~ # mysql -ufred -pfred<br/>ERROR 1045 (28000): Access denied for user 'fred'@'localhost' (using password: YES)<br/><br/>--下面使用新密码登陆成功<br/>SZDB:~ # mysql -ufred -ppasswd<br/><br/>fred@localhost[(none)]> <br/><br/>--检索数据库是否存在jack用户，如下密码为null<br/>root@localhost[(none)]> select host,user,password from mysql.user where user='jack';<br/>+-----------+------+----------+<br/>| host      | user | password |<br/>+-----------+------+----------+<br/>| localhost | jack |          |<br/>+-----------+------+----------+<br/><br/>c、加密方式更新系统表user的password列<br/>--我们尝试直接更新密码列(不使用加密函数方式)<br/>root@localhost[(none)]> update mysql.user set password='jack' where user='jack';<br/>Query OK, 1 row affected (0.00 sec)<br/>Rows matched: 1  Changed: 1  Warnings: 0<br/><br/>--由于直接使用明文，因此系统表user列password显示为明文<br/>root@localhost[(none)]> select host,user,password from mysql.user where user='jack';<br/>+-----------+------+----------+<br/>| host      | user | password |<br/>+-----------+------+----------+<br/>| localhost | jack | jack     |<br/>+-----------+------+----------+<br/><br/>--Author : Leshami<br/>--Blog   ：http://blog.csdn.net/leshami<br/><br/>root@localhost[(none)]> flush privileges;<br/>Query OK, 0 rows affected (0.02 sec)<br/><br/>--此时无法登陆<br/>SZDB:~ # mysql -ujack -pjack -h localhost      <br/>ERROR 1045 (28000): Access denied for user 'jack'@'localhost' (using password: YES)<br/><br/>--下面我们通过set方式来修改jack的密码，提示找不到jack用户<br/>root@localhost[(none)]> set password for 'jack'@'localhost'=password('jack');<br/>ERROR 1133 (42000): Can't find any matching row in the user table<br/><br/>--我们切换到mysql数据库下尝试，<br/>root@localhost[(none)]> use mysql   <br/><br/>root@localhost[mysql]> set password for 'jack'@'localhost'=password('passwd');  <br/>--在mysql数据库下依旧无法更新用户jack的密码<br/>ERROR 1133 (42000): Can't find any matching row in the user table<br/><br/>--下面我们尝试用password函数方式来更新password列<br/>root@localhost[mysql]> update user set password=password('passwd') where user='jack'; --此方式更新成功<br/>Query OK, 1 row affected (0.04 sec)<br/>Rows matched: 1  Changed: 1  Warnings: 0<br/><br/>root@localhost[mysql]> select host,user,password from user where user='jack';    --可以看到密码已经变成了密文<br/>+-----------+------+-------------------------------------------+<br/>| host      | user | password                                  |<br/>+-----------+------+-------------------------------------------+<br/>| localhost | jack | *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 |<br/>+-----------+------+-------------------------------------------+<br/><br/>root@localhost[mysql]> flush privileges;<br/>Query OK, 0 rows affected (0.00 sec)<br/><br/>--此时登陆成功<br/>robin@SZDB:~> mysql -ujack -ppasswd          <br/><br/>jack@localhost[(none)]> <br/></span>

<span style="font-size: 14px; font-family: 微软雅黑, "Microsoft YaHei";">--假定此时我们的root帐户忘记或遗失了密码，如下面的演示，我们给出的是xxx，不能登陆到mysql(真实的密码为mysql)<br/>SZDB:~ # mysql -uroot -pmysql<br/><br/>root@localhost[(none)]> <br/><br/>SZDB:~ # mysql -uroot -pxxx       #忘记密码，此时无法正常登录  <br/>Enter password: <br/>ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)<br/><br/>--首先停止mysql服务器<br/>SZDB:~ # service mysql stop<br/>Shutting down MySQL..                               done<br/><br/>--使用--skip-grant-tables选项跳过授权表验证，<br/>SZDB:~ # mysqld --help --verbose     #获取mysqld帮助信息<br/><br/>--skip-grant-tables Start without grant tables. This gives all users FULL<br/>                      ACCESS to all tables.<br/><br/>--使用--skip-grant-tables启动mysql服务器<br/>SZDB:~ # mysqld --skip-grant-tables --user=mysql &<br/>[1] 10209<br/>SZDB:~ # ps -ef | grep mysql<br/>mysql    10209 14240  4 13:52 pts/0    00:00:00 mysqld --skip-grant-tables --user=mysql<br/>root     10229 14240  0 13:53 pts/0    00:00:00 grep mysql<br/>SZDB:~ # mysql   <br/><br/>root@localhost[(none)]> select user,host,password from mysql.user where user='root';<br/>+-------+-----------+-------------------------------------------+<br/>| user  | host      | password                                  |<br/>+-------+-----------+-------------------------------------------+<br/>| root  | %         | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |<br/>| root  | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |<br/>+-------+-----------+-------------------------------------------+<br/><br/>--更新mysql账户密码为NULL或设定为新密码，注设定为空密码时可以直接设置，无须使用加密函数，2者等同<br/>root@localhost[(none)]> update mysql.user set password='' where user='root';<br/>Query OK, 2 rows affected (0.00 sec)<br/>Rows matched: 2  Changed: 2  Warnings: 0<br/><br/>root@localhost[(none)]> select user,host,password from mysql.user where user='root';<br/>+------+-----------+----------+<br/>| user | host      | password |<br/>+------+-----------+----------+<br/>| root | %         |          |<br/>| root | 127.0.0.1 |          |<br/>+------+-----------+----------+<br/><br/>root@localhost[(none)]> exit<br/>Bye<br/><br/>#再次停止mysql数据库服务器<br/>SZDB:~ # service mysql stop<br/>Shutting down MySQL.                                                  done<br/>[1]+  Done                    mysqld --skip-grant-tables --user=mysql<br/>SZDB:~ # service mysql start<br/>Starting MySQL..                                                      done<br/>SZDB:~ # mysql            #重启后再次登陆，不再需要任何密码<br/><br/>root@localhost[(none)]>   <br/><br/></span>