Remote access to a MySQL server running on Ubuntu 18

by

SSH into Ubuntu

Allow MySQL service listen on the server IP address instead of 127.0.0.1

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Under [msqld], change bind-address = 127.0.0.1 to the db_server_IP_address.

Restart the MySQL server:

sudo systemctl restart mysql

Allow SQL traffic through the firewall: 

sudo ufw allow mysql

Create a new user with permissions:

CREATE USER 'remote_username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'remote_username'@'%' IDENTIFIED BY 'password';

Update an existing database and user to allow remote access:

mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';

202.54.10.20 is the remote client IP address