Cyanogen

Logo phpMyAdmin

Aligerar el peso de phpMyAdmin

Si te dedicas a la consultoría web estarás acostumbrado a manejar phpMyAdmin como cliente de MySQL sobre la web. Sin lugar a dudas, pasa por ser la solución la solución más completa de las que existen en este campo y teniendo la precaución evidente de no dejarlo colgado de una ruta demasiado visible que pueda ser rastreada hasta por los bots que andan buscando sitios susceptibles de ser atacados, no deberíamos tener ningún problema usándolo de cara a seguridad (la idea es la que os digo, taparlo un poco usando una ruta rara y al dejar de usarlo borrarlo inmediatamente).

Como veis la idea es la siguiente:

  • Subir phpMyAdmin
  • Esconderlo en una ruta diferente a las estándares
  • Usarlo
  • Eliminarlo cuanto lo hayamos terminado de usar

Logo phpMyAdmin

El problema que tiene este método es que phpMyAdmin tiene que ser subido/bajado repetidas veces y para que os hagáis una idea de su peso, la versión actual ocupa un total de 27,4 MB siendo 1.538 archivos si tenéis que hacer esta subida por FTP y con una línea ADSL... vais a perder mucho tiempo.

La solución: aligerar lo que no es necesario

Si entráis dentro del paquete que se descarga veréis que hay algunas carpetas que no son necesarias y que tienen bastante peso, ¿de cuáles os hablo?

  • doc: que contiene documentación del proyecto
  • examples: ejemplos de uso
  • locale: traducciones, si no os importa trabajar en inglés podéis borrarla al completo

Tras borrar estas carpetas, volvemos a ver cuánto peso queda y tenemos que se ha reducido a prácticamente la mitad: 14,2MB aunque el número de archivos no lo ha hecho en la misma medida, 1.399 archivos.

Lo mejor de mi RSS del 7 al 13 de abril de 2014

Llegó ya el Domingo de Ramos y eso es sinónimo de días de desconexión para prácticamente todos. Supongo que esta semana que ahora empieza, será menos intensa pero esta que acaba ha sido más próspera en generación de contenido que semanas anteriores. Veamos lo más interesante.

Foreign key

Error en MySQL: Can’t create table ‘xxx’ (errno: 150)

Soy de los que piensa que MySQL (o MariaDB si queréis algo no dependiente de Oracle) es una gran base de datos incluso para desarrollos serios y lo pongo en negrita porque todavía hoy me encuentro con muchos que defienden alternativas como Oracle DB o PostgreSQL para aplicaciones más complejas y MySQL para aplicaciones más sencillas.

Una de las características que debe tener todo modelo relacional medianamente serio, es el uso de claves foráneas que deben ir especificadas en la construcción de las diferentes tablas de forma que conozcan que campos hacen referencia a qué otros campos. Esto con InnoDB es perfectamente posible en MySQL.

Foreign key

Aquí tenéis la documentación y un ejemplo de cómo crear restricciones de clave foránea de MySQL:

