MySQL/Chuletario de trucos y recetas

INTRODUCCIÓN

En esta sección se incluyen trucos, recetas y consejos para el trabajo diario con MySQL.

TIMEOUT EN LAS CONEXIONES TCP A MYSQL

mysql wait_timeout: Por defecto mysql cierra una conexión después de 8 horas sin recibir ninguna petición del cliente y sin que el cliente sea notificado de las mismas. En una aplicación web puede ser normal abrir un pool de conexiones desde un servidor (Tomcat p.ej) de forma que siempre haya conexiones de sobra sin usar. Esto en la práctica se traduce en que debemos hacer una de dos: - bien reconfigurar mysql (/etc/mysql/my.cnf) para aumentar el wait_timeout - o bien "refrescar" periodicamente las conexiones del pool para resetear el contador interno de timeout de mysql.

Los servidores de aplicaciones suelen tener también parámetros de configuración que nos ayudan: P.ej, Tomcat incluye también un sistema automático de pool de conexiones que en principio soluciona el problema. En la práctica lo minimiza pero aleatoriamente cada 2 o 3 semanas, por experiencia del que escribe estas líneas, vuelve a fallar (Tomcat versión 5).

Por tanto puede ser una buena opción el desabilitar el wait_timeout editando /etc/mysql/my.conf añadiendo la linea wait_timeout=0.

PROBLEMAS CON CORTAFUEGOS

En alguna extraña ocasión al activar el cortafuegos (IPtables) impidiendo la conexión de un cliente al puerto TCP 3306 de mysql, la conexión cliente queda en estado SYNC_SEND y el driver (JDBC) de mysql se bloquea por completo teniendo que reiniciar el servidor (tomcat). (*Pendiente: Ver con que versión del driver se produce).

PROBLEMAS CON DNS

La opción skip-name-resolve evita que el servidor mysql realice una resolución inversa de DNS (obtener el nombre simbólico del cliente a partir de su IP) de forma que es conveniente activarla si sólo vamos a trabajar con IPs ya que si falla el DNS la conexión se retarda y puede generar "timeouts" en el cliente de forma aleatoria.

RESUMEN DE GESTIÓN DE ÍNDICES EN MYSQL

Destaco lo que la documentación de MySQL dice al respecto:

"Una PRIMARY KEY es una KEY única donde todas las columnas de la key deben ser definidas como NO NULAS. Si no se declaran explicitamente como NOT NULL, MySQL lo hace implicitamente (y en silencio). Una tabla puede tener solo una PRIMARY KEY. Si no tiene tal PRIMARY KEY y una aplicación pregunta por la misma, MySQL devuelve el primer índice ÚNICO que no tiene columnas NULL como la PRIMARY KEY."

Ejemplo de creación de índice típico (entero, llave primaria y autoincrementado):

... idx INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

Otras fórmulas comunes:

CREATE TABLE test (
   idx INT NOT NULL,
   last_name CHAR (30) NOT NULL,
   first_name CAHR (30) NOT NULL,
   PRIMARY KEY (idx),
   INDEX full_name (last_name, first_name)
);

ALTER TABLE test ADD INDEX USING HASH|BTREE (idx)

Conviene repasar también la documentación para ver las optimizaciones de velocidad que soporta MySQL a la hora de gestionar índices (Mysql Sección Optimizando MySQL - DELAYED_KEY_WRITE )

Backup MySQL

El siguiente script sirve de ejemplo para realizar backups de varios servidores MySQL de forma cómoda . Funciona en sistemas GNU (Linus, BSD,...), Solaris y Windows+Cygwin. Los servidores en este caso vienen definidos por sus IPs (IP1 IP2 IP3).

backupMySQL.sh

#!/bin/sh
LOGFILE=/var/log/backupMySQL.$(whoami).$(date +%Y%m%d).log
exec 1>>${LOGFILE} 2>&1
echo -n "Iniciando ${0} " ; date +%Y-%m-%d\ %H:%M:%S
cd /var/backupMysql
USUARIO="XXXX"
PASSWRD="YYYY"
for server in IP1 IP2 IP3; do
    echo ""
    echo "$(date +%Y/%m/%d-%H:%M:%S) Comenzando mysqldump del servidor ${server}"
    ddbbNames=$(mysql -h${server} -u${USUARIO} -p${PASSWRD} -e "show databases;" | grep -v Database | grep -v nombreBBDDNoBackup)
    for ddbb in $ddbbNames; do
        tables=$(mysql -h${server} -u${USUARIO} -p${PASSWRD} $ddbb -e "show tables;" | grep -v -i backup | \
            grep -v -i Tables_in_ | while read line ; do echo -en "${line} " ; done ;)
        mysqldumpOpts="--add-locks --disable-keys --extended-insert --delayed-insert"
        cmd="mysqldump ${mysqldumpOpts} -h${server} -u${USUARIO} -p${PASSWRD} ${ddbb} ${tables}"
        tiempoEmpleado=$((time ${cmd} > ${server}_mysqldump_${ddbb}.sql) 2>&1 | grep real | cut -f 2)
        echo "$(date +%Y/%m/%d-%H:%M:%S) Servidor:${server}, BBDD:${ddbb}, Comando:${cmd},  Tiempo Empleado: ${tiempoEmpleado}";
    done;
