what is mysql root

Root refers to the super administrator; when MySQL is installed, a user named root will be created by default. This user has super permissions and can control the entire MySQL server. The root user has very high permissions and can not only change his own password, but also change the passwords of other users.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, root refers to the super administrator, and the system will default to a super administrator.

When MySQL is installed, a user named root will be created by default. This user has super privileges and can control the entire MySQL server.

In the daily management and operation of MySQL, in order to prevent someone from maliciously using the root user to control the database, we usually create some users with appropriate permissions and use the root user as little or as little as possible to log in to the system. to ensure secure access to data.

Generally, the root super administrator has much greater permissions than ordinary users, so some operations require root permissions to run.

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

MySQL changes root password

In MySQL, the root user has very high permissions, so the security of the root user password must be ensured.

Modify the user table of the MySQL database

Because all account information is stored in the user table, you can change the root user's password directly by modifying the user table.

After the root user logs in to the MySQL server, you can use the UPDATE statement to modify the authentication_string field of the user table of the MySQL database to modify the user's password.

The syntax format for using the UPDATA statement to modify the root user password 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 executing the UPDATE statement, you need to execute the FLUSH PRIVILEGES statement to reload user permissions.

Example

The following uses the UPDATE statement to change the password of the root user to "rootpwd2".

After logging in to the MySQL server as the root user, the SQL statement and running 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:0
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.06 sec)

The results show that the password has been changed successfully. And the FLUSH PRIVILEGES; statement is used to load permissions. After logging out, you must log in with a new password.

Use the SET statement to modify the password of the root user

The SET PASSWORD statement can be used to reset the login password of other users or the password of the account you use. The syntax structure of using the SET statement to change the password is as follows:

SET PASSWORD = PASSWORD ("rootpwd");

Example

The following uses the SET statement to change the password of the root user to "rootpwd3".

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

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

The results show that the SET statement was successfully executed and the root user's password was successfully set to "rootpwd3" .

MySQL root changes the password of ordinary users

Use the SET statement to change the password of ordinary users

In MySQL, only the root user can update the MySQL database to change your password. After logging in to the MySQL server as the root user, you can use the SET statement to modify the ordinary user password. The syntax format is as follows:

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

Among them, the username parameter is the user name of an ordinary user, the hostname parameter is the host name of an ordinary user, and newpwd is the new password to be changed.

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

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

SET PASSWORD = PASSWORD('newpwd');

Example 1

First create a testuser user without a password. The SQL statement and running results are as follows:

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

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

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

It can be seen from the running results that the SET statement is executed successfully and the password of the testuser user is successfully set to "newpwd" ".

The following verifies whether the password of the testuser user has been modified successfully. Exit the MySQL server, log in as the testuser user, and enter the password "newpwd". The SQL statement and running results are as follows:

C:\Users\leovo>mysql -utestuser -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server 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 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.

It can be seen from the running results that the testuser user logged in successfully and changed the password successfully.

Example 2

Use the testuser user to log in to the MySQL server, and then use the SET statement to change the password to "newpwd1". The SQL statement and running results are as follows:

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

By It can be seen from the running results that the password was changed successfully.

Use the UPDATE statement to modify the password of an ordinary user

After logging in to the MySQL server as the root user, you can use the UPDATE statement to modify the authentication_string field of the user table of the MySQL database, thereby modifying Password for normal users. The syntax of the UPDATA statement is as follows:

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

Among them, the username parameter is the user name of an ordinary user, the hostname parameter is the host name of an ordinary user, and newpwd is the new password to be changed.

Note that after executing the UPDATE statement, you need to execute the FLUSH PRIVILEGES statement to reload user permissions.

Example 3

使用 root 用户登录 MySQL 服务器，再使用 UPDATE 语句将 testuser 用户的密码修改为“newpwd2”的 SQL 语句和运行结果如下：

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: 1
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

由运行结果可以看出，密码修改成功。testuser 的密码被修改成了 newpwd2。使用 FLUSH PRIVILEGES 重新加载权限后，就可以使用新的密码登录 testuser 用户了。

使用 GRANT 语句修改普通用户密码

除了前面介绍的方法，还可以在全局级别使用 GRANT USAGE 语句指定某个账户的密码而不影响账户当前的权限。需要注意的是，使用 GRANT 语句修改密码，必须拥有 GRANT 权限。一般情况下最好使用该方法来指定或修改密码。语法格式如下：

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

其中，username 参数是普通用户的用户名，hostname 参数是普通用户的主机名，newpwd 是要更改的新密码。

示例 4

使用 root 用户登录 MySQL 服务器，再使用 GRANT 语句将 testuser 用户的密码修改为“newpwd3”，SQL 语句和运行结果如下：

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

由运行结果可以看出，密码修改成功。

【相关推荐：mysql视频教程】

The above is the detailed content of what is mysql root. For more information, please follow other related articles on the PHP Chinese website!