Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to change user password and reset root password in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let Xiaobian lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Changing passwords for database users is one of the more common tasks in DBA. There are several different ways to change the password of a MySQL user account. The recommended way is to use the encryption function to change the password. This article mainly describes how to change the user's password in several different ways and how to deal with the loss of the mysql root account password (reset the root password).

1. Several methods of password modification

A, you can specify a password when you create a user, and when you create a user directly using grant.

For existing users, you can also change your password by using grant directly.

As follows:

-- demonstration version

Root@localhost [(none)] > show variables like 'version%'

+-- +

| | Variable_name | Value |

+-- +

| | version | 5.5.37 | |

| | version_comment | MySQL Community Server (GPL) |

| | version_compile_machine | x86room64 | |

| | version_compile_os | Linux |

+-- +

Let's 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 successfully log in to mysql below

SZDB:~ # mysql-ufred-pfred

Fred@localhost [(none)] >

B. Use set password to change the account password

Let's use set password to set the 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)

When you log in again, the previous password is invalid and cannot be logged in.

SZDB:~ # mysql-ufred-pfred

ERROR 1045 (28000): Access denied for user 'fred'@'localhost' (using password: YES)

-- successfully log in with the new password below

SZDB:~ # mysql-ufred-ppasswd

Fred@localhost [(none)] >

-- retrieve whether there is a linuxidc user in the database. 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 system table user by encryption

-- We try to update the password column directly (without using encryption functions)

Root@localhost [(none)] > update mysql.user set password='linuxidc' where user='linuxidc'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

-- because plaintext is used directly, the system table user column password is displayed as plaintext

Root@localhost [(none)] > select host,user,password from mysql.user where user='linuxidc'

+-+

| | host | user | password | |

+-+

| | localhost | linuxidc | jack | |

+-+

Root@localhost [(none)] > flush privileges

Query OK, 0 rows affected (0.02 sec)

-- cannot log in at this time

SZDB:~ # mysql-ulinuxidc-pjack-h localhost

ERROR 1045 (28000): Access denied for user 'linuxidc'@'localhost' (using password: YES)

-- next, we use set to change the linuxidc password, indicating that the linuxidc user cannot be found.

Root@localhost [(none)] > set password for 'linuxidc'@'localhost'=password (' linuxidc')

ERROR 1133 (42000): Can't find any matching row in the user table

Let's switch to the mysql database and try.

Root@localhost [(none)] > use mysql

Root@localhost [mysql] > set password for 'linuxidc'@'localhost'=password (' passwd');-- the password of user linuxidc still cannot be updated under the mysql database

ERROR 1133 (42000): Can't find any matching row in the user table

Let's try to update the password column with the password function.

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)

-- Log in successfully at this time

Robin@SZDB:~ > mysql-ulinuxidc-ppasswd

Linuxidc@localhost [(none)] >

2. Reset the password of the root account

Suppose our root account forgets or loses its password at this time, as shown in the following demonstration, we give xxx and cannot log in to mysql (the real password is mysql)

SZDB:~ # mysql-uroot-pmysql

Root@localhost [(none)] >

SZDB:~ # mysql-uroot-pxxx # forgot your password and could not log in normally at this time

Enter password:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Stop the mysql server first

SZDB:~ # service mysql stop

Shutting down MySQL.. Done

-- use the-- skip-grant-tables option to skip authorization table verification

SZDB:~ # mysqld-- help-- verbose # get mysqld help information

-- skip-grant-tables Start without grant tables. This gives all users FULL ACCESS to all tables.

-- start the mysql server using-- skip-grant-tables

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 |

+-- +

Update the mysql account password to NULL or set it to a new password, and you can set it directly when the password is set to empty. There is no need to use the encryption function. Both are the same.

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

# stop the mysql database server again

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 # reboot and log in again, no longer need any password

Root@localhost [(none)] >

3. Summary

A. You can use set password for 'user_name'@'host_name' password=password (' new_pwd') to change the password.

B. You can use update system table mode, update user set password=password ('passwd') where user='user_name'

Note: for the password class of user table, if you do not use the password function, you will not be able to log in after the update. However, if the account is updated with an empty password, the encryption function can be used or not, and the two are the same.

C, you can also use grant method to update the user password directly after the user has created it.

D. If the root password is lost or the root password needs to be reset, you need to use the system option-- skip-grant-tables to reset after starting the server.

This is the end of the introduction to "how to change the user's password and reset the root password in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.