Introduction to three ways to change passwords in MySQL (code examples)

This article brings you an introduction to the three ways to change passwords in MySQL (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. Helps.

The following methods are available

(Recommended course: MySQL Tutorial)

The first method:

The easiest way is to use the third-party tool Navicat for MySQL to modify it. The method is as follows:

1. Log in to mysql to the specified library, such as: log in to the test library.

#2. Then click the "User" button above.

#3. Select the user name you want to change, and then click the "Edit User" button above.

#4. The interface as shown below appears, enter the new password, confirm the new password, and click the "Save" button.

Second method:

Method 1: Use the SET PASSWORD command

First log in to MySQL.

Format: mysql> set password for username@localhost = password('new password');

Example: mysql> set password for root@localhost = password('123');

Method 2: Use mysqladmin

##Format: mysqladmin -u username -p old password password new password (Note: There is no space between -p and the old password, and there is a space between password and the new password)

Example: mysqladmin -uroot -p123456 password 123

Method 3: Use UPDATE to directly edit the user table

First log in to MySQL.

mysql> use mysql;  
mysql> update user set password=password('123') where user='root' and host='localhost';  
mysql> flush privileges;

Method 4: When you forget the root password, you can do this

Take windows as an example:

##1. Close the running MySQL service.

2. Open the DOS window and go to the mysql\bin directory.

3. Enter mysqld --skip-grant-tables and press Enter. --skip-grant-tables means to skip the permission table authentication when starting the MySQL service.

4. Open another DOS window (because the DOS window just now cannot be moved), go to the mysql\bin directory.

5. Enter mysql and press Enter. If successful, the MySQL prompt > will appear.

#6. Connect to the permission database: use mysql;.

#6. Change password: update user set password=password("123") where user="root"; (don’t forget to add the semicolon at the end) .

#7. Refresh privileges (required step): flush privileges;.

#8. Quit.

9. Log out of the system, re-enter, and log in using the user name root and the new password 123 you just set.

The third way:

1. Open the location where mysql.exe and mysqld.exe are located Folder, copy path address

2. Open the cmd command prompt and enter the folder where mysql.exe is located in the previous step.

3. Enter the command mysqld --skip-grant-tables and press Enter. At this time, the user verification of mysql is skipped. Note that the command line cannot be operated after entering this command. At this time, you can open a new command line. Note: Before entering this command, end the mysqld.exe process in the task manager to ensure that the mysql server has ended.

#4. Then enter mysql directly. You can log in to the database by pressing Enter without any login parameters.

5. Enter show databases; You can see all database instructions for successful login.

The mysql library is where the user name is saved. Enter use mysql; to select the mysql database.

show tables View all tables and you will find that there is a user table, which stores account information such as user names, passwords, permissions, etc.

Enter select user,host,password from user; to view account information.

To change the root password, enter update user set password=password('123456') where user='root' and host='localhost';

Check the account information again, select user,host,password from user; You can see that the password has been modified.

Exit the command line, restart the mysql database, and try to log in with a new password.

Tested to log in to mysql without a password, and found that I can still log in, but when displaying the database, I can only see two databases, indicating that the password verification has been skipped after restarting. cancelled.

The reason why I can still log in without a password after restarting the database is because there is an account in my database that does not require a password.

Notes

Pay attention to several places where you need to restart the database

The above is the detailed content of Introduction to three ways to change passwords in MySQL (code examples). For more information, please follow other related articles on the PHP Chinese website!