Importación de datos en una instancia de base de datos MySQL
Puede usar varias técnicas diferentes para importar datos en una instancia de base de datos de RDS for MySQL. El mejor enfoque depende del origen de los datos, de la cantidad de datos y de si la importación se hace una vez o es continua. Si está migrando una aplicación junto con los datos, tenga en cuenta también la cantidad de tiempo de espera que desea experimentar.
Información general
En la tabla siguiente encontrará técnicas para importar datos en una instancia de base de datos de RDS for MySQL.
| Origen | Cantidad de datos | Una vez o continua | Tiempo de inactividad de las aplicaciones | Técnica | Más información |
|---|---|---|---|---|---|
|
Base de datos MySQL existente localmente o en Amazon EC2 |
Cualquiera |
Una vez |
Alguno |
Crear una copia de seguridad de una base de datos local, almacenarlo en Amazon S3 y luego restaurar el archivo de copia de seguridad en una nueva instancia de base de datos de Amazon RDS que ejecute MySQL. |
Restauración de una copia de seguridad en una instancia de base de datos MySQL. |
|
Cualquier base de datos existente |
Cualquiera |
Una vez o continua |
Mínima |
Utilizar AWS Database Migration Service para migrar la base de datos con un tiempo de inactividad mínimo y, para numerosos motores de base de datos, continuar las replicaciones en curso. |
Qué es AWS Database Migration Service y Uso de una base de datos compatible con MySQL como destino para AWS DMS en la Guía del usuario de AWS Database Migration Service |
|
Instancia de base de datos MySQL existente |
Cualquiera |
Una vez o continua |
Mínima |
Cree una réplica de lectura para la replicación continua. Promocione la réplica de lectura para la creación única de una nueva instancia de base de datos. |
Trabajo con réplicas de lectura de instancias de base de datos |
|
Base de datos de MySQL o MariaDB existente |
Pequeña |
Una vez |
Alguno |
Copie los datos directamente en la instancia de base de datos de MySQL utilizando una utilidad de línea de comandos. | |
|
Datos no almacenados en una base de datos existente |
Media |
Una vez |
Alguno |
Cree archivos sin formato e impórtelos utilizando instrucciones LOAD DATA LOCAL
INFILE de MySQL. |
Importación de datos de cualquier origen a una instancia de base de datos de MySQL o MariaDB |
|
Base de datos de MySQL o MariaDB existente en las instalaciones o en Amazon EC2 |
Cualquiera |
Continuo |
Mínima |
Configure la replicación con una base de datos de MariaDB o MySQL existente como origen de replicación. |
nota
La base de datos del sistema 'mysql' contiene la información de autenticación y autorización necesaria para iniciar sesión en la instancia de base de datos y obtener acceso a los datos. La eliminación, la modificación, el cambio de nombre o el truncamiento de tablas, datos u otros contenidos de la base de datos 'mysql' de la instancia de base de datos puede provocar un error e impedir el acceso a la instancia de base de datos y a los datos. En ese caso, puede restaurar la instancia de base de datos desde una instantánea usando el comando AWS CLI de la restore-db-instance-from-db-snapshot. Puede recuperar la instancia de base de datos utilizando el comando AWS CLI de la restore-db-instance-to-point-in-time.
Consideraciones sobre la importación de datos
A continuación, puede encontrar información técnica adicional sobre la carga de datos en MySQL. Esta información está dirigida a usuarios avanzados familiarizados con la arquitectura de servidor MySQL.
Registro binario
Las cargas de datos incurren en una penalización de desempeño y requieren más espacio en disco libre (hasta cuatro veces más) cuando está activado el registro binario que cuando está desactivado. El alcance de la penalización de desempeño y la cantidad de espacio en disco libre necesario es directamente proporcional al tamaño de las transacciones utilizadas para cargar los datos.
Tamaño de transacción
El tamaño de transacción tiene un efecto muy importante en las cargas de datos de MySQL. Supone una influencia decisiva en el consumo de recursos, la utilización de espacio en disco, el proceso de continuación, el tiempo de recuperación y el formato de la entrada (archivos sin formato o SQL). En esta sección se describe el modo en que el tamaño de transacción afecta al registro binario y se argumentan los beneficios de desactivar el registro binario durante la carga de grandes volúmenes de datos. Como se ha señalado, el registro binario se activa y desactiva estableciendo el periodo de retención de copia de seguridad automatizado de Amazon RDS. Un valor distinto de cero activa el registro binario y el valor cero lo desactiva. También se describe el impacto de las transacciones de gran tamaño en InnoDB y por qué es importante que los tamaños de transacción sean pequeños.
Transacciones pequeñas
En las transacciones pequeñas, el registro binario duplica el número de escrituras en disco requeridas para cargar los datos. Este efecto puede degradar el desempeño notablemente en otras sesiones de base de datos y aumentar el tiempo requerido para cargar los datos. La degradación experimentada depende en parte de la velocidad de carga, de la actividad restante de la base de datos durante la carga y la capacidad de la instancia de base de datos de Amazon RDS.
Los registros binarios también consumen un espacio en disco aproximadamente igual al volumen de datos cargado hasta el momento en que se hace una copia de seguridad de ellos y se retiran. Afortunadamente, Amazon RDS minimiza este impacto creando copias de seguridad de los registros binarios y retirándolos con frecuencia.
Transacciones grandes
Las transacciones de gran tamaño causan una penalización del triple de IOPS y de consumo de disco cuando el registro binario está activado. Esto se debe al volcado en el disco de la caché del registro binario, que consume espacio en disco y provoca una E/S adicional para cada escritura. La caché no puede escribirse en el registro binario hasta que la transacción se confirma o se revierte, por lo que consume un espacio en disco proporcional al volumen de datos cargado. Cuando se confirma la transacción, la caché debe copiarse en el registro binario, creando una tercera copia de los datos en el disco.
Por ello debe haber al menos el triple de espacio en disco libre disponible para cargar los datos en comparación con la carga cuando el registro binario está desactivado. Por ejemplo, 10 GiB de datos cargados con una transacción única consumen al menos 30 GiB de espacio en disco durante la carga. Consume 10 GiB para la tabla + 10 GiB para la caché de registro binaria + 10 GiB para el propio registro binario. El archivo de caché permanece en el disco hasta que la sesión que lo ha creado termina, o hasta que llena la caché de registro de nuevo a causa de otra transacción. El registro binario debe permanecer en el disco hasta que se haga una copia de seguridad, por lo que puede transcurrir cierto tiempo hasta que se liberen los 20 GiB de espacio adicionales.
Si los datos se cargan con LOAD DATA LOCAL INFILE y la base de datos debe recuperarse desde una copia de seguridad creada antes de la carga, se crea otra copia más. Durante la recuperación, MySQL extrae los datos desde el registro binario en un archivo sin formato. MySQL ejecuta a continuación LOAD DATA LOCAL INFILE, igual que en la transacción original. Sin embargo, esta vez el archivo de entrada es local en el servidor de base de datos. Continuando con el ejemplo anterior, la recuperación genera un error si no hay al menos 40 GiB de espacio libre en disco disponible.
Desactivación del registro binario
Siempre que sea posible, desactive el registro binario durante la carga de grandes volúmenes de datos para evitar el gasto adicional de recursos y los requisitos de espacio en disco añadidos. En Amazon RDS desactivar el registro binario es tan sencillo como configurar el valor cero para el periodo de retención de copia de seguridad. Si lo hace, le recomendamos que tome una instantánea de base de datos de la instancia de base de datos inmediatamente antes de la carga. Al hacerlo, puede deshacer de forma rápida y sencilla los cambios realizados durante la carga si lo necesita.
Después de la carga, vuelva a configurar un valor adecuado (distinto de cero) para el periodo de retención de copia de seguridad.
No es posible configurar el valor cero para el periodo de retención de copia de seguridad cuando la instancia de base de datos es un origen de réplicas de lectura.
InnoDB
La información de esta sección argumenta de forma sólida la necesidad de que los tamaños de transacción sean pequeños cuando se usa InnoDB.
Deshacer
InnoDB genera registros para deshacer con el fin de hacer posibles funciones como la reversión de transacciones y MVCC. Los registros para deshacer se almacenan en el espacio de tablas del sistema InnoDB (normalmente ibdata1) y se conservan hasta que el subproceso de purga los elimina. El subproceso de purga no puede ir más allá del registro para deshacer correspondiente a la transacción activa más antigua, por lo que queda bloqueado hasta que la transacción se confirma o se revierte por completo. Si la base de datos procesa otras transacciones durante la carga, sus registros para deshacer también se acumulan en el espacio de tablas del sistema y no pueden eliminarse incluso cuando se hayan confirmado y ninguna otra transacción los requiera para MVCC. En esta situación, todas las transacciones (incluidas las de solo lectura) con acceso a cualquiera de las filas modificadas por cualquier transacción (y no solo la de carga) se vuelven más lentas. La ralentización se debe a que tienen que comprobar los registros para deshacer que podrían haberse purgado si no fuera por lo prolongado de la transacción de carga en curso.
Los registros para deshacer se almacenan en el espacio de tabla del sistema y dicho espacio de tabla del sistema no se reduce de tamaño nunca. De este modo, las transacciones de carga de gran tamaño pueden provocar que el espacio de tablas del sistema sea muy grande, consumiendo espacio en disco que no puede recuperar sin volver a crear la base de datos desde cero.
Reversión
InnoDB está optimizado para las confirmaciones. La reversión de una transacción de gran tamaño puede requerir mucho tiempo. En algunos casos podría resultar más rápida una recuperación a un momento dado o la restauración de una instantánea de base de datos.
Formato de los datos de entrada
MySQL admite datos de entrada en dos formas: archivos sin formato y SQL. En esta sección se señalan algunos de los principales beneficios e inconvenientes de cada método.
Archivos sin formato
La carga de archivos sin formato con LOAD DATA LOCAL INFILE puede ser el método de carga de datos más rápido y menos costoso, siempre que el tamaño de las transacciones sea relativamente pequeño. Comparados con la carga de los mismos datos con SQL, los archivos sin formato suelen requerir menos tráfico de red, lo que reduce los costos de transmisión, y se cargan mucho más rápido debido al menor esfuerzo de procesamiento en la base de datos.
Transacción única de gran tamaño
LOAD DATA LOCAL INFILE carga todo el archivo sin formato en una sola transacción. Esto no es necesariamente malo. Si se puede conseguir que el tamaño de los archivos individuales sea pequeño, este método presenta ciertos beneficios:
Posibilidad de reanudación: es fácil hacer un seguimiento de los archivos que se han cargado. Si surge un problema durante la carga, puede continuar donde se detuvo sin mucho esfuerzo. Podría ser necesario volver a transmitir algunos datos a Amazon RDS pero si los archivos son pequeños el volumen será mínimo.
Carga de datos en paralelo: si dispone de IOPS y ancho de banda adicionales para la carga de un archivo, la carga en paralelo podría ahorrar tiempo.
Limitación de la velocidad de carga: ¿está afectando la carga de los datos negativamente a otros procesos? puede limitarla aumentando el intervalo entre los archivos.
Cuidado
Los beneficios de LOAD DATA LOCAL INFILE desaparecen rápidamente al aumentar el tamaño de la transacción. Si dividir un conjunto de datos grande en conjuntos más pequeños no es una opción, SQL podría ser el método más recomendable.
SQL
SQL tiene un beneficio fundamental con respecto a los archivos sin formato: facilita que los tamaños de transacción sean pequeños. Sin embargo, SQL puede tardar mucho más en cargar que los archivos sin formato, y puede ser difícil determinar dónde continuar la carga si se produce un error. Por ejemplo, los archivos mysqldump no pueden reiniciarse. Si se produce un error durante la carga de un archivo mysqldump, es necesario modificarlo o sustituirlo para poder continuar. La alternativa consiste en restaurar a un momento anterior a la carga y volver a procesar el archivo una vez corregida la causa del error.
Puntos de comprobación con instantáneas de Amazon RDS
Si una carga va a tardar varias horas o incluso días, ejecutarla sin registro binario no es una perspectiva muy tentadora, salvo que se disponga de puntos de comprobación periódicos. Y aquí la característica de instantánea de base de datos de Amazon RDS resulta muy práctica. Una instantánea de base de datos crea una copia coherente de una instancia de base de datos en un momento dado que puede utilizarse para restaurar su estado en ese momento si después se produce un error o situación similar.
Para crear un punto de comprobación, basta con obtener una instantánea de base de datos. Las instantáneas de base de datos obtenidas anteriormente como puntos de comprobación pueden eliminarse sin que ello afecte a la durabilidad ni al tiempo de restauración.
Además, las instantáneas son rápidas, por lo que una mayor frecuencia de los puntos de comprobación no afecta significativamente al tiempo de carga.
Reducción del tiempo de carga
Las siguientes son algunas sugerencias adicionales para reducir los tiempos de carga:
Cree todos los índices secundarios antes de la carga. Esto puede parecer poco intuitivo para quienes estén familiarizados con otras bases de datos. La adición o modificación de un índice secundario hace que MySQL cree una nueva tabla con los cambios del índice, copie los datos de la tabla anterior a la nueva y elimine la tabla original.
Cargue los datos en el orden de la clave primaria. Esto es especialmente útil para las tablas de InnoDB, donde los tiempos de carga pueden reducirse en un 75-80% y el tamaño del archivo de datos puede caer a la mitad.
Desactive las restricciones de claves externas con foreign_key_checks=0. Para los archivos sin formato con LOAD DATA LOCAL INFILE, esto es obligatorio en muchos casos. Sea cual sea al carga, desactivando las comprobaciones de claves externas se consigue una mejora significativa del desempeño. Solo tiene que asegurarse de volver a activar las restricciones y comprobar los datos después de la carga.
Efectúe la carga en paralelo, salvo que se aproxime al límite de los recursos. Utilice tablas particionadas donde resulte oportuno.
Utilice inserciones de varios valores al cargar con SQL para minimizar la sobrecarga al ejecutar instrucciones. Cuando se usa mysqldump, esto se hace automáticamente.
Reduzca la E/S de registro de InnoDB con innodb_flush_log_at_trx_commit=0
Si va a cargar datos en una instancia de base de datos que no tiene réplicas de lectura, establezca el parámetro sync_binlog en 0 mientras se cargan los datos. Cuando se complete la carga de datos, vuelva a establecer el parámetro sync_binlog en 1.
Cargue los datos antes de convertir la instancia de base de datos en una implementación Multi-AZ. Sin embargo, si la instancia de base de datos ya usa una implementación Multi-AZ, el cambio a una implementación Single-AZ para la carga de datos no se recomienda porque solo proporciona pequeñas mejoras.
nota
El valor innodb_flush_log_at_trx_commit=0 hace que InnoDB vacíe sus registros cada segundo, en lugar de hacerlo con cada confirmación. Esto supone una mejora significativa de la velocidad, pero puede provocar pérdidas de datos en caso de error. Utilice esta opción con precaución.
Temas
- Restauración de una copia de seguridad en una instancia de base de datos MySQL.
- Importación de datos de una base de datos de MariaDB o MySQL externa a una instancia de base de datos de RDS para MariaDB o RDS para MySQL
- Importación de datos a una base de datos de Amazon RDS MariaDB o MySQL con un tiempo de inactividad reducido
- Importación de datos de cualquier origen a una instancia de base de datos de MySQL o MariaDB