done
echo -n "Finalizando ${0} " ; date +%Y-%m-%d\ %H:%M:%S

La salida del comando es similar a:

2009/06/01-12:09:45 Comenzando mysqldump del servidor IP1
2009/06/01-12:09:46 Servidor:IP1, BBDD:nombre1, ...nombreN  \
      nombre1, ...nombreN  Tiempo Empleado: 1m0.036s
 ...
2009/06/01-12:09:46 Comenzando mysqldump del servidor IP2
2009/06/01-12:09:47 Servidor:192.168.0.215, BBDD:nombre1, ...nombreN  \
      nombre1, ...nombreN  Tiempo Empleado: 3m0.568s
 ...

Es importante revisar de vez en cuando la salida comprobando que el tiempo empleado no es excesivo. Normalmente no suele tardarse más de un segundo para las tablas administrativas con metadatos y un par de minutos para las tablas "grandes" (10 millones de registros) en un PC moderno.

MOTORES SOPORTADOS POR MySQL

Utilizamos el comando show engines

mysql> show engines;
+------------+----------+------------------------------------------------------------+
| Engine     | Support  | Comment                                                    |
+------------+----------+------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance     |
| HEAP       | YES      | Alias for MEMORY                                           |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables  |
| MERGE      | YES      | Collection of identical MyISAM tables                      |
| MRG_MYISAM | YES      | Alias for MERGE                                            |
| ISAM       | YES      | Obsolete storage engine, now replaced by MyISAM            |
| MRG_ISAM   | YES      | Obsolete storage engine, now replaced by MERGE             |
| InnoDB     | YES      | Supports transactions, row-level locking, and foreign keys |
| INNOBASE   | YES      | Alias for INNODB                                           |
| BDB        | DISABLED | Supports transactions and page-level locking               |
| BERKELEYDB | DISABLED | Alias for BDB                                              |
| NDBCLUSTER | DISABLED | Clustered, fault-tolerant, memory-based tables             |
| NDB        | DISABLED | Alias for NDBCLUSTER                                       |
| EXAMPLE    | NO       | Example storage engine                                     |
| ARCHIVE    | YES      | Archive storage engine                                     |
| CSV        | YES      | CSV storage engine                                         |
| BLACKHOLE  | NO       | Storage engine designed to act as null storage             |
+------------+----------+------------------------------------------------------------+

Optimizando MySQL

Extraido de MySQL Query Optimization tiene la respuesta.:

   """For MyISAM tables, another strategy for reducing index flushing is to use the DELAYED_KEY_WRITE table option. With this option, data rows are written to the data file immediately as usual, but the key cache is flushed only occasionally rather than after each insert. To use delayed index flushing on a server-wide basis, start mysqld with the --delay-key-write=on option. In this case, index block writes for a table are delayed until blocks must be flushed to make room for other index values, until a FLUSH TABLES command has been executed, or until the table is closed.
   If you choose to use delayed key writes for MyISAM tables, abnormal server shutdowns can cause loss of index values. This is not a fatal problem because MyISAM indexes can be repaired based on the data rows, but to make sure that the repairs happen, you should start the server with the --myisam-recover=FORCE option. This option causes the server to check MyISAM tables when it opens them and repair them automatically if necessary."""

En el manual de referencia de Mysql:

   --delay-key-write[= OFF | ON | ALL]

   Specify how to use delayed key writes. Delayed key writing causes key buffers not to be flushed between writes for MyISAM tables. OFF disables delayed key writes. ON enables delayed key writes for those tables that were created with the DELAY_KEY_WRITE option. ALL delays key writes for all MyISAM tables. Available as of MySQL 4.0.3. See Section 7.5.2, “Tuning Server Parameters”, and Section 14.1.1, “MyISAM Startup Options”.

En la sección 7.5.2 se ve entre otros que podemos definir el "delay" máximo por número de inserts o por un timeout:

http://dev.mysql.com/doc/refman/4.1/en/server-parameters.html

delayed_insert_limit     current value: 100
delayed_insert_timeout   current value: 300

Chequeo/Reparación automática de tablas Mysql

Palabras relacionadas: pérdida de datos, recuperación de datos, backup, disaster recovery, fichero corrupto.

