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

Remote Connection Issues with MySQL on Ubuntu

Despite granting remote access privileges to a user, users face difficulties connecting remotely to their MySQL server. This article addresses these issues and provides comprehensive solutions.

Troubleshooting Remote Connection Errors

One common issue is forgetting to uncomment the bind-address line in the MySQL configuration file. For MySQL versions 5.6 and below, this line is found in /etc/mysql/my.cnf, while for versions 5.7 and above, it is in /etc/mysql/mysql.conf.d/mysqld.cnf. Uncomment the line and assign it to the server's IP address or 0.0.0.0 for unrestricted access.

Checking MySQL Connections

To verify that MySQL is listening on the correct IP address, run

lsof -i -P | grep :3306

This should display the listening port with the server's IP address. If this fails, ensure MySQL is indeed listening on the specified IP address.

Granting Remote Access Privileges

Even with the correct bind-address, remote users may still face access issues. To resolve this, the user must be created with identical privileges for both localhost and %. For example:

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

Next, grant the user all privileges on *.*:

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

Additional Troubleshooting

If lsof does not work, consider installing it for your specific Linux distribution. Additionally, check the configuration file /etc/mysql/mariadb.conf.d/50-server.cnf for any conflicting bind-address settings. By implementing these solutions, users can establish successful remote connections to their MySQL server on Ubuntu.

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