How to Enable Remote MySQL Connections on Ubuntu and Resolve 'Access Denied' Errors?

Remote MySQL Connections on Ubuntu

Issue:

Despite attempts to establish remote connections, MySQL server remains inaccessible with error message "Access denied for user".

Resolution:

Step 1: Configure MySQL Binding Address

In both MySQL versions 5.6 and below and 5.7 and above, uncomment the bind-address parameter in the corresponding configuration file:

• Version 5.6 and below: /etc/mysql/my.cnf
• Version 5.7 and above: /etc/mysql/mysql.conf.d/mysqld.cnf

Assign the bind-address to your computer's IP address (replacing xxx with the actual address):

bind-address = xxx.xxx.xxx.xxx

Alternatively, use 0.0.0.0 to expose MySQL to all interfaces.

Step 2: Verify MySQL Binding

Restart MySQL and verify the binding by running:

lsof -i -P | grep :3306

The result should display your IP address bound to port 3306, confirming that MySQL is accessible remotely.

Step 3: Create Remote User

Establish remote connectivity requires a user with access privileges on both localhost and %. Thus, create the user in both domains:

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

Step 4: Grant Privileges

Grant the necessary privileges to the remote user:

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

Step 5: Flush Privileges

Finally, flush the privilege changes and exit:

FLUSH PRIVILEGES;
EXIT;

With these steps, you should be able to establish remote connections securely to your MySQL server. Remember, verify the configuration settings and correct privileges to prevent any potential security risks.

The above is the detailed content of How to Enable Remote MySQL Connections on Ubuntu and Resolve 'Access Denied' Errors?. For more information, please follow other related articles on the PHP Chinese website!