El siguiente script repara de forma automática fallos en Mysql. ¡¡¡Antes de ejecutarlo hay que asegurar que hemos hecho copias de los ficheros correspondientes *MYD (Datos), *MYI(Indices) y *frm(esquema)!!!

mysqlcheck es el commando encargado de realizar la tarea ya permite recuperar "en caliente" -con mysqld en marcha-.

#!/bin/sh
commonOpts="-uABC -p???"  # <- Sustituir con usuario y contraseña reales.
ddbbList=`mysql $commonOpts -e "show databases;" | grep -v Database | grep -v information_schema`
for ddbbItem in $ddbbList; do
    mysqlcheck $commonOpts --auto-repair --extended $ddbbItem ;   # Ver 'man mysqlcheck' para más detalles.
done

Algunas notas importantes a tener en cuenta.

--auto-repair trata de reperar automáticamente en caso de error.
--extended puede reemplazarse por --fast( chequea sólo tablas no cerradas correctamente) ,
--medium-check (recomendado, encuentra el 99,9% de los errores) o
--quick (chequea sólo índices, que es la tabla que más problemas suele dar).

Cuando trabajamos con tablas MyISAM (lo normal) existen 3 ficheros por tabla. MYD (Datos), MYI (Índices) y frm (esquema/definición de la tabla). Si MYI está corrupto podemos recuperar MYD apoyandonos solamente en frm (el esquema/definición es un fichero de sólo lectura una vez creada la tabla y por tanto no debería estar "corrupto" salvo por fallos físicos del disco duro mientras que MYI contiene índices y puede corromperse con mayor facilidad debido a cortes de alimentación, ...). De todas formas se recomienda no utilizar frm salvo que MYI no pueda ser usado (en realidad mysqlcheck -myisamchk - no permiter la opción frm y hay que utilizar 'REPAIR TABLE USER_FRM' una vez conectados a mysql para poder hacerlo).

Extraido de la doc. oficial de MySQL:

http://dev.mysql.com/doc/refman/4.1/en/repair-table.html
As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR TABLE. Use this if the
.MYI index file is missing or if its header is corrupted. In this mode, MySQL
re-creates the .MYI file using information from the .frm file. This kind of
repair cannot be done with myisamchk.
Note: Use this mode only if you cannot use regular REPAIR modes. The .MYI
header contains important table metadata (in particular, current AUTO_INCREMENT
value and Delete link) that are lost in REPAIR ... USE_FRM. Don't use
USE_FRM if the table is compressed because this information is also stored in the
.MYI file.

En ocasiones mysqlcheck no es capaz de reparar la BBDD. En tal caso podemos intentar parar el servidor Mysql y ejecutar a mano el comando myisam sobre el fichero físico afectado (normalmente /var/lib/mysql/NombreBBDD/tabla.myd).

Monitorización myTop

Equivale a un top de Linux, pudiendo acceder a bbdd. remotas.Ej:

mytop -h servidorA -uABC -p???

Stress Testing

Utilizamos SysBench. Ej. Suponemos 2 nodos ndb, uno en localhost y otro en IP2.

Preparamos la BBDD de prueba. Para ello primero ejecutamos un "CREATE DATABASE sbtest;" en los dos host con mysqld. A continuación:

./sysbench --test=oltp --mysql-table-type=ndbcluster --oltp-table-siz e=100000 \
    --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-user=ABC --mysql-password=?????? prepare

Con esto queda preparada la bb.dd. en modo cluster.

Ejecutamos un primer sysbench contra IP2:

./sysbench --num-threads=16 --max-requests=100000 \
    --test=oltp --oltp-table-size=100000 --oltp-read-only \
    --mysql-user=ABC --mysql-password=ABC --mysql-host=IP2 run

En localhost:

./sysbench --num-threads=16 --max-requests=100000 \
    --test=oltp --oltp -table-size=100000  --oltp-read-only \ 
    --mysql-user=ABC --mys ql-password=ABC --mysql-socket=/var/run/mysqld/mysqld.sock run

VARIOS SIN ORDENAR

Google mMAIM es una herramienta desarrollada por Google. mMAIM es la abreviatura de MySQL Monitoring And Investigation Module. El objetivo de mMAIM es facilitar la monitorización y análisis de servidores MySQL e integrarse fácilmente en el entorno. Permite mostrar estadísticas de conexión Maestro/Esclavo, estadísticas de eficiencia y estadísticas de la mayoría de los comandos "show".

Gestión gráfica de MySQL mediante SQLYog

SQLYog ofrece una versión gratuita de cliente gráfico para Mysql que corre sobre Windows y Wine (soportado este último de forma "oficial"). Así mismo ofrece una versión de pago con características avanzadas como posibilidad de ver diferencias entre bases de datos, sincronizar entre servidores, etc...