Llegamos a ustedes gracias a:



Reportajes y análisis

10 herramientas esenciales de MySQL para administradores

[26/10/2018] MySQL es un sistema complejo que requiere muchas herramientas para repararlo, diagnosticarlo y optimizarlo. Afortunadamente para los administradores, MySQL ha atraído a una vibrante comunidad de desarrolladores que han implementado herramientas de código abierto de alta calidad para ayudar con la complejidad, el rendimiento y la salud de los sistemas MySQL, la mayoría de los cuales están disponibles de forma gratuita.

Las siguientes 10 herramientas de código abierto son recursos valiosos para cualquier persona que use MySQL, desde una instancia independiente hasta un entorno de múltiples nodos. La lista ha sido compilada teniendo en cuenta la variedad. Encontrará herramientas para ayudar a realizar copias de seguridad de los datos de MySQL, aumentar el rendimiento, protegerse contra la desviación de los datos y registrar los datos pertinentes de resolución de problemas cuando éstos surgen.

Hay varias razones por las que debe considerar estas herramientas en lugar de crear sus propias herramientas internas. Primero, gracias a su amplio uso, son maduras y probadas en el campo. Segundo, debido a que son de código abierto y gratuitas, se han beneficiado del conocimiento y la experiencia de la comunidad MySQL.

Muchas de estas herramientas son mantenidas activamente por Percona, un proveedor de MySQL y otras soluciones de base de datos de código abierto, por lo que continúan mejorando y adaptándose a la evolución de la industria de MySQL. Tenga en cuenta que dos de las herramientas, mycheckpoint y oak-security-audit, ya no se mantienen. Sin embargo, todavía pueden parecerle útiles.

Tenga en cuenta que hay muchas más herramientas que merecen su atención. He elegido enfatizar el código libre y abierto, y errar por el lado de la utilidad. También tenga en cuenta que todos son programas de línea de comandos de Unix, en gran parte porque MySQL se implementa y se desarrolla más ampliamente en estos sistemas.

Ahora, conoceremos estas 10 herramientas esenciales de administración de MySQL.

Herramienta esencial de administración de MySQL: pt-query-digest

Nada frustra más que el lento rendimiento de MySQL. Con demasiada frecuencia, el hardware más rápido es culpado del problema, una solución que funciona solo si de hecho el hardware es el culpable. La mayoría de las veces, el rendimiento deficiente puede atribuirse a la ejecución lenta de consultas que bloquean otras consultas, creando un efecto dominó de tiempos de respuesta lentos. Debido a que es mucho más barato optimizar las consultas que actualizar el hardware, el primer paso lógico en la optimización de MySQL es el análisis del registro de consultas.

Los administradores de bases de datos deben analizar los registros de consultas con frecuencia, dependiendo de la volatilidad del entorno. Y si nunca ha realizado el análisis de registro de consultas, es hora de comenzar, incluso si confía en software de terceros, que a menudo se supone que está optimizado cuando, de hecho, no lo está.

Un gran analizador de registro de consultas es pt-query-digest. Co-escrito por Baron Schwartz y yo mismo, está desarrollado activamente, completamente documentado y probado exhaustivamente. Las distribuciones de MySQL incluyen el analizador de registro de consultas mysqldumpslow, pero la herramienta no está actualizada, está mal documentada y no está probada.

pt-query-digest analiza los registros de consultas y genera informes con información estadística agregada sobre los tiempos de ejecución y otras métricas. Dado que los registros de consultas generalmente contienen miles, sino millones, de consultas, el análisis del registro de consultas requiere una herramienta.

pt-query-digest puede ayudarlo a encontrar las consultas que demoran más tiempo en ejecutarse en comparación con otras consultas. La optimización de estas consultas lentas hará que MySQL se ejecute más rápido al reducir los mayores retrasos. El verdadero arte de la optimización de consultas es más matizado, pero el objetivo básico es el mismo: encontrar consultas lentas, optimizarlas y aumentar los tiempos de respuesta de las consultas.

La herramienta es fácil de usar; la ejecución de pt-query-digest slow-query.log imprimirá las consultas más lentas en slow-query.log. La herramienta incluye soporte para "revisiones de consultas", para consultas de informes que aún no ha visto o aprobado, lo que hace que los análisis de registros frecuentes sean rápidos y eficientes.

Puede descargar pt-query-digest desde Percona. También está disponible como parte del kit de herramientas de Percona.

