MySQL changes user password and resets root Password

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 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 bkjia user exists in the database. The password below is null.
Root @ localhost [(none)]> select host, user, password from mysql. user where user = 'bkjia ';
+ ----------- + ------ + ---------- +
| Host | user | password |
+ ----------- + ------ + ---------- +
| Localhost | bkjia |
+ ----------- + ------ + ---------- +

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 = 'bkjia 'where user = 'bkjia ';
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 = 'bkjia ';
+ ----------- + ------ + ---------- +
| Host | user | password |
+ ----------- + ------ + ---------- +
| Localhost | bkjia | jack |
+ ----------- + ------ + ---------- +

-- Author: Leshami
-- Blog:

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:

1 2 Next Page