Create a SSH Tunnel in Private Network to get access in remote database for WordPress

May 6, 2017 4.4k views
WordPress MySQL Ubuntu 16.04
arifulhb
By:
arifulhb

Hi,

I've a droplet (D1) running in SanFransisco 01 datacenter. This is my Apache2 web server running a WordPress site. I've created a new Droplet (D2) in SanFransisco 01 datacenter. This one is for MySQL Database. Both has Private Network enabled.

I've created a SSH Tunnel in D1 to access database of D2

ssh -fNg -L 3307:127.0.0.1:3306 root@D2_PUBLIC_IP_ADDRESS

But I am getting high latency and want to improve it through connecting it via private network. How can I achieve it?

I've tried to ssh to Private IP 

ssh root@D2_PRIVATE_IP

but I get this error

connect to host D2_PRIVATE_IP port 22: No route to host

How can I achieve this to minimize latency?

Log In to Comment
2 Answers
jtittle1 May 6, 2017

@arifulhb

You don't specifically need to setup a tunnel to connect to from D1 (Web) to D2 (DB). You can change the port that MySQL binds to in MySQL's configuration file and set it to the private network IP -- then restart MySQL.

Specifically, you'd be looking for bind-address. So if your private network IP is 10.0.0.2, you'd then set the bind-address like so:

bind-address = 10.0.0.2

Then service mysql restart.

You would then need to create a user for the connecting host. So if your web server has a private IP of 10.0.0.1, then you'd do:

create database name_of_db;

grant all on name_of_db.* to 'name_of_user'@'10.0.0.1' identified by 'password';

name_of_db = name of database

name_of_user = name of database user

password = password for database user

You'd then use those credentials and the private IP of the database server to connect to MySQL.

Reply
  • arifulhb May 6, 2017

    Thanks, Let me try it and get back here :)

  • arifulhb May 6, 2017

    Okay, After complete all these steps,

    mysql -u my_new_remote_user -h D1_IP_OF_WEBSERVER -p

    I receive password prompt, but after I enter the password, no response.

    UPDATE:

    mysql -u my_new_remote_user -h D2_IP_OF_MYSQL -p

    Not working :(

    UPDATE 2:

    It worked. First I made mistake in assigning correct IP in MySQL grant permission step.

hansen May 6, 2017

Hi @arifulhb

Even with SSH over the private networking, there will still be some lag. Have you considered connecting directly over the private networking for MySQL?
https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql

If you activated the firewall at one point, that might be what's blocking you currently.

sudo ufw status

You can check what services are listening on the different interfaces with this command:

sudo lsof -iTCP -sTCP:LISTEN -P
How To Set Up a Remote Database to Optimize Site Performance with MySQL
by Justin Ellingwood
When your site begins to get more visitors, you may have to think about how to handle the additional traffic. One easy way of doing this is to separate your database operations from your web server so that each can handle its own load. In this guide, we'll demonstrate how to do this for a WordPress site built on Nginx by configuring a remote MySQL database.
Reply
Have another answer? Share your knowledge.

Related Questions