How to enable remote access on MySQL 8?

Hello, friends. This short post will help you to enable remote access to MySQL. Although this is not always recommended, it can be helpful.

By default, and for security reasons, MySQL remote access is disabled, and you will only be able to access using Localhost. In most cases, this will suffice, but it is not always the case.

A practical case is that some developers use virtual machines to do their testing and require a remote connection to it. Furthermore, if you need to manage the database remotely, it is also useful.

In any case, it is always good to know and have it handy to use.

Enabling remote access to MySQL

This post assumes that you already have MySQL installed. As this database manager is so popular, we have prepared many posts about it.

For example, you can install it on CentOS 9 or Ubuntu 22.04 and the procedure is simple.

To enable remote access to MySQL, we just need to modify the configuration file and add or modify a specific field.

In the case of Debian and its derivatives, the file is this /etc/mysql/mysql.conf.d/mysqld.cnf.

So, you can make a backup of it before you start editing it.

sudo cp /etc/mysql/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

Now, edit it with your favorite editor

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Locate the bind-address directive and change its value to the IP address of the server or using a wildcard you can make it allow traffic from any address

bind-address = 0.0.0.0.0

Save the changes and close the editor. This is enough.

To apply the changes, just restart MySQL.

sudo systemctl restart mysql

Configuring users to remotely access MySQL

The fact that MySQL can already receive remote connections does not imply that users have permissions to do so. It is therefore necessary to allow this access.

To achieve this, you have two ways: modify the existing user(s) or create a new user with specific permissions. Normally, you would use the second option.

To modify a created user to allow remote access, you need to update a configuration of the user in the MySQL server.

First, access the MySQL console

sudo mysql -u root -p

And then, run

RENAME USER 'user'@'localhost' TO 'user'@'server-ip';

Then, you can exit the console. It’s that simple.

The second and more recommended option is to create a new user for remote access with specific (or all) permissions on one or more databases.

Go back to the MySQL console and create it

CREATE USER 'user'@'server-ip' IDENTIFIED BY 'password';

Then assign the appropriate permissions on the database. As you know, there are a lot of them, but in this case, I will assign them all.

GRANT ALL PRIVELEGES on *.* TO 'user'@'server-ip' WITH GRANT OPTION';

Refresh permissions

FLUSH PRIVILEGES;

And exit the console

exit

As simple as that.

Conclusion

Enabling remote access to MySQL is essential in many situations and although it is disabled for security reasons, we can always change it.