Skip to content

MySQL root user remote access configuration

To enable remote access for the root user in MySQL, the default host restriction must be changed. By default, the root user is typically configured to accept connections only from localhost.^[600-developer__database__mysql__mysql8-portable.md]

Procedure

  1. Connect to MySQL: Log in to the MySQL server as root.^[600-developer__database__mysql__mysql8-portable.md]
  2. Switch to the mysql database: Execute the command use mysql; to operate on the system tables containing user privileges.^[600-developer__database__mysql__mysql8-portable.md]
  3. Review current permissions: Verify the current User and Host settings by running SELECT User, Host FROM mysql.user;.^[600-developer__database__mysql__mysql8-portable.md]
  4. Update the host: Execute the update command to change the root user's host from localhost to % (which allows connections from any host).^[600-developer__database__mysql__mysql8-portable.md]
  5. Flush privileges: Run FLUSH PRIVILEGES; to reload the grant tables and apply the changes immediately.^[600-developer__database__mysql__mysql8-portable.md]
  6. Set authentication: Use the ALTER USER command to update the authentication plugin and password.^[600-developer__database__mysql__mysql8-portable.md]
  7. Restart the service: Restart the MySQL service to ensure all changes take effect.^[600-developer__database__mysql__mysql8-portable.md]

Commands

USE mysql;
SELECT User, Host FROM mysql.user;
UPDATE mysql.user SET host='%' WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

Authentication Configuration

When configuring remote access, it may be necessary to specify the authentication plugin. The command ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; explicitly sets the plugin to mysql_native_password and assigns the password.^[600-developer__database__mysql__mysql8-portable.md] This configuration can also be defined globally in the my.ini configuration file using the default_authentication_plugin directive.^[600-developer__database__mysql__mysql8-portable.md]

Sources

^[600-developer__database__mysql__mysql8-portable.md]