¿Cómo hacer copias de seguridad de bases de datos MySQL con 5 métodos diferentes?

A medida que se almacena más y más datos en la base de datos, la copia de seguridad de los datos se vuelve cada vez más importante. La copia de seguridad de la base de datos es una tarea muy crítica para asegurar que las empresas o individuos no sufran pérdidas importantes debido a la pérdida de datos. MySQL es un sistema de gestión de bases de datos relacionales de código abierto muy popular, que se utiliza ampliamente en las empresas. Este artículo discutirá cómo hacer una copia de seguridad de la base de datos MySQL.

Tipos de copia de seguridad de la base de datos MySQL

Según el tamaño de la base de datos, existen cuatro tipos de copias de seguridad, que se utilizan en diferentes ocasiones:

Copia de seguridad completa

Este es un método común utilizado por la mayoría de las personas. Puede hacer una copia de seguridad de toda la base de datos, incluyendo todos los objetos de la base de datos como tablas de usuarios, tablas del sistema, índices, vistas y procedimientos almacenados. Pero lleva más tiempo y espacio, por lo que generalmente se recomienda hacer una copia de seguridad completa una vez a la semana.

Copia de seguridad del registro de transacciones

El registro de transacciones es un archivo separado que registra los cambios en la base de datos. Las copias de seguridad solo necesitan copiar los cambios realizados en la base de datos desde la última copia de seguridad, por lo que tarda muy poco tiempo. Para garantizar la robustez de la base de datos, se recomiendan copias de seguridad del registro de transacciones cada hora o incluso con mayor frecuencia.

Copia de seguridad diferencial

Es otro método de respaldo que solo cubre parte de la base de datos, no utiliza el registro de transacciones, en su lugar, usa una nueva imagen completa de la base de datos. Es más pequeño que el respaldo completo original porque solo contiene las bases de datos que han cambiado desde el último respaldo completo. Tiene la ventaja de un almacenamiento y recuperación rápidos. Se recomienda realizar un respaldo diferencial todos los días.

Copia de seguridad de archivo

Una base de datos puede constar de muchos archivos en el disco duro. Si la base de datos es muy grande y no se puede respaldar durante la noche, se pueden utilizar copias de seguridad de archivos para respaldar parte de la base de datos cada noche. Dado que las bases de datos generalmente no son tan grandes como para requerir múltiples tiendas de archivos, este tipo de respaldo no es muy común.

Según el estado de la base de datos, se puede dividir en tres tipos:

1. Copia de seguridad fría

Las copias de seguridad frías son copias de seguridad que se realizan en los datos mientras la base de datos está desconectada y no es accesible para los usuarios. Dado que las copias de seguridad se realizan mientras la base de datos está desconectada, las copias de seguridad frías también se llaman a menudo copias de seguridad fuera de línea. Las copias de seguridad frías a menudo consumen menos recursos y, ya que no se puede agregar ningún nuevo dato (la base de datos está desconectada), el proceso de copia de seguridad puede respaldar todos los datos de una sola vez. Por supuesto, las copias de seguridad frías tienen la limitación de que no se puede acceder a la base de datos cuando se está realizando la operación de copia de seguridad.

2. Copia de seguridad caliente

Las copias de seguridad en caliente son copias que se realizan en los datos mientras la base de datos está en línea y accesible para los usuarios. Las copias de seguridad en caliente suelen ser una solución muy conveniente porque no requieren tiempo de inactividad y la interrupción del sistema operativo es muy mínima, pero también tienen ciertas desventajas: si los datos que se están respaldando se modifican de alguna manera mientras se está realizando la copia de seguridad, esta podría no ser igual a la versión final de los datos en la base de datos.

3. Copia de seguridad lógica

La copia de seguridad lógica utiliza software para extraer datos de una base de datos y escribe los resultados en un archivo.

¿Cómo hacer una copia de seguridad de las bases de datos MySQL?

Según el método de recuperación, para MySQL hay copias de seguridad lógicas y físicas. La copia de seguridad lógica consiste en respaldar las declaraciones SQL y ejecutarlas durante la recuperación para reproducir los datos de la base de datos. La copia de seguridad física copia los archivos de la base de datos en el mismo formato en que se almacenan en el disco.

Estos dos tipos de copias de seguridad tienen sus propias ventajas y desventajas. En general, la velocidad de recuperación de la copia de seguridad física es más rápida pero ocupa más espacio, mientras que la velocidad de la copia de seguridad lógica es más lenta pero ocupa menos espacio.

