To Allow Remote Connections to Mysql Server, we have manually add some code to the configurations of the mysql server. This article is about Allow Remote Connections to Mysql Server Ubuntu operating system (Debian based). Ok. Let’s start, At the time of installation MySQL is already available to the localhost of the system.To allow remote connections or other than the localhost, you have to enable or uncomment the /etc/mysql/my.cnf and assigned to your computers IP address and not loopback address.
#Replace xxx with your IP Address bind-address = xxx.xxx.xxx.xxx
Or add a bind address like this, if you don’t want to specify the IP
bind-address = 0.0.0.0
Note: If you can’t find the bind-address in the above specified location, look at the following location too.
/etc/mysql/mysql.conf.d/mysqld.cnf. It should be there. Also you can easily locate any file using the command locate filename
Now stop and restart MySQL with the new my.cnf entry. After restarting the MySQL server type the following comments in the terminal :
lsof -i -P | grep :3306
It will give an output, something like this with your actual IP in the xxx’s
mysqld 1046 mysql 10u IPv4 5203 0t0 TCP xxx.xxx.xxx.xxx:3306 (LISTEN)
Note: Even if not displaying above, it may be functional (seems working on some server, which do not give an output like this). If the above statement returns correctly, which means that you will then be able to accept remote users.
Now, to connect a remote user with correct privileges, you need to have that user created in both the localhost and ‘%’ as in.
Log into MySQL Terminal, type the command
mysql -u root -p
and enter the password.
CREATE USER 'remoteuser'@'localhost' IDENTIFIED BY 'securepass'; CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'securepass';
Note: securepass or whatever you give.Now, we have to grant privileges to that user, as follows.
GRANT ALL ON *.* TO 'remoteuser'@'localhost'; GRANT ALL ON *.* TO 'remoteuser'@'%';
as a final step,
FLUSH PRIVILEGES; EXIT;
The “%” indicate that, can access from any machine publicly or from any IP.(Since, this involves risk, use it care fully).
Also, you can specify a mysql database like this ‘mydb.*’ instead of ‘*.* , if you want to restrict that user to a particular database.
Note : After completing above steps successfully, restart MySQL and Apache ( Without restarting Apache, it’s not working sometimes).
To restart MySQL and Apache, to view their status use this comments on terminal:
To restart MySQL
sudo /etc/init.d/mysql restart
or
sudo service mysql restart
To view status
sudo service mysql status
To test the remote connection to your server, use this command and type your MySQL server password:
mysql -u remoteuser -h REMOTE_SERVER_IP -p
And select the database,
use mydb;
Hope you understand everything. Allow Remote Connections to Mysql Server, this post is for Ubuntu only. If you have doubts, drop your comments below.Thanks for reading.