MySQL changes user password and resets Rootpassword

Changing password for database users is one of the more common tasks for DBAs. There are several different ways to password changes to the MySQL user account. The recommended way to use cryptographic functions is to change the password.

This paper describes the processing methods of changing user password and MySQL root account password lost (resetting Rootpassword) through several different ways.

1. Several methods of password alteration

A, can specify password when creating the user.   and specify password when creating a user directly using Grant.  It is also possible to change password for existing users directly using the Grant method:--Demo version number [email protected][(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 | +-------------------------+------------------------------+--below we create a new account using the Grant method Fred, and set password[email  protected][(none)]> grant usage on * * to ' fred ' @ ' localhost ' identified by ' Fred '; Query OK, 0 rows Affected (0.00 sec)--View the account you just created [email protected][(none)]> Select Host,user,password from Mysql.user where user= ' Fred '; +-----------+------+-------------------------------------------+|  Host    | user | Password |+-----------+------+-------------------------------------------+| localhost | Fred | *6c69d17939b2c1d04e17a96f9b29b284832979b7 |+-----------+------+-------------------------------------------+-- The following can successfully login mysqlszdb:~ # mysql-ufred-pfred[email protected][(none)]> B, use Set Password method to change the account password--The following we use Set Password mode to set the password [email protected][(none)]> set password for ' Fred ' @ ' localhost ' =password (' passwd '); Query OK, 0 rows Affected (0.00 sec) [email protected][(none)]> flush privileges; Query OK, 0 rows Affected (0.00 sec)-When landing again. The previous password has failed. Unable to login szdb:~ # Mysql-ufred-pfrederror 1045 (28000): Access denied for user ' Fred ' @ ' localhost ' (using Password:yes)--The following uses the new Password login succeeded szdb:~ # mysql-ufred-ppasswd[email protected][(None)]>--Retrieve the database for the presence of the Jack user. For example the following password is null[email protected][(none)]> Select Host,user,password from Mysql.user where user= ' jack '; +--- --------+------+----------+| Host | user | PasSword |+-----------+------+----------+| localhost |          Jack | |+-----------+------+----------+c, encryption Update system table user's password column--We try to update the password column directly (without using cryptographic functions) [email protected][ (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--because clear text is used directly. Therefore the system table user column password is displayed as clear text [email protected][(none)]> Select Host,user,password from Mysql.user where user= ' Jack '; +-----------+------+----------+| Host | user | Password |+-----------+------+----------+| localhost | Jack | Jack |+-----------+------+----------+--author:leshami--blog:http://blog.csdn.net/leshami[email protected][ (none)] > Flush Privileges; Query OK, 0 rows affected (0.02 sec)-cannot log in at this time szdb:~ # mysql-ujack-pjack-h localhost ERROR 1045 (28000): Access Deni Ed for user ' Jack ' @ ' localhost ' (using Password:yes)--The following we change Jack's password by the Set method, indicating that the jack user could not be found [email protected][ (none)] > Set password for ' jack ' @ ' localhost ' =pAssword (' Jack ');  ERROR 1133 (42000): Can ' t find any matching row in the user table--we switch to MySQL database try, [email protected][(none)]> use  MySQL [email protected][mysql]> set password for ' jack ' @ ' localhost ' =password (' passwd '); --Still unable to update user Jack's password under MySQL database error 1133 (42000): Can ' t find any matching row in the user table--below we try to update password with PASSW function Ord column [email protected][mysql]> Update user set Password=password (' passwd ') where user= ' Jack '; --This method updated successfully query OK, 1 row affected (0.04 sec) Rows matched:1 changed:1 warnings:0[email protected][mysql]> Sele    CT host,user,password from user where user= ' Jack '; --can see the password has become ciphertext +-----------+------+-------------------------------------------+| Host | user | Password |+-----------+------+-------------------------------------------+| localhost | Jack | *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 |+-----------+------+-------------------------------------------+[ Email protected][mysql]> Flush Privileges; Query OK, 0 rows Affected (0.00 sec)-Landed successfully [email protected]:~> MYSQL-UJACK-PPASSWD [Email protec  ted][(None)]>

2, reset root account password

-assuming our root account forgot or lost password at this time, as shown in the following demo, we give xxx, can't login to MySQL (real password for MySQL) szdb:~ # mysql-uroot-pmysql[email  protected][(None)]> szdb:~ # mysql-uroot-pxxx #忘记password, unable to log in at this time Enter Password:error 1045 (28000): A Ccess denied for user ' root ' @ ' localhost ' (using Password:no)--First Stop mysqlserverszdb:~ # service MySQL stopshutting down MyS                               Ql.. done--use--skip-grant-tables option to skip authorization table validation, szdb:~ # mysqld--help--verbose #获取mysqld帮助信息--skip-grant-tables Start without G Rant tables. This gives all users tables.--use--skip-grant-tables to start mysqlserverszdb:~ # mysqld-- Skip-grant-tables--user=mysql &[1] 10209szdb:~ # Ps-ef | grep mysqlmysql 10209 14240 4 13:52 pts/0 00:00:00 mysqld--skip-grant-tables--user=mysqlroot 10229 14240 0 1 3:53 pts/0 00:00:00 grep mysqlszdb:~ # mysql [email protected][(none)]> select User,host,password from MySQL. User where user= ' root '; +-------+-----------+-------------------------------------------+| user | Host | Password |+-------+-----------+-------------------------------------------+| Root | %         | *e74858db86eba20bc33d0aecae8a8108c56b17fa | | Root | 127.0.0.1 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |+-------+-----------+-------------------------------------------+-- Update MySQL account password is null or set to new password, note set to empty password can be set directly. Without the use of cryptographic functions, 2 equals [email protected][(none)]> update mysql.user set password= ' where user= ' root '; Query OK, 2 rows Affected (0.00 sec) Rows Matched:2 changed:2 warnings:0[email protected][(none)]> Select User , Host,password from Mysql.user where user= ' root '; +------+-----------+----------+| user | Host | Password |+------+-----------+----------+| Root |          %         | || Root |          127.0.0.1 | |+------+-----------+----------+[email protected][(none)]> exitbye# stop MySQL database again serverszdb:~ # Service         MySQL stopshutting down MySQL.                                         done[1]+ done mysqld--skip-grant-tables--user=mysqlszdb:~ #                                                      Service MySQL startstarting MySQL. doneszdb:~ # mysql #重新启动后再次登陆.    No longer required no matter what password[email protected][(none)]>

3. Summary
A, can use Set password for ' user_name ' @ ' host_name ' =password (' new_pwd ') way to change the password #更正 @20141031
b, the ability to use the Update system table, update user set Password=password (' passwd ') where user= ' user_name '
Note: For the user table password class, assume that the password function is not necessary. Unable to log in after an update is caused.
However, if you update your account to a blank password, you can use cryptographic functions. can also not use, 2 equals.
C, you can also use the Grant method to update the user's password directly after the user has created it.

D, the corresponding root password is missing or need to reset the root password situation, you need to use the System option--skip-grant-tables start the server after the reset.
E, about MySQL permissions and user management. To specify a password when creating a user, please refer to: MySQL User and Rights management

idkey=e61fdd22421b38cb5eef12db3c564ac0b5840f0f5cae4958e8b646740a79e680 "target=" _blank ">

MySQL changes user password and resets Rootpassword