Solución de problemas comunes de migración de MySQL y errores de transferencia de datos

Las migraciones de MySQL fallan de algunas maneras familiares. La importación se detiene en una clave foránea. Los caracteres se convierten en signos de interrogación. Un volcado de MySQL 5.7 no se carga limpiamente en MySQL 8.0. Los datos se cargan, pero la aplicación se rompe porque una rutina almacenada, un disparador, un usuario o un modo SQL no se transfirieron como esperabas. Ninguno de estos problemas es inusual, pero son mucho más fáciles de manejar cuando tratas la migración como un proceso repetible en lugar de una copia única.

El mejor hábito de migración es ensayar. Toma una copia de seguridad real, restáurala en un destino de prueba, ejecuta los mismos comandos de importación que planeas usar en producción y anota cada advertencia. Un ensayo te dice si tu volcado está completo, si tu configuración de destino es compatible y cuánto tiempo toma realmente la carga. También te da un plan de reversión que es más realista que "lo resolveremos durante la ventana de mantenimiento".

Comienza identificando el tipo de fallo

Cuando una migración se rompe, no empieces a cambiar variables del servidor al azar. Coloca el error en uno de estos cubos:

Compatibilidad: diferencias de versión, palabras reservadas, características eliminadas, valores predeterminados cambiados.
Codificación: desajustes de conjunto de caracteres y cotejamiento.
Restricciones: claves foráneas, claves únicas, restricciones de verificación, columnas generadas.
Cobertura de objetos: disparadores, rutinas, eventos, vistas, usuarios o permisos faltantes.
Rendimiento: la importación es demasiado lenta, el disco está lleno, los registros binarios están creciendo, los índices tardan demasiado.
Comportamiento de la aplicación: datos importados, pero las consultas o escrituras se comportan de manera diferente.

Esa clasificación te da el siguiente comando a ejecutar. Un error de clave duplicada y un emoji corrupto son ambos "problemas de migración", pero tienen causas completamente diferentes.

Desajustes de versión: MySQL 5.7 a 8.0 y saltos similares

Las actualizaciones de versión principal son donde aparecen muchas sorpresas. MySQL 8.0 cambió los valores predeterminados, las palabras reservadas, el comportamiento de autenticación, los internos del diccionario de datos y el comportamiento del optimizador en comparación con 5.7. Algo de sintaxis antigua todavía funciona; algo no. MariaDB agrega otra capa de compatibilidad porque no es un reemplazo directo para cada característica de MySQL.

Antes de la migración, captura la configuración de origen:

SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

En el destino, ejecuta las mismas comprobaciones y compara. sql_mode merece atención especial. Un volcado que se carga en un origen permisivo puede fallar en un destino más estricto con errores como fechas inválidas, valores predeterminados faltantes para columnas NOT NULL o fechas cero que ya no se aceptan bajo el modo de destino.

Si te encuentras con un error como:

ERROR 1067 (42000): Invalid default value for 'created_at'

no relajes sql_mode para siempre de inmediato. Primero inspecciona la definición de la tabla y los datos. Puede que necesites corregir valores predeterminados, convertir fechas cero o actualizar suposiciones de la aplicación. Igualar temporalmente el sql_mode de origen durante la importación puede ayudarte a completar una restauración por etapas, pero la producción debe moverse hacia un modo conocido y explícito contra el cual tu aplicación haya sido probada.

Las palabras reservadas también pueden romper esquemas antiguos. Una columna o tabla llamada rank, groups u otra palabra reservada más nueva puede necesitar entrecomillado o renombramiento. Si el error aparece en DDL, inspecciona la declaración exacta del volcado y prueba una versión corregida en el destino.

Problemas con el plugin de autenticación

Una migración que incluye un cambio de aplicación a menudo falla antes de que se ejecute cualquier consulta porque los clientes no pueden autenticarse. MySQL 8.0 comúnmente usa caching_sha2_password de forma predeterminada, mientras que los clientes más antiguos pueden esperar mysql_native_password.

Verifica los usuarios de destino:

SELECT user, host, plugin FROM mysql.user;

La mejor solución suele ser actualizar la biblioteca del cliente o el controlador. Si eso no es posible antes del cambio, es posible que necesites una cuenta de compatibilidad temporal:

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_secret';

Trata esto como una decisión de compatibilidad, no como una mejor práctica genérica. La configuración de autenticación afecta la seguridad, y la respuesta correcta depende de las versiones de tu cliente y del modelo de riesgo.

Problemas de conjunto de caracteres y cotejamiento

Los problemas de conjunto de caracteres son dolorosos porque la importación puede terminar con éxito mientras los datos ya están dañados. El síntoma clásico es ?, mojibake, caracteres acentuados rotos o inserciones fallidas que involucran emojis.