1. Usando mysqldump

Mysqldump es una herramienta de copia de seguridad que viene con MySQL, en el directorio bin: /usr/local/mysql/bin/mysqldump, que admite copias de seguridad en caliente basadas en InnoDB. Sin embargo, como es una copia de seguridad lógica, la velocidad no es muy rápida y es adecuada para escenarios donde los datos de respaldo son relativamente pequeños. La copia de seguridad completa de Mysqldump + registro binario puede lograr la recuperación en un punto específico en el tiempo.

Para las tablas del motor de almacenamiento MyISAM, solo se puede usar una copia de seguridad en caliente. En este momento, se debe evitar la escritura de datos, por lo que primero agrega un bloqueo de lectura. En este punto, también puedes entrar a la base de datos para agregar manualmente un bloqueo de lectura, pero esto es molesto. Puedes tener directamente una opción de bloqueo en la herramienta mysqldump, que es --lock-all-tables, como mysqldump --databases test --lock-all- tables --flush-logs > /tmp/backup_test_`date+%F-%H-%M`.sql.

Si estás haciendo una copia de seguridad de una sola tabla, simplemente agrega el nombre de la tabla después del nombre de la biblioteca test.

Para las tablas del motor de almacenamiento InnoDB, se puede realizar una copia de seguridad en caliente sin bloquear la base de datos. Agregue una opción para realizar una copia de seguridad en caliente: --single-transaction, por ejemplo: mysqldump --databases test --single-transaction --flush-logs --master-data=2> /tmp/backup_test_`date+%F-%H-%M`.sql.

P.D. No olvides cerrar el registro binario al restaurar:

mysql> set sql_log_bin=0;

Debido a que este método se basa en una copia de seguridad lógica, la ejecución de SQL insertará datos y los registrará en el registro binario. Dado que esto es una recuperación, se puede desactivar el registro binario insertado para acortar el tiempo de recuperación.

Haz clic para obtener más información sobre la copia de seguridad y la recuperación de la base de datos MySQL con o sin Mysqldump.

2. Copia de seguridad basada en el snapshot de LVM

En términos de copia de seguridad física, existe una copia de seguridad física basada en el sistema de archivos (snapshot de LVM), y también puedes usar comandos como tar para empaquetar y hacer una copia de seguridad del directorio de la base de datos completa, pero estos solo se pueden usar para copias de seguridad en frío. Diferentes motores de almacenamiento respaldan diferentes contenidos. MyISAM respalda automáticamente a nivel de tabla, mientras que InnoDB solo puede respaldar toda la base de datos si no habilita el espacio de tabla independiente.

Lo siguiente es una introducción al uso de la función de instantáneas de LVM para copias de seguridad.

1) Por seguridad, primero imponga un bloqueo de lectura en la base de datos

mysql>FLUSH TABLES WITH READ LOCK

2) Actualice el registro binario para la recuperación de punto en tiempo

mysql>FLUSH LOGS

3) Luego cree el volumen de instantánea

lvcreate –L 1G –s –n data-snap –p –r /dev/myvg/mydata

4) Ingrese a la base de datos para liberar el bloqueo de lectura

UNLOCK TABLES

5) Montar el volumen de instantánea para la copia de seguridad

mount –r /dev/myvg/data-snap /mnt/snap

6) Finalmente, empaqueta y respalda los archivos en /mnt/snap

Para restaurar:

1) cierre mysqld

2) luego respalda el registro binario

3) restaurar el archivo de copia de seguridad original

4) luego restaure hasta el punto en el tiempo del error a través del registro binario (no olvide cerrar temporalmente el registro binario al restaurar el punto de tiempo a través del mismo)

Entre 2010 y 2012, la base de datos MySQL se implementó en el entorno de nube de Amazon. Proporcionaron instantáneas de LVM, que son muy convenientes y rápidas. Usando instantáneas de LVM, la recuperación también es extremadamente rápida en la red de 10G de Amazon.

3. Copia de seguridad con Tar

Prepárate para la primera copia de seguridad de datos desde la base de datos, bloquea temporalmente todas las tablas y abre la ventana 1

mysql> flush tables with read lock;

Consulta correcta, 0 filas afectadas (0.00 seg)

mysql> show master status;

