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
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 18.104.22.168 --dport 3306 -j ACCEPT
-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
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
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
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
22.214.171.124. 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.