Remote Connections to mysql

By default remote access to the MySQL database server is disabled for security reasons. The standard configuration of MySQL is intended to be as fast as possible, so encrypted connections are not used by default.

If we want to allow remote connections then we need to follow these steps:

  • Edit /etc/my.cnf
  • Comment out the following options:
skip-networking
bind-address
  • Restart the mysql daemon (sudo service mysql restart)
  • GRANT access to the remote IP (see GRANT, SHOW GRANTS, REVOKE)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'ThePasswordGoesHere' WITH GRANT OPTION;
FLUSH PRIVILEGES;
  • We can see current grants using: SHOW GRANTS
  • The GRANT can be limited to a specific IP

Alternatives

Allow remote connections doesn’t seem like such a great idea because the traffic will be unencrypted. Instead of allowing remote connections we have the following options:

  • Setup a VPN, see: OpenVPN
    • Complicated setup, but easy for users to connect to and transparent for applications
    • Setting OpenVPN is a bit complex, see this guide. To summarize:
      • install openvpn package
      • Generate a master Certificate Authority (CA) – used to sign Server & Client certificates
      • Generate Server certificates
      • Generate Client certificates
      • Configure the Client (OpenVPN GUI)

References

Advertisements
Posted in Linux, MySQL, Security

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: