MySQL How to allow remote connections in MySQL

By default, when installing MySQL on a server, it will allow only local connections. Additionally, running the web server and the database on the same machine is a common practice for small applications.

However, in order to scale your application, a more distributed approach is needed and one of the simplest things to do is to move the database on its own machine. After doing that, you will have to configure MySQL to allow remote connections.

1. Open mysqld.cnf

Open the mysqld.cnf file using your favorite text editor (we use nano).

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

2. Replace the bind-address

Search for the bind-address option in the opened file. Once you found it, replace its value (127.0.0.1) with 0.0.0.0

In case you don't find a bind-address option, add it at the end of the file.

lc-messages-dir = /usr/share/mysql
skip-external-locking
# 
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

After updating the value, save the changes and close the file.

3. Restart MySQL

For the changes to take effect, you have to restart MySQL.

sudo systemctl restart mysql

4. Create a new user

From now on, your MySQL server will listen for connections coming from anywhere. However, you still need to update the host of an existing user or to create a new one. We will create a new one.

CREATE USER 'weap'@'%' IDENTIFIED BY 'your-password';
GRANT ALL PRIVILEGES ON *.* TO 'weap'@'%';
FLUSH PRIVILEGES;

The user weap will be able to connect to MySQL using any IP address. If you want to allow the connection only from a specific IP address, replace % with the IP address you want to allow, as shown below.

# This user will be able to connect only from the IP address 123.45.67
CREATE USER 'weap'@'123.45.67' IDENTIFIED BY 'your-password';
GRANT ALL PRIVILEGES ON *.* TO 'weap'@'123.45.67';
FLUSH PRIVILEGES;