Verifica las definiciones de la base de datos y tablas de origen:

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = 'appdb';

Verifica también las columnas:

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'appdb'
  AND character_set_name IS NOT NULL;

Para la mayoría de las aplicaciones modernas, utf8mb4 es el conjunto de caracteres de destino correcto porque admite todo el rango Unicode, incluidos los emojis. El nombre utf8 más antiguo de MySQL no es lo mismo que UTF-8 completo en versiones anteriores; comúnmente es un conjunto de caracteres de 3 bytes.

Al volcar e importar, sé explícito:

mysqldump --default-character-set=utf8mb4 -u user -p appdb > appdb.sql
mysql --default-character-set=utf8mb4 -u user -p appdb < appdb.sql

Si los datos de origen son realmente latin1, no los declares ciegamente como utf8mb4 y esperes lo mejor. Primero determina si los bytes son válidos en la codificación de origen. Algunos sistemas antiguos contienen datos "doble codificados" donde la columna afirma un conjunto de caracteres pero la aplicación almacenó bytes de otro. Eso necesita una conversión probada, no una búsqueda y reemplazo global.

Las diferencias de cotejamiento también pueden cambiar el comportamiento. El orden de clasificación, las comparaciones de unicidad y la sensibilidad a mayúsculas y minúsculas pueden diferir entre cotejamientos. Si un índice único falla durante la migración, verifica si el cotejamiento de destino trata dos cadenas como iguales cuando el origen no lo hacía.

Fallos de clave foránea

Los errores de clave foránea generalmente significan una de cuatro cosas:

Las tablas secundarias se importaron antes que las tablas principales.
El volcado es parcial y faltan filas referenciadas.
Los datos de origen ya tenían referencias inconsistentes.
El esquema de destino difiere del origen.

La solución común para carga masiva es:

SET FOREIGN_KEY_CHECKS = 0;
-- importar datos
SET FOREIGN_KEY_CHECKS = 1;

Esto puede ser apropiado para una restauración lógica completa desde un volcado confiable. No es una herramienta de limpieza. Volver a habilitar FOREIGN_KEY_CHECKS no revalida completamente cada fila existente de la manera que muchos asumen, por lo que puedes importar relaciones incorrectas y no notarlo hasta más tarde.

Si estás fusionando datos o importando solo una parte de un esquema, mantén las comprobaciones habilitadas cuando sea posible y carga las tablas principales primero. Si debes deshabilitar las comprobaciones, ejecuta consultas de validación después. Por ejemplo:

SELECT c.*
FROM orders c
LEFT JOIN customers p ON p.id = c.customer_id
WHERE c.customer_id IS NOT NULL
  AND p.id IS NULL
LIMIT 20;

Usa consultas como esa para tus relaciones reales, especialmente tablas de alto valor como pedidos, pagos, cuentas y permisos.

Errores de clave duplicada

Un error de clave duplicada significa que el destino ya tiene un valor que los datos entrantes quieren insertar:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

Si el destino debe ser una copia exacta, la solución limpia suele ser eliminar y recrear la base de datos de destino, luego importar de nuevo. Un destino medio cargado no es un buen punto de partida para un segundo intento a menos que tu proceso esté diseñado para reanudarse.

Si estás fusionando datos, decide la política de conflictos antes de la importación. INSERT IGNORE oculta duplicados saltando filas. REPLACE INTO elimina la fila existente e inserta la nueva, lo que puede activar cascadas y cambiar columnas de actualización automática. ON DUPLICATE KEY UPDATE es más explícito, pero aún necesita reglas cuidadosas.

Para migraciones, prefiero tablas de staging para fusiones. Carga los datos entrantes en tablas staging_*, inspecciona los conflictos, luego escribe declaraciones deliberadas INSERT ... SELECT o UPDATE ... JOIN. Es más lento de diseñar, pero evita descartar datos silenciosamente.

Disparadores, rutinas, eventos y vistas faltantes

Una migración puede parecer exitosa porque existen tablas y filas, mientras que falta lógica importante de la base de datos. Las opciones de mysqldump importan:

mysqldump -u user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  appdb > appdb.sql

Las vistas y rutinas pueden fallar en la importación debido a cuentas de definidor. Una vista puede hacer referencia a:

DEFINER=`old_user`@`old_host`

Si esa cuenta no existe en el destino, el objeto puede fallar al crearse o fallar cuando se use. Puedes crear la cuenta de definidor necesaria con privilegios apropiados, o ajustar los definidores durante un proceso de migración controlado. No elimines definidores ciegamente sin entender el modelo de seguridad de la aplicación.

Después de la importación, compara los recuentos de objetos:

SELECT ROUTINE_TYPE, COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'appdb'
GROUP BY ROUTINE_TYPE;

