Allow mysql client to access remote mysql databases

Linux, MySQL / March 22, 2021 / ashishpsk / 0

One thing you must understand is that mysql server only allow localhost connections by default. So what does that mean ? Before going deep let me put up an example mysql-server. (1)

” Consider abc.com (5.5.5.5) is our mysql-server and is hosted on a VPS machine. We have setup a lamp environment on 5.5.5.5 and everything is ready to go. You can install mysql-server without apache and php by the way if you don’t want a lamp setup. At this point, I will guess you have mysql-server up and running. ”

(1) Back to the localhost part, if you access mysql-server from within the server abc.com (5.5.5.5) by ssh into it then you are accessing mysql-server as localhost. If you access mysql database with root privileges then it is root@localhost.

For example, here you are in abc.com server with shell access.
You used putty to “ssh root@abc.com”
root@abc [~]# mysql -u root -p
Enter password:

mysql>

Now check how are you logged in as

Select all
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+

The above command shows that the access to the mysql-server is from within the crybit.com server(so its shows localhost) and by user root, which is being shown as root@localhost.

Checking who have access to mysql-server

By using this below command you can find out which users and hosts are allowed access to mysql server. You can see that currently localhost , its IPV4 and IPV6 are allowed by default.

Select all
mysql> select user,host from mysql.user;
+----------------+--------------------+
| user | host |
+----------------+--------------------+
| root | 127.0.0.1 |
| root | localhost |
| root | ::1 |
+----------------+--------------------+

This means remote users cannot have access to the server. So we have to allow them manually. Everyone other than abc.com(5.5.5.5) are remote users and thus without any entry of those clients in mysql.user table they won’t have access.

Allowing access to a remote client machine

Suppose your client is xyz.com(7.7.7.7) you can allow access by using this command in server(abc.com).
root@abc [~] mysql -u root -p
Enter password:

Select all
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'xyz.com' IDENTIFIED BY 'your-password';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'7.7.7.7' IDENTIFIED BY 'your-password';
Query OK, 0 rows affected (0.00 sec)

With this you are telling the abc.com server that client xyz.com(7.7.7.7) can access it.

Recheck and now you can see an entry for client xyz.com along with its ip 7.7.7.7 which is for redundancy in the mysql.user table.

Select all
mysql> select user,host from mysql.user;
+----------------+--------------------+
| user | host |
+----------------+--------------------+
| root | 127.0.0.1 |
| root | localhost |
| root | ::1 |
| root | xyz.com |
| root | 7.7.7.7 |
+----------------+--------------------+

Checking access to server from client side

Login or access from client xyz.com using a PHP program or through mysql client shell.
root@xyz [~] # mysql -h crybit.com -u root -p
Enter password:

Success it now shows the prompt.
mysql>