MySQL server SSH access

[phods]

I have a RPi3 "server" with MySQL, and I want acess with another RPi3 client (python script) and with some arduinos. but I can acess with RPi3's client when I use ssh tunnel, but not with the arduino.
Can I disable the ssh security and connect directly?

[scotty101]

You shouldn't...

It is best not to open an SQL server (of any type) to the network directly. It would be better to use another language to provide a API to access the data within the database.
Perhaps a simple HTTP GET that returns a JSON object or plain text. Simpler the better when dealing with a low powered device like an Arduino.

[phods]

You shouldn't...

It is best not to open an SQL server (of any type) to the network directly. It would be better to use another language to provide a API to access the data within the database.
Perhaps a simple HTTP GET that returns a JSON object or plain text. Simpler the better when dealing with a low powered device like an Arduino.

ok I shouldn't, but if I wanted?? because for me is the easy way, in the future I will change for the best model, but now, i needed of a functional version. ( and I don't know how to use the hhtp e json right now ).

[mfa298]

ok I shouldn't, but if I wanted?? because for me is the easy way, in the future I will change for the best model, but now, i needed of a functional version. ( and I don't know how to use the hhtp e json right now ).

MySQL by default only listens on localhost (127.0.0.1). You can change that with a config setting in /etc/mysql/my.cnf. From memory the option is bind-address and the simplest option is probably to comment it out.

You will also need to grant access within MySQL to users on remote hosts (these don't have to match the system users on any host).

I'm not really sure how you plan to connect arduinos to your MySQL database, I don't think they'll be able to run a full MySQL client to connect to the DB directly so you're going to need something in the middle providing an API the arduino can talk to.

Going back to an earlier comment this isn't anything to do with ssh security. I suspect you were using SSH as a way to tunnel the MySQL connection between hosts rather than changing the MySQL security model.

[DougieLawson]

I have a RPi3 "server" with MySQL, and I want acess with another RPi3 client (python script) and with some arduinos. but I can acess with RPi3's client when I use ssh tunnel, but not with the arduino.
Can I disable the ssh security and connect directly?

Is that within your private LAN or are you thinking of connecting from the public internet?

If it's your LAN then

Code: Select all

bind-address            = 127.0.0.1

changes to

Code: Select all

bind-address            = 0.0.0.0

in /etc/mysql/my.cnf and a restart will open MySQL to your LAN.

If you're thinking of doing it across the public internet then stick with the ssh tunnel.

[phods]

ok I shouldn't, but if I wanted?? because for me is the easy way, in the future I will change for the best model, but now, i needed of a functional version. ( and I don't know how to use the hhtp e json right now ).

MySQL by default only listens on localhost (127.0.0.1). You can change that with a config setting in /etc/mysql/my.cnf. From memory the option is bind-address and the simplest option is probably to comment it out.

You will also need to grant access within MySQL to users on remote hosts (these don't have to match the system users on any host).

I'm not really sure how you plan to connect arduinos to your MySQL database, I don't think they'll be able to run a full MySQL client to connect to the DB directly so you're going to need something in the middle providing an API the arduino can talk to.

Going back to an earlier comment this isn't anything to do with ssh security. I suspect you were using SSH as a way to tunnel the MySQL connection between hosts rather than changing the MySQL security model.

I already connect the arduino with mysql server in my online host, but now I need one offline.(I just use a properly library for arduino, and connect directly to a mysql and execute querys)

Code: Select all

#include "EmonLib.h"
#include <SPI.h>
#include <Wire.h> 
#include <ESP8266WiFi.h>
// Create an instance of the server
// specify the port to listen on as an argument

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

const char* ssid = "******";
const char* password = "******";
//----------------------
EnergyMonitor emon1;

//Tensao da rede eletrica
int rede = 220;
 
//Pino do sensor SCT
int pino_sct = A0;
//----------------------

//dbsim.allianse.com.br
IPAddress addr(******);  // IP of the MySQL *server* here
char user[] = "******";              // MySQL user login username
char pass[] = "******";        // MySQL user login password
//***************************

int cont=3;
char INSERT_DATA[] = "INSERT INTO allsim.energia (power,corrente) VALUES ('%s',%s)";
//char INSERT_DATA[] = "INSERT INTO test.arduino (esp1) VALUES (%s)";
char query[128];
char var[10];
char var2[10];

WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
   Wire.begin();
  Serial.begin(115200);  
  // Connect to WiFi network
  Serial.println();

   //Pino, calibracao - Cur Const= Ratio/BurdenR. 2000/33 = 60
  emon1.current(pino_sct, 60);
   //emon1.current(pino_sct, 111.1); // Current: input pin, calibration.
//emon1.current(pino_sct, 56.49); 
  
  Serial.print("Connecting to ");
  Serial.println(ssid);
  
  WiFi.begin(ssid, password);
  
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  //Serial.println("WiFi connected");
  
  // Print the IP address
  //Serial.println(WiFi.localIP());
  //conectar na wifi
 // Serial.println("Connecting BD...");
  if (conn.connect(addr, 3306, user, pass)) {
    delay(1000);
  }
  else
    Serial.println("Connection failed.");
}

void loop() {
  Serial.println();
  Serial.println("Recording data.");
//Calcula a corrente
  double Irms = emon1.calcIrms(1480);
  //Mostra o valor da corrente no serial monitor e display
  dtostrf(Irms, 1, 2, var);
 dtostrf(Irms*230, 1,1, var2);
   sprintf(query, INSERT_DATA,var2, var);
  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Execute the query
  cur_mem->execute(query);
  // Note: since there are no results, we do not need to read any data
  // Deleting the cursor also frees up memory used
  delete cur_mem;
////////////////////

 
 

 delay(1000);
}

one more thing, when I create one mysql server with Xampp in a Windows machine, I can acess directly with a python script or an arduino code, but if this server is in raspberry i just access with python script using a library of sshtunnel

[phods]

I have a RPi3 "server" with MySQL, and I want acess with another RPi3 client (python script) and with some arduinos. but I can acess with RPi3's client when I use ssh tunnel, but not with the arduino.
Can I disable the ssh security and connect directly?

Is that within your private LAN or are you thinking of connecting from the public internet?

If it's your LAN then

Code: Select all

bind-address            = 127.0.0.1

changes to

Code: Select all

bind-address            = 0.0.0.0

in /etc/mysql/my.cnf and a restart will open MySQL to your LAN.

If you're thinking of doing it across the public internet then stick with the ssh tunnel.

is just a local network point to point (the raspberry pi is the dhcp server for the others raspberry pi and arduinos),
I will try change the bind address, thk

[phods]

I have a RPi3 "server" with MySQL, and I want acess with another RPi3 client (python script) and with some arduinos. but I can acess with RPi3's client when I use ssh tunnel, but not with the arduino.
Can I disable the ssh security and connect directly?

Is that within your private LAN or are you thinking of connecting from the public internet?

If it's your LAN then

Code: Select all

bind-address            = 127.0.0.1

changes to

Code: Select all

bind-address            = 0.0.0.0

in /etc/mysql/my.cnf and a restart will open MySQL to your LAN.

If you're thinking of doing it across the public internet then stick with the ssh tunnel.

thank you Dougie, change the bind work, just like I want. thank you