mysql set root

MySQL is a commonly used database management system that provides many powerful tools and applications to support various complex applications. In MySQL, root permission is a very important permission, which has the right to access and manage all databases. Therefore, protecting the root user is an important part of MySQL security. In this article, we will discuss how to correctly set the password and permissions of the root user to protect the security of MySQL.

1. Set the root user password

In MySQL, the root user is the default administrator account. If you are using a new MySQL installation, the root user does not have a password. This is a security risk because anyone can access the MySQL database using the root user. Therefore, we first need to set the root user's password.

Open the terminal and log in to MySQL as root:

$ mysql -u root

Then, use the following command to set the password of the root user:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

Here, 'new_password' is what you want to set new password. After the execution is completed, use the following command to refresh the permissions:

mysql> FLUSH PRIVILEGES;

Now, the password of the root user has been set.

2. Restrict root user login

In order to protect the security of the MySQL server, we can restrict the root user to only log in on a specific IP address. Specifying the IP addresses that allow root user login ensures that only trusted people can use the root user to log in to the MySQL server.

Open the MySQL configuration file my.cnf and find the following line:

bind-address = 127.0.0.1

Replace 127.0.0.1 with the IP address that you allow the root user to log in to MySQL. For example, if you want to allow the root user to log into MySQL only on the computer with IP address 192.168.0.100, you can replace the above line with:

bind-address = 192.168.0.100

Then, save and close the my.cnf file, and re- Start the MySQL server.

Now you can only log in to MySQL using the root user on the specified IP address.

3. Restrict the permissions of the root user

In order to protect the security of MySQL, we can limit the permissions of the root user to specific databases or tables. This way, even if the root user's password is compromised, hackers won't be able to access your sensitive data.

In MySQL, we can use the GRANT and REVOKE commands to assign or revoke user permissions. For example, the following command allows the root user to perform all operations in the test database:

mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost';

This will allow the root user to create, delete tables, insert, update, delete rows, and perform all other operations in the test database. If you want to have tighter control over the root user's permissions, you can use the GRANT and REVOKE commands to grant and revoke specific permissions, respectively.

For example, the following command only allows the root user to insert, update, and delete rows in the test database:

mysql> GRANT INSERT, UPDATE, DELETE ON test.* TO 'root'@'localhost';

Or, the following command cancels all permissions of the root user in the test table:

mysql> REVOKE ALL PRIVILEGES ON test.* FROM 'root'@'localhost';

By limiting the permissions of the root user, we can make MySQL more secure and maintainable.

Conclusion

In MySQL, the root user is a very important administrator account. In order to protect the security of the MySQL server, we need to set the root user's password and restrict the root user's login and permissions. This will help us prevent hackers from breaking into the MySQL server and protect our sensitive data from being compromised. If you are running a MySQL database, I recommend that you always follow the above steps to secure your system.

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