Herramienta esencial de administración de MySQL: mydumper

Ser capaz de generar volcados de datos rápidamente es vital para las copias de seguridad y la clonación de servidores. Desafortunadamente, mysqldump, que se envía con las distribuciones de MySQL, es de un solo hilo y, por lo tanto, demasiado lento para trabajos con gran cantidad de datos. Afortunadamente, el reemplazo moderno, mydumper, usa múltiples hilos, lo que lo hace muchas veces más rápido que mysqldump.

También conocida como MySQL Data Dumper, esta herramienta no administra conjuntos de copias de seguridad, diferenciales u otras partes de un plan de copia de seguridad completo. Simplemente descarga los datos de MySQL lo más rápido posible, lo que le permite completar las copias de seguridad con restricciones de tiempo estrictas, como pasar la noche, mientras los empleados están fuera de línea, o realizar copias de seguridad con mayor frecuencia que con mysqldump.

Un punto técnico que se debe conocer sobre mydumper es que bloquea las tablas, por lo que no es la herramienta ideal para realizar copias de seguridad durante las horas de funcionamiento. Por otra parte, la recuperación profesional de datos cuesta cientos de dólares por hora, y siempre recibe una factura, incluso si los datos no son recuperables. mydumper es gratis y vale la pena explorar incluso para copias de seguridad básicas.

mydumper también es útil cuando se clonan servidores. Otras herramientas realizan duplicaciones completas del disco duro, pero cuando todo lo que necesita son datos de MySQL, mydumper es la forma más rápida de obtenerlos. Los servidores aprovisionados en una nube son particularmente adecuados para la clonación utilizando mydumper. Simplemente descargue sus datos MySQL desde un servidor existente y cópielos a la nueva instancia.

La clonación vale la pena para crear servidores esclavos, puntos de referencia y perfiles, pero en ninguna parte es más vital que en las pruebas y el desarrollo. Ser capaz de hacer girar una réplica para una prueba rápida antes de su puesta en marcha es esencial para los entornos dinámicos de MySQL. Con mydumper, puede crear rápidamente un servidor que sea casi idéntico a su servidor de producción, permitiendo que los resultados de sus pruebas imiten mejor los resultados de producción.

Puedes descargar mydumper de GitHub.

Herramienta esencial de administración de MySQL: XtraBackup

Si sus bases de datos están en uso todos los días, todo el día, lo que no le permite parar de la "noche a la mañana, y durante el cual las tablas se pueden bloquear para realizar copias de seguridad, XtraBackup es su solución. Esta herramienta gratuita de código abierto realiza copias de seguridad sin bloqueo, lo que permite que las copias de seguridad se ejecuten en sistemas transaccionales mientras las aplicaciones permanecen completamente disponibles.

XtraBackup también ofrece copias de seguridad incrementales, lo que le permite realizar copias de seguridad solo de los datos que han cambiado desde la última copia de seguridad completa. La adición de copias de seguridad incrementales a su proceso de copia de seguridad es potente, dado el impacto reducido en el rendimiento de estas copias de seguridad tremendamente más pequeñas.

Un proyecto que creció alrededor de XtraBackup hace que la administración de un plan de respaldo completo sea aún más fácil: XtraBackup Manager. Esta herramienta agrega funciones avanzadas como copias de seguridad giratorias con grupos, y el conjunto de copias de seguridad que caduca. Juntos, XtraBackup y XtraBackup Manager son una solución de copia de seguridad formidable y gratuita.

Puede descargar XtraBackup de Percona. XtraBackup Manager se puede descargar desde Google Code Archive y de GitHub.

Herramienta esencial de administración de MySQL: tcprstat

Tcprstat es probablemente la herramienta más esotérica en esta lista. tcprstat supervisa las solicitudes de TCP e imprime estadísticas sobre los tiempos de respuesta de bajo nivel. Cuando se familiariza con la manera en que el tiempo de respuesta piensa acerca del desempeño, la recompensa de tcprstat es significativa.

El principio está elaborado en el libro Optimización del rendimiento de Oracle, por Cary Millsap y Jeff Holt, y se aplica igualmente a MySQL. La idea básica es que un servicio, en este caso MySQL, acepte una solicitud (consulta), cumpla con esa solicitud (tiempo de ejecución) y responda con resultados (conjunto de resultados). El tiempo de respuesta del servicio es el intervalo de tiempo entre la recepción de una solicitud y el envío de una respuesta. Cuanto más corto sea el tiempo de respuesta, más solicitudes se pueden atender en la misma cantidad de tiempo.

