Securely connect your application server to a remote MySQL server! We'll cover firewalls, networking binding and user security within MySQL.

This will explain the parts you need to know about MySQL to connect remotely without sacrificing security.Here's the basic things standing in our way of connecting to a remote MySQL server:

• AWS security Rules, if you're using AWS
• Firewall rules on both servers (Notably the INPUT chain on the MySQL server)
• MySQL's Bind Address (what network it's listening on)
• MySQL User allowed to be used for remote connections

Firewalls

Assuming AWS rules are all set (that's shown in the video but outside the scope of it), let's start with the Firewalls.

We need to ensure the MySQL server allows incoming connections on MySQL's port 3306. Let's open up port 3306 to allow those connections through.

Here are the commands used to create the current firewall rules on the MySQL server:

sudo iptables -A INPUT -i lo -j ACCEPT
sudo iptables -A INPUT -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 22 -j ACCEPT
sudo iptables -A INPUT -j DROP

To add onto this rules and allow incoming requests on port 3306, we can do the following:

sudo iptables -I INPUT 4 -p tcp --dport 3306 -j ACCEPT

However, let's make this more secure and only allow these incoming requests from a specific IP address (our application server):

sudo iptables -I INPUT 4 -p tcp -s 173.10.0.22 --dport 3306 -j ACCEPT

Where -s is the --source IP address (your application server).

See the Security series to learn more about Firewalls

MySQL Bind Address

MySQL needs to listen on the network being connected to. This means editing the bind-address parameter in the my.cnf file (/etc/mysql/my.cnf in Ubuntu/Debian).

By default, bind-address will default to localhost or 127.0.0.1. To allow remote connections, we can have MySQL do a few things:

Listen on all network interfaces

; Comment bind-address out, as it defaults to 0.0.0.0
; bind-address = 'localhost'

; Or set bind-address to 0.0.0.0 explicitly
bind-address = '0.0.0.0'

Listen on specific network interface

bind-address 192.168.33.10

If you bind to a specific address, you'll need to connect to it using that host when connecting from a remote server:

mysql -h 192.168.33.10 -u some_user -p

MySQL User

Lastly we need a user that can be connected to. MySQL users are created per host. The host can be a wildcard (any host) % or a specific host, such as localhost or perhaps the IP address of your application server at 192.169.33.11. We'll create a user that can be connected from a remote host - our application server.

Note, I'll grant all privileges to his user when connected from all hosts. You may want to be more restrictive.

Here's what it would look like if creating a user who can be connected to from anywhere:

CREATE USER 'your_user'@'%' IDENTIFIED BY 'some_secure_password';
GRANT ALL PRIVILEGES on your_db.* TO 'your_user'@'%';

Here's what it would look like if creating a user who can be connected to from a specific host, and with specific [grant permissions](https://dev.mysql.com/doc/refman/5.6/en/grant.html:

CREATE USER 'your_user'@'192.168.33.11' IDENTIFIED BY 'some_secure_password';
GRANT SELECT, DELETE, UPDATE, INSERT, INDEX, LOCK TABLES on your_db.* TO 'your_user'@'192.168.33.11';

In our case, we'll do a mix:

CREATE USER 'your_user'@'192.168.33.11' IDENTIFIED BY 'some_secure_password';
GRANT  ALL PRIVILEGES on your_db.* TO 'your_user'@'192.168.33.11';

Here's more information on grants needed for mysqldump here.

We should be all set after these changes. We can now log into our application server and attempt to login to the MySQL server!

Side note, If you're interested in a service to help you manage MySQL-optimized, backup and (eventually) replication-enabled database servers, sign up here to let me know! The idea is to allow you to better manage your MySQL servers, taking advantage of many of MySQL's more advanced options, especially around backup and recovery.