How to Allow a Remote MySQL Database Connection

November 11, 2020

Tags: Oracle, Dba Lounge, Site Reliability Engineering, Data Guard, Technical Track, Articles, Dba, Devops, Resources, Log Buffer

Here at Pythian we like to pay attention to what brings people to our site, and one of the main queries we've been seeing is "How can I allow a remote MySQL database connection?" Since our mission is to help you love your data, we asked Matthias Crauwels, Lead Database Consultant on one of Pythian's MySQL teams to address this topic. Over to Matthias! I'm hoping this may be the start of a series of posts where I can share some common MySQL knowledge in a (hopefully) clear and understandable manner. So let's get started.

Technical requirements

Historically MySQL allows a client to connect in two different ways. You can either use a local socket solution (on a Linux/Unix system this is called a Unix socket, whereas on Windows systems it's a named pipe) or via a TCP connection to a listening port (by default 3306). Since the introduction of MySQL 8.0, a third way is also available using a newer protocol called the X-protocol. However, from a connectivity standpoint this is just an extra address and port for the server to listen on. The first method, using the socket, works only locally on the machine where the MySQL instance is running. This is the nature of these sockets; think of them like a file in the filesystem. The only difference is that this file connects you into a running program, instead of containing actual data. The location of this "file" is defined by the server variable socket . This variable will point to a location in the filesystem where the socket will be placed for a Linux-based system. On a Windows system this is the name value of the named pipe. The TCP method is the only way to connect to MySQL from outside the local server. In order to do this you need to ensure that the daemon binds to a port on an address that is reachable from the outside. This is configured by the variable bind_address . The default bind_address on the latest version of MySQL (8.0) is set to * . This value makes the MySQL server bind to any available IPv4 and IPv6 addresses the machine might be configured with. The default port where the server listens is 3306. This can be changed using the port variable. None of the above mentioned variables are dynamic. They all need a server restart if you want to change them. Thinking about this makes sense because it's during start up that the server initializes its connectivity.

Caveat for TCP connections: the firewall

I'm probably over-simplifying things here; just because your MySQL is listening on a certain address and port combination doesn't mean you'll always be able to connect. From the MySQL perspective all prerequisites are fulfilled, however a network configuration does not end there. There are many other possible blocking options. For example, firewall applications or devices might prevent network connectivity to your MySQL instance. In order to get traffic flowing you'll need to talk to the administrator(s) of these firewalls to get an exempt approval. In many cases these people won't approve your request without a good reason. This brings me to my next point.

Consider why you want to allow remote connections

The purpose of a database system like MySQL is obviously to store data. To be able to use this data you'll need to be able to connect to the server (machine) and service (database instance). This is a good reason to allow remote connection but only to the machines that need access to the database. It's generally a bad idea to open connectivity to your MySQL database for a wide audience, let alone to the internet. Your database tier should be on the most secure part of your network and should be protected with multiple security protocols in order to avoid unauthorized access to the data. If you make it too open, all kinds of malicious pieces of software such as port-scanners, brute-force attacks, etc. will hit your database. You don't want such a piece of software to gain access to your data; you don't even want it to be able to try.

The cloud

The cloud is very popular these days, and storing data in the cloud is a common practice. Services like RDS or Aurora in AWS, CloudSQL or Spanner in GCP or Azure SQL Database are fully managed services which store relational data in a similar way to MySQL data storage. In most cases the cloud provider has safeguards in place to protect you from exposing your database to the internet. If you look carefully there are ways to work around that (and probably shoot yourself in the foot). Please think twice before going that route.

Conclusion

It is quite possible and fairly simple to allow remote connections to your database servers. However, it's important to do so in a controlled way. Here at Pythian our slogan is LOVE YOUR DATA — we highly value data security and we use our expertise and experience to help keep your data safe. Contact us if you have any questions or if you want to have one of our database experts audit your data security. Be safe! Interested in our MySQL database services and support? Contact Us!

Insight and analysis of technology and business strategy