Rapido: Lista de queries en MySQL

Para mi es muy entretenido ver datos y que pega contra las bases de datos. En ausencia del famoso "mytop" (luego escribire de el) aqui hay una forma de ver la lista de todos los queries ejecutandose en la base de datos.

La lista se actualiza cada segundo:
# watch -n 1 mysqladmin --user=root --password= processlist

Notas:
1. Esto lo debes correr desde la linea de comandos, no desde el cliente de MySQL
2. para cambiar el intervalo de actualizacion cambia el valor de "n"
3. despues de "password=" debes poner el password de tu base de datos

Estadisticas sobre tu set de datos en MySQL

No se a ti pero a mi muchas veces me paso que en alguna junta me preguntaran "cuanto mide la base de datos??... y cuanto aumenta por dia/semana/mes?". Casi siempre la pregunta planteada por alguien que quiere saber mas que nada por curiosidad pero que te mete en un problema... y que tal si te preguntan sobre tablas especificas??!!

Contar numero de registros es facil pero que tal espacio utilizado en disco? si usas InnoDB entonces lo siguiente te servira.

Con el siguiente query puedes ver el numero de registros en TODA una base de datos, espacio en disco ocupado por datos, espacio ocupado por indices, espacio total y la relacion indices vs. datos.

Ejemplo usando la base de datos 'mysql'. Para usar la tuya solo cambia el final despues de TABLE_SCHEMA:

SELECT count(*) TABLES,
concat(round(sum(table_rows)/1000000,4),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),4),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),4),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),4),'G') total_size,
round(sum(index_length)/sum(data_length),4) idxfrac
FROM information_schema.TABLES where TABLE_SCHEMA='mysql';

El query te va a regresar algo similar a:
+--------+---------+---------+---------+------------+---------+
| TABLES | rows | DATA | idx | total_size | idxfrac |+--------+---------+---------+---------+------------+---------+
| 17 | 0.0018M | 0.0004G | 0.0001G | 0.0005G | 0.1562 | +--------+---------+---------+---------+------------+---------+

Que tal si solo quieres el dato para una tabla en particular? Entonces usa esto:
SELECT count(*) TABLES, concat(round(sum(table_rows)/1000000,4),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),4),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),4),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),4),'G') total_size, round(sum(index_length)/sum(data_length),4) idxfrac FROM information_schema.TABLES where TABLE_SCHEMA='pm_beta' and TABLE_NAME='nombre_de_mi_tabla';

Y la salida sera:
+--------+---------+---------+---------+------------+---------+
| TABLES | rows | DATA | idx | total_size | idxfrac |+--------+---------+---------+---------+------------+---------+
| 1 | 8.9598M | 0.7090G | 1.0834G | 1.7923G | 1.5281 | +--------+---------+---------+---------+------------+---------+

Notese que cambie a una tabla que tuviera mas datos.

Ahora, vamos a ver como sacar el desgloze por cada base de datos limitando a 10 max:
SELECT count(*) TABLES, table_schema,concat(round(sum(table_rows)/1000000,4),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),4),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),4),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),4),'G') total_size, round(sum(index_length)/sum(data_length),4) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10;

Salida:
+--------+--------------------+------------+-----------+-----------+------------+---------+
| TABLES | table_schema | rows | DATA | idx | total_size | idxfrac |+--------+--------------------+------------+-----------+-----------+------------+---------+
| 358 | produccion | 7722.5668M | 411.1646G | 870.1474G | 1281.3120G | 2.1163 |
| 17 | mysql | 0.0018M | 0.0004G | 0.0001G | 0.0005G | 0.1562 |
| 6 | test | 0.0000M | 0.0000G | 0.0000G | 0.0000G | 4.0716 |
| 17 | information_schema | NULL | 0.0000G | 0.0000G | 0.0000G | NULL |
| 1 | maatkit | 0.0000M | 0.0000G | 0.0000G | 0.0000G | NULL |
|+--------+--------------------+------------+-----------+-----------+------------+---------+

