MySQL changes user password and resets root Password

Changing the password for a database user is one of the common tasks of DBA. There are several different ways to change the password of a MySQL user account. We recommend that you use the encryption function to modify the password.

Changing the password for a database user is one of the common tasks of DBA. There are several different ways to change the password of a MySQL user account. We recommend that you use the encryption function to modify the password.

Changing the password for a database user is one of the common tasks of DBA. There are several different ways to change the password of a MySQL user account. We recommend that you use the encryption function to change the password. This article mainly describes how to change the user password and how to deal with the loss of the password of the mysql root Account (reset the root password) through several different methods.

1. Password modification methods

A. You can specify a password when creating a user and directly use grant to create a user.
You can change the password of an existing user by using grant.
As follows:

-- Demo version
Root @ localhost [(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 |
+ ------------------------- + -------------------------------- +

-- Use grant to create a new account, fred, and set the password.
Root @ localhost [(none)]> grant usage on *. * to 'fred '@ 'localhost' identified by 'fred ';
Query OK, 0 rows affected (0.00 sec)

-- View the account you just created
Root @ localhost [(none)]> select host, user, password from mysql. user where user = 'fred ';
+ ----------- + ------ + --------------------------------------------- +
| Host | user | password |
+ ----------- + ------ + --------------------------------------------- +
| Localhost | fred | * 6C69D17939B2C1D04E17A96F9B29B284832979B7 |
+ ----------- + ------ + --------------------------------------------- +

-- You can log on to mysql
SZDB :~ # Mysql-ufred-pfred

Fred @ localhost [(none)]>

B. Use the set password method to change the account password
-- Set password
Root @ localhost [(none)]> set password for 'fred '@ 'localhost' = password ('passwd ');
Query OK, 0 rows affected (0.00 sec)

Root @ localhost [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

-- The previous password has expired and cannot be logged on again
SZDB :~ # Mysql-ufred-pfred
ERROR 1045 (28000): Access denied for user 'fred '@ 'localhost' (using password: YES)

-- Login Succeeded with the new password below
SZDB :~ # Mysql-ufred-ppasswd

Fred @ localhost [(none)]>

-- Query whether the database has a linuxidc user. The password below is null.
Root @ localhost [(none)]> select host, user, password from mysql. user where user = 'linuxidc ';
+ ----------- + ------ + ---------- +
| Host | user | password |
+ ----------- + ------ + ---------- +
| Localhost | linuxidc |
+ ----------- + ------ + ---------- +

C. Update the password column of the user in the system table through encryption
-- We try to update the password column directly without using the encryption function)
Root @ localhost [(none)]> update mysql. user set password = 'linuxidc id' where user = 'linuxid ';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

-- Because plain text is used directly, the password in the user column of the system table is displayed as plain text
Root @ localhost [(none)]> select host, user, password from mysql. user where user = 'linuxidc ';
+ ----------- + ------ + ---------- +
| Host | user | password |
+ ----------- + ------ + ---------- +
| Localhost | linuxidc | jack |
+ ----------- + ------ + ---------- +

-- Author: Leshami
-- Blog:

Root @ localhost [(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)

-- Login fails at this time
SZDB :~ # Mysql-ulinuxidc-pjack-h localhost
ERROR 1045 (28000): Access denied for user 'linuxid' @ 'localhost' (using password: YES)

-- Next we use the set method to change the password of linuxidc, prompting that the user of linuxidc cannot be found.
Root @ localhost [(none)]> set password for 'linuxid' @ 'localhost' = password ('linuxid ');
ERROR 1133 (42000): Can't find any matching row in the user table

-- Switch to the mysql database and try,
Root @ localhost [(none)]> use mysql

Root @ localhost [mysql]> set password for 'linuxidc '@ 'localhost' = password ('passwd'); -- the user's linuxidc password cannot be updated in the mysql database.
ERROR 1133 (42000): Can't find any matching row in the user table

-- Next we will try to use the password function to update the password column.
Root @ localhost [mysql]> update user set password = password ('passwd') where user = 'linuxidc '; -- this method is updated successfully.
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 = 'linuxidc '; -- you can see that the password has become ciphertext
+ ----------- + ------ + --------------------------------------------- +
| Host | user | password |
+ ----------- + ------ + --------------------------------------------- +
| Localhost | linuxidc | * 59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 |
+ ----------- + ------ + --------------------------------------------- +

Root @ localhost [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

-- The logon is successful.
Robin @ SZDB: ~> Mysql-ulinuxidc-ppasswd

Linuxidc @ localhost [(none)]>

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

For more details, please continue to read the highlights on the next page: