Connecting MySQL server from remote/local

In this tutorial, I am going to show you how we can connect our MySQL server from a remote location or from our local computers.

The Problem

Most of the companies use Managed Database solutions offered by different cloud providers. For example RDS of AWS, Azure SQL database of Azure, DBaaS of Digital Ocean, etc.

With Managed Databases, we don’t have to manage anything except for our data. There won’t be any problem while connecting to the database from a remote location (or local) if we have allowed the network from our security groups/access lists. But, If we have installed a database on the server manually without using Managed Database, then by default we cannot connect that database from a remote server/local computer(the database will be accessible only from inside the server)

To cope with such an issue of connecting our database servers from remote/local, there are few configurations we need to change on the database server itself. Such changes are described below:

The Solution

Step 1: log in to the server with ssh.

ssh username@server_ip

If you have a server on AWS, then you can ssh with:

ssh -i keyfile.pem username@server_ip

Note: you have to allow port 22 for SSH and port 3306 if you have MySQL server or port 1433 for SQL server.

Step 2: Change the database configuration file inside your server. Go to /etc/mysql/mysql.conf.d/ directory, Open the file mysqld.cnf, Go onto the [mysqld] section, which looks like:

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
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

The easiest way is to comment out the bind-address line. Otherwise, we can put your remote server’s IP address on that bind-address. After the change, mysqld.cnf file be like:

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#bind-address           = 0.0.0.0

Save the file, and then perform mysql restart with:

sudo service mysql restart

Step 3: We also need to configure our database user to allow connection from remote hosts. By default, the admin user for the database will be root. For this:

1. Connect to the database from inside the server:

mysql -u root -p

2. Execute the following commands inside MySQL:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Note: you need to change ‘root’ by your mysql user and ‘password’ by your users password.

That’s all we need to do inside the server and database. Now, try to connect to that database from remote server/local computer with:

mysql -h host_ip -u root -p

Note: If you still got the error after completing all these steps, please check the firewall of the server to see if the database port is blocked or not. In case of Ubuntu server, you can do it with the following commands:

sudo ufw status
sudo ufw allow mysql
sudo service ufw restart