CREATE TABLE padre(
  id INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE hijo(
  id INT, 
  id_padre INT,
  INDEX par_ind (id_padre),
  FOREIGN KEY (id_padre) 
    REFERENCES padre(id) 
    ON DELETE CASCADE
) ENGINE=INNODB;

El ejemplo es sencillo pero a la vez muy común, es muy frecuente tener una tabla hija que haga referencia al id de su tabla padre: por ejemplo el típico ejemplo de factura y línea factura.

Error: Can’t create table 'xxx' (errno: 150)

Haciendo algo similar a esto, esta mañana he sufrido un error (el del título) que nunca antes me había pasado. He empezado a comprobar y tras dar muchas vueltas he dado con el fallo: los dos tipos de datos deben ser idénticos.

Y cuando idénticos digo: mismo tipo de datos, misma longitud, signed o unsigned los dos, si es not null igual el otro.

Esto es lo lógico pero a mí se me había escapado un detalle, uno era signed y el otro unsigned y he perdido unos minutos hasta dar con el error (tras la primera y típica reacción "será problema de MySQL").

MySQL logo

Ordenando consultas con valores NULL en MySQL

MySQL logoCuando trabajamos con bases de datos, normalmente tendremos casos en los que existan valores nulos tanto en nuestras tablas como en el resultado de nuestras consultas. Sin embargo, esta realidad, también provoca que algunas acciones como las ordenaciones sean problemáticas. Para poder superar estas dificultades disponemos de funciones como coalesce() (buscad dentro del enlace).

La función coalesce()

¿Qué hace esta función? Recibe una serie de parámetros y devuelve el primero que no es nulo.

Forma de uso

Por lo tanto, si tenemos una columna edad, por ejemplo, que puede ser nulo o no serlo, y ponemos:

COALESCE(edad, 0) esta función devolverá la edad si no es nula y si es nula, el primer valor no nulo de la lista de parámetros, como sólo hay dos parámetros, será el siguiente, 0.

Ordenando con valores nulos

Siguiendo este ejemplo podríamos hacer una ordenación a nuestro antojo, es decir, si queremos ordenar primero las fechas de los más mayores, es decir, en sentido descendente y poner al final los valores nulos, podríamos hacer justo:

SELECT nombre, apellidos FROM personas ORDER BY (edad, 0)

Logo MySQL

Listar todas las tablas de una base de datos en MySQL

Siempre que trabajamos con MySQL o con cualquier otro sistema gestor de bases de datos relacional tenemos la costumbre de hacer operaciones sobre las tablas y no sobre las bases de datos en sí. Básicamente lo único que hacemos es:

 USE mi_base_de_datos

O con el lenguaje que estemos usando la elegimos y trabajamos sobre ella o como mucho haciendo consultas entre varias bases de datos.

Logo MySQL

Sin embargo, hoy trabajando en un proyecto que estoy haciendo para un cliente de CODECTION, he tenido la necesidad de listar y operar sobre los nombres de las tablas de una base de datos. ¿Cómo lo he hecho?

SHOW FULL TABLES FROM mi_base_de_datos

Y obtenéis un listado de todas las tablas que hay en esa base de datos.

De hecho, podéis hacer consultas del tipo:

SHOW FULL TABLES FROM mi_base_de_datos LIKE '%busqueda%'

Así tenéis una más herramientas todavía para trabajar con nuestra querida MySQL.

Obtener una lista HTML de todas las categorías en WordPress

Como todos sabréis si usáis WordPress, toda la información que el sistema maneja: las entradas, las páginas, o cualquier otro tipo de dato que hayáis creado (los llamados Custom Post Type), se estructura en base una taxonomía.

Las taxonomías por defecto son dos, que aunque internamente se implementan igual, externamente funcionan de forma diferente. Las categorías actúan comos los capítulos de un libro, como la tabla de contenido inicial, partiendo el contenido del blog en temáticas y las etiquetas actúan como el índice final donde se referencias temáticas de forma cruzada, el llamado índice de palabras (que dan lugar a nubes de etiquetas similares a las que os muestro a continuación).

WordPress nube de tags

Si manejáis otro tipo de datos, podéis crear vuestras propias taxonomías y así personalizar al máximo esta potencialidad para conseguir que WordPress sea una auténtica plataforma para desarrollar aplicaciones web.

Por cualquier razón, podéis queréis tener una lista de tipo <select><option> en HTML de las categorías, podéis usar las siguientes funciones que he creado y que uso para esto mismo:

function get_list_categories(){
	return get_list_subcategories(0);
}

function get_list_subcategories($parent){
	$result = "";

	$args = array(
		'taxonomy'	=> "category",
		'parent'	=> $parent,
		'hide_empty' => 0
	);

	$categories = get_categories($args);

	foreach($categories as $category)
		$result .= '<option value="'.$category->cat_ID.'">' . $category->cat_name . '</option>';

	return $result;
}

Si tenéis dudas preguntadme pero básicamente hay dos llamadas:

  • get_list_categories(): para llamar a todas las subcategorías de padre 0, es decir, las categorías padre que no tienen hijas.
  • get_list_subcategories($parent): indicando el ID del padre, obtenemos todas la categorías que son hijas inmediatas del padre definido en $parent.

Internamente básicamente tiene funcionalidad la segunda función y hace una llamada a la función de la API get_categories pasándole los argumentos para conseguir que nos devuelva de la taxonomía estándar, "category", todas las categorías (sin esconder las que están vacías) cuyo padre inmediato es $parent.

Posteriormente se obtiene una colección de objetos de categorías al que le extraemos la cat_ID y la cat_name para usarlos en la representación de la colección.

mysql-caching-techniques

Comprobar el estado de la caché de MySQL

mysql-caching-techniquesHace unos días hablamos de cómo activar la caché de MySQL. Espero que la hayáis probado y que hayáis visto cómo vuestros tiempo de consulta se reducen considerablemente con sólo activarla. Os dije que os comentaría cómo monitorizarla y de esa promesa, esta entrada.

Soy de los que dice que algo por bien que funcione, si no puede medirse, no funciona tan bien. Las sensaciones son esos y aunque veamos que el tiempo de ejecución ha caído, no sabemos nada sobre la caché. Todos los que hayáis estudiado algo relacionado con la informática, conoceréis conceptos como la tasa de fallos y de aciertos de cualquier caché y ésta, no podía ser menos.

¿Cómo podemos ver esos datos? Veamos.

Comprobar si la caché está activada

Para esta labor usaremos el siguiente comando (siempre desde la consola de MySQL):

SHOW VARIABLES LIKE 'have_query_cache';

Que nos devolverá si está activada lo siguiente:

+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+

Parámetros de estado

Para ver todos los parámetros del estado de la caché, una vez sabemos que está activada hacemos:

SHOW STATUS LIKE 'Qcache%';

Y el resultado es el siguiente:

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 3409      |
| Qcache_free_memory      | 501720656 |
| Qcache_hits             | 3265000   |
| Qcache_inserts          | 1321208   |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 72160     |
| Qcache_queries_in_cache | 10027     |
| Qcache_total_blocks     | 24143     |
+-------------------------+-----------+

Veamos parámetro a parámetro cuál es su significado (el significado lo he buscado en la referencia de MySQL, donde aparece el significado de todas las variables del servidor):

  • Qcache_free_blocks: El número de bloques de memoria libres en la cache de consultas.
  • Qcache_free_memory: El total de memoria libre en la cache de consultas.
  • Qcache_hits: Número de aciertos de la caché.
  • Qcache_inserts: Número de consultas que han sido agregadas a la caché.
  • Qcache_lowmem_prunes: Número de consultas borradas de la caché porque no había suficiente memoria.
  • Qcache_not_cached: Número de consultas no cacheadas, por no ser cacheables.
  • Qcache_queries_in_cache: Número de consultas registradas en la caché (no se me quedó nunca claro la diferencia con Qcache_inserts, ¿alguien la conoce?)
  • Qcache_total_blocks: Número de bloques que tiene actualmente la caché de consultas.

¿Cómo medir la eficacia de nuestra caché?

Para medir la eficacia de la caché debemos hacer la siguiente fórmula para calcular el porcentaje de acierto y por lo tanto el porcentaje de consultas que en lugar de ir a buscarse a la base de datos física, se miran directamente en la caché.

El coeficiente será el siguiente: Qcache_hits/(Com_select+Qcache_hits)

¿Qué es Com_select? Este parámetro indica la cantidad de consultas SELECT que se han ejecutado fuera de la caché. Para obtenerlo ejecutamos la siguiente consulta:

show global status like 'Com_select%';

En mi caso obtengo:

+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Com_select    | 1371295 |
+---------------+---------+

Mi tasa será por lo tanto: 3265000 / (1371295 + 3265000) = 70,42%, no está nada mal, ¿y la vuestra?

¿Qué motor de base de datos de MySQL está usando una tabla?

MySQL es un sistema gestor de base de datos con mejores o peores consideraciones. Como punto positivo, está la posibilidad de usar más de un motor de base de datos. Básicamente dos son los más conocidos: MyISAM e InnoDB.

¿Cómo saber desde la consola de MySQL esta información? Con la siguiente orden, fijaos que el DESCRIBE TABLE no ofrece esta información, sólo la información referente al tipo de dato de cada columna, así que tenemos que usar:

SHOW TABLE STATUS WHERE Name = 'nombre_tabla'

Tened en cuenta que aunque tendremos un motor por defecto, el motor es propio de cada tabla.

MySQL

Guardar procedimientos, funciones y triggers en MySQL al hacer un dump

Si sois de los que aprovecháis las nuevas funcionalidades de MySQL como yo y dentro de vuestra base de datos usáis: stored procedures, functios o los famosos triggers (disparadores), os habréis dado cuenta que en vuestro dump, es decir en vuestras copias de seguridad, éstos no aparecen si los hacéis de la forma típica.

Yo me di cuenta de la mala forma, es decir, cuando recuperas el dump y te das cuenta de que no están en ningún sitio. Por eso mismo os traigo esta entrada, para que no os pase y tengáis en cuenta que si usáis este tipo de cosas, quizás debáis reformar la forma en la que llamáis a mysqldump.

Por defecto y por fortuna los triggers sí son guardados por defecto por mysqldump. Para añadir procedimientos y funciones, las llamadas rutinas, deberemos añadir el parámetro --routines, veamos:

mysqldump  <resto de opciones> --routines salida.sql

Con esto ya tendríamos el problema resuelto, sin embargo, si lo que queréis es separar tablas y datos, de triggers y rutinas, podemos hacer lo siguiente, y generar un fichero con cada funcionalidad:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt  > solo_triggers_rutinas.sql

Finalmente, el paso contrario, para recuperar estos ficheros, sería el siguiente:

mysql <base_datos> < salida.sql

Todo ello adecentado con el usuario y contraseña correspondiente y los parámetros típicos para conectaros a MySQL:

mysql -u nombre_usuario -h direccion_servidor -p

Esta información es una traducción y adaptación de este original en inglés del blog inglés: MDLog:/sysadmin.

Modificar o activar el tamaño de caché de MySQL

En las instalaciones por defecto de MySQL, en muchas ocasiones, o aparece deshabilitada la caché de consultas o aparece, pero con muy poco espacio disponible.

Como podréis figuraros, la caché de consultas almacena en memoria física, la RAM, las últimas consultas realizadas y su resultado, de forma que si alguien repite dichas consultas, el acceso a su resultado sea casi inmediato comparado con recurrir a la base de datos en sí, a la que probablemente debamos acceder a disco y recorrer sus complejos árboles B y B+ internos.

El resultado de usar caché de MySQL es muy evidente en cuando a velocidad y en una entrada posterior os comentaré cómo sacar estadísticas para ver con más concreción cuál es la mejora.

¿Cómo activarla? ¿Cómo aumentar su tamaño?

Para ver si está activada nos dirigimos al fichero my.cnf (que probablemente esté en /etc o /etc/mysql) y buscamos dentro las siguientes cadenas:

query_cache_limit = 16M
query_cache_size = 512M

Como podéis imaginaros tenemos dos opciones:

  • Que no aparezcan dichas opciones: la caché está desactivada, podéis agregar dichas líneas sin problema en dicho fichero
  • Que aparezcan: ¿está activada? Pero, ¿qué significan dichas opciones?

Por partes:

  • query_cache_limit: establece la consulta máxima a cachear, por defecto viene a 1MB, yo trabajo en ocasiones con datos BLOB recurrentes, de ahí que haya puesto un valor exageradamente grande, 16MB.
  • query_cache_size: tamaño de la caché, si tenéis un equipo con varios gigas de RAM, con 512MB tendréis para almacenar muchísimas consultas y 512MB no os supondrán gran pérdida. De hecho si es un servidor sólo con MySQL sería razonable incluir incluso más cantidad, cada caso es particular y ya sabréis cuál es la mejor cifra por vuestra experiencia.

Una vez hecho eso, sólo debemos reiniciar el servicio y si MySQL arranca bien, todo está hecho, para confirmar, mirad la siguiente entrada para ver estadísticas y más datos:

sudo service mysql restart

Reparar y optimizar todas las bases de datos en MySQL

Probablemente, en alguna ocasión te hayas enfrentado vía log o vía mensaje de urgencia con un mensaje de este tipo: "table marked as crashed and should be repaired" cuando trabajas con MySQL. Más aún si has tenido un reinicio de emergencia, si usas MyISAM o si tienes problema con tu sistema de ficheros (o tus discos).

Puedes tratar de reparar tabla a tabla, sin embargo, lo más productivo es hacer todas de una vez. Antes de cualquier operación de este tipo, haz una copia de todo por si acaso (aquí en Linux Hispano hablé de cómo hacer backups y recuperarlos).

La orden a ejecutar será la siguiente y el resultado lo veréis en la consola, si OK o si hay problemas:

mysqlcheck -u root -p --auto-repair --optimize --all-databases

Para cambiar de usuario, cambiar root por el usuario que quieras y para conectarte a otro servidor que no sea el local, introduce un parámetro -h e indícalo.

Rubyrep (replicación de base de datos que no lastima)

Hola, quería aportar contenido sobre una interesante herramienta para implementar replicación de base de datos (para postgres y mysql). Me tope con ésta herramienta hace unos días, cuando estaba buscado información/soporte para utilizar Slony-I (herramienta de replicación para PostgreSQL). Resulta ser una solución muy buena, fácil de implementar y relativamente simple. (en comparación con Slony-I). Está escrita en JRuby, lo cual la hace independiente de la plataforma (donde corra Java, lo hará ésta herramienta).

Características principales:

  • open-source
  • Replicación asincrónica, Master-Maste, Master-Slave.
  • Muy fácil de utilizar y configurar (a través de archivos .conf muy sencillos de entender).
  • Independiente del motor de base de datos (actualmente soporta Postgresql y MySql).
  • Puede escanear dos bases de datos en busca de diferencias.
  • Puede sincronizar dos bases de datos.
  • Puede replicar continuamente entre dos bases de datos.

En fin, dejo el link hacía la página web de la herramienta. Estaría muy bueno que se pueda compartir, es un proyecto open-source, y por lo tanto, es LIBRE y necesita la colaboración de todos.

http://www.rubyrep.org

Saludos, Matias Miguez

Abrir acceso a MySQL desde equipos externos

Cuando trabajamos con MySQL es muy frecuente que tengamos que acceder al servidor para realizar desde equipos cliente operaciones con el mismo. Una solución muy frecuente en el mundo de la web, es ahorrarnos abrir conexiones y trabajar directamente desde dicho equipo, ¿cómo? Usando un software que se instale en el equipo servidor de base de datos y permita a la vez interactuar contra él mediante un interfaz web. Hablo de phpMyAdmin.

Sin embargo, en ocasiones, preferimos directamente conectarnos al servidor. Yo personalmente lo hago porque prefiero usar SQLyog Community sobre Wine antes que phpMyAdmin. ¿Cómo lo hacemos? Veamos:

Bind address

Lo primero es buscar en el fichero de configuración de MySQL y modificar la línea siguiente:

bind-address = 127.0.0.1

Por esta otra:

bind-address = TU_IP

Si indicamos 0.0.0.0 estamos dejando conectarnos desde cualquier dirección.

Dar permisos a nuestro usuario

La segunda parte viene derivada de dar permisos a nuestro usuario. ¿Cómo? Desde la consola de MySQL:

GRANT ALL PRIVILEGES
ON base_datos.*
TO ‘usuario’@'tu_IP'
IDENTIFIED BY 'tu_password';

Consideraciones a tener en cuenta:

  • Indicad la contraseña, si no lo hacéis, no podréis entrar con la que tenéis para entrar como usuarios en el propio servidor.
  • Si queréis entrar desde cualquier destino usad: TO ‘usuario’@'%'
  • Cuando queráis deshabilitar este permiso, tenéis que hacer la orden contraria, en lugar de GRANT, REVOKE
  • En lugar del nombre de la base de datos simplemente, podéis llegar a afinar a nivel de tabla.
  • O directamente permitir todas las bases de datos con un comodín: ON *.*
  • Si queréis afinar en privilegios mirad la orden GRANT
  • Podéis hacer esto con el interfaz gráfico de SQLyog