El procesamiento paralelo y otros factores de bajo nivel juegan un papel importante aquí, pero el resultado simplificado es que hay 28.800 segundos en una jornada laboral de ocho horas, por lo que reducir los tiempos de respuesta en solo cuatro décimas de segundo (de 0,5 a 0,1 segundos) resulta en 230.400 más solicitudes atendidas cada día. Tcprstat le ayuda a lograr esto.

Solo tengo espacio suficiente en este artículo para despertar su curiosidad, así que terminaré la introducción de esta herramienta y le contaré el primer paso para comenzar con la optimización del tiempo de respuesta de MySQL: lea Optimización del rendimiento de Oracle. Luego empiece a usar tcprstat.

Puede descargar tcprstat de GitHub.

Herramienta esencial de administración de MySQL: pt-table-checksum

La "deriva de datos" es un problema importante para los entornos dinámicos de MySQL. Este problema, en el que los datos del esclavo no están sincronizados con el maestro, a menudo se debe a la escritura de datos en un esclavo o la ejecución de ciertas consultas no deterministas en el maestro. Lo que es peor es que las diferencias de datos pueden pasar inadvertidas hasta que se vuelvan paralizantes. Introduzca pt-table-checksum, una herramienta que realiza los cálculos complejos y sensibles necesarios para verificar los datos en dos o más tablas idénticas.

pt-table-checksum funciona con servidores independientes y servidores en una jerarquía de replicación, donde se puede ver fácilmente el mayor valor de la herramienta. La verificación de los datos de la tabla entre un maestro y un esclavo debe tener en cuenta la consistencia de la replicación. Debido a que los cambios en el maestro se replican en esclavos con cierta demora ("retraso"), la simple lectura de los datos de los servidores no es confiable para verificar la consistencia, dado que los datos cambian constantemente y están incompletos hasta que se replican por completo. Bloquear tablas y esperar a que todos los datos se repliquen permitiría lecturas consistentes, pero hacerlo significaría detener efectivamente los servidores. pt-table-checksum le permite realizar sumas de comprobación coherentes y sin bloqueo de datos maestros y esclavos.

Además de la consistencia de la replicación, existen otros problemas con la verificación de datos. El tamaño de la mesa es uno de ellos. El comando MySQL CHECKSUM TABLE es suficiente para tablas pequeñas, pero las tablas grandes requieren "fragmentación" para evitar bloqueos largos o sobrecargar recursos de CPU o memoria con cálculos de suma de comprobación.

La fragmentación resuelve un segundo problema: La necesidad de verificaciones regulares de la consistencia de los datos. Si bien la desviación de datos puede ocurrir una sola vez, a menudo es recurrente. pt-table-checksum está diseñada para verificar continuamente las tablas, revisando en ciertos tramos una parte de la ejecución hasta que finalmente se haya revisado toda la tabla. La naturaleza continua de este proceso ayuda a garantizar que se corrija la deriva recurrente.

Puede descargar pt-table-checksum desde Percona. También está disponible como parte del kit de herramientas de Percona.

Herramienta esencial de administración de MySQL: pt-stalk

Los problemas tienen una forma de aparecer cuando usted no los ve o está en su casa descansando y diagnosticarlos después del hecho es a veces imposible sin datos sobre el estado de MySQL y el servidor en el momento del problema. La inclinación natural es escribir su propio script para esperar o detectar un problema y luego comenzar a registrar datos adicionales porque, después de todo, nadie conoce su sistema mejor que usted. El problema es que conoce su sistema cuando está funcionando y, si conociera los tipos de problemas que tendría el sistema, simplemente los solucionaría en lugar de intentar capturarlos y analizarlos.

Afortunadamente, aquellos que se especializan en saber cuándo no funciona MySQL, y en solucionar los problemas, han escrito una herramienta llamada pt-stalk. pt-stalk espera a que ciertas condiciones se vuelvan realidad, luego recopila datos para ayudarle a diagnosticar el problema. También puede usar pt-stalk para recopilar datos a pedido sin esperar a que se produzca un activador.

