El Blog de Imosa

Como hacer un «snapshot» en MySQL

s una chorrada y bien conocida, aunque a mí me costó averiguar cuál es la solución más rápida y fiable –¿soy sólo yo el lento o también ocurre lo mismo a otros?–, así que aquí va por si a alguien le sirve.

MySQL funciona muy bien para mantener réplicas (slaves) de un servidor maestro (master), pero a veces hay algunos problemas:

1. El comando LOAD DATA FROM MASTER desde la réplica no está recomendado.
2. La base de datos es muy grande y toma mucho tiempo hacer el “mysqldump”, es mucho más rápido hacerlo con los mysqlhotcopy que sólo copia los ficheros.
3. Antes de hacer el mysqldump (si se hace) hay que obtener el estado del “binlog” del servidor para indicarlo en la réplica.

La mejor opción es hacer copia de los ficheros de la base de datos con el mysqlhotcopy y además indicarle que grabe en una tabla el estado del “binlog”. Con esto tendremos una copia rápida de la base de datos en “producción” que nos servirá para iniciar fácilmente una nueva réplica y/o reparar una que haya perdido el sincronismo o coherencia con el máster (suele suceder).

Para ello primero hay que crear esa tabla (está documentado en perldoc mysqlhotcopy).

CREATE TABLE log_pos (
                 host            varchar(60) NOT null,
                 time_stamp      timestamp(14) NOT NULL,
                 log_file        varchar(32) default NULL,
                 log_pos         int(11)     default NULL,
                 master_host     varchar(60) NULL,
                 master_log_file varchar(32) NULL,
                 master_log_pos  int NULL,

                 PRIMARY KEY  (host)
               );

Yo creé esa tabla en la base de datos mysql del Menéame. Así para el Menéame tengo el siguiente comando que se ejecuta cada madrugada:

mysqlhotcopy -q --flushlog --noindices --record_log_pos=mysql.log_pos  
                    --addtodest  mysql meneame /backups/hotcopy/

El comando anterior sólo tarda unos 6-10 segundos para copiar todos los datos de la base de datos, sin los índices y ocupando sólo 800 MB (el .sql comprimido si se usase mysqldump ocupa 1.5 GB).

La opción --noindices indica que no copie los índices, ellos pueden ser luego regenerados con el mysqlcheck -rq.

La opción --record-log_pos es para indicar la base de datos y tabla dónde guardar la información del log del máster, es la que servirá para sincronizar las réplicas (en este caso le indico que copie las dos base de datos, mysql y meneame).

Los ficheros de backup se copiarán a /backups/hotcopy/mysql y /backups/hotcopy/meneame. En la tabla se guardarán datos como los siguientes:

| host             | time_stamp          | log_file         | log_pos | ...
+------------------+---------------------+------------------+---------+
| db.meneame.net   | 2007-10-03 11:01:37 | mysql-bin.000634 |      98 |

Para [re] iniciar una réplica sólo hay que copiar esos ficheros a [por ejemplo] /var/lib/mysql/meneame en el servidor correspondiente y:

1. Detener el slave si está en marcha:slave stop;
2. Mejor es detener también el mysql.
3. Regenerar los índices conmysqlcheck -qr /var/lib/mysql/meneame/*.MYI
4. Poner el propietario adecuado: para Debian,chown -R mysql.mysql /var/lib/mysql/meneame

   también es mejor poner los permisos adecuados:

   chmod 0660 /var/lib/mysql/meneame/*

5. Arrancar el mysql e indicar el estado del máster (uso el ejemplo anterior):CHANGE MASTER TO MASTER_HOST='tu.master.com', MASTER_USER='tu_usuario', MASTER_PASSWORD='tu_password', MASTER_LOG_FILE='mysql-bin.000634', MASTER_LOG_POS=98;
6. Arrancar el slave:SLAVE START

Ya está, con el comando sql show slave status deberías ver como se sincroniza.

Creo que esta opción es la mejor porque además que la copia es rápida, si la haces cada día tienes siempre una “imagen” lista para ser usada para recuperar o iniciar cualquier otro slave sin necesidad de detener la base de datos en producción.

Nota: si harás réplicas de un máster tendrás que habilitar la “conexión a la red”, así que no te olvides de poner las iptables, definir qué direcciones IP pueden conectarse al puerto del mysql y poner claves a los usuarios habilitados para replicar. Sino podrías tener una importante fuga de datos

Add comment octubre 16th, 2014