Por ultimo vamos a ver como hacerlo para una sola tabla:
SELECT count(*) TABLES, TABLE_NAME, table_rows, concat(round(sum(data_length)/(1024*1024*1024),4),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),4),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),4),'G') total_size, round(sum(index_length)/sum(data_length),4) idxfrac FROM information_schema.TABLES WHERE TABLE_SCHEMA='mi_base_de_datos' AND table_name = 'mi_tabla' group by TABLE_NAME;

Salida:
+--------+------------------------+------------+----------+-----------+------------+---------+
| TABLES | TABLE_NAME | table_rows | DATA | idx | total_size | idxfrac |+--------+------------------------+------------+----------+-----------+------------+---------+
| 1 | mi_tabla | 1209954640 | 75.8538G | 176.0356G | 251.8894G | 2.3207 |+--------+------------------------+------------+----------+-----------+------------+---------+

Si quieres ver todas las tablas o un grupo de tablas solo cambia lo siguiente:
table_name = 'mi_tabla'
por...
table_name like '%substring_en_mis_tablas%'

Con esto podras sacar muy buena informacion.... el formato de salida puede ser manipulado facilmente con php o perl y puedes correr tus comandos usando cron de manera que acumules nueva informacion cada dia o semana o mes segun prefieras.

aburrrrrrrrr........

Respaldos [backups] en MySQL para los pobres

Casi todos los dias se presenta algo nuevo en mis instalaciones de MySQL. No son cosas malas, simplemente son gajes del oficio. Sobre todo considerando que las bases de datos que manejo estan rondando 1.5TB y alrededor de 40 millones de queries/consultas en un dia (para mi master, en mis esclavos sube al doble y en las bases de datos de reportes son ~110 millones al dia).

Uno de los problemas con ese volumen de datos y de trafico es hacer backups porque no hay un momento del dia en que se pueda decir que estamos en mantenimiento, al menos no donde trabajo! Supongo que es conveniente poner un banner en un homepage diciendo "El servicio no estara disponible de 1am a 4am"... quizas algun dia.

Existen varias soluciones para respaldar en MySQL. La mas conocida por estar incluida con el server es mysqldump.

El problema es que con eso se generan archivos de texto con los datos de las tablas y si algun dia pasas de los 200GB en tu base de datos te daras cuenta que importar tu base de datos de nuevo es algo que te llevara minimo 24hrs (dependiendo de tus discos, indices y otras variables).

La solucion? innodbbackup? no porque bloquea tus tablas mientras se estan copiando.

Para mi lo mas sencillo y que realmente es practico es realizar copias directas de archivos en formato binario. Mi proceso es:
1. Detener la replicacion en un esclavo (mysql -e "slave stop")
2. Detener el proceso de la base de datos (service mysql stop en Fedora, CentOS, RedHat)
3. Una vez que mysqld se detenga copiar todo /var/lib/mysql (cp -Rvf /var/lib/mysql /lugar/a/donde/quiero/copiar/)

En varias ocasiones he tomado cursos con gente de MySQL AB (no se como se llamen desde que los compro Sun y que Oracle compro a este) y aunque no recomiendan operaciones directas sobre archivos yo no he tenido ningun problema.

La gran ventaja es que puedo tomar los archivos binarios, copiarlos a otro servidor e iniciar una replica de la base de datos inmediatamente, sin importar archivos de texto, conservando mis grants, etc.

Otro truco que no es popular pero que es muy util con tablas en MyISAM es copiar los archivos sin detener el servidor SIEMPRE Y CUANDO no se esten modificando las tablas.

Si tienes tablas que mas que nada son de lectura las puedes copiar sin detener tu server simplemente ejecutando:
# cp -Rvf /var/lib/mysql/nombre_de_tu_BD/nombre_de_la_tabla.* /lugar/a/donde/quiero/copiar/)