Copias de Seguridad

Tipos de respaldo y recuperación

Copias de seguridad físicas (sin procesar) versus copias de seguridad lógicas

Las copias de seguridad físicas consisten en copias sin procesar de los directorios y archivos que almacenan el contenido de la base de datos. Este tipo de copia de seguridad es adecuado para bases de datos grandes e importantes que deben recuperarse rápidamente cuando surgen problemas. Los métodos de respaldo físico tienen estas características:

  • La copia de seguridad consta de copias exactas de los directorios y archivos de la base de datos. Normalmente, se trata de una copia de todo o parte del directorio de datos de MySQL.
  • Los métodos de copia de seguridad física son más rápidos que lógicos porque solo implican la copia de archivos sin conversión.
  • La salida es más compacta que la de la copia de seguridad lógica.
  • Además de las bases de datos, la copia de seguridad puede incluir cualquier archivo relacionado, como archivos de registro o de configuración.
  • Las copias de seguridad son portátiles solo a otras máquinas que tienen características de hardware idénticas o similares.

Los métodos de respaldo lógico tienen estas características:

  • La copia de seguridad se realiza consultando el servidor MySQL para obtener la estructura de la base de datos y la información del contenido.
  • La copia de seguridad es más lenta que los métodos físicos porque el servidor debe acceder a la información de la base de datos y convertirla a formato lógico. Si la salida está escrita en el lado del cliente, el servidor también debe enviarla al programa de respaldo.
  • La salida es mayor que la de la copia de seguridad física, especialmente cuando se guarda en formato de texto.
  • La granularidad de copia de seguridad y restauración está disponible a nivel de servidor (todas las bases de datos), nivel de base de datos (todas las tablas en una base de datos en particular) o nivel de tabla. Esto es cierto independientemente del motor de almacenamiento.
  • La copia de seguridad no incluye archivos de registro o de configuración, ni otros archivos relacionados con la base de datos que no forman parte de las bases de datos.
  • Las copias de seguridad almacenadas en formato lógico son independientes de la máquina y altamente portátiles.
  • Las copias de seguridad lógicas se realizan con el servidor MySQL en ejecución. El servidor no se desconecta.

Copias de seguridad locales versus copias de seguridad remotas

