Connecting to MySQL is important for every MySQL DBA and developer alike – in this blog, we’re going to walk you through how you can connect to MySQL remotely using an administrative account. Let’s begin.
By the time you’re reading this blog, we assume that you already know your way around MySQL and perhaps other database management systems. Frequent readers of this blog may also remember that the default way to connect to MySQL by providing a username and a password through the CLI is not recommended since it leaves the used username and password in the history of the commands of the CLI for everyone to observe and my.cnf (or my.ini if you’re using Windows) should be used instead.
However, there’s another way you can use to connect to MySQL – one can also connect to MySQL using admin account remotely.
Why Connect to MySQL Using Admin Account Remotely?
A remote connection to MySQL or any of its flavors (Percona Server or MariaDB) may be necessary if a database and the web server reside in a distributed environment.
A distributed environment means data distributed across different mediums. Doing so can even be a necessity in some cases – that’s especially the case for organizations that are very security-focused. Having a separate web server and a separate database server improves security, performance, and in the event of an attack or a natural disaster, preserves the availability of the resources of your company.
How to Connect to MySQL Remotely?
Before connecting to MySQL remotely, you should be aware that there are prerequisites to completing such a task. These are as follows:
Once you make sure that you have MySQL hosted separately and can access both of the servers, the first thing you need to do is open the mysqld.cnf file on the MySQL server. The command will look like this (sudo may not be necessary and the directory towards my.cnf may differ):
$ [sudo] nano /var/lib/mysql/my.cnf
Once you have write access to the file, please change the IP address associated with the variable named “
bind-address.” The initial value of this variable should be 127.0.0.1 limiting access to MySQL from a local machine:
Here you need to replace the IP of 127.0.0.1 with the IP of the server that needs to access the MySQL server. Once done, restart MySQL by running:
$ systemctl restart mysql
To access the server from a remote machine, you need to let the traffic through a firewall. That’s pretty much as easy to do as to say – run the following command to allow access from a server bearing a certain IP (replace the IP to the IP of the server) and you’re done:
$ sudo iptables -A INPUT -p tcp -s [SERVER.IP.HERE] --dport 3306 -j ACCEPT
There’s also an option to accept all traffic without specifying a specific IP:
$ sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
In these commands:
Save the changes by running
service iptables save, and you’re done – your MySQL server is now able to accept connections. Connect to the server by running (provide the password after using the
-p option if you didn’t specify the password in my.cnf):
$ mysql -u username -h [ip.of.the.server]
All you need to do now is grant a specific user the permissions to access the database – it’s rather simple and you do it just as you do it locally, just replace ‘localhost’ with an IP. Do it like so (also, use stronger passwords than
1 GRANT ALL PRIVILEGES on your_database_name.* 2 TO remote_user@’ip.of.the.server’ 3 IDENTIFIED BY ‘strongpassword’;
Following Best Practices – How Not to Connect to MySQL Remotely
Congratulations – you now know how you should go about connecting to MySQL instances in a remote fashion. With all that in mind, there still are things that you should avoid doing when connecting to any database:
Follow these tips and your database should be on its way to the security heaven.
In this blog, we have provided you with information that’s necessary to connect to a MySQL instance using an admin account remotely. By using these tips and actionable advice you will be able to increase your database performance as well as increase availability. Make sure to follow our blog The Table for more actionable database advice, and until next time!
Why Should I Connect to MySQL Using Admin Account Remotely?
You should consider remotely connecting to your databases including MySQL because a separate server provides you with security and availability benefits.
What Are The Things I Should Consider Before Connecting to MySQL Remotely?
The things you should consider include, but are not limited to the configuration of your MySQL instances, your firewall capabilities, and the security measures that are already in place.
Is iptables The Only Option I Can Employ?
No – there are other options, but since the utility of iptables is available in most Linux distributions by default, we’ve used that as an example. Feel free to open the doors for your IP using other methods too.
Are There Other Things I Can Do To Secure MySQL?
Yes – there are quite a few. Make sure to use strong passwords, keep track of the users you have in your database and what privileges they have, also keep in mind that MySQL offers the capability to have account categories, roles, reserved accounts, and always keep in mind that in newer versions of MySQL, account locking is an option as well. The documentation should guide you through everything you need to know.