CONNECTION
MySQL

How to Connect to MySQL Using Admin Account Remotely

intro

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MYSQL 8.0+
THE MYSQL DATABASE VERSION 8 OR LATER

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):

Copy
        
$ [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:

Copy
        
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:

Copy
        
$ 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:

Copy
        
$ 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:

Copy
        
$ 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):

Copy
        
$ 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”):

Copy
        
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.
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 SELECT privilege, others may only need UPDATEs, 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.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
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.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22
title

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15
title

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11
title

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08
title

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04
title

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01
title

SQL CAST Function: Everything You Need to Know

author Antonello Zanini tags CAST MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-03-28
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

MySQL CREATE DATABASE Statement: Definitive Guide

author Antonello Zanini tags Create database MySQL 7 min 2024-02-08
title

A Complete Guide to SUBSTRING_INDEX in SQL

author Antonello Zanini tags MySQL 6 min 2024-01-16

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