pt-stalk recopila no solo la información estándar que MySQL puede informar sobre sí mismo, sino también muchos más datos que podría no haber incluido: lsof, strace, tcpdump, etc. Por lo tanto, si tiene que consultar a un profesional que se especializa en solucionar problemas de MySQL, tendrá todos los datos que necesita.

pt-stalk es configurable, por lo que puede utilizarse para casi cualquier problema. El único requisito es una condición definible para establecer un disparador. Si varias condiciones señalan el problema, es posible que también deba consultar a un profesional para una revisión más exhaustiva de su entorno MySQL, ya que los problemas pueden aparecer en MySQL aunque la causa subyacente esté en otra parte.

pt-stalk puede usarse proactivamente, también. Por ejemplo, si sabe que nunca debería haber más de 50 conexiones activas de MySQL a la vez, entonces podría monitorear de forma proactiva este valor, haciendo que pt-stalk sea útil tanto para problemas que conoce como para problemas que aún no ha visto.

Puedes descargar pt-stalk desde Percona. También está disponible como parte del kit de herramientas de Percona.

Herramienta esencial de administración de MySQL: mycheckpoint

No siempre deseará esperar a que algo salga mal antes de abordar un problema, y los paneles de control le brindan una manera esencial de monitorear su entorno MySQL para detectar posibles problemas antes de que surjan.

Existen muchas aplicaciones de monitoreo gratuitas y comerciales para MySQL, algunas específicas de MySQL y otras genéricas con complementos o plantillas de MySQL.

mycheckpoint es notable porque es gratuita, de código abierto, específico para MySQL y con todas las funciones. La autora, Shlomi Noach, ha descontinuado el desarrollo de mycheckpoint, pero el código fuente y los binarios todavía están disponibles de forma gratuita.

Al igual que muchas soluciones de monitoreo, mycheckpoint está basada en la web. Aquí hay un ejemplo de tabla:

Herramientas MySQL

mycheckpoint puede configurarse para monitorear tanto las métricas de MySQL como las del servidor, como los vaciados de la agrupación de búferes InnoDB, las tablas temporales creadas, la carga del sistema operativo, el uso de la memoria y más. Si no te gustan los gráficos, mycheckpoint también puede generar informes legibles.

Al igual que con pt-stalk, las condiciones de alerta se pueden definir con notificaciones por correo electrónico, aunque no se recopilarán datos de solución de problemas adicionales. Otra característica útil es la capacidad de mycheckpoint para monitorear las variables de MySQL para detectar cambios que pueden conducir a problemas, o para indicar que alguien ha modificado MySQL cuando no debería haberlo hecho.

La supervisión de MySQL no es solo para centros de datos o grandes implementaciones. Incluso si tiene un único servidor MySQL, el monitoreo es esencial; al igual que con su vehículo, hay mucho que saber sobre el sistema mientras está funcionando para ayudarlo a prever o evitar un mal funcionamiento. mycheckpoint es una solución entre muchas que vale la pena probar.

Puede descargar mycheckpoint del Google Code Archive. La documentación está disponible en el sitio web de openl de Shlomi Noach.

Herramienta de administración esencial de MySQL: Shard-Query

Las consultas contra conjuntos de datos particionados o fragmentados pueden acelerarse dramáticamente utilizando Shard-Query, que paraliza ciertas consultas detrás de la escena. Las consultas que utilizan las siguientes construcciones pueden beneficiarse de la ejecución paralela de Shard-Query:

  • Subconsultas en la cláusula FROM
  • UNION y UNION ALL
  • EN
  • ENTRE

Las funciones agregadas SUM, COUNT, MIN y MAX también se pueden usar con esas construcciones. Por ejemplo, esta consulta se puede ejecutar en paralelo con Shard-Query:

SELECT DayOfWeek, COUNT(*) AS c
FROM ontime_fact

JOIN dim_date USING(date_id)
WHERE Year

BETWEEN 2000 AND 2008
GROUP BY DayOfWeek

ORDER BY c DESC;

Los benchmark muestran que paralelizar esa consulta reduce su tiempo de respuesta en aproximadamente un 85%, de 21 segundos a tres.

Shard-Query no es una herramienta independiente; requiere otros programas como Gearman y su configuración es relativamente compleja. Pero si sus datos están particionados y sus consultas utilizan cualquiera de las construcciones enumeradas anteriormente, entonces los beneficios merecen el esfuerzo.

