Why Can't I Connect to My Remote MySQL Server on Ubuntu?

Remote Connectivity Issues with MySQL on Ubuntu

The inability to establish remote connections to MySQL may stem from a combination of misconfigurations. Here's a detailed explanation to resolve the problem:

my.cnf Configuration

For MySQL version 5.6 and below, ensure that "/etc/mysql/my.cnf" contains the "bind-address" directive and replace the loopback address ("127.0.0.1") with your server's IP address:

# For MySQL version 5.6 and below
bind-address        = your_server_ip

For MySQL version 5.7 and above, modify "/etc/mysql/mysql.conf.d/mysqld.cnf" to specify the bind address:

# For MySQL version 5.7 and above
bind-address        = your_server_ip

Alternatively, set "bind-address = 0.0.0.0" to listen on all IP addresses.

User Privileges

To allow remote access for a specific user, create the user in both "localhost" and "%" in MySQL:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

Grant the necessary privileges to the user:

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

Execute "FLUSH PRIVILEGES;" to update the permissions.

Verifying Network Access

Use "lsof -i -P | grep :3306" to check if MySQL is listening on the correct IP address and port number (3306). This should return an output indicating that the server is listening on your server's IP address.

Troubleshooting

If remote connections still fail, check the following:

• Ensure that your firewall is not blocking incoming connections on port 3306.
• Re-check the bind-address setting in my.cnf and ensure it's not overridden by any other configuration file.
• Make sure that the user and password used for authentication are valid.
• Consider updating MySQL to the latest version.

The above is the detailed content of Why Can't I Connect to My Remote MySQL Server on Ubuntu?. For more information, please follow other related articles on the PHP Chinese website!