+++++
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |  
| mysql-bin.000003 | 194554 | | |  
1 row in set (0.00 sec)

Esta ventana no puede cerrarse y debe mantenerse hasta que se complete el paquete tar.

--Re-open a shell window, go to the data file directory tar package
open window 2
[root@myfstv_21_11 data]# tar -zcvf mysqla1.tar.gz mysqla
tar -zcvf mysqla1.tar.gz mysqla Compressed
Switch to window 1 and execute the unlock command

mysql> unlocktables;

Query OK, 0 rows affected (0.00 sec)
mysql>

Copie el paquete tar a otro servidor de biblioteca de MySQL, sobrescriba el directorio de datos y luego reinicie el servicio de base de datos de MySQL.

4. xTraBackup proporcionado por Percona

Soporta copias de seguridad físicas en caliente de InnoDB, soporta copias de seguridad completas e incrementales y es muy rápido, soporta la migración de datos entre diferentes bases de datos causada por el almacenamiento de InnoDB y soporta copias de seguridad y recuperación de esclavos en modo de replicación. Para que xTraBackup soporte más extensiones funcionales, puede configurar un espacio de tabla independiente y habilitar la función innoDB_file_per_table. Entonces, podrá soportar copias de seguridad individuales de tablas.

Principio de la copia de seguridad xTraBackup:

xTraBackup se basa en la función de recuperación después de un fallo de InnoDB. Copiará el archivo de datos de InnoDB. Dado que la tabla no está bloqueada, los datos copiados son inconsistentes. Al recuperar, utilice la recuperación después de un fallo para hacer que los datos sean coherentes.

innoDB mantiene un registro de rehacer, también conocido como registro de transacciones, que contiene todos los cambios en los datos de innoDB. Cuando innoDB se inicia, primero verificará el archivo de datos y el registro de transacciones, y realizará los siguientes pasos:

Cuando xTraBackup está realizando una copia de seguridad, copia los datos de la página de InnoDB página por página sin bloquear la tabla. Al mismo tiempo, xTraBackup tiene otro hilo para monitorear el registro de transacciones. Una vez que el registro cambia, copiará las páginas del registro cambiado. Debido a que el tamaño del archivo de registro de transacciones es limitado, comenzará a escribir desde el principio una vez que esté lleno, por lo que los nuevos datos pueden sobrescribir los datos antiguos.

Durante el proceso de preparación, xTraBackup usa el registro de transacciones copiado para realizar la recuperación de fallos en el archivo de datos de innoDB respaldado.

5. Copia de seguridad de bases de datos MySQL con Vinchin Backup & Recovery

Vinchin Backup & Recovery ofrece una funcionalidad poderosa para proteger sus bases de datos en máquinas virtuales y servidores físicos. Al trabajar bien con las copias de seguridad a nivel de máquina virtual, se proporciona un doble seguro a los usuarios de entornos virtuales para sus datos y sistemas de información clave. Vinchin Backup & Recovery admite la protección de Oracle DB, MySQL, SQL Server, PostgreSQL, Postgres Pro y MariaDB instalados tanto en máquinas físicas como virtuales, con características poderosas de copia de seguridad y restauración de bases de datos. También proporciona estrategias de copia de seguridad completa, diferencial, incremental y de registro de transacciones para que pueda configurar su propio plan de copia de seguridad según sea necesario.

Vinchin Backup & Recovery admite la copia de seguridad en caliente eficiente de MySQL, SQL Server y Oracle sin afectar el funcionamiento normal de las bases de datos.

La operación de Vinchin Backup & Recovery es muy simple, solo unos pocos pasos sencillos.

1. Seleccione el objeto de copia de seguridad.

2. Seleccione el destino de la copia de seguridad.

3. Configura estrategias de respaldo.

4. Revisar y enviar el trabajo.

Puedes comenzar a usar este potente sistema con una prueba gratuita completa de 60 días. Solo haz clic en el botón para obtener el paquete de instalación. Puedes hacer clic aquí para obtener más información sobre cómo hacer una copia de seguridad de MySQL con Vinchin Backup & Recovery.

Resumen

La base de datos MySQL se utiliza ampliamente en muchas industrias. Para garantizar su seguridad, en este artículo compartimos cinco métodos diferentes para respaldar los datos de MySQL. Puedes elegir el método más adecuado según tu situación real. ¿A qué esperas? ¡ date prisa y realiza un respaldo de tu base de datos MySQL!