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:
- A MySQL server hosted in a different server than the web server.
- Access to both of the servers (the web server and the database server.)
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:
1 bind-address=127.0.0.1
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:
-A will define what to allow (we’re allowing input – traffic.)
-p will specify the type of the protocol (tcp.)
--dport will specify the port of MySQL
-j will accept traffic.
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 “strongpassword”):
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:
- Make sure both of your servers follow appropriate security protocols and harden their security where possible – splitting the web server and the database server helps, but needless to say, servers with hardened security measures will provide an even bigger challenge for a would-be attacker.
-
Avoid providing your password via the CLI – the history of commands provided via the CLI can be observed by anyone, so it’s not a recommended practice. Instead, practice providing the password to access your database instances in the files relevant to them (for MySQL, that’s my.cnf, for PostgreSQL, that’s postgresql.conf, for others the files differ – also, one can change the username of root as well – that could help a little too):
Securing your database through my.cnf Consider renaming the root account – renaming the main (“root”) account as shown in the example above will help your database be more secure. Of course, that’s an example of “security through obscurity”, but it’s better than nothing.
Grant users only the privileges that are absolutely necessary – it should not come as a surprise that the more users have privileges exceeding their reach, the more problems your database will face. Aim to grant only those privileges that are necessary – for users that only read data, grant only the
SELECTprivilege, others may only needUPDATEs, etc.
Follow these tips and your database should be on its way to the security heaven.
Summary
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!
FAQs
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.
About the author
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
Top comments (0)