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

This article mainly introduces what mysql root means. The introduction in this article is very detailed and has certain reference value. Interested friends must read it!

Root refers to the super administrator;MySQL is installed by default to create a user named root, who has super privileges and can control the entire MySQL server. The root user has very high privileges and can modify not only his own password, but also the passwords of other users.

Operating environment of this tutorial: Windows 7 system, MySQL8 version, Dell G3 computer.

In mysql, root means super administrator, and the system defaults to a super administrator.

MySQL is installed with a default user named root, who has super privileges and can control the entire MySQL server.

In the daily management and operation of MySQL, in order to avoid malicious use of root user control database, we usually create some users with appropriate permissions, as far as possible do not use root user login system, in order to ensure safe access to data.

The general root super administrator privileges will be much greater than the privileges of ordinary users, so some operations need root privileges to run.

The root user has very high privileges and can modify not only his own password, but also the passwords of other users.

MySQL Change root password

In MySQL, the root user has very high privileges, so the root password must be secured.

Modify MySQL database user table

Because all account information is stored in the user table, the root password can be changed directly by modifying the user table.

After the root user logs on to the MySQL server, he or she can modify the user password by modifying the authentication_string field of the user table of the MySQL database using the UPDATE statement.

The syntax for modifying the root password using the UPDATA statement is as follows:

UPDATE mysql.user set authentication_string = PASSWORD ("rootpwd) WHERE User = "root" and Host="localhost";

The new password must be encrypted using the PASSWORD() function. After the UPDATE statement is executed, the FLUSH PRIVILEGES statement needs to be executed to reload user privileges.

example

Change the password of root to "rootpwd2" using UPDATE.

After logging in to MySQL server as root user, SQL statements and results are as follows:

mysql> UPDATE mysql.user set authentication_string = password ("rootpwd2") -> WHERE User = "root" and Host = "localhost";Query OK, 1 row affected, 0 warning (0.00 sec)Rows matched: 1 Changed: 1 Warnings:0mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.06 sec)

The result showed that the password modification was successful. And the FLUSH PRIVILEGES; statement load permission is used. After logging out, you must log in with a new password.

Change the password of root user using SET statement

The SET PASSWORD statement can be used to reset the login password of another user or the password of an account used by oneself. The syntax for modifying passwords using SET statements is as follows:

SET PASSWORD = PASSWORD ("rootpwd");

example

Change the password of root to "rootpwd3" using the SET statement.

After logging in to MySQL server as root user, SQL statements and results are as follows:

MySQL> SET PASSWORD = password ("rootpwd3");Query OK, 0 rows affected (0.00 sec)

The result shows that the SET statement was executed successfully and the password of root user was successfully set to "rootpwd3."

MySQL root Changing the password of a normal user

Use SET statement to modify password of normal user

In MySQL, only root can change passwords by updating the MySQL database. After logging in to MySQL Server as root user, you can use SET statement to modify normal user password. The syntax format is as follows:

SET PASSWORD FOR 'username'@'hostname' = PASSWORD ('newpwd');

where the username parameter is the username of the normal user, the hostname parameter is the hostname of the normal user, and newpwd is the new password to change.

Note: The new password must be encrypted using the PASSWORD() function. If it is not encrypted using PASSWORD(), it will succeed, but the user will not be able to log in.

If you are an ordinary user changing your password, you can omit the FOR clause to change your password. The syntax format is as follows:

SET PASSWORD = PASSWORD('newpwd');

Examples 1

First create a testuser user without password, SQL statement and run results as follows:

mysql> CREATE USER 'testuser'@'localhost';Query OK, 0 rows affected (0.14 sec)

After the root user logs in to MySQL server, use SET statement to change the password of testuser user to "newpwd". SQL statement and running result are as follows:

mysql> SET PASSWORD FOR 'testuser'@'localhost' = PASSWORD("newpwd");Query OK, 0 rows affected, 1 warning (0.01 sec)

As can be seen from the running results, SET statement execution succeeds, testuser password is successfully set to "newpwd".

Verify that the testuser password has been modified successfully. Log out of MySQL server, log in as testuser, enter password "newpwd", SQL statement and run result as follows:

C:\Users\leovo>mysql -utestuser -pEnter password: ******Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 15Server version: 5.7.29-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

As can be seen from the running results, testuser successfully logged in and changed password successfully.

Example 2

Log in to MySQL server using testuser and change password to "newpwd1" using SET statement. SQL statement and running result are as follows:

mysql> SET PASSWORD = PASSWORD('newpwd1');Query OK, 0 rows affected, 1 warning (0.00 sec)

As can be seen from the running results, the password modification was successful.

Use UPDATE to modify the password of a normal user

After logging in to the MySQL server as root, you can modify the password of a normal user by modifying the authentication_string field of the MySQL database's user table using the UPDATE statement. The syntax of the UPDATA statement is as follows:

UPDATE MySQL.user SET authentication_string = PASSWORD("newpwd") WHERE User = "username" AND Host = "hostname";

where the username parameter is the username of the normal user, the hostname parameter is the hostname of the normal user, and newpwd is the new password to change.

Note that after the UPDATE statement is executed, the FLUSH PRIVILEGES statement needs to be executed to reload user privileges.

Example 3

Log in to MySQL server as root user, and then use UPDATE statement to change the password of testuser user to "newpwd2" SQL statement and run the result as follows:

mysql> UPDATE MySQL.user SET authentication_string = PASSWORD ("newpwd2") -> WHERE User = "testuser" AND Host = "localhost";Query OK, 1 row affected, 1 warning (0.07 sec)Rows matched: 1 Changed: 1 Warnings: 1mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.03 sec)

As can be seen from the running results, the password modification was successful. testuser password changed to newpwd2. After reloading permissions using FLUSH PRIVILEGES, testuser can log in with the new password.

Use the GRANT statement to modify a normal user password

In addition to the methods described above, you can also use the GRANT USAGE statement to specify an account password at the global level without affecting the account's current permissions. It should be noted that to use the GRANT statement to modify the password, you must have GRANT permission. It is generally best to use this method to specify or modify passwords. The syntax format is as follows:

GRANT USAGE ON *.* TO 'user'@'hostname' IDENTIFIED BY 'newpwd';

where the username parameter is the username of the normal user, the hostname parameter is the hostname of the normal user, and newpwd is the new password to change.

Example 4

Log in to MySQL server as root user, and then use GRANT statement to change the password of testuser user to "newpwd3". SQL statement and running result are as follows:

mysql> GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY 'newpwd3';Query OK, 0 rows affected, 1 warning (0.05 sec)

As can be seen from the running results, the password modification was successful.

The above is "mysql root refers to what means" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

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.