MySQL change user password and reset root password

modifying passwords for database users is one of the more common tasks for DBAs. There are several different ways to modify the password for a MySQL user account, using cryptographic functions to modify the password in the recommended way. This article mainly describes how to modify the user password and the MySQL root account password loss (reset root password) in several different ways.

1, several methods of password modification

A, you can specify a password when creating a user, and specify a password when creating a user directly using Grant.  For users who already exist, the password can also be modified using the Grant method:--Demo Version [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 the 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 be successfully logged mysqlszdb:~ # mysql-ufred-pfred[email protected][(none)]> B, using the Set password method to modify the account password--below 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 you log in again, the previous password has expired and cannot be logged in szdb:~ # Mysql-ufred-pfrederror 1045 (28000): Access denied fo R user ' fred ' @ ' localhost ' (using password:yes)--Login successfully with new password below szdb:~ # mysql-ufred-ppasswd[email protected][(None) ]>--Retrieves the database for the presence of the Jack user, 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--due to the direct use of clear text, the system table user column password is displayed as clear [email p rotected][(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)--Below we modify Jack's password by the Set method, indicating that jack user cannot 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 '; --you can see that the password has become ciphertext +-----------+------+-------------------------------------------+| Host | user | Password |+-----------+------+-------------------------------------------+| localhost | Jack | *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 |+-----------+------+-------------------------------------------+[ email protected][mysql]> flush Privileges; Query OK, 0 rows Affected (0.00 sec)--At this time login succeeded [email protected]:~> mysql-ujack-ppasswd [email protected][(None)]>  

-assuming our root account forgot or lost the password at this point, 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 #忘记密码 and cannot log in at this time Enter Password:error 1045 (28000): Access denied for user                               ' Root ' @ ' localhost ' (using Password:no)--first stop MySQL server szdb:~ # service MySQL stopshutting down MySQL. done--use--skip-grant-tables option to skip authorization table validation, szdb:~ # mysqld--help--verbose #获取mysqld帮助信息--skip-grant-tables Sta RT without Grant tables. This gives all users tables.--use--skip-grant-tables to start the MySQL server szdb:~ # mysqld--ski P-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 a new password, note set to a blank password can be set directly, without the use of cryptographic functions, 2 equivalent [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 server again szdb:~ # service MySQL                                                  Stopshutting down MySQL. Done[1]+ done mysqld--skip-grant-tables--user=mysqlszdb:~ # service MySQL startstarting MySQL. doneszdb:~ # mysql #重启后再次登陆, no more password required [email protected][(none)    ]>

3. Summary
A, you can use Set password for ' user_name ' @ ' host_name ' password=password (' new_pwd ') way to modify the password
b, you can use the Update system table, update user set Password=password (' passwd ') where user= ' user_name '
Note: For the user table password class, if you do not use the password function, it will not log in after the update.
However, if you update the account to a blank password, you can use the encryption function, or you can not use, 2 is equivalent.
C, you can also use the Grant method to update the user's password directly after the user has created it.
D. If the root password is lost or the root password needs to be reset, the System option--skip-grant-tables to start the server and reset it.
E, for MySQL permissions and user management, when creating a user specified password, please refer to: MySQL User and Rights management

MySQL change user password and reset root password