How to grant MySQL remote access

By default, MySQL is installed only listening to localhost. It’s closed so that it won’t listen to your public ips externally. This tutorial works both for Ubuntu, Debian and CentOS. To grant mysql remote access, we need to first of all open the MySQL to listen to external connections, and then, enable the user for remote networks.

Step 1 – Allow incomming external connections to the MySQL server

Before grant mysql remote access, it’s necessary to open the server port 3306.

First, we will update our system and install Nmap. This utility shows the current opened ports in the server.

In Ubuntu/Debian:

In CentOS:

Now we will run nmap to make sure mysql isn’t listening in our public ip. Make sure to replace [server-ip] with your public server IP.

The output should be something like:

As we can see, there is no server daemon opened on port 3306, and we need to configure MySQL to listen in our public IP.

Let’s edit our my.cnf file

Now we you must find this line:

And replace with:

Also, make sure this line is commented (with a # in the beginning):

This will tell MySQL to listen in both localhost (127.0.0.1) and in the public IP of the server.

After finishing, type CTRL+X to exit, and Y to save.

Now you must restart the MySQL server. This restart process may be different according to your linux distro.

In Debian 7 and older

In Ubuntu 14 and newer

In CentOS 6.x and older:

In CentOS 7.x and Debian 8:

Let’s run nmap again to confirm if the port 3306 is opened in the public IP:

We can see 3306/tcp open mysql, which means that the MySQL port is opened and waiting for connections.

To test, we will use HeidiQSL, which is a freeware Windows MySQL client, and we will try to connect to our server:

grant mysql remote access 1

We will get an error message, because we haven’t granted the privileges to the user to access remotely yet.

grant mysql remote access 2

Step 2 – Grant remote access to MySQL users

To solve this issue, we must grant the remote access privileges to our myqsl user. You must open connect to mysql locally:

Now we will grant the privileges. Please note that even if the username is already configured for the localhost access, we will have to set a new password for it to connect remotely. You must replace [user-name] and [new-password] in this command.

This command will ensure all the IP addresses can connect to the server using this username and password. We will explain after how to limit and restrict the access to certain IPs or networks.

Now you should be able to connect to the remote MySQL server using the configured username and password.

grant mysql remote access 3

 

Grant remote access to specific IPs or networks

To restrict the IP or network, you should replace the % (wildcard) with the IP or block.

Examples:

This will only allow connections from the IP 177.85.98.15

This will allow connections from the IP range  177.85.98.1  to  177.85.98.254

Now, this example grant mysql remote access to any hostname ending in *.myprovider.com

To allow access from any network, without restriction, you should keep only the % (wildcard). This will grant mysql remote access to any IP or network.

Grant mysql remote access to specific MySQL databases and tables

To restrict the access to a specific database or table, you should replace the *.* with: [database].[table]

This allows the access only to the mydb database, with any table:

This command grants access only to the table clients, inside the mydb database:

If you need to grant access specifically to more than one table, you should run the command more than once:

Grant option

You can also allow such user to be able to dellegate and grant privileges to other users, by adding WITH GRANT OPTION to the end of the command.

It would look like:

If you need to recover the mysql user password, you can also read this article.

Leave a Reply

Your email address will not be published. Required fields are marked *