problem connecting to MySQL Server

Hi,

I have MySql server running at home on raspberry pi, from my MacBook I am able to connect to MySql running on raspberry pi using MySQL Workbench,

however when I try to connect to insert entry to test_arduino database , the arduino mega with Ethernet shield mounted ontop fails to establish connection.

I have seen many examples of mysql connector on the web, like the below one is what I am trying:

/**
* Example: Hello, MySQL!
*
* This code module demonstrates how to create a simple 
* database-enabled sketch.
*/
#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 };
IPAddress server_addr(192, 168, 1, 3);

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

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

void setup() {
  Ethernet.begin(mac_addr);
  Serial.begin(115200);
  delay(1000);
  Serial.println("Connecting...");
  if (my_conn.mysql_connect(server_addr, 3306, user, password))
  {
    delay(500);
     /* 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() {
}

but when I connect from macBook using workbench, I see MySQL has different IP then what my raspberry pi web server running MySQL.

See the attachment 1, it has MySQL running at 127.0.0.1 and pi running on 192.168.1.3

so I am confused about the above part in the arduino sketch what should be the server IP ?

Screen Shot 2016-12-11 at 11.29.05 AM.png

It appears your macBook is connecting to its own copy of MySQL, not the RPi's MySQL. 127.0.0.1 is localhost.

SurferTim:
It appears your macBook is connecting to its own copy of MySQL, not the RPi's MySQL. 127.0.0.1 is localhost.

No I do not have MySQL installed on macBook and other databases that list on MySQL workbench after login are on Rasp pi web server running wordpress etc.

127.0.0.1 is localhost. That is a server on the local computer (macBook).

SurferTim:
127.0.0.1 is localhost. That is a server on the local computer (macBook).

well look at the I ssh my raspberry pi got into mysql prompt:

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

mysql> SELECT SUBSTRING_INDEX(USER(), '@', -1) AS ip,  @@hostname as hostname, @@port as port, DATABASE() as current_database;
+-----------+----------+------+------------------+
| ip        | hostname | port | current_database |
+-----------+----------+------+------------------+
| localhost | ceres    | 3306 | NULL             |
+-----------+----------+------+------------------+
1 row in set (0.01 sec)

ceres is hostname of my raspberry pi server, secondly my MacBook is turned off and you can still post comment on wp blog at san.gotdns.ch

127.0.0.1 is localhost on whatever computer you are working on. On your macBook, 127.0.0.1 is on the macBook. On the RPi, 127.0.0.1 is the RPi.

SurferTim:
127.0.0.1 is localhost on whatever computer you are working on. On your macBook, 127.0.0.1 is on the macBook. On the RPi, 127.0.0.1 is the RPi.

so if 127.0.0.1 what would be public ip for mysql server than ?

Go to http://www.whatsmyip.org/ from your mysql box. Probably you need to open the port in your router.

so if 127.0.0.1 what would be public ip for mysql server than ?

No. NEVER.

Hi Everyone,

I was able to solve this problem following these steps I have documented on my blog here . Now I don't need to run web client either from my arduino.