Why do I get access denied when I do have mysql root user added to grant all privileges?

Question

I am using the below mysql example sketch to upload data directly to mysql db.

#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"

/* Setup for Ethernet Library */
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
// Set the static IP address to use if the DHCP fails to assign
IPAddress ip(192, 168, 1, 177);
IPAddress server_addr(192, 168, 1, 3);
//char server_addr[] = "192.168.1.3"; 

/* Setup for the Connector/Arduino */
Connector my_conn; // The Connector/Arduino reference

char user[] = "root";
char password[] = "secret";
char INSERT_SQL[] = 
 "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!', NULL)";

void setup() {
  Ethernet.begin(mac_addr, ip);
  Serial.begin(9600);
  delay(1000);
  Serial.println("Connecting...");
  if (my_conn.mysql_connect(server_addr, 3306, user, password))
  {
    delay(500);
    Serial.println("Connection Successfull,inserting to database.");
     /* Write Hello, World to MySQL table test_arduino.hello */
     my_conn.cmd_query(INSERT_SQL);
     Serial.println("Query Success!"); 
  } 
  else
    Serial.println("Connection failed.");
}

void loop() {
}

I am getting the error in serial Monitor:

Connecting...

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

Connection failed.

even when I have all prvialages on root user for the ip of client !

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 ran the following query :

mysql> SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip,  @@hostname as hostname, @@port as port, DATABASE() as current_database;

and got the below result !

+-----------+----------+------+------------------+
| ip        | hostname | port | current_database |
+-----------+----------+------+------------------+
| localhost | ceres    | 3306 | test_arduino     |
+-----------+----------+------+------------------+
1 row in set (0.00 sec)

however I am trying to access the test_arduino database from a different IP other than the one mentioned above, does that mean where it says localhost, it is supposed to have 192.168.1.177 as well ? if so How do I insert ip to database test_arduino ?

then what am I missing ? please help.

Answer 1

I finally figured out

By default it may proabbly have an IP address 127.0.0.1 bind to the 3306 port . This won't let your Arduino Ethernet shield connect to mysql server running on a different machine. In my case, I have mysql server running on raspberry pi 3, and it has IP 192.168.1.145. So for arduino 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.