MySQL
Mysql ese motor tan amplio
- Obtener el MAX y el MIN en un objeto json dentro de una columna json
- DBeaver (java - mysql) Public Key Retrieval is not allowed
- Mysql no reduce espacio consumido tras un delete en tablas InnoDb
- Mysql conexión vía socket. Como saber donde está
Obtener el MAX y el MIN en un objeto json dentro de una columna json
Información previa
A veces tenemos que obtener información contenida en columnas tipo json, existentes en nuestra base de datos.
Aquí vamos a ver cómo podemos hacerlo.
En el momento de escribir este artículo no encontré forma de hacerlo en Laravel 9. Si te animas hay un post en Laracast - Is there a way to get the max of a JSON array in MYSQL (no hace falta ser usuario de pago)
Escenario
Tabla con una columna tipo json llamada data
. Su contenido tipo es el de abajo, y buscamos el MIN y el MAX del valor response_time
En este escenario el valor está normalizado number
{
"data": {
"csq": -62,
"icc": "8944501801213377498",
"imei": "869640059692007",
"device": "modem",
"sunset": 1652071940,
"sunrise": 1652124780,
"datetime": 1652082623,
"response_time": 11.29
},
"message": "successful",
"success": true
}
Sql
mysql> SELECT MAX(JSON_EXTRACT(data, "$.data.response_time")) as response FROM table;
+----------+
| response |
+----------+
| 55.88 |
+----------+
1 row in set (0,03 sec)
mysql> SELECT MIN(JSON_EXTRACT(data, "$.data.response_time")) as response FROM table;
+----------+
| response |
+----------+
| 6.15 |
+----------+
1 row in set (0,04 sec)
Bonus
Es posible, sobre todo si trabajamos en un proyecto no depurado todavia en el que guardamos datos a los bruto, sin normalización o verificación, que nos salgan valores anormales.
Entonces es bueno obtener los datos de un número limitado de la respuesta ordenados para ver si es algo que falla.
SELECT id, JSON_EXTRACT(data, "$.data.response_time") as response FROM tabla ORDER BY JSON_EXTRACT(data, "$.data.response_time") DESC LIMIT 20;
+--------+----------+
| id | response |
+--------+----------+
| 224623 | 600.47 |
| 200807 | 598.7 |
| 177004 | 598.54 |
| 177006 | 598.29 |
| 177005 | 598.29 |
| 177008 | 597.68 |
| 153187 | 596.45 |
| 153189 | 596.33 |
| 129369 | 595.87 |
| 129368 | 595.85 |
| 129371 | 595.33 |
| 10806 | 588.38 |
| 10807 | 588.35 |
| 10809 | 588.19 |
| 273886 | 25.08 |
| 24709 | 22.62 |
| 265865 | 22.32 |
| 92768 | 20.92 |
| 275543 | 20.91 |
| 122073 | 20.15 |
+--------+----------+
20 rows in set (1,30 sec)
Evidentemente aquí hay una desviación bastante clara que debemos limpiar o revisar.
Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad. El contenido el contenido se entrega, tal y como esta, sin que ello implique ningún obligación ni responsabilidad por parte de Castris
Si necesitas soporte profesional puedes contratar con Castris soporte profesional.
DBeaver (java - mysql) Public Key Retrieval is not allowed
Introducción
DBeaver es una app para manejo remoto de bases de datos de distinto motores, basada en Java.
Sin embargo a veces aparacen errores un tanto extraños para quienes no estamos acostumbrados a Java, como puede ser, Public Key Retrieval is not allowed al tratar de configurar un tunle de accesso SSH a una máquina remota.
Solución
En el post Connection Java - MySQL : Public Key Retrieval is not allowed hay una respuesta válida escrita por Javier Aviles
- Editar conexión
- Driver Propierties
- Añadir en user propierties dos nuevas propiedades: useSSL con valor false y allowPublicKeyRetrieval con valor true
Esto puede diferrir entre versiones.
Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad. El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de Castris
Si necesitas soporte profesional puedes contratar con Castris soporte profesional.
Mysql no reduce espacio consumido tras un delete en tablas InnoDb
Introducción
Muchas veces los administradores de sistemas, o los clientes de estos, no son conscientes que no es lo mismo jugar o trabajar con bases de datos de tamaño medio, y otra muy distinta es enfrentarse a databases de 40GB o más.
En estos entornos, mysqldump no es una opción viable, y se hace necesario el uso de una estructura maestro-esclavo como mínimo, para hacer los backups en el esclavo, en lugar de en el maestro, con sus correspondientes problemas además de ser un medio eficaz, por si algo ocurre.
Muchos Sysadmin jamás se han enfrentado a una restore de un servidor mysql de más de 50GB y los que lo han hecho saben la de cositas que pueden salir por ahí.
Recuperación de espacio
Bien, muchas veces en algunos escenarios hay que vaciar tablas pesadas, ya sea por fechas o contenidos, pero tras el vaciado este no suele ocurrir una reducción del espacio en el servidor.
En este documento hablo de mi experiencia con Mysql 8 en una base de datos con InnoDb, y además con el valor innodb_file_per_table
activado. Esto es importante, pues de lo contrario el ataque debe ser realizado con otra óptica.
mysql> show variables like "innodb_file_per_table";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
Liberar espacio
Por ejemplo queremo liberar datos en una tabla entre dos fechas.
> mysql
mysql> use mi_database;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> DELETE FROM logger_xxxxx WHERE created_at BETWEEN '2023-01-01 00:00:00' AND '2023-04-30 23:59:59';
Query OK, 1849983 rows affected (5 min 55,64 sec)
mysql> ALTER TABLE logger_xxxxx ENGINE=InnoDB;
Query OK, 0 rows affected (6 min 3,97 sec)
Records: 0 Duplicates: 0 Warnings: 0
Evidentemente la tabla ya estaba en formato InnoDb, pero el comando en sí mismo realizado, una optimización, como la que haríamos con mysqlcheck --auto-repair --optimize database
sólo que con ese comando las tablas InnoDb no se optimizarán.
Agradecimientos
En particular mysqldump.guru donde encontrarás maravillosos snipets para usar con mysql.
Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad. El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de Castris
Si necesitas soporte profesional puedes contratar con Castris soporte profesional.
Mysql conexión vía socket. Como saber donde está
Introducción
Soy amigo de usar los sockets para casi todo lo que puedo por eficacia. Asi que una que suele ocurrirme, es cuando entro a una máquina nueva que no se donde esta.
Revisar el Archivo de Configuración de MySQL
El archivo de configuración de MySQL generalmente se encuentra en /etc/my.cnf o /etc/mysql/my.cnf. Puedes buscar la línea que define la ubicación del socket.
grep -i 'socket' /etc/my.cnf /etc/mysql/my.cnf
socket = /var/run/mysqld/mysqld.sock
A veces no te da nada o te da error porque no encuentra alguno de los ficheros.
Usar el Cliente MySQL
Puedes conectarte al servidor MySQL y ejecutar un comando SQL para obtener la información del socket
mysql -u root -p -e "SHOW VARIABLES LIKE 'socket';"
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| socket | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+
Revisar el Estado del Servicio MySQL
No suele decir mucho proque depende de como esta configurado el servicio en systemctl
grep -i 'socket' /var/log/mysql/*.log /var/log/mysqld.log
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sun 2024-07-07 15:39:12 UTC; 4 weeks 2 days ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 992459 (mysqld)
Status: "Server is operational"
Tasks: 66 (limit: 48144)
Memory: 7.8G
CGroup: /system.slice/mysql.service
└─992459 /usr/sbin/mysqld
Warning: journal has been rotated since unit was started, output may be incomplete.
lsof
❯ lsof -U | grep mysql
mysqld 992459 mysql 1u unix 0xffff9411ca168000 0t0 96369986 type=STREAM
mysqld 992459 mysql 2u unix 0xffff9411ca168000 0t0 96369986 type=STREAM
mysqld 992459 mysql 3u unix 0xffff9411ca16b740 0t0 96369989 type=DGRAM
mysqld 992459 mysql 21u unix 0xffff941565e79540 0t0 96373782 /var/run/mysqld/mysqlx.sock type=STREAM
mysqld 992459 mysql 25u unix 0xffff941565e7ee80 0t0 96373785 /var/run/mysqld/mysqld.sock type=STREAM
mysqld 992459 mysql 235u unix 0xffff941730f6c840 0t0 272319038 /var/run/mysqld/mysqld.sock type=STREAM
mysqld 992459 mysql 242u unix 0xffff94194a1d1980 0t0 272319039 /var/run/mysqld/mysqld.sock type=STREAM
mysqld 992459 mysql 243u unix 0xffff941646f8aec0 0t0 272319040 /var/run/mysqld/mysqld.sock type=STREAM
mysqld 992459 mysql 521u unix 0xffff94173c81ea40 0t0 272290305 /var/run/mysqld/mysqld.sock type=STREAM
mysqld 992459 mysql 524u unix 0xffff94190a088440 0t0 272290325 /var/run/mysqld/mysqld.sock type=STREAM
mysqld 992459 mysql 525u unix 0xffff941837156a40 0t0 272290403 /var/run/mysqld/mysqld.sock type=STREAM
mysqld 992459 mysql 528u unix 0xffff9417321b2200 0t0 272289661 /var/run/mysqld/mysqld.sock type=STREAM
mysqld 992459 mysql 529u unix 0xffff9417321b3b80 0t0 272289664 /var/run/mysqld/mysqld.sock type=STREAM
Aviso
Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad. El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de Castris
Si necesitas soporte profesional puedes contratar con Castris soporte profesional.