Prácticas de Backup y Migración en MySQL (MySQL Backup & Migration)

Todo el mundo habla de hacerlo, pero pocos desarrolladores lo hacen - lo adivinaste, backup de los datos!. Tal vez sea un problema de quemar los datos al menos una vez, o de tener políticas apropiadas en la compañía (léase, tener a un jefe respirando en la nuca), pero llevarlo a cabo es sorprendentemente complicado. Como sea, el fin de semana tuve que investigar cómo migrar una gran base de datos MySQL, y acá les pongo unos pocos tips desde las trincheras. Al final, el proceso completo es sorprendentemente simple.

Herramientas y Estrategias MySQL

Tan popular como es MySQL, no existen muchas herramientas de backup que hagan un backup apropiado de los datos. Si quieres evitar datos corruptos, transacciones con commits incompletos, y muchos otros problemas, lo mejor que puedes hacer es utilizar alguna de las utilidades de backup que vienen con MySQL: mysqldump, mysqlhotcopy, mysqlsnapshot, y si tienes el dinero, ibbackup. Para seleccionar la herramienta apropiada para el trabajo, primero encuentre las respuestas a las siguientes preguntas: backup en línea vs fuera de línea, y volcado de datos vs backup crudo (raw).

Backups en línea vs. fuera de línea

El backup en línea es el método mas común, puesto que tener la base de datos temporalmente no disponible es generalmente inaceptable. Teniendo claro lo anterior, hay que aclara que los backups fuera de línea son generalmente mas rápidos y menos propensos a fallas, dedo que no tenemos que lidiar con ejecución de transacciones, bloqueo de tablas, procesos huérfanos y otros problemas de consistencia. Si usted puede conseguir un pequeño período de tiempo de indisponibilidad para la base de datos, o si tiene la suficiente suerte de tener una replicación maestro-esclavo de la base de datos, el backup fuera de línea es la forma correcta de hacer de las cosas.

Volcado de datos vs backups crudos (raw)

Un backup de volcado de datos da como resultado una secuencia de sentencias SQL las cuales pueden ser ejecutadas en cualquier base de datos para reconstruir la estructura y los datos originales de la base de datos. mysqldump es la herramienta por excelencia en este campo, y puede ser usada en cualquier tipo de tabla localmente e incluso por una red. Sin embargo, los backups de volcado de datos incurren en mucha sobrecarga con la sitaxis SQL extra, se generan archivos de datos muy grandes, son mucho mas consumidores de CPU, y lo mas importante, requieren una reconstrucción total de los índices cuando los datos están siendo recuperados.

Podría decirse que la forma mas eficiente de hacer backup de su base datos es por medio de una copia cruda de los archivos MySQL tal como existen en disco. Dado que nos estamos evitando todos los pasos de la conversión, este proceso es mucho mas eficiente que los procesos de volcado de datos. Para ejecutar un backup apropiado de una tabla MyISAM, usted va a tener que copiar los archivos de datos y de índices; para InnoDB necesitará hacer backup del tablespace entero y los logs de transacciones asociados.

mysqldump / mysqlhotcopy / mysqlsnapshot / ibbackup

mysqldump – (en línea, volcado de datos) – es la herramientas mas utilizada en nuestro kit de herramientas. Ejecutará un volcado de datos completo de una base de datos en línea bloqueando las tablas y escribiendo un pesado archivo al disco o a un lugar en la red. Es ideal para base de datos pequeñas dado que el proceso no es muy eficiente.

# uso típico de backup con mysqldump and recuperación de datos
mysqldump -u root -pPassword -x --all-databases > db_dump.sql
mysql -u root -pPassword < db_dump.sql
 
# volcado de datos en dos archivos de texto <data, table_structure> dentro del directorio 'backup' en la máquina local
mysqldump -T backup --fields-terminated-by=',' database-name -u root -pPassword
 
# comprimir los datos volcados al mismo tiempo
mysqldump -u root -pPassword --all-databases | bzip2 -c > db_dump.bz

mysqlhotcopy – (en línea – crudo(raw)) – ejecutará un backup completo en crudo de cualquier base de datos de tablas ISAM o MyISAM. Opera obteniendo un bloqueo de lectura en todas las tablas, copíandolas, y liberando el bloqueo.

# ejecutar un backup en línea dentro de /backup/location
mysqlhotcopy -u root -p password database_name /backup/location

mysqlsnapshopt (en línea, crudo(raw)) – una gran herramienta para obtener una copia completa de cualquier base de datos MySQL sin ponerla fuera de línea. Se puede configurar para comprimir los datos, y puede proveer archivos .tar separados para cada base de datos. El único punto en contra: únicamente tablas MyISAM.

# guardar una copia completa de una base de datos en línea dentro de /backup/location

mysqlsnapshot -u root -pPassword -s /backup/location
 
# recuperar una copia
tar -xvf /backup/location/db.tar

ibbackup (en línea, crudo(raw)) – ejecutará un backup en línea de tablas InnoDB y MyISAM en cualquier base de datos MySQL. Un gran herramienta, pero es de pago. Si usted es un asiduo usuario de tablas InnoDB, podría valer su precio.

# ejecuta un beckup en línea de tablas MyISAM / InnoDB

ibbackup /etc/my.cnf /etc/ibbackup.cnf
 
# recuperar backup (como se configure en ibbackup.cnf)
ibbackup --restore /etc/ibbackup.cnf

cp, scp, nc – (fuera de línea, crudo(raw)) – si usted puede poner su base de datos fuera de línea, los backups crudos son tan simples como hacer una copia, o una transferencia remota a una máquina diferente. Puede sonar ordinario, pero podría decirse que es la forma mas segura de obtener una copia de sus datos.

Backup de red: netcat & mysqldump

Para máxima seguridad, usted debería realizar backup a diferentes drives, y aún mejor, a diferentes computadores. Por suerte, este proceso es muy sencillo con la ayuda de netcat, o aún el mismo mysqldump:

# replicar y cargar a una base de datos remota en un solo paso (volcado de datos redirigido)

mysqldump --opt --compress --user=username database | mysql --user=username2 --password=p2 --host=hostB -D database -C database
 
# backup de datos con netcat
  # recibidor de backup – escuchando en el puerto 6000, escribe los datos recibidos backup.bz2
  nc -l 6000 > backup.bz2
 
  # iniciador de backup – volcado de datos, comprimir, y envíar a hostB:6000
  mysqldump --opt -u user1 -t database | bzip2 -c | nc -w1 hostB 6000

Un poco difícil de realizar al principio, pero una vez usted envuelve su cabeza con los conceptos de línea vs. fuera de línea, y copia cruda vs. Volcado de datos; no es tan complicado. Y déjeme decirle, que una vez automatizado, usted tiende a dormir muho mejor!.