Backup lógico vs backup físico en MySQL

¿QUÉ ES UN BACKUP?

Una copia de seguridad, respaldo, copia de respaldo, copia de reserva (del inglés backup) en ciencias de la información e informática es una copia de los datos originales fuera de la infraestructura que se realiza con el fin de disponer de un medio para recuperarlos en caso de su pérdida. Las copias de seguridad son útiles ante distintos eventos y usos: recuperar los sistemas informáticos y los datos de una catástrofe informática, natural o ataque; restaurar una pequeña cantidad de archivos que pueden haberse eliminado accidentalmente, corrompido, infectado por un virus informático u otras causas; guardar información histórica de forma más económica que los discos duros y además permitiendo el traslado a ubicaciones distintas de la de los datos originales; etc.

En este tutorial vamos a comprar el rendimiento (en tiempo) de un backup lógico y físico en un servidor de bases de datos con MySQL.

Backup Lógico

• Se utiliza la herramienta mysqldump para realizar el backup
• Se puede realizar el backup con el servidor online
• Permite "transportar" bases de datos entre servidores
• Permite hacer restauraciones parciales (p.ej.: recuperar una tabla)
• Más costoso (en tiempo y espacio) que backup físico
• Puede configurarse restauración "en tiempo" utilizando logs

Backup Físico

• No se necesitan herramientas para el backup ya que se copian los ficheros del filesystem
• Es preferible detener el servidor de Bases de Datos
• Mas fácil para recuperar Bases de Datos complejas
• Menos costoso que backup lógico
• No se permite restaurar "en tiempo"

1.- REQUERIMIENTOS

Los únicos requerimientos para seguir este tutorial son los siguientes:

• PC con GNU/Linux, Windows, ó MacOS (preferiblemente GNU/Linux)
• Servidor de MySQL
• Alguna(s) base(s) de datos para respaldar/restaurar

2.- PASOS PREVIOS

Para las pruebas de backup y restauración, utilizaremos la misma base de datos creada con datos fictícios. La base de datos tendrá un total de 1.000.000 de registros y su estructura es muy sencilla, una tabla con 2 campos (id, nombre).

Si ya tenéis una base de datos lo suficientemente grande para probar, estos pasos podéis saltarlos.

Lo primero es crear una base de datos, para ello con el comando "mysql" entramos en la consola con las credenciales de root. Crearemos la base de datos benchmark con el siguiente comando:

mysql> CREATE DATABASE benchmark;
Query OK, 1 row affected (0.002 sec)

Ahora debemos establecer la base de datos sobre la que queremos trabajar, para ello ejecutaremos:

mysql> use benchmark;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

El siguiente paso será crear una tabla con 2 campos (id y name):

mysql> CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.42 sec)

Si queremos ver si la estructura que hemos creada es la correcta, podemos comprobarlo de la siguiente forma:

mysql> describe users;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.08 sec)

Como la base de datos no tiene datos, lo que vamos a hacer es llenarla con 1.000.000 de registros. Existen mejores formas de hacer un "populate" en una base de datos, pero en este caso he usado la siguiente (el inconveniente es que tarda mucho mas que usando otras formas):

for i in `seq 1 10000000`; do mysql -uroot -proot benchmark -e "insert into users (name) VALUES ('here_a_name')"; done

3.- REALIZAR UN BACKUP LÓGICO

Para realizar un backup lógico usaremos la herramienta mysqldump de la siguiente forma:

raul@localhost:~$ mysqldump -u[usuario] -p[contraseña] [base_de_datos] > [fichero_fackup].sql

Por ejemplo, para este tutorial hemos una base de datos llamada workbench, con permisos para el usuario root y contraseña root, por tanto usaremos el siguiente comando:

raul@localhost:~$ mysqldump -uroot -proot workbench > workbench_backup.sql

4.- RESTAURACIÓN DEL BACKUP LÓGICO

Para restaurar el backup lógico y obtener el tiempo para las pruebas de importación, se han realizado los siguientes pasos: Reiniciar servidor MySQL y luego importar el backup sql de la siguiente forma:

raul@localhost:~$ time mysql -uroot -proot workbench  [fichero_backup].sql
mysql: [Warning] Using a password on the command line interface can be insecure.

real    1m2.907s
user    0m4.004s
sys    0m0.216s

5.- REALIZAR UN BACKUP FÍSICO

Para el backup físico de la base de datos, se ha copiado el directorio correspondiente a dicha base de datos usando el siguiente comando y se ha obtenido su tiempo:

raul@localhost:~$ time cp -R /var/lib/mysql/benchmark/ /tmp/backup1

6.- RESTAURAR UN BACKUP FÍSICO

Para restaurar el backup físico, se hace el paso inverso al apartado 5 (debemos tener en cuenta que la base de datos debe existir para que funcionen las consultas correctamente). El propietario y grupo de los ficheros los cambiamos a propietario=mysql y grupo=mysql:

raul@localhost:~$ time cp -R /tmp/backup1/* /var/lib/mysql/benchmark/ && chmod -R mysql.mysql /var/lib/mysql/benchmark/

7.- COMPARATIVA BACKUP/RESTAURACIÓN LÓGICA vs FÍSICA

Utilizando la base de datos utilizada en este ejemplo (1.000.000 de registros), se han sacado los tiempos que se tarda en hacer el backup y restaurarlo de forma lógica y física. No tedremos en cuenta si paramos/arrancamos el servidor de MySQL. Si lo paramos/arrancamos en los 2 tipos de copias, se puede omitir el tiempo de parada/arranque y tener solo en cuenta el tiempo de backup/restauración.

Los resultados obtenidos son los siguientes en milisegundos:

8 - CONCLUSIONES

Si la base de datos es muy muy muy grande, sin duda la copia física es mucho mas rápida pero tendremos el problema de que recuperar una única tabla será casi imposible. Es recomendado para un full-restore. En caso contrario, usaremos el backup lógico.

Benchmark

ENJOY!