By default, MySQL does not allow users to connect to it remotely. This means, if you have a database server running MySQL, you cannot access it from your computer or any other computer directly unless it MySQL is configured to allow this. To access, you have to login to that server via SSH and run your MySQL connection commands there.

This article will help us know how to configure the MySQL server to allow remote connections

Prerequisites

• You should have a server that already has MySQL or MariaDB installed

Procedure

1.Login to your server via SSH

2.Using your favorite editor, open the MySQL configuration file.

By default, this is located under /etc/mysql/mysql.conf.d/mysqld.cnf on Debian based operating systems e.g Ubuntu and /etc/my.cnf on Redhat based operating systems eg Centos

3. Look for bind-address on the file and change the IP so that its as follows.

bind-address = 0.0.0.0

If there is no such line, simply add the following somewhere under [mysqld] section

4. Save and quit the editor then restart MySQL service

$ systemctl restart mysqld

After you perform the above, you have successfully enabled remote access on your account.

Other Considerations

However, it may be necessary to perform some additional steps to be able to access the database remotely:

1. Allow MySQL traffic on your firewall

If you use Ubuntu/Debian, you may be using a firewall called UFW. To allow traffic on UFW, run the command below.

$ sudo ufw allow 3306/tcp

If using Redhat/Centos, you may be using firewalld. Use the command below to allow traffic

$ sudo firewall-cmd --zone=public --add-port=3306/tcp

2. Create a user who with remote access privileges

When you create database users initially, you may have created them to access the database from localhost or 127.0.0.1 only.

You need to create users and allow access from any host as follows

mysql > create user 'username_here'@'%' identified by 'password_here';
mysql > grant all privileges on database_name_here.* to 'username_here'@'%';
mysql > flush privileges;

Replace username_here with the actual username, password_here with the password and database_name_here with the database name the user should access.

The % sign shows the user is being granted permissions to access from any IP address.