How To Setup MySQL Server accepting Remote Connections

If you want to connect with a system or a network that is situated far away from your system remotely. These type of connections are needed while you are busy with your works outside and at the same time, you have to take care of your office works. Or you can say if you are traveling and you need to access your db server because of some reasons. The reasons can be you need some data from the server.

MySQL for Remote Connections

Here you need the remote connection in order to access the MySQL Database remotely. For example, you are installing WordPress in your system and you have to connect your database from a remote system. In these case, you have to install MySQL remotely and you have to connect with any other server .

Install And Configure Mysql Server

To install MySQL, first, you have to update the server which contains the package index.

sudo apt-get update

Then you have to install the MySQL server using the below command:

sudo apt-get install mysql-server

You will get a prompt message in order to create a root password and the reconfirmation of the password. You have to remember the password. Because the password will be asked later for connections.

Now you have to configure MySQL. For that you have to run the below command :

sudo mysql_secure_installation

If you do that correctly, this will again ask your root password and also some set of questions. If you need that give the option as ‘yes’ otherwise ‘no’. While you are creating for the first time you can give “no” for all the questions.

If you need to know the version of your MySQL and you can initialize the data directory as follows :

mysql --version

sudo mysql_install_db

You can check the current status of your MySQL

service mysql status

Create User And Give Privileges

It is not recommended to give permission for the root user to access remotely. So its better to create a new user and grant all the permissions to that user for connecting from the remote host.

You can create a new user by running below command :

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

if you type ‘localhost’, then it will connect to that particular IP address only. Instead of that if you use ‘%’ then it will connect to all IP’s. For that, use the command as given below:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Grant all permissions for the new user by running below commands:

GRANT ALL ON wordpress.* to wordpress@localhost;

If you want to give permission to all IP’s then type as :

GRANT ALL ON wordpress.* to wordpress@%;

Edit MySQL configuration file

To allow remote connections you need to edit the MySQL configuration. Open the configuration file using the following command.

sudo vi /etc/mysql/my.cnf

Comment out the following line in the my.cnf file as it binds the MySQL server only to the localhost.

bind-adress   = 127.0.0.1

So that you can get privileges to all IP’s. Otherwise, you have to give the particular IP address only to get privileges for that particular IP only.

Next, you have to update the services of MySQL by restarting the database with root privilege by the below command:

sudo service mysql restart

So your MySQL database is updated and will be running successfully.

Check Connectivity using Telnet :

You can check the connectivity using Telnet from any other connected server by running below command

telnet <IP address> 3306

If all the configurations are correct, telnet will get connected on port 3306. Now you can connect this database from any server.

Conclusion

In this guide, we covered the setup for connecting MySQL using remote connections. This could be useful for many scenarios like a tiered architecture wherein you have a dedicated database server.