How to Allow Root Access from All Hosts in MySQL on a Remote Ubuntu Machine?

Allowing Root Access from all Hosts in MySQL

You've encountered an issue when attempting to grant root access from all hosts on the internet for MySQL on a remote Ubuntu machine. The initial attempt to add a row with 'host = %' in the mysql.user table hasn't resolved the connectivity issue from your personal PC.

To resolve this, there are two crucial steps involved:

1. Granting Privileges

As the root user, execute the following command, replacing 'password' with your current root password:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

2. Binding to All Addresses

To bind MySQL to all interfaces and allow connections from all hosts, comment out the following line in the /etc/mysql/my.cnf (or /etc/mysql/mysql.conf.d/mysqld.cnf for Ubuntu 16):

#bind-address = 127.0.0.1

Restart MySQL using:

service mysql restart

By default, MySQL binds only to localhost, but commenting out this line instructs it to bind to all available interfaces and accept connections from all hosts.

To verify the binding, execute as root:

netstat -tupan | grep mysql

This will display the addresses and ports MySQL is listening on. Ensure that it includes a line with '0.0.0.0:3306' or '[::]:3306', indicating that MySQL is listening on all IPv4 and IPv6 addresses (or '*' if it's commented out).

The above is the detailed content of How to Allow Root Access from All Hosts in MySQL on a Remote Ubuntu Machine?. For more information, please follow other related articles on the PHP Chinese website!