Archivos de Categoría: MySQL - Paginas 2

Activar log de MySQL almacenándolo en base de datos

Hoy me he estado acordando de cierto argentino que iba de profesional, resulta que una serie de páginas que muestran estadísticas van a pedales y cuando les he echado un vistazo me encuentro que llegaba en ocasiones a dar cascar la base de datos.

Como es una web montada con PHP a pelo y de aquella forma, la opción más rápida para ver que consultas se ejecutaban y cuales daban problemas, ha sido activar el log de MySQL (y cuando ves que hay consultas que tardan más de 5 segundos es que hay un problema….).

Primero avisar que hay dos tipos de log:

  • general_log: que muestra cuando se ha ejecutado una consulta, usuario que ejecuta la consulta , el hilo de ejecución (útil por si tienes que matarlo porque se ha quedado pillado), el servidor, el tipo de consulta y la consulta ejecutada.
  • slow_log: que muestra cuando se ha empezado a ejecutar la consulta, el usuario que ejecuta la consulta, el tiempo de ejecución, tiempos de bloqueo, número de registros devueltos, número de registros donde se han buscado los resultados, la base de datos donde se ha ejecutado, ultimo id insertado, id insertado, el servidor y la consulta ejecutada.

Para activarlos se puede hacer desde el fichero de configuración de MySQL pero también se puede activar desde un cliente de MySQL (con un usuario que tenga permisos) con las siguientes sentencias:

-- Activar el log general:
SET global general_log = 1;

-- Activar el log detallado:
SET global long_query_time = 1;
SET global slow_query_log = 1;

-- Si queremos que los log se almacenen en base de datos en vez de en ficheros de texto:
SET global log_output = 'table';

Al almacenar los logs en base de datos conseguiremos poder consultarlos más fácilmente a parte de no tener que estar buscando el fichero de los logs en el servidor ya que se almacenarar en las tablas general_log y slow_log de la base de datos mysql. Por lo que para consultar los log podríamos hacerlo así:

select * from mysql.general_log;

select * from mysql.slow_log;

Para desactivarlos ejecutaríamos las mismas sentencias que hemos usado para activarlos pero con valor a cero.

PD: importante, si tenéis tablas con muchos registros debéis meter índices en los campos por los que vais a filtrar en las consultas.
PD2: aprender bien SQL, porque hay bastante diferencia entre una consulta con 13 union y sacar los mismos datos con un simple group by…

Buscador usando comillas para detectar palabras exactas

La gente no lo suele implementar en los buscadores pero siempre queda bien hacer que cuando se busca por palabras sueltas y mete varias entre comillas se haga la búsqueda por esas exactas. Por ejemplo si buscamos gatos «bosque siberiano» lo suyo es que haga la consulta:

Where campo like '%gatos%' or campo like '%bosque siberiano%'

Lo primero es la consulta, aunque es un poco más lenta, pero es más sencillo construirla con una expresión regular:

Where campo REGEXP '(gatos|bosque siberiano)'

Y para construir la cadena a ejecutar:

// cargamos en un array las palabras entre comillas
preg_match_all('/".*?"/', $_POST["search"], $matches);
foreach ($matches[0] as  $value) {
   // las palabras encontradas las borramos de la cadena original
   $_POST["search"] = str_replace($value, '', $_POST["search"]);
}
// cargamos en un array las palabras sueltas que no estaban entre comillas  y las unimos a las que si lo estaban 
$palabras = array_merge(explode(' ', $_POST["search"]), $matches[0]);
// eliminamos todos los posibles elementos vacios del array
$palabras = array_filter($palabras, function($value) { return $value !== ''; });
// y los unimos con el separador | que vamos a usar en la consulta
$cadena = str_replace('"', '', implode('|', $palabras));
// y ya tenemos el where
$where .= sprintf(" campo  REGEXP '(%s)'", mysql_escape_string($cadena));

Borrar todas las tablas de la base de datos

No es normal tener que borrar todas las tablas de una base de datos pero cuando ocurre lo más sencillo es eliminar y volver a crear la base de datos:

drop database NOMBRE_BASE_DATOS_A_BORRAR;
create database NOMBRE_BASE_DATOS_A_BORRAR;

pero este sistema tambien se cargaría posibles permisos que tengas sobre dicha base de datos por lo que es mejor eliminar solo las tablas:

SET FOREIGN_KEY_CHECKS = 0; 
SET @tables = NULL;
SELECT GROUP_CONCAT(table_schema, '.', table_name) INTO @tables
  FROM information_schema.tables 
  WHERE table_schema = 'NOMBRE_BASE_DATOS_A_BORRAR';

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1;