Se realiza una copia de seguridad local en el mismo host donde se ejecuta el servidor MySQL, mientras que una copia de seguridad remota se realiza desde un host diferente. Para algunos tipos de copias de seguridad, la copia de seguridad se puede iniciar desde un host remoto incluso si la salida se escribe localmente en el servidor. anfitrión.

  • [mysqldump] deSQL, puede realizar volcados locales o remotos y generar una salida en el cliente. Para la salida de texto delimitado (con la [--tab], los archivos de datos se crean en el host del servidor.

  • [SELECT ... INTO OUTFILE] se puede iniciar desde un host de cliente local o remoto, pero el archivo de salida se crea en el host del servidor.

  • Los métodos de copia de seguridad física generalmente se inician localmente en el host del servidor MySQL para que el servidor se pueda desconectar, aunque el destino de los archivos copiados puede ser remoto.

Ejemplo de estrategia de copia de seguridad y recuperación

  1. Establecimiento de una política de respaldo
  2. Uso de copias de seguridad para la recuperación
  3. Resumen de la estrategia de respaldo

Esta sección describe un procedimiento para realizar copias de seguridad que le permite recuperar datos después de varios tipos de fallas:

  • Caída del sistema operativo
  • Fallo de alimentación
  • Fallo del sistema de archivos
  • Problema de hardware (disco duro, placa base, etc.)

Los comandos de ejemplo no incluyen opciones como [--user] y [--password]para los programas cliente [mysqldump] y [mysql]. Debe incluir las opciones necesarias para permitir que los programas cliente se conecten al servidor MySQL.

Suponga que los datos se almacenan en el motor InnoDB de almacenamiento, que admite transacciones y recuperación automática de fallos. Suponga también que el servidor MySQL está bajo carga en el momento del bloqueo. Si no fuera así, nunca se necesitaría recuperación.

Para los casos de fallas del sistema operativo o fallas de energía, podemos asumir que los datos del disco de MySQL están disponibles después de un reinicio. Es InnoDBposible que los archivos de datos no contengan datos consistentes debido al bloqueo, pero InnoDBlee sus registros y encuentra en ellos la lista de transacciones pendientes comprometidas y no comprometidas que no se han vaciado en los archivos de datos. InnoDBrevierte automáticamente las transacciones que no se confirmaron y descarga en sus archivos de datos las que se confirmaron. La información sobre este proceso de recuperación se transmite al usuario a través del registro de errores de MySQL. El siguiente es un extracto de registro de ejemplo:

InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections

Para los casos de fallas del sistema de archivos o problemas de hardware, podemos asumir que los datos del disco MySQL no están disponibles después de un reinicio. Esto significa que MySQL no se inicia correctamente porque algunos bloques de datos del disco ya no se pueden leer. En este caso, es necesario reformatear el disco, instalar uno nuevo o corregir el problema subyacente. Entonces es necesario recuperar nuestros datos MySQL de las copias de seguridad, lo que significa que las copias de seguridad ya deben haberse realizado. Para asegurarse de que ese sea el caso, diseñe e implemente una política de respaldo.

Establecimiento de la política de respaldo

Para que sean útiles, las copias de seguridad deben programarse con regularidad. Se puede realizar una copia de seguridad completa (una instantánea de los datos en un momento determinado) en MySQL con varias herramientas.En este puto utilizaremos [mysqldump].

Supongamos que hacemos una copia de seguridad completa de todas nuestras tablas InnoDB en todas las bases de datos usando el siguiente comando el domingo a la 1 pm, cuando la carga es baja:

$> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql
El archivo .sql resultante producido por [mysqldump]contiene un conjunto de declaraciones SQL [INSERT] que se pueden usar para volver a cargar las tablas volcadas en un momento posterior.

Esta operación de copia de seguridad adquiere un bloqueo de lectura global en todas las tablas al comienzo del volcado (uso [FLUSH TABLES WITH READ LOCK]. Tan pronto como se ha adquirido este bloqueo, se leen las coordenadas del registro binario y se libera el bloqueo. Si se están ejecutando declaraciones de actualización largas cuando se emite la declaración [FLUSH], la operación de copia de seguridad puede detenerse hasta que finalicen esas declaraciones. Después de eso, el volcado se vuelve libre de bloqueos y no perturba las lecturas y escrituras en las tablas.

Se asumió anteriormente que las tablas para respaldar son tablas InnoDB, por lo que [--single-transaction] usa una lectura consistente y garantiza que los datos vistos por [mysqldump] no cambien.

Las copias de seguridad completas son necesarias, pero no siempre es conveniente crearlas. Producen archivos de copia de seguridad de gran tamaño y requieren tiempo para generarlos. No son óptimos en el sentido de que cada copia de seguridad completa sucesiva incluye todos los datos, incluso la parte que no ha cambiado desde la copia de seguridad completa anterior. Es más eficaz realizar una copia de seguridad completa inicial y luego realizar copias de seguridad incrementales. Las copias de seguridad incrementales son más pequeñas y requieren menos tiempo para producirse. La compensación es que, en el momento de la recuperación, no puede restaurar sus datos simplemente volviendo a cargar la copia de seguridad completa. También debe procesar las copias de seguridad incrementales para recuperar los cambios incrementales.

Para realizar copias de seguridad incrementales, necesitamos guardar los cambios incrementales. En MySQL, estos cambios se representan en el registro binario, por lo que el servidor MySQL siempre debe iniciarse con la opción [--log-bin] de habilitar ese registro. Con el registro binario habilitado, el servidor escribe cada cambio de datos en un archivo mientras actualiza los datos. Mirando el directorio de datos de un servidor MySQL que se ha estado ejecutando durante algunos días, encontramos estos archivos de registro binarios de MySQL:

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

Cada vez que se reinicia, el servidor MySQL crea un nuevo archivo de registro binario utilizando el siguiente número de la secuencia. Mientras el servidor se está ejecutando, también puede decirle que cierre el archivo de registro binario actual y comience uno nuevo manualmente emitiendo una declaración SQL [FLUSH LOGS] o con un [comando mysqladmin flush-logs]. [mysqldump] también tiene una opción para vaciar los registros. El archivo .index en el directorio de datos contiene la lista de todos los registros binarios de MySQL en el directorio.

Los registros binarios de MySQL son importantes para la recuperación porque forman el conjunto de copias de seguridad incrementales. Si se asegura de vaciar los registros cuando realiza la copia de seguridad completa, los archivos de registro binarios creados posteriormente contienen todos los cambios de datos realizados desde la copia de seguridad. Modifiquemos un poco el comando [mysqldump] anterior para que vacíe los registros binarios de MySQL en el momento de la copia de seguridad completa, y para que el archivo de volcado contenga el nombre del nuevo registro binario actual:

$> mysqldump --single-transaction --flush-logs --master-data=2\
         --all-databases > backup_sunday_1_PM.sql

Después de ejecutar este comando, el directorio de datos contiene un nuevo archivo de registro binario gbichot2-bin.000007, porque la opción [--flush-logs] hace que el servidor vacíe sus registros. La opción [--master-data] hace que [mysqldump] escriba información de registro binario en su salida, por lo que el archivo .sql de volcado resultante incluye estas líneas:

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

Debido a que el comando [mysqldump] realizó una copia de seguridad completa, esas líneas significan dos cosas:

  • El archivo de volcado contiene todos los cambios realizados antes de cualquier cambio escrito en el archivo de registro gbichot2-bin.000007  binario o superior.

  • Todos los cambios de datos registrados después de la copia de seguridad no están presentes en el archivo de volcado, pero están presentes en el archvo de registro binario gbichot2-bin.000007 o superior.

Los registros binarios de MySQL ocupan espacio en disco. Para liberar espacio, límpielos de vez en cuando. Una forma de hacerlo es eliminando los registros binarios que ya no son necesarios, como cuando hacemos una copia de seguridad completa:

$> mysqldump --single-transaction --flush-logs --master-data=2\
         --all-databases --delete-master-logs > backup_sunday_1_PM.sql
Nota: Eliminar los registros binarios de MySQL con [mysqldump --delete-master-logs] puede ser peligroso si su servidor es un servidor de origen de replicación, porque es posible que las réplicas aún no hayan procesado completamente el contenido del registro binario.

Uso de las copias de seguridad para la recuperación

Ahora, suponga que tenemos una salida inesperada catastrófica el miércoles a las 8 am que requiere la recuperación de las copias de seguridad. Para recuperar, primero restauramos la última copia de seguridad completa que tenemos (la del domingo 1 pm). El archivo de copia de seguridad completo es solo un conjunto de declaraciones SQL, por lo que restaurarlo es muy fácil:

$> mysql < backup_sunday_1_PM.sql

En este punto, los datos se restauran a su estado a la 1 pm del domingo. Para restaurar los cambios realizados desde entonces, debemos utilizar las copias de seguridad incrementales; es decir, los archivos de registro binarios gbichot2-bin.000007y gbichot2-bin.000008. Busque los archivos, si es necesario, desde donde se realizó la copia de seguridad y luego procese su contenido de esta manera:

$> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

Ahora hemos recuperado los datos a su estado a la 1 pm del martes, pero aún nos faltan los cambios desde esa fecha hasta la fecha del accidente. Para no perderlos, habríamos necesitado que el servidor MySQL almacenara sus registros binarios MySQL en una ubicación segura (discos RAID, SAN, ...) diferente del lugar donde almacena sus archivos de datos, para que estos registros no fueran en el disco destruido. (Es decir, podemos iniciar el servidor con una opción [--log-bin] que especifica una ubicación en un dispositivo físico diferente de aquel en el que reside el directorio de datos. De esa manera, los registros están seguros incluso si se pierde el dispositivo que contiene el directorio). habíamos hecho esto, tendríamos el archivo gbichot2-bin.000009 (y cualquier archivo posterior) disponible, y podríamos aplicarlos usando [mysqlbinlog] y [mysql] para restaurar los cambios de datos más recientes sin pérdida hasta el momento del bloqueo:

$> mysqlbinlog gbichot2-bin.000009 ... | mysql

Resumen de e estrategia de respaldo

En caso de una falla del sistema operativo o un corte de energía, él mismo InnoDB hace todo el trabajo de recuperar los datos. Pero para asegurarse de que puede dormir bien, observe las siguientes pautas:

  • Siempre ajuste el servidor MySQL con el registro binario habilitado (esa es la configuración predeterminada para MySQL 8.0). Si tiene un medio tan seguro, esta técnica también puede ser buena para equilibrar la carga del disco (lo que da como resultado una mejora del rendimiento).

  • Realice copias de seguridad completas periódicas, utilizando el comando [mysqldump] que se mostró anteriormente, que realiza una copia de seguridad en línea sin bloqueo.

  • Realice copias de seguridad incrementales periódicas vaciando los registros con [FLUSH LOGS] o [mysqladmin flush-logs].

Uso de mysqldump oara copuas de seguridad

Volcado de datos cob mysqldump

Esta sección describe cómo usar [mysqldump] para crear archivos de volcado en formato SQL. Para obtener información sobre cómo volver a cargar dichos archivos de volcado.

De forma predeterminada, [mysqldump] escribe información como declaraciones SQL en la salida estándar. Puede guardar la salida en un archivo:

$> mysqldump [arguments] > file_name

Para volcar todas las bases de datos, invoque [mysqldump] con la opción [--all-databases]:

$> mysqldump --all-databases > dump.sql

Para volcar solo bases de datos específicas, nómbrelas en la línea de comando y use la opción [--databases]:

$> mysqldump --databases db1 db2 db3 > dump.sql

La opción [--databases] hace que todos los nombres de la línea de comandos se traten como nombres de base de datos. Sin esta opción, [mysqldump]trata el primer nombre como un nombre de base de datos y los siguientes como nombres de tabla.

Con [--all-databases] o [--databases], [mysqldump] escribe [CREATE DATABASE] y declara [USE] antes de la salida de volcado para cada base de datos. Esto asegura que cuando se vuelve a cargar el archivo de volcado, crea cada base de datos si no existe y la convierte en la base de datos predeterminada para que los contenidos de la base de datos se carguen en la misma base de datos de la que provienen. Si desea que el archivo de volcado fuerce la eliminación de cada base de datos antes de volver a crearla, utilice también la opción [--add-drop-database]. En este caso, [mysqldump] escribe una declaración [DROP DATABASE] antes de cada declaración [CREATE DATABASE].

Para volcar una sola base de datos, asígnele un nombre en la línea de comando:

$> mysqldump --databases test > dump.sql

En el caso de una sola base de datos, está permitido omitir la --databasesopción:

$> mysqldump test > dump.sql

La diferencia entre los dos comandos anteriores es que sin [--databases], la salida de volcado no contiene declaraciones [CREATE DATABASE] o [USE].Esto tiene varias implicaciones:

  • Cuando recarga el archivo de volcado, debe especificar un nombre de base de datos predeterminado para que el servidor sepa qué base de datos recargar.

  • Para volver a cargar, puede especificar un nombre de base de datos diferente del nombre original, lo que le permite volver a cargar los datos en una base de datos diferente.

  • Si la base de datos que se va a recargar no existe, primero debe crearla.

  • Dado que la salida no contiene ninguna declaración [CREATE DATABASE], la opción [--add-drop-database] no tiene ningún efecto. Si lo usa, no produce ninguna declaración [DROP DATABASE].

Para volcar solo tablas específicas de una base de datos, asígneles un nombre en la línea de comando después del nombre de la base de datos:

$> mysqldump test t1 t3 t7 > dump.sql

Recupera copias de seguridad en formato SQL

Para recargar un archivo de volcado escrito por [mysqldump] que consta de declaraciones SQL, utilícelo como entrada al cliente [mysql]. Si el archivo de volcado fue creado por [mysqldump] con la opción [--all-databases] o la opción [--databases], que contiene [CREATE DATABASE] y [USE] no es necesario especificar una base de datos por defecto en el que al cargar los datos:

$> mysql < dump.sql

Alternativamente, desde [mysql]use un sourcecomando:

mysql> source dump.sql

Si el archivo es un volcado de una sola base de datos que no contiene declaraciones [CREATE DATABASE] y [USE], primero cree la base de datos (si es necesario):

$> mysqladmin create db1

Luego, especifique el nombre de la base de datos cuando cargue el archivo de volcado:

$> mysql db1 < dump.sql

Alternativamente, desde [mysql], cree la base de datos, selecciónela como la base de datos predeterminada y cargue el archivo de volcado:

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

Volcado y recuperación en formato de datos delimitado

Otra opción, alternativa al volcado SQL es el volcado con formato delimitado. En manual de SQL puedes encontrar más información al respecto.

Algunos consejos utilizando mysqldump

Hacer una copia de una base de datos

$> mysqldump db1 > dump.sql
$> mysqladmin create db2
$> mysql db2 < dump.sql

No use la opción [--databases] en la línea de comando [mysqldump]porque eso hace USE db1que se incluya en el archivo de volcado, lo que anula el efecto de nombrar db2en la línea de comando [mysql].

Copiar una base de datos de un servidor a otro

En el servidor 1:

$> mysqldump --databases db1 > dump.sql

Copie el archivo de volcado del servidor 1 al servidor 2.

En el servidor 2:

$> mysql < dump.sql

El uso de la opcion [--databases] con [mysqldump] hace que el archivo de volcado incluya las declaraciones [CREATE DATABASE] y [USE] que crean la base de datos si existe y lo convierten en la base de datos predeterminada para los datos a cargar.

Alternativamente, puede omitir la opción [--databases] en el comando [mysqldump]. Luego, debe crear la base de datos en el servidor 2 (si es necesario) y especificarla como la base de datos predeterminada cuando vuelva a cargar el archivo de volcado.

En el servidor 1:

$> mysqldump db1 > dump.sql

En el servidor 2:

$> mysqladmin create db1
$> mysql db1 < dump.sql
Puede especificar un nombre de base de datos diferente en este caso, por lo que omitir la opción [--databases] en el comando [mysqldump] permite volcar datos de una base de datos y cargarlos en otra.

Volcado de programas almacenados

Varias opciones controlan cómo [mysqldump] maneja los programas almacenados (procedimientos y funciones almacenados, disparadores y eventos):

  • [--events]: Volcado de eventos del programador de eventos

  • [--routines]: Volcar funciones y procedimientos almacenados

  • [--triggers]: Desencadenadores de volcado para tablas

La opción [--triggers] está habilitada de forma predeterminada para que cuando se vuelcan las tablas, vayan acompañadas de los activadores que tengan. Las otras opciones están deshabilitadas por defecto y deben especificarse explícitamente para volcar los objetos correspondientes. Para desactivar cualquiera de estas opciones de forma explícita, utilice su forma de salto: [--skip-events], [--skip-routines] o [--skip-triggers].

Definiciones y contenido de la tabla de dumping por separado

La opción [--no-data] le dice a [mysqldump] que no descargue los datos de la tabla, lo que da como resultado que el archivo de volcado contenga solo declaraciones para crear las tablas. Por el contrario, la opción [--no-create-info] le dice a [mysqldump] que suprima las declaraciones CREATE de la salida, de modo que el archivo de volcado contenga solo datos de la tabla.

Por ejemplo, para volcar definiciones de tabla y datos por separado para la base de datos test, use estos comandos:

$> mysqldump --no-data test > dump-defs.sql
$> mysqldump --no-create-info test > dump-data.sql

Para un volcado de solo definición, agregue las opciones [--routines] y [--events] para incluir también definiciones de eventos y rutinas almacenadas:

$> mysqldump --no-data --routines --events test > dump-defs.sql

Recuperación en un momento determinado (incremental)

Recuperación en un momento determinado con el registro binario

Nota Muchos de los ejemplos de esta sección y la siguiente usan el cliente [mysql] para procesar la salida de registro binaria producida por [mysqlbinlog]. Si su registro binario contiene caracteres \0(nulos), [mysql] no puede analizar esa salida a menos que la invoque con la opción [--binary-mode].

La fuente de información para la recuperación en un momento determinado es el conjunto de archivos de registro binarios generados después de la operación de copia de seguridad completa. Por lo tanto, para permitir que un servidor se restaure a un punto en el tiempo, el registro binario debe estar habilitado en él, que es la configuración predeterminada para MySQL 8.0.

Para restaurar datos del registro binario, debe conocer el nombre y la ubicación de los archivos de registro binarios actuales. De forma predeterminada, el servidor crea archivos de registro binarios en el directorio de datos, pero se puede especificar un nombre de ruta con la opción [--log-bin] de colocar los archivos en una ubicación diferente.Para ver una lista de todos los archivos de registro binarios, use esta declaración:

mysql> SHOW BINARY LOGS;

Para determinar el nombre del archivo de registro binario actual, emita la siguiente declaración:

mysql> SHOW MASTER STATUS;

La utilidad [mysqlbinlog] convierte los eventos en los archivos de registro binarios de formato binario a texto para que se puedan ver o aplicar.

[mysqlbinlog] tiene opciones para seleccionar secciones del registro binario según la hora de los eventos o la posición de los eventos dentro del registro. 

La aplicación de eventos del registro binario hace que se vuelvan a ejecutar las modificaciones de datos que representan. Esto permite la recuperación de cambios en los datos durante un período de tiempo determinado. Para aplicar eventos del registro binario, procese la salida de [mysqlbinlog] usando el cliente [mysql]:

$> mysqlbinlog binlog_files | mysql -u root -p

Si los archivos de registro binarios se han cifrado, lo que se puede hacer desde MySQL 8.0.14 en adelante, [mysqlbinlog] no puede leerlos directamente como en el ejemplo anterior, pero puede leerlos desde el servidor usando la opción [--read-from-remote-server] ( -R). Por ejemplo:

$> mysqlbinlog --read-from-remote-server --host=host_name --port=3306  --user=root --password --ssl-mode=required  binlog_files | mysql -u root -p
Aquí, la opción --ssl-mode=required se ha utilizado para garantizar que los datos de los archivos de registro binarios estén protegidos en tránsito, porque se envían a [mysqlbinlog] en un formato no cifrado.

La visualización del contenido del registro puede ser útil cuando necesita determinar las horas o las posiciones de los eventos para seleccionar el contenido parcial del registro antes de ejecutar los eventos. Para ver los eventos del registro, envíe la salida de [mysqlbinlog] a un programa de paginación:

$> mysqlbinlog binlog_files | more
Alternativamente, guarde la salida en un archivo y visualice el archivo en un editor de texto:

$> mysqlbinlog binlog_files > tmpfile
$> ... edit tmpfile ...
Guardar la salida en un archivo es útil como un paso preliminar para ejecutar el contenido del registro con ciertos eventos eliminados, como un accidente [DROP TABLE]. Puede eliminar del archivo cualquier instrucción que no deba ejecutarse antes de ejecutar su contenido. Después de editar el archivo, aplique el contenido de la siguiente manera:

$> mysql -u root -p < tmpfile

Si tiene más de un registro binario para aplicar en el servidor MySQL, el método seguro es procesarlos todos usando una sola conexión al servidor. A continuación, se muestra un ejemplo que demuestra lo que puede ser peligroso :

$> mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
$> mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!

El procesamiento de registros binarios de esta manera usando diferentes conexiones al servidor causa problemas si el primer archivo de registro contiene una declaración [CREATE TEMPORARY TABLE] y el segundo registro contiene una declaración que usa la tabla temporal. Cuando termina el primer proceso de [mysql], el servidor descarta la tabla temporal.Cuando el segundo proceso de [mysql] intenta usar la tabla, el servidor informa " tabla desconocida. "

Para evitar problemas como este, utilice una sola conexión para aplicar el contenido de todos los archivos de registro binarios que desee procesar. Aquí hay una forma de hacerlo:

$> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
Otro enfoque es escribir todo el registro en un solo archivo y luego procesar el archivo:

$> mysqlbinlog binlog.000001 >  /tmp/statements.sql
$> mysqlbinlog binlog.000002 >> /tmp/statements.sql
$> mysql -u root -p -e "source /tmp/statements.sql"

Recuperación en un momento determinado mediante posiciones de eventos

Como ejemplo, suponga que alrededor de las 20:06:00 del 11 de marzo de 2020, se ejecutó una instrucción SQL que eliminó una tabla. Puede realizar una recuperación en un momento determinado para restaurar el servidor a su estado justo antes de la eliminación de la tabla. Estos son algunos pasos de muestra para lograrlo:

  1. Restaure la última copia de seguridad completa creada antes del punto en el tiempo de interés ( llámelo, que son las 20:06:00 del 11 de marzo de 2020 en nuestro ejemplo). Cuando termine, anote la posición del registro binario hasta la cual ha restaurado el servidor para su uso posterior y reinicie el servidor. tp
  2. Encuentre la posición precisa del evento del registro binario correspondiente al momento en el que desea restaurar su base de datos. En nuestro ejemplo, dado que conocemos el tiempo aproximado en el que tuvo lugar la eliminación de la tabla (), podemos encontrar la posición del registro comprobando el contenido del registro en ese momento utilizando la utilidad [mysqlbinlog]. Utilice las opciones y para especificar un período de tiempo corto y luego busque el evento en la salida. Por ejemplo: tp[--start-datetime] [--stop-datetime]tp
$> mysqlbinlog --start-datetime="2020-03-11 20:05:00"\
                   --stop-datetime="2020-03-11 20:08:00" --verbose\
         /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE"

/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 232
#200311 20:06:20 server id 1  end_log_pos 355 CRC32 0x2fc1e5ea  Query   thread_id=16    exec_time=0 error_code=0
SET TIMESTAMP=1583971580/*!*/;
SET @@session.pseudo_thread_id=16/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
DROP TABLE `pets`.`cats` /* generated by server */
/*!*/;
# at 355
#200311 20:07:48 server id 1  end_log_pos 434 CRC32 0x123d65df  Anonymous_GTID  last_committed=1    sequence_number=2   rbr_only=no original_committed_timestamp=1583971668462467   immediate_commit_timestamp=1583971668462467 transaction_length=473
# original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
# immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT)
/*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 434
#200311 20:07:48 server id 1  end_log_pos 828 CRC32 0x57fac9ac  Query   thread_id=16    exec_time=0 error_code=0    Xid = 217
use `pets`/*!*/;
SET TIMESTAMP=1583971668/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE dogs

Desde la salida de [mysqlbinlog], la declaración DROP TABLE pets.cats se puede encontrar en el segmento del registro binario entre la línea # at 232y # at 355, lo que significa que la declaración tiene lugar después de la posición de registro 232, y el registro está en la posición 355 después de la declaración DROP TABLE.

  1. Aplique los eventos en el archivo de registro binario al servidor, comenzando con la posición del registro que encontró en el paso 1 (suponga que es 155) y terminando en la posición que encontró en el paso 2 que está antes de su punto en el tiempo de interés ( que es 232):
$> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456\ | mysql -u root -p

El comando recupera todas las transacciones desde la posición inicial hasta justo antes de la posición de parada. Debido a que la salida de [mysqlbinlog] incluye las declaraciones SET TIMESTAMP antes de cada declaración de SQL registrada, los datos recuperados y los registros de MySQL relacionados reflejan las horas originales en las que se ejecutaron las transacciones.

Su base de datos ahora se ha restaurado al punto en el tiempo de interés, justo antes de que se descartara la tabla . tp``pets.cats

  1. Más allá de la recuperación de un momento específico que ha finalizado, si también desea volver a ejecutar todas las declaraciones después de su momento de interés, use [mysqlbinlog] nuevamente para aplicar todos los eventos posteriores al servidor. Observamos en el paso 2 que después de la declaración que queríamos omitir, el registro está en la posición 355;podemos usarlo para la opción, de modo que se incluyan las declaraciones posteriores a la posición: tp[--start-position]

    $> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456\
         | mysql -u root -p