Allow Remote Access to MySQL

mysql_remote_access.md

Allow Remote Access to MySQL

Edit Configurations

First thing that needs to be done is to allow connections from all hosts to MySQL. mysqld.cnf file needs to updated as follows.

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

Update the value to bind-address which by default will be 127.0.0.1. Which means only local host connect to MySQL.

bind-address      =  0.0.0.0

After updating this, restart MySQL server using the following command.

$ sudo systemctl restart mysql

Now, MySQL will allow connections from any host. But yet we need to do some more configurations to actually connect to MySQL from a remote server.

Create User Account

We need an account which will connect to MySQL Server remotely. If already created an account you will need to reconfigure that account to connect from the remote server instead of localhost.

Login to MySQL

$ mysql -u root -p

If you are updating an existing account then,

mysql> RENAME USER 'username'@'localhost' TO 'username'@'remote_ip_address';

Else, if you are creating a new account then,

mysql> CREATE USER 'username'@'remote_ip_address' IDENTIFIED BY 'a_strong_password';

Then run,

mysql> FLUSH PRIVILEGES;
mysql> exit;

Allow Incoming Connections on MySQL Server's Port

By default MySQL runs on port 3306. So we need to allow connections on this port.

$ sudo ufw allow from remote_ip_address to any port 3306

Now you can connect to your MySQL Server from any remote system using:

$ mysql -u username -h mysql_servers_ip_address -p

Reference

1. How To Allow Remote Access to MySQL
2. How To Ubuntu Linux Firewall Open Port Command