How to configure MySQL / MariaDB to listen publicly so that remote connections will work

Introduction

In order to connect to MySQL or MariaDB from a computer outside of the server, you must configure MySQL or MariaDB to listen on a public IP address.

You may use the following procedure to configure MySQL and MariaDB to listen publicly.

NOTE: Configuring your database to listen publicly will introduce additional security risks to the server. Only do this if you are positive that you need to, and if you are aware of the risks involved and are willing to accept them. Please consult with a security specialist if you have concerns about this.

Procedure

1. Login to the cPanel server as the root user via SSH or Terminal

2. Check to see if the database process is listening publicly already. The following shows that the MySQL process is listening only on localhost (127.0.0.1) which will prevent remote connections:

# ss -tunlp | grep -i "mysql\|maria\|3306"
tcp LISTEN 0 0 127.0.0.1:3306 *:* users:(("mysqld",1814,24))

3. If the database is listening only on localhost, edit the /etc/my.cnf file and add the following configuration under the [mysqld] section:

bind-address=*

NOTE: Be sure to remove any existing bind-address configurations.

4. Restart the server with the following command:

/scripts/restartsrv_mysql --restart

5. Verify that MySQL or MariaDB is now listening on * which means that it will bind to all IP addresses of the server, both public and private:

# ss -tunlp | grep -i "mysql\|maria\|3306"
tcp LISTEN 0 0 *:3306 *:* users:(("mysqld",30016,12))

NOTE: You may also specify a particular IP address if you want, but the * option is generally perfectly fine to use. If you have more specific needs, the options are explained in detail in the MySQL Documentation.