Why Can't I Remotely Access My MySQL Server on Ubuntu, and How Do I Fix It?

Establish Remote MySQL Connections on Ubuntu

Problem Statement

Remote access attempts to a MySQL server fail with an access denied error, despite granting remote privileges to the user.

Solution

Configure MySQL Bind Address

To accept remote connections in MySQL versions 5.6 and below, ensure the following line is uncommented in /etc/mysql/my.cnf:

#Replace xxx with your IP Address 
bind-address        = xxx.xxx.xxx.xxx

Alternatively, use bind-address = 0.0.0.0 to allow connections from any IP.

For MySQL versions 5.7 and above, modify the bind address in /etc/mysql/mysql.conf.d/mysqld.cnf.

Confirm IP Address Binding

Restart MySQL and verify the updated bind address using lsof -i -P | grep :3306. It should return an IP address for your server.

Create Remote User and Privileges

Create the remote user with appropriate privileges in both localhost and %:

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

Grant the necessary permissions:

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

Finally, flush privileges and exit:

FLUSH PRIVILEGES; 
EXIT;

Verify Remote Access

If unsuccessful, check for additional error messages or review the my.cnf configuration. Consider installing lsof for troubleshooting if it is not available.

Note

If the bind-address change in my.cnf doesn't resolve the issue, try modifying it in /etc/mysql/mariadb.conf.d/50-server.cnf, where it was originally declared.

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