MySQL account password modification method (summary)

Preface:

In the daily use of the database, it is inevitable that you will encounter situations where you need to modify your account password, such as when your password is too simple and needs to be modified, when your password expires and needs to be modified, when you forget your password and need to modify it, etc. This article will introduce the scenarios where passwords need to be changed and several ways to change passwords.

1. Forgot root password

It is quite common to forget the root password, especially when the test environment you set up has not been used for a long time. It is easy to forget the password you set at that time. The commonly used method at this time is to skip permission verification, then change the root password, and then enable permission verification. Taking MySQL 5.7 as an example, let’s briefly talk about the main process:

First, modify the configuration file and add a sentence in the [mysqld] section: skip-grant-tables. The purpose of adding this parameter is to skip permission verification. Then restart the database. After the database is started again, we can log in to the database directly without a password to change the password.

# Modify root password in skip-grant-tables mode [root@host ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update mysql.user set authentication_string = password ('xxxxxx') where user = 'root' and host = 'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

After changing the root password, remove the skip-grant-tables parameter again and restart the database.

2. Several ways to change passwords

In addition to forgetting your password, there may be other situations where you need to change your password. At this time, you can use the normal method to change your password. Still taking MySQL 5.7 as an example, we will introduce several commonly used methods for changing passwords.

Use alter user to modify

For example, if we want to change the password of the testuser account, we can log in using the root account and then execute the alter user command to change the password of the testuser account.

mysql> alter user 'testuser'@'%' identified by 'Password1';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Using the SET PASSWORD command

Use SET PASSWORD to modify the password. The command format is SET PASSWORD FOR 'username'@'host' = PASSWORD('newpass'); Similarly, the root account can be used to modify the passwords of other accounts.

mysql> SET PASSWORD FOR 'testuser'@'%' = PASSWORD('Password2');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Use mysqladmin to change the password

Use the mysqladmin command to modify the account password format: mysqladmin -u username -p old password password new password

[root@host ~]# mysqladmin -utestuser -pPassword2 password Password3
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@host ~]# mysql -utestuser -pPassword3
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2388
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Directly update the user table

In fact, all MySQL account information is stored in the mysql.user table, and we can also modify the password directly by updating the user table.

# 5.7 and later versions mysql> update mysql.user set authentication_string = password ('Password4') where user = 'testuser' and host = '%';
Query OK, 1 row affected, 1 warning (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

# 5.6 and earlier versions update mysql.user set password=password('new password') where user='user name' and host='host'; 

3. Set login-path for local quick login

In order to prevent password exposure or forgetting, we can also set login-path to achieve quick login without entering password locally.

Login-path is a new feature supported by MySQL 5.6. By using the mysql_config_editor tool, the authentication information for logging into the MySQL service is encrypted and saved in the .mylogin.cnf file (located in the user's home directory by default). The MySQL client tool can connect to MySQL by reading the encrypted file to achieve quick login.

Suppose we want to configure the root account to log in locally quickly, we can do this:

# After pressing Enter, you need to enter the root password once [root@host ~]# mysql_config_editor set --login-path=root -uroot -hlocalhost -p -P3306 
Enter password: 

# After configuration, you can use login-path to log in [root@host ~]# mysql --login-path=root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2919
Server version: 5.7.23-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Summarize:

This article mainly introduces several methods of modifying database account passwords, basically covering all scenarios. Here I would also like to remind everyone that it is best to limit database account logins to IP segments, make passwords as complex as possible, and modify them regularly, especially in important environments. You cannot be careless at all. It's the end of the year, and safety is king.

This is the end of this article about the complete method of changing MySQL account password (summary). For more relevant content about changing MySQL account password, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!