Archivado de datos de bases de datos, ¿cuál es el mejor método?

Como DBA de MySQL, se puede decir que no hay nadie que no conozca pt-archiver. Como miembro importante de la suite pt-toolkit. a menudo puede ayudar fácilmente a los administradores de bases de datos a resolver problemas de archivo de datos. Por ejemplo, es suficiente tener una tabla en ejecución. La empresa solo necesita almacenar los datos en ejecución de los últimos tres meses y archivar los datos de hace tres meses. En este momento, pt-archiver puede ayudarlo a completar esta tarea fácilmente. Incluso configurarte para automatizar tareas sin intervención humana.

Como administradores de bases de datos, debemos saber qué está sucediendo y por qué, para poder utilizar la herramienta pt con confianza. Creo que muchos administradores de bases de datos han estudiado el principio de cambio de esquema en línea de pt, por lo que hoy planificaremos el principio de funcionamiento de pt-archiver con más profundidad.

1. Principio de observación

Los nativos tienen sus propios métodos. Abrimos directamente el registro general para observar cómo pt-archiver completa el archivo.

Comando

pt-archiver --source h=127.0.0.1,u=xucl,p=xuclxucl,P=3306,D=xucl,t=t1 --dest h =127.0.0.1,P=3306,u=xucl,p=xuclxucl,D= xucl_archive、t=t1 --progreso 5000 \

--estadísticas --charset=utf8mb4 --limit=10000 - -txn-size 1000 --sleep 30

Opciones comunes

-- analizar

Especifique la herramienta para realizar la operación "ANAlizar TABLA" en la tabla después completar el archivo de datos. Especifíquelo como "--analyze=ds", donde s representa la tabla de origen yd representa la tabla de destino, o se puede especificar por separado.

--ask-pass

Solicita una contraseña en la línea de comando para proteger las contraseñas, siempre que el módulo perl-TermReadKey esté instalado.

--buffer

Especifica que los datos del búfer se vacían en el archivo especificado por la opción "--file" y se vacían al enviarlos.

Solo cuando se confirma la transacción, el archivo especificado por "--file" se vaciará automáticamente y el archivo se vaciará en el disco, lo que significa que el archivo será vaciado por el bloque del sistema operativo. por lo tanto, antes de confirmar la transacción, algunos datos se descargarán implícitamente al disco. De forma predeterminada, el archivo se descarga en el disco después de cada línea de operación.

--Batch delete

Especifica una única instrucción para eliminar bloques en lotes de filas, ejecutando implícitamente la opción "--commit-each".

Utilice una única instrucción DELETE para eliminar las filas de la tabla correspondientes a cada bloque. El método habitual es eliminar las filas una por una mediante la clave principal. La eliminación por lotes puede mejorar en gran medida la velocidad, pero si hay "DÓNDE" complejos. "condiciones, la velocidad se reducirá. Podría ser más lenta.

--[no]bulk-delete-limit (límite de eliminación masiva)

Predeterminado: Sí

Especifique agregar "--" a la declaración que ejecuta las opciones de eliminación masiva de archivos" y "--limit".

--bulk-insert

