SoFunction
Updated on 2025-03-04

Detailed explanation of how MySQL creates a root account that can be accessed remotely

Create a user

The default root user can only access localhost on the current node and cannot be accessed remotely. Therefore, we need to create a root account to help users access remotely.

create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234';

This command is used to create a new user's SQL statement in the MySQL database management system.

Let's take a look at the various components of the command in detail:

  • CREATE USER: This is the beginning of the SQL statement, which tells MySQL that we want to create a new user.

  • 'root'@'%': This section defines the username of the new user and where it can connect to the MySQL server.

    • 'root': This is the username of the new user. In MySQL,rootUsually a superuser with full access to all databases and tables.
    • '%': This means that the user can connect to the MySQL server from any IP address. If we want to restrict users from only connecting from a specific IP address or domain name, we can%Replace it with a specific IP address or domain name.

  • IDENTIFIED WITH mysql_native_password: This section specifies a plug-in or mechanism for authenticating users.

    • mysql_native_password: This is an authentication method that tells MySQL to use traditional methods to store and verify user passwords. This is the standard authentication method for older versions in MySQL.

  • BY '1234': This part sets the user's password.

    • '1234': This is the user's password. In practical situations, stronger passwords should be used to improve security.

To sum up, this command creates arootA new user, this user can connect to the MySQL server from any IP address and usemysql_native_passwordAs an authentication method, its password is set to1234。

Note that using root users and weak passwords (such as '1234' in the example) can pose security risks. In production environments, it is recommended to use a more secure username and strong password and limit the user's connection IP address for enhanced security. Additionally, newer versions of MySQL may use different default authentication plugins (e.g.caching_sha2_password), so in different versions of MySQL, the specific format of the command may vary.

In MySQL 8.0 version, the command to create a user is slightly different, mainly because the default authentication plugin is frommysql_native_passwordChange tocaching_sha2_password. The following are the modified commands for MySQL 8.0:

CREATE USER 'root'@'%' IDENTIFIED WITH 'caching_sha2_password' BY '1234';

Let's take a look at the various components of the command in detail:

  • CREATE USER: This is the beginning of the SQL statement to notify MySQL that we are creating a new user.

  • 'root'@'%': Defines the username of the new user and where it can connect to the MySQL server.

    • 'root'It is the user name of the new user. In MySQL,rootUsually a superuser with all permissions.
    • '%'Indicates that the user can connect to the MySQL server from any IP address. If you want to restrict users from only being able to connect from specific IP addresses or domain names, you can%Replace it with a specific IP address or domain name.

  • IDENTIFIED WITH 'caching_sha2_password': Specifies the plugin for authentication.

    • 'caching_sha2_password'is the default authentication plugin in MySQL 8.0, which provides a bettermysql_native_passwordHigher security.

  • BY '1234': Set the user's password.

    • '1234'It is the user's password. In actual environments, stronger passwords should be used to improve security.

This command creates a name calledrootnew user, allowing it to connect to the MySQL server from any IP address, usingcaching_sha2_passwordAs an authentication method, the password is set to1234。

Please note that for safety reasons, it is not recommended to use it.rootUsername or weak password (such as ‘1234’ in the example), especially in production environments. At the same time, it should be considered to limit the user's connection source address to enhance security. Depending on our MySQL configuration and requirements, it may be necessary to enable or configurecaching_sha2_passwordPlugins to ensure compatibility and security.

Assign permissions to root users

GRANT ALL ON *.* TO 'root'@'%';

This SQL command is a common command used for authorization in MySQL databases. Let's analyze the various parts of this command one by one:

  • GRANT ALL: This part is the core of the command.GRANTis a SQL command used to grant user permissions.ALLIndicates that all permissions are granted. This means that after executing this command, the user will gain all operational permissions on the database, including the ability to create, read, update, delete data, and manage the database.

  • ON *.*: This section specifies the scope of application of permissions.

    • The first one*Represents all databases.
    • The second one*Represents all tables in the database.
    • so,*.*Indicates granting permissions to all databases and all tables in them.

  • TO 'root'@'%': This section specifies which user to grant permissions and where that user can connect to the MySQL server.

    • 'root'It is the user's user name, here is the super user of MySQL.
    • '%'Indicates that the user can connect to the MySQL server from any IP address. If you need to restrict users from only connecting from a specific IP address or domain name, you can%Replace it with a specific IP address or domain name.

To sum up, orderGRANT ALL ON *.* TO 'root'@'%';The meaning of   is: Grant the user name asroot�

Please note that this kind of authorization is very wide and will give users a very high level of permissions, including modifying the database structure, accessing and modifying all data, managing user permissions, etc. In production environments, such permissions are usually only granted to very trusted administrators, as it can lead to security risks, especially whenrootUsers can connect from anywhere. It is generally recommended to assign the user the minimum necessary permissions as needed to reduce potential security risks.

Consider security issues

Security is crucial when creating remote users and setting permissions. Give some suggestions to ensure the security of the database:

  • Strong password strategy: Ensure that you set up complex and difficult-to-guess passwords for users. Avoid using simple words, numbers, or common combinations.
  • Restrict access scope: Try not to use the %wildcard character to allow connections from any host. Instead, explicitly specify the host name or IP address that allows connections.
  • Minimum permission principle: Grant the minimum permissions required to the user. Avoid granting unnecessary permissions to reduce potential security risks.
  • Regular review permissions: Regularly review user permissions to ensure that no unnecessary permissions are granted. If you find permissions that are no longer needed, they should be revoked in time.
  • Using a firewall: Use a firewall between a MySQL server and a remote client to limit access to the MySQL port. Only the necessary IP addresses or IP address ranges are allowed to access the MySQL port.
  • Enable SSL connection: Enable SSL connection if possible to increase security of data transfer. This prevents man-in-the-middle attacks and stealing sensitive data.

Summarize

This is the article about how MySQL creates a root account that can be accessed remotely. For more information about MySQL creating a root account, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!