Puede descargarla de GitHub como parte de la colección de herramientas de Swanhart Shard-Query. La documentación adicional está disponible en MariaDB Knowledge Base.

Herramienta esencial de administración MySQL: pt-archiver

A medida que las tablas se hacen más grandes, las consultas contra ellas pueden hacerse más lentas. Muchos factores influyen en los tiempos de respuesta, pero si ha optimizado todo lo demás, y el único sospechoso restante es una tabla muy grande, entonces el archivar filas de esa tabla puede restaurar tiempos rápidos de respuesta de consulta.

A menos que la tabla no sea importante, no debe eliminar las filas descaradamente. El archivo requiere delicadeza para garantizar que los datos no se pierdan, que la tabla no esté bloqueada excesivamente, y que el proceso de archivo no sobrecargue a MySQL o al servidor.

El objetivo es un proceso de archivo que sea confiable e imperceptible, excepto por el efecto beneficioso de reducir los tiempos de consulta. Pt-archiver logra todo eso.

Pt-archiver tiene dos requisitos fundamentales, el primero de los cuales es que las filas a archivar deban ser identificables. Por ejemplo, si la tabla tiene una columna de fecha y sabe que solo se necesitan los últimos N años de datos, se pueden archivar las filas con fechas anteriores a N años atrás. Además, debe existir un índice único para ayudar a pt-archiver a identificar las filas archivables sin escanear toda la tabla. Escanear una tabla grande es costoso, por lo que se usa un índice y sentencias SELECT específicas para evitar escaneos de tablas.

En la práctica, pt-archiver maneja automáticamente los detalles técnicos. Todo lo que tiene que hacer es decirle qué tabla archivar, cómo identificar las filas que se pueden archivar y dónde archivar esas filas. Estas filas se pueden purgar, copiar en otra tabla o escribir en un archivo de volcado para futuras restauraciones, si es necesario. Una vez que se sienta cómodo con la herramienta, hay muchas opciones para ajustar el proceso de archivo. Además, pt-archiver es conectable, por lo que puede usarse para resolver necesidades complejas de archivado sin parchear el código.

Puede descargar pt-archiver desde Percona. También está disponible como parte del kit de herramientas de Percona.

Herramienta de administración esencial de MySQL: oak-security-audit

¿Cuándo fue la última vez que auditó la seguridad de sus servidores MySQL? No está solo si la respuesta es "nunca". Hay muchas compañías que proporcionan auditorías de seguridad, pero a menos que nada cambie después de esas auditorías, entonces la seguridad de su entorno MySQL debe verificarse regularmente.

Las amenazas externas son una razón obvia para imponer la seguridad de MySQL, pero las amenazas internas, como los empleados actuales o anteriores, suelen ser más peligrosas porque son (o eran) de confianza. La seguridad también es importante para hacer cumplir la privacidad (reglamentaciones médicas/HIPAA), evitar el acceso accidental (por ejemplo, iniciar sesión en el servidor de producción en lugar del servidor de desarrollo) o permitir que los programas de terceros interactúen con sus sistemas.

Para aquellos que buscan aumentar la seguridad de sus implementaciones, Oak-security-audit es una herramienta de código abierto, gratuita y que vale la pena y que realiza auditorías de seguridad básicas de MySQL. No requiere ninguna configuración; simplemente ejecútela en sus servidores MySQL e imprima un informe con riesgos y recomendaciones sobre cuentas, privilegios de cuenta, contraseñas y algunas prácticas recomendadas generales, como deshabilitar el acceso a la red. Aquí hay un fragmento de un informe:

-- Looking for anonymous user accounts
-- --------------------------------------------
-- Passed
--
-- Looking for accounts accessible from any host
-- ---------------------------------------------
-- Found 1 accounts accessible from any host. Recommended actions:
RENAME USER 'msandbox'@'%' TO 'msandbox'@'<specific host>';

Oak-security-audit se enfoca solo en la seguridad de MySQL, por lo que no es un reemplazo para una auditoría de seguridad del sistema por parte de un humano, pero es una excelente primera línea de defensa que es fácil de usar. Podría ejecutarla semanalmente con cron y recibir los informes por correo electrónico.

Puede descargar oak-security-audit como parte de la colección de herramientas de MySQL del kit openark de Shlomi Noach. La documentación está disponible en las páginas de GitHub de Shlomi Noach.

Crédito foto portada: Kevin Severud, Licencia: CC BY-SA 2.0