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.
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
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
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';
And exit the console
As simple as that.
Enabling remote access to MySQL is essential in many situations and although it is disabled for security reasons, we can always change it.