MySQL remote root access configuration¶

Configuring remote root access for MySQL involves modifying the user's host permissions and ensuring compatibility with the authentication plugin. By default, the root user is often restricted to localhost, requiring explicit updates to the mysql.user table to accept connections from other hosts (%).^[600-developer-database-mysql-mysql8-portable.md]

Configuration Steps¶

Update Host Permissions: Access the MySQL shell and use the mysql database. Run the update command to change the root user's host from localhost to % (wildcard).^[600-developer-database-mysql-mysql8-portable.md]
```
UPDATE mysql.user SET host='%' WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;
```
Alter User Authentication: MySQL 8.0 defaults to caching_sha2_password. To ensure compatibility with older clients or specific configurations, alter the user to use mysql_native_password and set a password.^[600-developer-database-mysql-mysql8-portable.md]
```
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
```
Server Configuration: Ensure the my.ini configuration file includes the directive default_authentication_plugin=mysql_native_password to align the server's default plugin with the user's settings.^[600-developer-database-mysql-mysql8-portable.md]

Sources¶

^[600-developer-database-mysql-mysql8-portable.md]