Troubleshooting Remote Connections to MySQL on Ubuntu

Facing difficulties establishing remote connections to your MySQL server can be frustrating. If you've attempted various solutions without success, this guide offers comprehensive troubleshooting steps to help you resolve this issue.

Configuring bind-address

To allow connections from other machines, ensure that the bind-address line in your MySQL configuration file is uncommented and set to your computer's IP address, not the loopback interface.

For MySQL 5.6 and below:

Locate /etc/mysql/my.cnf and edit the #bind-address = 127.0.0.1 line to your IP address.

For MySQL 5.7 and above:

Edit /etc/mysql/mysql.conf.d/mysqld.cnf and change #bind-address = 127.0.0.1 to your IP address.

You can also set bind-address = 0.0.0.0 to accept connections from any IP.

Restarting MySQL

Once you've updated the configuration, restart MySQL to apply the changes:

systemctl restart mysql

Creating Remote Users

To grant privileges to remote users, you need to create the same user in both the localhost and '%' domains.

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

Granting Privileges

Grant appropriate privileges to the remote user:

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

Refreshing Privileges

Execute the following commands to ensure the privileges take effect:

FLUSH PRIVILEGES;
EXIT;

Verifying Remote Connectivity

To verify if remote connections are working correctly, try logging in from a remote machine or use the lsof command to check if MySQL is listening on the configured IP.

lsof -i -P | grep :3306

If the command returns the correct IP address and port, remote connections should be allowed.

Additional Troubleshooting

If issues persist, you may need to check the following:

• Ensure the user has the necessary privileges in both the localhost and % domains.
• Check if the MySQL firewall is configured to allow remote connections.
• Review the my.cnf configuration file for any errors or missing settings.
• If all else fails, consult the official MySQL documentation or seek help from the MySQL community.

The above is the detailed content of How to Troubleshoot Remote MySQL Connections on Ubuntu?. For more information, please follow other related articles on the PHP Chinese website!