How to connect mysql server remotely?

Question

I want my mysql server to be remotely accessible by ip 192.168.1.3

so I changed the bind-address in /etc/mysql/my.cnf

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
bind-address            = 192.168.1.3

but still I am getting ceres is not allowed to MySQL.

$ mysql -u username -h 192.168.1.3 -p
Enter password: 
ERROR 1130 (HY000): Host 'ceres' is not allowed to connect to this MySQL server

server that is running mysql server is ceres @ 192.168.1.3

mysql> SHOW VARIABLES WHERE Variable_name = 'hostname';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | ceres |
+---------------+-------+
1 row in set (0.00 sec)

following google search result that lead me to SO, I did this

mysql> SELECT host FROM mysql.user WHERE User = 'root';
+-------------+
| host        |
+-------------+
| 127.0.0.1   |
| ::1         |
| localhost   |
| raspberrypi |
+-------------+
4 rows in set (0.00 sec)

which did not listed the ip of remote client that is trying to insert to sql db ! So I added root to run from remote using

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.177';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host FROM mysql.user WHERE User = 'root';
+---------------+
| host          |
+---------------+
| 127.0.0.1     |
| 192.168.1.177 |
| ::1           |
| localhost     |
| raspberrypi   |
+---------------+
5 rows in set (0.00 sec)

I also ran the above pn %.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host FROM mysql.user WHERE User = 'root';
+---------------+
| host          |
+---------------+
| %             |
| 127.0.0.1     |
| 192.168.1.177 |
| ::1           |
| localhost     |
| raspberrypi   |
+---------------+
6 rows in set (0.00 sec)

mysql> FLUSH PRIVILEGES;

and now On remote machine Iam still getting this error:

Error: 76 = Access denied for user 'root'@'192.168.1.177' (using password: YES).

If you paste the exact text of your error message into google teh first it is an answer on SO that will likely solve this problem. — user9517, Dec 17 '16 at 10:41
I followed your suggestion on the first search result of google that leads to stackoverflow, still give me this error: `Error: 76 = Access denied for user 'root'@'192.168.1.177' (using password: YES).` on the remote ! please see updated post above — Ciasto piekarz, Dec 17 '16 at 10:54
You need to start reading the [docs](https://dev.mysql.com/doc/refman/5.5/en/privilege-system.html). — Sven, Dec 17 '16 at 11:29

score 0 · Accepted Answer · answered Dec 18 '16 at 02:19

I finally figured out

By default my.conf may proabbly have an IP address 127.0.0.1 bind to the 3306 port . In my case, I have mysql server running on raspberry pi 3, and it has IP 192.168.1.145. So for the remote client to push directly to databse on remote mysql server, you should have mysql server IP 192.168.145 bind to 3306 port of mysql server running on it. So that MySQL server is visible to remote client on local network on which your arduino Ethernet shield is connected to via LAN. Then you login to mysql server , and create a user :

CREATE USER 'myuser'@'192.168.156' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

then you grant privileges as shown below.

GRANT ALL ON *.* TO 'myuser'@'192.168.156';
GRANT ALL ON *.* TO 'myuser'@'%';

Important Note: the ip address shown in above mysql queries 192.168.156 is the ip address of my ethernet client establishing connection with database on mysql remote server.

Thats it. This worked for me.

score 0 · Answer 2 · answered Dec 18 '16 at 06:29

If your web/application server or client have a static IP you should execute this command

CREATE USER '<user>'@'<client/server IP' IDENTIFIED BY '<pass>';

But server or client have not a static IP

CREATE USER '<user>'@'%' IDENTIFIED BY '<pass>;

And execute grant permision or access permision.

Debian or Ubuntu server

$ufw allow mysql

Or CentOS, RHEL

$firewall-cmd --zone=public --add-service=mysql

How to connect mysql server remotely?

2 Answers2