MySQL allow remote connections, from another server

While developing or testing PHP-MySQL based applications, we often need to allow remote connections from another external server to an existing MySQL database.

By default the mysql server is configured to bind the localhost interface to port 3306.

But for a remote connection, we need to bind all the available network interfaces to port 3066, or at least one external IP address, through which we want to connect the server.

So, for that we need to access the server through SSH and make some changes to the /etc/mysql/my.cnf file and the database itself. Of course we need super user privilege to make that changes.

MySQL allow remote connections

To allow remote connection from another different server, follow the steps below.

1. Fisrt SSH to the remote server, where the mysql database is running.

   ssh user_name@remote_server      # SSH to the remote server

2. Edit the /etc/mysql/my.cnf file with your favorite text editor after log in.

   sudo nano /etc/mysql/my.cnf      # On the server

3. Search for the line bind-address and change it to 0.0.0.0 from 127.0.0.1, then save and exit the editor. Tip: Use the Ctrl + w shortcut with nano to easily find the line.
4. Restart the mysql server, either with systemd or use the older service command.

   sudo systemctl restart mysqld.service  

5. Connect to the MySQL database as root, or any other username you want to use.

   mysql -u root -p

6. Run the commands below, but you must modify it according to your system.

   GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%' IDENTIFIED BY 'pass';
   FLUSH PRIVILEGES;
   exit;

The highlighted text above in red, user_name and pass are the mysql database username and password respectively.

So, you also need to know the default root password for the MySQL root user. In case you don't know, have a look at the files of the /etc/mysql/conf.d/ directory.

Testing MySQL remote connections

If you've successfully completed all the steps above, then you should be able to connect to that particular MySQL database, with the proper username and password.

I used the mysql command line application on a local machine to test. To install the client only, on Debian or Ubunntu, use apt.

sudo apt-get --no-install-recommends install mysql-client

To test the database connection,

mysql -u <database user name> -h <database server ip address> -p

As you may already know, we have to use the database username after the -u option and the database server's IP address or host name after the -h option.

Alternatively you can also use the LibreOffice Base application to test the mysql remote connections. Note: To connect remote MySQL server with JDBC, install the libmysql-java package along with LibreOffice.

Conclusion

Of course allowing connections form all interface to a MySQL server is a bit dangerous. You should also take proper precautions to prevent unauthorized database access, like using a strong enough password, allowing only a specific set of IP to connect the server.

Actually this post is written as a short note, faced the similar problem while I was developing a web based CRM application. The PHP code runs on Heroku's cloud infrastructure, but the free ClearDB database was not enough to hold around 50K user data.

So, That's all about MySQL allow remote connections . This tutorial is tested on a server, running 64 bit Ubuntu server 16.04.3 and MySQL version 5.7.20. hope it'll be helpful.