Remote Connection to MySQL Database in the Local Network Area(LAN)

Hello there! It has been long time to not write any post in the blog. I do not want to say that I did not have any time, inspiration or something. I had time, really good ideas but somehow I was too lazy to write them down to the blog. But, today I wanted to break this laziness. Anyway, from the title most probably you got what I want to do. But, basically I will explain it again.

I have a private wireless LAN at home as everybody and two device is connected to this network.

Here is the technical description.

1 - My laptop's config(It will be the server machine):

- IP Adress:192.168.1.106
- MySQL Server 5.7 on port 3306
- Operating System: Windows 10
- The database is in this machine

2 - My Raspberry Pi's config(It will be the client machine):

- IP Adress:192.168.1.108
- MySQL Server 5.7 on port 3306
- Operating System: Raspbian Jessie Lite
- This machine will connect to my laptop

Both are on a wireless LAN connected through a TP-LINK router(192.168.1.1).

So the big question is that how can I configure the devices to connect to the database and execute queries?

We need to follow a simple checklist:

1 - Try to ping the server machine: The first thing to do in the list is to ping from client machine to the server machine. You should open a terminal/command line in Raspberry and check if it is possible to ping the server machine(sudo ping <server_host_ip>). Then, we can understand that both machines are connected. I used sudo keyword, because sometimes we need root permission.

sudo ping 192.168.1.106

2 - Ensure MySQL is running on the specified port 3306 i.e. it has not been modified: This is the second thing to check, if it runs on the other port. We need to change this to default port 3306, or we may need to change the configuration file of the MySQL.

3 - Create a user in the client machine: The next step is to create a MySQL user that has the permission to connect remotely to another machine. First, we need to open MySQL and the following commands in MySQL line.

sudo mysql

Now, we are in MySQL command line. After that we need to create a new MySQL user or you can update any of the existing user. But I suggest to create a new one(CREATE USER '<user_name>'@'<server_host_ip>' IDENTIFIED BY '<password>';).

CREATE USER 'root'@'%' IDENTIFIED BY 'raspberry';

Here, be careful! The user name is 'root', the server host IP is '%' and password is 'raspberry'. What does % mean? It means that 'root' user whose password is 'raspberry' can connect any machine on the network. Instead of writing '%' if you write '192.168.1.106', then it can only connect to '192.168.1.106' machine. Now, we need to give the permission to do everything in the database(GRANT ALL PRIVILEGES ON *.* TO '<user_name>'@'<server_host_ip>' IDENTIFIED BY PASSWORD '<password>' WITH GRANT OPTION;).

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '' WITH GRANT OPTION;

And now we should do the last step in MySQL command line.

FLUSH PRIVILEGES;

After that we should exit MySQL and restart it.

sudo service mysql restart

4 - Ensure that the server machine is not blocking inbound connections on that port: If it is, add a firewall exception to allow connections on port 3306 and allow inbound connections in general. In my case, I basically close the firewall in the private network by doing followings: 

- Open Control Panel > Windows Firewall

- Close the Private Network Firewall

My solution is not secure. But, it is easy and fast one. You can add an exception to allow connections on port 3306 and allow inbound connections in general as I mentioned above.

5 - Connect from client machine to server machine: We did everything. Now, the last step is to connect from Raspberry Pi(client machine) to my laptop(server machine). We need the command(sudo mysql -u <user_name> -p -h <server_host_ip> -P <server_host_port>).

sudo mysql -u root -p -h 192.168.1.106 -P 3306

This is the whole step to connect a MySQL database remotely. If you have any problem, do not hesitate to ask. It would be nice if you could post the exact error as it is displayed when you attempt to make that connection. For now good bye until the new post.