Inserta filas mediante la inserción masiva de bloques utilizando el método LOAD DATA LOCAL INFILE (las opciones "--bulk-delete" y "--commit- se especifican implícitamente " cada")

En lugar de insertar individualmente ejecutando la operación de inserción fila por fila, esto es más rápido que ejecutar la instrucción INSERT en una sola fila. No realizaremos operaciones de inserción por lotes directamente, sino que crearemos implícitamente una tabla temporal para almacenar las filas (bloques) que deben insertarse en lotes y realizaremos una carga de datos unificada después de completar la inserción de cada bloque en la tabla temporal.

Para garantizar la seguridad de los datos, esta opción forzará el uso de la opción "--eliminar por lotes", asegurando efectivamente que la inserción sea completamente exitosa antes de la eliminación.

--channel

Cuando el entorno de replicación maestro-esclavo es de replicación de múltiples fuentes, especifique qué estación maestra archivar. Esto es adecuado para la replicación de múltiples fuentes donde se corresponden varias estaciones maestras. a una estación esclava.

--charset,-A

Especifica el juego de caracteres de conexión.

--[no]check-charset

Predeterminado: Sí

Especifica una verificación para garantizar que la conexión de la base de datos tenga el mismo conjunto de caracteres que el carácter de la tabla. colocar.

--[no]check-columns

Predeterminado: Sí

Especifique la verificación para garantizar que la tabla fuente especificada por las opciones "--source" y "--dest" "La tabla de destino especificada tiene los mismos campos.

No verificará la clasificación y el tipo de campos en la tabla, solo verificará si los campos existen en la tabla de origen y en la tabla de destino. Si los campos son inconsistentes, la herramienta saldrá con un. error. Si desea desactivar esta verificación, especifique "--no-check-columns".

--check-slave-lag

Especifica pausar la operación de archivo después de que el retraso de replicación maestro-esclavo sea mayor que el valor especificado por la opción "--max-lag" . De forma predeterminada, la herramienta verifica todas las dependencias, pero esta opción solo es válida para dependencias específicas (a través del método de conexión DSN).

--check-interval

Predeterminado: 1s

Si también se especifica la opción "--check-slave-lag", el tiempo especificado por este opción Es el tiempo de pausa cuando la herramienta detecta un retraso en la replicación maestro-esclavo. Verifique cada 100 filas.

--columns, -c

Especifique los campos de la tabla que se archivarán. Si hay varios campos, sepárelos con "," (coma).

--commit-each

Especifica el número de filas que se recuperarán y archivarán por confirmación. Esta opción deshabilita la opción "--txn-size".

Después de cada recuperación y archivado de datos de la tabla, se confirmará una transacción y el archivo especificado por la opción "--sleep" se actualizará antes de la siguiente recuperación y el tiempo de suspensión especificado por la opción "- -sleep". El tamaño de la transacción está controlado por la opción "--limit".

--host,-h

Especifique la dirección IP de la base de datos a la que conectarse.

-port, -P

Especifique el puerto de la base de datos al que conectarse.

--user, -u

Especifique el usuario de la base de datos al que conectarse.

--contraseña, -p

Especifique la contraseña del usuario de la base de datos conectada.

--socket, -S

Especifica el uso de una conexión de archivo SOCKET.

--databases, -d

Especifique la base de datos a la que conectarse

--source

Especifique la tabla para realizar operaciones de archivado ; Esta opción es obligatoria y se expresa mediante el método DSN.

--dest

Utilice el método DSN para especificar la tabla de destino en la que archivar.

Si no se especifica esta opción, el valor predeterminado es usar la misma tabla que la opción "--source" que especifica la tabla fuente.

--where

Especifica que los datos que se archivarán se especifican mediante la declaración condicional WHERE, y esta opción es una opción obligatoria. La palabra clave WHERE "no es obligatoria. Si realmente no necesita restringir la condición WHERE, puede especificar "--where 1=1".

--File

Especificar que los datos de la tabla deben archivarse. En qué archivo. Utilice un esquema de nomenclatura de formato similar a MySQL DATE_FORMAT()

El contenido del archivo utiliza el mismo formato que la instrucción SELECT INTO OUTFILE en MySQL.

Las opciones de nombres de archivos son las siguientes:

%Y: año, número, cuatro dígitos

%m: mes, número (01...12)

%d: mes, día, número (01...31)

%H: hora (00...23)

%i: minuto (00... 59) )

%s: segundos (00..59)

%s.segundos (00...59)

%D: nombre de la base de datos

p>

%t: nombre de la tabla

Ejemplo: --file '/var/log/archive/%Y-%m-%d-%D.% t'

--output-format

Utilice la opción "--file" para especificar el formato de salida del contenido del archivo.

