MySQL修改用户密码及重置root密码 - 数据库编程

试用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、小结

MySQL修改用户密码及重置root密码(二)