SELECT TRIGGER_SCHEMA, COUNT(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'appdb'
GROUP BY TRIGGER_SCHEMA;

También verifica los eventos programados si tu aplicación depende de ellos:

SHOW EVENTS FROM appdb;

Importaciones lentas y tablas grandes

Las importaciones grandes generalmente están limitadas por E/S de disco, mantenimiento de índices, registro binario, comprobaciones de claves foráneas o tamaño de transacción. Antes de ajustar, observa el destino:

iostat -xz 1
df -h
top

Para volcados lógicos, usa inserciones extendidas. mysqldump hace esto de forma predeterminada en la mayoría de los casos, pero confirma que no estás usando --skip-extended-insert a menos que necesites diferencias legibles por humanos más que velocidad.

Para importaciones de InnoDB, un innodb_buffer_pool_size más grande puede ayudar si el destino tiene memoria disponible. No lo configures tan alto que el SO comience a intercambiar. Durante una carga única, algunos equipos relajan temporalmente la configuración de durabilidad como innodb_flush_log_at_trx_commit o deshabilitan el registro binario para la sesión de importación. Esas opciones intercambian recuperación ante fallos o recuperación en un punto en el tiempo por velocidad, por lo que deben usarse solo cuando puedas permitirte reiniciar la importación desde una copia de seguridad conocida.

Si el destino también es una fuente de replicación, ten cuidado con los registros binarios. Deshabilitar el registro binario puede acelerar la importación, pero las réplicas posteriores no recibirán esos cambios. En una topología con réplicas, decide dónde debe ocurrir la importación y cómo deben fluir los cambios antes de desactivar los registros.

Para tablas muy grandes, considera herramientas de copia de seguridad física o las utilidades de volcado y carga de MySQL Shell en lugar de mysqldump simple. Los volcados lógicos son portátiles y fáciles de inspeccionar, pero no siempre son la ruta más rápida para conjuntos de datos de varios cientos de gigabytes.

Fallos de espacio en disco

Los fallos de disco durante la migración son comunes y evitables. Necesitas espacio para el archivo de volcado, los datos importados, los índices, los archivos temporales, los registros binarios y, a veces, el almacenamiento doble mientras se reconstruyen las tablas.

Verifica antes de la importación:

df -h
du -sh /var/lib/mysql

Dentro de MySQL, verifica los tamaños de las tablas:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_gb DESC;

Si la importación falla porque el disco se llena, no elimines archivos aleatorios del directorio de datos. Libera espacio de manera segura, inspecciona si el destino está parcialmente cargado y decide si reiniciar desde cero.

Validación posterior a la migración

Una migración no está terminada cuando el comando de importación sale. Valida el resultado.

Comienza con recuentos de filas para tablas importantes:

SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM payments;

Los recuentos de filas por sí solos no son suficientes. Compara sumas o verificaciones para datos críticos del negocio:

SELECT COUNT(*), SUM(total_amount), MIN(created_at), MAX(created_at)
FROM orders;

Ejecuta las mismas consultas en origen y destino durante el período de calma final. Para tablas que continúan cambiando durante la migración, usa una congelación planificada, puesta al día de replicación o reconciliación a nivel de aplicación.

Prueba los flujos de trabajo de la aplicación contra el destino antes del cambio:

Inicio de sesión y creación de sesión.
Creación y actualización de registros principales.
Búsquedas e informes que dependen de cotejamientos o índices.
Trabajos en segundo plano, disparadores y eventos programados.
Verificaciones de permisos y acciones de administración.

La prueba de la aplicación es importante porque la base de datos puede estar técnicamente importada pero ser incorrecta en el comportamiento.

Una lista de verificación práctica de triaje de migración

Cuando aparece un error de migración, usa esta secuencia:

Guarda el mensaje de error exacto y la declaración SQL fallida si está disponible.
Identifica el cubo: compatibilidad, codificación, restricción, cobertura de objetos, rendimiento o comportamiento de la aplicación.
Compara las versiones de MySQL de origen y destino, sql_mode, conjuntos de caracteres y cotejamientos.
Para errores de restricción, inspecciona las filas principales y secundarias específicas o las claves duplicadas.
Para problemas de codificación, detén la importación hasta que sepas si los bytes de origen son válidos y cómo los está interpretando la conexión del cliente.
Para importaciones lentas, verifica el disco, la memoria, los registros binarios y el mantenimiento de índices antes de cambiar variables aleatorias.
Después de una corrección, vuelve a ejecutar la migración en staging antes de aplicarla a producción.

La migración de MySQL más confiable es la que puedes desechar y repetir. Mantén los comandos en un script, mantén los cambios de configuración documentados y haz que la validación sea parte del plan en lugar de un vistazo final esperanzador.