How to allow remote access to MySQL through phpMyAdmin

Remote access to MySQL can help you be more flexible in managing and maintaining your WordPress database. It also allows you to respond quicker to emergencies should the need arise.

While MySQL only allows local connections by default, updating this setting is easier than you might think. phpMyAdmin, a popular web-based MySQL client, makes this even easier thanks to its friendly GUI (graphical user interface).

In this article, we will look at how to allow remote access to MySQL through phpMyAdmin. We will also look at other available methods as well as what you need to consider when allowing such access.

The same procedures apply if you’re connecting to a MariaDB server.

MySQL: allow remote connections

While in most cases, a remote connection to the WordPress database is not required, having one can be of great benefit. Whether you want to connect a MySQL client such as MySQL Workbench, carry out MySQL hardening remotely, or troubleshoot an issue, remote connections to the database server have a lot of advantages.

Out-of-the-box, MySQL does not allow remote access. This is for security reasons more than anything else. Accessing the database remotely carries risk; however, this risk can be managed.

In most cases, MySQL, WordPress, and phpMyAdmin are installed on the same server. However, each of these can reside on a completely different server – which is where remote connections come into play. We will start by looking at the setting that underpins MySQL remote connections – the bind-address directive.

The bind-address directive

The bind-address is a setting that specifies which IP addresses MySQL should listen to. By default, this is set to 127.0.0.1. This allows only local connections, that is to say, connections from the same server.

The setting lives in the mysqld.cnf configuration file. It can easily be edited using a basic editor such as nano or vim. You will find the configuration file in the /etc/mysql/mysql.conf.d/ directory. We will discuss how to access this through SSH later in the article.

However, if you do not have SSH access to the server, you can also edit this variable through phpMyAdmin.

To state the obvious, phpMyAdmin must be installed on the same server as MySQL. In most cases, this will be the same server as the WordPress web server is installed on. If you have just installed phpMyAdmin on a different server and want to be able to access your WordPress database, you will need to update the bind-address direction through SSH. This also assumes that you already have a successfully configured WordPress website.

Keep in mind that this setting allows access to the MySQL server and not a specific MySQL database. We will cover the latter in the next section.

The bind-address can be changed to allow connections from specific IPs or any IPs.

Choosing between a specific IP or any IP

When updating the bind-address directive, you can choose between a specific IP/IPs or any IP.

Choosing a specific IP is the more secure option. However, you might need to update the IP address occasionally if your ISP assigns you a dynamic IP. Most residential connections come with dynamic IPs, meaning your outward-facing IP address can change from time to time. In such cases, you will temporarily lose remote access to your MySQL server until the bind-address directive is updated with the new IP.

On the other hand, configuring the bind-address directive to accept connections from any IP is a far less secure method, as it potentially allows anyone to connect. Of course, they will still need to provide a username and a password to connect, but you would effectively have taken down one of the security barriers.

There is no right or wrong answer, as it will largely depend on your specific requirements and circumstances. Specifying a specific IP is the safest route and should be your first option, especially if you need to comply with security standards or laws.

Updating bind-address directive in phpMyAdmin

phpMyAdmin makes many MySQL tasks easy to undertake. Its GUI (Graphical User Interface), while not modern by any stretch of the imagination, is very intuitive to use. phpMyAdmin is also widely seen as an industry standard, so you’ll find plenty of tutorials and support should you need it.

To update the bind-address in phpMyAdmin:

1. In the phpMyAdmin home screen, click on Variables and highlighted in the screenshot above
2. Scroll down the list of variables until you find the bind-address entry
3. Click on Edit and enter the IP address or IP addresses
4. Click on Save to save changes

To accept connections from any IP, update the bind-address to ‘0.0.0.0’. If you are entering multiple IP addresses, separate each one by a comma.

Updating bind-address directive through SSH

If phpMyAdmin is installed on a separate server or is unavailable, you will need to update the configuration file through SSH. This is because a remote connection can only be enabled locally.

Connect to the server over SSH, and once logged in, run the following command:

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

