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 :
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
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.
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.