sudo apt update
sudo apt install mosquitto mosquitto-clients -y
sudo apt update
sudo apt install mosquitto mosquitto-clients -y
sudo systemctl start mosquitto
sudo systemctl enable mosquitto
sudo nano /etc/mosquitto/mosquitto.conf
listener 1883
allow_anonymous true
sudo systemctl restart mosquitto
Código para el ESP32
#include <WiFi.h>
#include <PubSubClient.h>
const char* ssid = "RED DIAZ1";
const char* password = "no tengo clave";
const char* mqttServer = "broker.hivemq.com"; // O usa la IP de tu Azure
const int mqttPort = 1883;
WiFiClient espClient;
PubSubClient client(espClient);
void setup() {
Serial.begin(115200);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("Conectado a WiFi");
client.setServer(mqttServer, mqttPort);
while (!client.connected()) {
if (client.connect("ESP32Client")) {
Serial.println("Conectado al Broker MQTT");
} else {
Serial.print("Error MQTT: ");
Serial.println(client.state());
delay(2000);
}
}
}
void loop() {
int distancia = random(10, 100);
float temperatura = random(20, 40) / 1.0;
int luminosidad = random(100, 1000);
float voltaje = random(3, 5) / 1.0;
String payload = "{\"distancia\": " + String(distancia) +
", \"temperatura\": " + String(temperatura) +
", \"luminosidad\": " + String(luminosidad) +
", \"voltaje\": " + String(voltaje) + "}";
client.publish("sensores/valores", payload.c_str());
Serial.println("Datos publicados: " + payload);
delay(5000);
}
sudo apt install python3-pip
pip3 install paho-mqtt mysql-connector-python
y
sudo nano /var/www/html/mqtt_to_mysql.py
import paho.mqtt.client as mqtt
import mysql.connector
import json
# Configuración de la base de datos MySQL
db = mysql.connector.connect(
host="localhost",
user="root", # Cambia esto si tienes otro usuario
password="admin", # Ajusta tu contraseña
database="ESP32AZ" # Base de datos donde guardarás los datos
)
cursor = db.cursor()
# Callback cuando se recibe un mensaje MQTT
def on_message(client, userdata, message):
try:
# Decodificar el mensaje y convertirlo a JSON
data = json.loads(message.payload.decode("utf-8"))
print("Datos recibidos:", data)
# Insertar los datos en la base de datos
sql = "INSERT INTO sensor_data (luminosidad, distancia, temperatura, voltaje) VALUES (%s, %s, %s, %s)"
values = (data['luminosidad'], data['distancia'], data['temperatura'], data['voltaje'])
cursor.execute(sql, values)
db.commit()
print("Datos guardados en la base de datos:", values)
except Exception as e:
print("Error:", e)
# Configuración del cliente MQTT
client = mqtt.Client()
mqtt_broker = "broker.hivemq.com" # Usa tu servidor MQTT si es local o en Azure
mqtt_topic = "sensores/valores"
# Conectar al broker y suscribirse al tópico
client.connect(mqtt_broker, 1883, 60)
print("Conectado al broker MQTT:", mqtt_broker)
client.subscribe(mqtt_topic)
print("Suscrito al tópico:", mqtt_topic)
# Asignar el callback
client.on_message = on_message
# Mantener el cliente en funcionamiento
print("Esperando mensajes...")
client.loop_forever()
cd /var/www/html/
sudo python3 mqtt_to_mysql.py
sudo pip3 install --upgrade paho-mqtt
python3 -c "import paho.mqtt.client; print('Paho MQTT instalado correctamente')"
sudo apt install python3-pip --reinstall
sudo pip3 install mysql-connector-python
python3 -c "import mysql.connector; print('MySQL Connector instalado correctamente')"
sudo python3 mqtt_to_mysql.py
sudo mysql -u root -p
USE ESP32AZ;
SELECT * FROM sensor_data;
sudo nano /etc/systemd/system/mqtt_to_mysql.service
[Unit]
Description=MQTT to MySQL Service
After=network.target
[Service]
ExecStart=/usr/bin/python3 /var/www/html/mqtt_to_mysql.py
Restart=always
User=root
[Install]
WantedBy=multi-user.target
sudo systemctl daemon-reload
sudo systemctl enable mqtt_to_mysql.service
sudo systemctl start mqtt_to_mysql.service
sudo systemctl status mqtt_to_mysql.service
Opcion 2
sudo apt update && sudo apt upgrade -y
sudo apt install mosquitto mosquitto-clients -y
sudo systemctl enable mosquitto
sudo systemctl start mosquitto
sudo systemctl status mosquitto
sudo mysql -u root -p
CREATE DATABASE ESP32MQTT;
USE ESP32MQTT;
CREATE TABLE sensor_data (
id INT AUTO_INCREMENT PRIMARY KEY,
luminosidad FLOAT,
distancia FLOAT,
temperatura FLOAT,
voltaje FLOAT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
EXIT;
sudo apt install python3-pip -y
pip3 install paho-mqtt mysql-connector-python
sudo nano /var/www/html/mqtt_to_mysql.py
import paho.mqtt.client as mqtt
import mysql.connector
import json
# Configuración de la base de datos
db = mysql.connector.connect(
host="localhost",
user="root",
password="admin",
database="ESP32MQTT"
)
cursor = db.cursor()
# Callback cuando se recibe un mensaje
def on_message(client, userdata, message):
data = json.loads(message.payload)
sql = "INSERT INTO sensor_data (luminosidad, distancia, temperatura, voltaje) VALUES (%s, %s, %s, %s)"
values = (data['luminosidad'], data['distancia'], data['temperatura'], data['voltaje'])
cursor.execute(sql, values)
db.commit()
print("Datos guardados:", values)
# Configuración del cliente MQTT
client = mqtt.Client()
client.connect("broker.hivemq.com", 1883, 60)
client.subscribe("sensores/valores")
client.on_message = on_message
print("Conectado al Broker MQTT. Esperando datos...")
client.loop_forever()
python3 /var/www/html/mqtt_to_mysql.py
sudo nano /var/www/html/espmqtt.php
<?php
// Configuración de la base de datos
$host = "localhost";
$user = "root";
$pass = "admin";
$dbname = "ESP32MQTT";
$conn = new mysqli($host, $user, $pass, $dbname);
if ($conn->connect_error) {
die("Conexión fallida: " . $conn->connect_error);
}
$sql = "SELECT * FROM sensor_data ORDER BY timestamp DESC LIMIT 10";
$result = $conn->query($sql);
$data = [];
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
$conn->close();
?>
<!DOCTYPE html>
<html>
<head>
<title>MQTT Dashboard</title>
<script src="https://code.highcharts.com/highcharts.js"></script>
</head>
<body>
<h1>Datos en Tiempo Real (MQTT)</h1>
<div id="chart" style="width: 600px; height: 400px;"></div>
<script>
const data = <?php echo json_encode($data); ?>;
const categories = data.map(item => item.timestamp);
const luminosidad = data.map(item => parseFloat(item.luminosidad));
const distancia = data.map(item => parseFloat(item.distancia));
const temperatura = data.map(item => parseFloat(item.temperatura));
const voltaje = data.map(item => parseFloat(item.voltaje));
Highcharts.chart('chart', {
title: { text: 'Valores de Sensores' },
xAxis: { categories: categories },
series: [
{ name: 'Luminosidad', data: luminosidad },
{ name: 'Distancia', data: distancia },
{ name: 'Temperatura', data: temperatura },
{ name: 'Voltaje', data: voltaje }
]
});
</script>
</body>
</html>
sudo systemctl restart apache2
python3 /var/www/html/mqtt_to_mysql.py
mosquitto_pub -h localhost -t "sensores/valores" -m "Prueba MQTT" -r
mosquitto_sub -h localhost -t "sensores/valores"
version 3
nano mqtt_subscriber.py
broker = "test.mosquitto.org" # Broker público correcto
port = 1883 # Puerto estándar MQTT
client.subscribe("/ESP32/ultrasonico")
client.subscribe("/ESP32/temp1")
client.subscribe("/ESP32/temp2")
client.subscribe("/ESP32/ldr")
sudo nano /var/www/html/tablamqtt.php
<?php
// Configuración de la base de datos
$host = "localhost";
$user = "root"; // Usuario de MySQL
$pass = "admin"; // Contraseña de MySQL
$dbname = "MQTTDB"; // Base de datos creada
// Conexión a la base de datos
$conn = new mysqli($host, $user, $pass, $dbname);
// Verificar la conexión
if ($conn->connect_error) {
die("Conexión fallida: " . $conn->connect_error);
}
// Consulta SQL para obtener el último valor de cada variable
$sql = "
SELECT topic, payload, timestamp
FROM sensor_data
WHERE id IN (
SELECT MAX(id)
FROM sensor_data
GROUP BY topic
)
ORDER BY timestamp DESC;
";
$result = $conn->query($sql);
// Cerrar conexión si no hay datos
if (!$result || $result->num_rows === 0) {
die("No hay datos disponibles.");
}
// Organizar los datos en un array asociativo
$data = [];
while ($row = $result->fetch_assoc()) {
$data[$row['topic']] = [
'payload' => $row['payload'],
'timestamp' => $row['timestamp']
];
}
$conn->close();
?>
<!DOCTYPE html>
<html lang="es">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Tabla de Datos MQTT</title>
<style>
table {
width: 50%;
margin: 20px auto;
border-collapse: collapse;
text-align: center;
}
th, td {
border: 1px solid #ddd;
padding: 8px;
}
th {
background-color: #4CAF50;
color: white;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
</style>
</head>
<body>
<h1 style="text-align:center;">Últimos Valores de Sensores MQTT</h1>
<table>
<tr>
<th>Variable</th>
<th>Valor</th>
<th>Timestamp</th>
</tr>
<tr>
<td>Distancia (Ultrasonico)</td>
<td><?= isset($data['/ESP32/ultrasonico']['payload']) ? $data['/ESP32/ultrasonico']['payload'] : 'N/A'; ?></td>
<td><?= isset($data['/ESP32/ultrasonico']['timestamp']) ? $data['/ESP32/ultrasonico']['timestamp'] : 'N/A'; ?></td>
</tr>
<tr>
<td>Temperatura 1 (LM35_1)</td>
<td><?= isset($data['/ESP32/temp1']['payload']) ? $data['/ESP32/temp1']['payload'] : 'N/A'; ?></td>
<td><?= isset($data['/ESP32/temp1']['timestamp']) ? $data['/ESP32/temp1']['timestamp'] : 'N/A'; ?></td>
</tr>
<tr>
<td>Voltaje (LM35_2)</td>
<td><?= isset($data['/ESP32/temp2']['payload']) ? $data['/ESP32/temp2']['payload'] : 'N/A'; ?></td>
<td><?= isset($data['/ESP32/temp2']['timestamp']) ? $data['/ESP32/temp2']['timestamp'] : 'N/A'; ?></td>
</tr>
<tr>
<td>Luminosidad (LDR)</td>
<td><?= isset($data['/ESP32/ldr']['payload']) ? $data['/ESP32/ldr']['payload'] : 'N/A'; ?></td>
<td><?= isset($data['/ESP32/ldr']['timestamp']) ? $data['/ESP32/ldr']['timestamp'] : 'N/A'; ?></td>
</tr>
</table>
</body>
</html>
sudo systemctl restart apache2
http://172.172.28.74//tablamqtt.php
sudo nano /var/www/html/tablamqtt.php
<?php
// Configuración de la base de datos
$host = "localhost";
$user = "root"; // Usuario de MySQL
$pass = "admin"; // Contraseña de MySQL
$dbname = "MQTTDB"; // Base de datos creada
// Conexión a la base de datos
$conn = new mysqli($host, $user, $pass, $dbname);
// Verificar la conexión
if ($conn->connect_error) {
die("Conexión fallida: " . $conn->connect_error);
}
// Consulta SQL para obtener el último valor de cada variable
$sql = "
SELECT topic, payload, timestamp
FROM sensor_data
WHERE id IN (
SELECT MAX(id)
FROM sensor_data
GROUP BY topic
)
ORDER BY timestamp DESC;
";
$result = $conn->query($sql);
// Organizar los datos en un array asociativo
$data = [];
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$data[$row['topic']] = [
'payload' => $row['payload'],
'timestamp' => $row['timestamp']
];
}
}
$conn->close();
// Devolver JSON si se hace una petición AJAX
if (isset($_GET['ajax'])) {
header('Content-Type: application/json');
echo json_encode($data);
exit;
}
?>
<!DOCTYPE html>
<html lang="es">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Tabla de Datos MQTT</title>
<style>
table {
width: 50%;
margin: 20px auto;
border-collapse: collapse;
text-align: center;
}
th, td {
border: 1px solid #ddd;
padding: 8px;
}
th {
background-color: #4CAF50;
color: white;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
</style>
<script>
function updateTable() {
fetch('tablamqtt.php?ajax=1')
.then(response => response.json())
.then(data => {
document.getElementById('distancia').innerText = data['/ESP32/ultrasonico'] ? data['/ESP32/ultrasonico']['payload'] : 'N/A';
document.getElementById('distancia_time').innerText = data['/ESP32/ultrasonico'] ? data['/ESP32/ultrasonico']['timestamp'] : 'N/A';
document.getElementById('temp1').innerText = data['/ESP32/temp1'] ? data['/ESP32/temp1']['payload'] : 'N/A';
document.getElementById('temp1_time').innerText = data['/ESP32/temp1'] ? data['/ESP32/temp1']['timestamp'] : 'N/A';
document.getElementById('temp2').innerText = data['/ESP32/temp2'] ? data['/ESP32/temp2']['payload'] : 'N/A';
document.getElementById('temp2_time').innerText = data['/ESP32/temp2'] ? data['/ESP32/temp2']['timestamp'] : 'N/A';
document.getElementById('ldr').innerText = data['/ESP32/ldr'] ? data['/ESP32/ldr']['payload'] : 'N/A';
document.getElementById('ldr_time').innerText = data['/ESP32/ldr'] ? data['/ESP32/ldr']['timestamp'] : 'N/A';
});
}
setInterval(updateTable, 3000); // Actualiza cada 3 segundos
window.onload = updateTable; // Cargar datos al inicio
</script>
</head>
<body>
<h1 style="text-align:center;">Últimos Valores de Sensores MQTT</h1>
<table>
<tr>
<th>Variable</th>
<th>Valor</th>
<th>Timestamp</th>
</tr>
<tr>
<td>Distancia (Ultrasonico)</td>
<td id="distancia">N/A</td>
<td id="distancia_time">N/A</td>
</tr>
<tr>
<td>Temperatura 1 (LM35_1)</td>
<td id="temp1">N/A</td>
<td id="temp1_time">N/A</td>
</tr>
<tr>
<td>Voltaje (LM35_2)</td>
<td id="temp2">N/A</td>
<td id="temp2_time">N/A</td>
</tr>
<tr>
<td>Luminosidad (LDR)</td>
<td id="ldr">N/A</td>
<td id="ldr_time">N/A</td>
</tr>
</table>
</body>
</html>
sudo systemctl restart apache2
<?php
// Configuración de la base de datos
$host = "localhost";
$user = "root"; // Usuario de MySQL
$pass = "admin"; // Contraseña de MySQL
$dbname = "MQTTDB"; // Base de datos creada
// Conexión a la base de datos
$conn = new mysqli($host, $user, $pass, $dbname);
// Verificar la conexión
if ($conn->connect_error) {
die("Conexión fallida: " . $conn->connect_error);
}
// Consulta SQL para obtener el último valor de cada variable
$sql = "
SELECT topic, payload, timestamp
FROM sensor_data
WHERE id IN (
SELECT MAX(id)
FROM sensor_data
GROUP BY topic
)
ORDER BY timestamp DESC;
";
$result = $conn->query($sql);
// Organizar los datos en un array asociativo
$data = [];
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$data[$row['topic']] = [
'payload' => $row['payload'],
'timestamp' => $row['timestamp']
];
}
}
$conn->close();
// Devolver JSON si se hace una petición AJAX
if (isset($_GET['ajax'])) {
header('Content-Type: application/json');
echo json_encode($data);
exit;
}
?>
<!DOCTYPE html>
<html lang="es">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Tabla y Gráficos en Tiempo Real</title>
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/highcharts-more.js"></script>
<script src="https://code.highcharts.com/modules/solid-gauge.js"></script>
<style>
table {
width: 50%;
margin: 20px auto;
border-collapse: collapse;
text-align: center;
}
th, td {
border: 1px solid #ddd;
padding: 8px;
}
th {
background-color: #4CAF50;
color: white;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
.gauge-container {
width: 300px;
height: 200px;
display: inline-block;
margin: 20px;
}
</style>
<script>
let gauges = {};
function createGauge(containerId, title, value) {
return Highcharts.chart(containerId, {
chart: {
type: 'solidgauge'
},
title: { text: title },
pane: {
startAngle: -150,
endAngle: 150,
background: [{
outerRadius: '100%',
innerRadius: '60%',
backgroundColor: Highcharts.color('#EEE').setOpacity(0.3).get(),
borderWidth: 0
}]
},
yAxis: {
min: 0,
max: 100,
stops: [[0.3, '#55BF3B'], [0.7, '#DDDF0D'], [1, '#DF5353']],
title: { text: title }
},
series: [{
name: title,
data: [value],
tooltip: { valueSuffix: '' }
}]
});
}
function updateTableAndGauges() {
fetch('tablamqtt.php?ajax=1')
.then(response => response.json())
.then(data => {
document.getElementById('distancia').innerText = data['/ESP32/ultrasonico'] ? data['/ESP32/ultrasonico']['payload'] : 'N/A';
document.getElementById('temp1').innerText = data['/ESP32/temp1'] ? data['/ESP32/temp1']['payload'] : 'N/A';
document.getElementById('temp2').innerText = data['/ESP32/temp2'] ? data['/ESP32/temp2']['payload'] : 'N/A';
document.getElementById('ldr').innerText = data['/ESP32/ldr'] ? data['/ESP32/ldr']['payload'] : 'N/A';
// Actualizar los gauges
updateGauge('gauge1', data['/ESP32/ultrasonico'] ? parseFloat(data['/ESP32/ultrasonico']['payload']) : 0);
updateGauge('gauge2', data['/ESP32/temp1'] ? parseFloat(data['/ESP32/temp1']['payload']) : 0);
updateGauge('gauge3', data['/ESP32/temp2'] ? parseFloat(data['/ESP32/temp2']['payload']) : 0);
updateGauge('gauge4', data['/ESP32/ldr'] ? parseFloat(data['/ESP32/ldr']['payload']) : 0);
});
}
function updateGauge(containerId, value) {
if (gauges[containerId]) {
gauges[containerId].series[0].setData([value]);
} else {
gauges[containerId] = createGauge(containerId, containerId, value);
}
}
setInterval(updateTableAndGauges, 3); // Actualiza cada 3 segundos
window.onload = updateTableAndGauges;
</script>
</head>
<body>
<h1 style="text-align:center;">Últimos Valores de Sensores MQTT</h1>
<table>
<tr>
<th>Variable</th>
<th>Valor</th>
</tr>
<tr>
<td>Distancia (Ultrasonico)</td>
<td id="distancia">N/A</td>
</tr>
<tr>
<td>Temperatura 1 (LM35_1)</td>
<td id="temp1">N/A</td>
</tr>
<tr>
<td>Voltaje (LM35_2)</td>
<td id="temp2">N/A</td>
</tr>
<tr>
<td>Luminosidad (LDR)</td>
<td id="ldr">N/A</td>
</tr>
</table>
<!-- Contenedores para los gráficos tipo velocímetro -->
<div class="gauge-container" id="gauge1"></div>
<div class="gauge-container" id="gauge2"></div>
<div class="gauge-container" id="gauge3"></div>
<div class="gauge-container" id="gauge4"></div>
</body>
</html>
No hay comentarios.:
Publicar un comentario