What does this command mean? Let’s break it down:

• sudo is short for superuser do. It is the equivalent of ‘Run as administrator’ on a Windows machine and allows us to execute the following command as a super user
• vim is short for VI iMproved, which is a plain-text editor
• Lastly, we have the file path of the configuration file

Hit enter to open the mysqld.cnf file. This should look like the first screenshot in the article. Switch to edit mode and navigate to the bind-address directive. Enter the new IP address and save.

For the changes to take effect, you will need to restart the MySQL service. You can do so by running the following command:

sudo systemctl restart mysqld

Do note that if you have a firewall, you will need to add a firewall rule to ensure the connection is able to go through.

Additional configurations for remote phpMyAdmin

You can also install phpMyAdmin on a different machine than the one MySQL is installed on. For this to work, you will need to update your phpMyAdmin configuration file with the IP of the MySQL server.

Head to the Server parameters section and update the host entry with the IP address of the MySQL server, as shown in the screenshot below. Then, simply replace ‘X.X.X.X’ with the IP of the server you want to connect to. Since we’re connecting remotely, this needs to be the public IP address of the server.

If you’re using a different port than the standard 3306, add the port to the IP in the following format:

X.X.X.X:port number

Where port number is the number of the port on which connections are allowed.

Next, we will look at how you can connect to a remote database by ensuring your user has the required privileges.

The MySQL user

Now that we have enabled remote connections to the MySQL server, we need to make sure that the user we connect with has privileges on the database we want to manage. Following the principle of least privilege, this user should only have access to the databases that need to be managed remotely.

You might want to choose a different user than the MySQL user provided during WordPress installation. If in doubt, you can consult the WordPress wp config file to confirm which user WordPress is using to connect to MySQL and run SQL queries.

Users reside in the user table of the MySQL database. We can create a user through phpMyAdmin or MySQL directly after connecting through SSH. We will cover both.

It’s important to note that MySQL users are different than WordPress site users.

Creating the MySQL user

MySQL user accounts reside in the user table within the mysql database. To create a new user run the following command:

CREATE USER username@hostname IDENTIFIED BY 'password';

Where:

• username is the name of the user account
• hostname is the name of the host
• password is the user’s MySQL database password

Pro note: Some ISPs may append their domain to username. This can be especially important if you are connecting to the remote server through a script. In such cases, you will need to use this domain name as the MySQL hostname.

Next, we need to assign the user rights to the WordPress database as follows:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER
    -> ON wordpressdatabase.*
    -> TO username@hostname;

Where:

• wordpressdatabase is the name of the WordPress database
• username is the name of the user account as configured in the previous step
• hostname is the name of the host as configured in the previous step

The GRANT statement ensures that the user has the proper permissions to undertake different tasks on the database.

You can also grant the user all privileges on all databases regardless of the hostname/domain name they connect from. This is not advisable. It is recommended that you user an account with the least MySQL privileges possible to improve WordPress security. However, such an account can be useful if you need to troubleshoot any issues. You can accomplish this using the following commands:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

Once you finish troubleshooting, you should lock the account using the following command:

ALTER USER 'user_name'@'host' ACCOUNT LOCK;

Lastly, we need to flush privileges so that the changes take effect. Run the following command:

FLUSH PRIVILEGES;

As explained earlier, you can also create the MySQL user through the SSH command line. The exact same commands apply.

Harden MySQL

When you accept remote connections, you invariably increase the attack surface. As such, it is crucial to mitigate those risks. Do not connect remotely from unsecured networks such as public WiFis, and never use the root password for the remote user. Undertaking a MySQL hardening exercise goes a long way in staying protected and safe.

Similarly, keeping WordPress secure is of great importance. WordPress 2FA is a low-hanging fruit that vastly improves the security of user accounts and WordPress. It is also a requirement if you’re looking to comply with legislation such as GDPR and PCI DSS.

Other considerations to keep in mind

Optimizing the WordPress database can help you make sure you provide the best WordPress website performance possible while facilitating administrative tasks such as data migration and backups.

Frequently Asked Questions