El valor predeterminado si no se especifica esta opción es usar el carácter de tabulación como separador de campo. Si se especifica esta opción, use ',' (coma) como separador de campo y use '"' (. comillas dobles; instrucción. --share-lock

Especifica agregar una cláusula LOCK IN SHARE MODE a cada instrucción SELECT ejecutada por el archivo

--header

Especifica que el nombre del campo se escribe como la primera línea del archivo en el encabezado

--ignore

Especifica agregar la opción IGNORE a la instrucción INSERT <. /p>

--limit<. /p>

Predeterminado: 1

Especifique el número de filas para cada declaración para recuperar y archivar la tabla

-- local

Especifique no combinar OPTIMIZE y ANALYZE. La declaración se escribe en binlog

--max-lag

Valor predeterminado: 1s

. Especifica el tiempo de retardo máximo permitido de replicación maestro-esclavo (en segundos). Si el retraso maestro-esclavo excede el valor especificado después de recuperar los datos de cada fila, la operación de archivado se suspenderá y el tiempo de suspensión se especificará mediante la opción "--". check-interval". Una vez finalizado el tiempo de suspensión, la duración del retraso maestro-esclavo se verificará nuevamente y el esclavo realizará la verificación. Determinado por el valor "Seconds_Behind_Master". Si el retraso de replicación maestro-esclavo es continuo mayor que el valor especificado en este parámetro, o la biblioteca esclava deja de replicarse, la operación esperará hasta que se reinicie la biblioteca esclava y el retraso sea menor que el valor especificado en este parámetro

--no-. eliminar

Especifica no eliminar los datos de la tabla archivada

--Optimización

Especifica que la herramienta eliminará los datos después de archivarlos. La tabla realiza la operación "OPTIMIZAR". Operación TABLE". El método de especificación es "--analyze=ds", donde s representa la tabla de origen y d representa la tabla de destino.

--primary-key-only<. /p>

Especifica que solo se archivarán los campos de clave principal, esto es una abreviatura de la opción "--columns=primary-key"

Esto es más efectivo si la operación de archivo de la herramienta es BORRAR, ya que solo es obligatorio. Leer un campo de la clave principal en lugar de todos los campos de la fila

--progress

Especifique imprimir información de progreso para cada fila, imprimir la hora actual, el tiempo transcurrido y la hora que se archivará. Número de filas.

--purge

Especifica que se realizará una operación de purga en lugar de una operación de archivado. Esta operación permite ignorar las opciones "--dest" y "--file". Si es solo una operación de limpieza, se puede usar en combinación con la opción "--primary-key-only" para mejorar la eficiencia.

--quiet, -q

Especifica la herramienta de ejecución silenciosa y no genera ninguna información de ejecución.

--replace

Especifique la opción de escritura "--dest" para reescribir la instrucción INSERT en una instrucción REPLACE cuando el destino especificado finalice la tabla.

--Reintentar

Valor predeterminado: 1

Especifique el número de reintentos cuando la operación de archivo encuentra un punto muerto o se agota el tiempo de espera. Cuando el número de reintentos excede el valor especificado por esta opción, la herramienta saldrá con un error.

--run-time

Especifica cuánto tiempo debe ejecutarse la operación de archivo de la herramienta antes de salir. Los sufijos de tiempo permitidos son s=segundos, m=minutos, h=horas y d=días, el valor predeterminado es s si no se especifica.

--[no]safe-auto-increment

Predeterminado: Sí

Especifique no archivar el número máximo de columnas de incremento automático (AUTO_INCREMENT) correspondientes tomó.

Esta opción agrega una cláusula WHERE adicional a la purga del archivo para evitar que la herramienta elimine filas con el valor máximo del atributo AUTO_INCREMENT del campo ascendente de una sola columna, de modo que el valor de AUTO_INCREMENT se use después de reiniciar la base de datos. , pero esto puede provocar que las filas con los valores máximos correspondientes para los campos no se puedan archivar ni eliminar.

--set-vars

Predeterminado:

wait_timeout=10000

innodb_lock_wait_timeout=1

lock_wait_timeout= 60

Especifique los valores de los parámetros al archivar la herramienta. Si hay varios parámetros, sepárelos con ',' (coma). Por ejemplo, "--set-vars=wait_timeout=5000".

---skip-foreign-key-checks

Especifica deshabilitar las comprobaciones de claves externas mediante la instrucción SET FOREIGN_KEY_CHECKS = 0.

--sleep

Especifica cuánto tiempo debe dormir la herramienta antes de recuperar datos archivados mediante una instrucción SELECT. El valor predeterminado es no dormir. Las transacciones no se confirmarán antes de dormir y el archivo especificado por la opción "--file" no se actualizará. Si se especifica la opción "--commit-each", la transacción se confirma y el archivo se vacía antes de dormir.

--Estadísticas

Especifique la herramienta para recopilar e imprimir estadísticas de tiempo de operación.

Las estadísticas de muestra son las siguientes:

Comenzó en 2008-07-18T07:18:53 y finalizó en 2008-07-18T07:18 :53

Fuente: D=db,t=table

SELECCIONAR 4

INSERTAR 4

BORRAR 4

Porcentaje de tiempo de recuento de acciones

compromiso 10 0,1079 88,27

SELECCIONAR 5 0,0047 3,87

Eliminar 4 0,0028 2,29

Insertar 4 0,0028 2,28

Otro 0 0.0040 3.29

--txn-size

Predeterminado: 1

Especifique el número de registros que se procesarán para cada transacción. Si el valor es 0, la funcionalidad de transacción está deshabilitada.

--Version

Muestra la versión de la herramienta y sale.

--[no]version-check

Predeterminado: sí

Comprueba las últimas versiones del kit de herramientas Percona, MySQL y otros programas.

--why-quit

Especifica que la herramienta imprime el motivo de salida si sale que no sea debido al número de líneas archivadas completadas.

Esta opción es muy conveniente cuando se realizan tareas de archivado automático junto con la opción "--run-time". La opción "--run-time" se utiliza para determinar si la tarea de archivado se completa dentro del archivo. tiempo especificado. Si también se especifica la opción "--stats", se imprimen todos los motivos de salida.

2. Principios de análisis

Basándonos en la salida de los registros generales, organizamos las series de tiempo de la siguiente manera

3.

A primera vista A primera vista, no parece haber ningún problema con este proceso, pero ¿qué pasa si después de escanear los datos de la tabla original, los datos antiguos cambian durante el proceso de inserción en la nueva tabla?

Pensando en esto, establecimos un punto de interrupción en la línea 6839 de pt-archiver según el código fuente

Luego, hice las siguientes operaciones en varias ventanas de sesión

p>

Finalmente, pt-archiver genera los siguientes resultados:

# Hay una actualización de software disponible:

RECUENTO DEL TIEMPO TRANSCURRIDO

2020- 04- 08T09:13:21 0 0

2020-04-08T09:13:21 0 1

Comenzó en 2020-04-08T09:13:21, finalizó en 2020- 04- 08T09:13:51

Fuente: A=utf8mb4,D=xucl,P=3306,h=127.0.0.1,p=...,t=t1,u=xucl

Dest: A=utf8mb4,D=xucl_archive,P=3306,h=127.0.0.1,p=...,t=t1,u=xucl

SELECCIONAR 1

INSERT 1

ELIMINAR 1

Porcentaje de tiempo de recuento de acciones

dormir 1 30,0002 99,89

insertar 1 0,0213 0,07

commit 2 0.0080 0.03

seleccionar 2 0.0017 0.01

eliminar 1 0.0005 0.00

otro 0 0.0008 0.00

Obviamente, id =3 registros no se archivan (cambiamos la columna de condición aquí).

Cambiamos la columna condicional aquí, pero en producción es posible que hayamos cambiado otras columnas, lo que provocó que se archivaran datos inexactos)

Entonces, ¿cómo solucionamos esta situación?

Obviamente, la base de datos puede evitar que otros programas modifiquen los datos correspondientes agregando un bloqueo exclusivo en la base de datos. pt-archiver realmente nos ha ayudado a considerar esta situación. pt-archiver proporciona dos opciones

--for-update: agregue el modificador FOR UPDATE a la instrucción SELECT

--share-. lock: agregue el modificador LOCK IN SHARE MODE a la instrucción SELECT

Cuarto resumen

Como herramienta de archivo, pt-archiver es sin duda la operación y el mantenimiento diario de MySQL DBA. poderosas herramientas en . En el proceso de uso de pt-archiver, debe dominar el método de uso de acuerdo con los principios de pt-archiver

Durante el proceso de archivado, es mejor bloquear los registros del archivo para evitar datos de archivo inexactos

En un entorno maestro-esclavo, es mejor controlar la velocidad de archivado para evitar causar retrasos entre maestro-esclavo

Intente controlar el tamaño del bloque y no los haga demasiado grandes. El tamaño del bloque no debe ser demasiado grande para evitar provocar grandes transacciones