Hacer un volcado y restaurar de la manera descrita significará que MySQL tiene que reconstruir completamente los índices a medida que se importan los datos. También tiene que analizar los datos cada vez.
Sería mucho más eficiente si pudiera copiar archivos de datos en un formato que MySQL ya comprenda. Una buena manera de hacerlo es usar innobackupex de Percona
(Código abierto y distribuido como parte de XtraBackup disponible para descargar desde aquí).
Esto tomará una instantánea de las tablas MyISAM y, para las tablas InnoDB, copiará los archivos subyacentes y luego reproducirá el registro de transacciones contra ellos para garantizar un estado coherente. Puede hacer esto desde un servidor en vivo sin tiempo de inactividad (¿no tengo idea si ese es un requisito tuyo?)
Le sugiero que lea la documentación, pero para hacer una copia de seguridad en su forma más simple, use:
$ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
$ innobackupex --apply-log /path/to/BACKUP-DIR/
Si los datos están en la misma máquina, entonces innobackupex incluso tiene un comando de restauración simple:
$ innobackupex --copy-back /path/to/BACKUP-DIR
Hay muchas más opciones y diferentes formas de hacer la copia de seguridad, por lo que le recomiendo que lea bien la documentación antes de comenzar.
Como referencia a la velocidad, nuestro servidor de prueba lento, que realiza aproximadamente 600 IOPS, puede restaurar una copia de seguridad de 500 GB en aproximadamente 4 horas con este método.
Por último:mencionó lo que se podría hacer para acelerar la importación. Dependerá principalmente de cuál sea el cuello de botella. Por lo general, las operaciones de importación están vinculadas a E/S (puede probar esto comprobando las esperas de E/S) y la forma de acelerarlo es con un rendimiento de disco más rápido, ya sea discos más rápidos o más de ellos al unísono.
Asegúrese de aumentar su "max_allowed_packet " variable a un tamaño lo suficientemente grande. Esto realmente ayudará si tiene muchos datos de texto. El uso de hardware de alto rendimiento seguramente mejorará la velocidad de importación de datos.
mysql --max_allowed_packet=256M -u root -p < "database-file.sql"
Una cosa que puedes hacer es
SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0
Y también puedes jugar con los valores
innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_flush_method
en my.cnf para ponerte en marcha, pero en general, también deberías echar un vistazo al resto de parámetros de innodb para ver cuál te conviene más.
Este es un problema que he tenido en el pasado y siento que no lo he abordado por completo, pero espero haberme apuntado en esta dirección desde el principio. Me habría ahorrado bastante tiempo.
Faltan muchos parámetros para comprender completamente el motivo del problema. tales como:
- Versión MySQL
- Tipo de disco y velocidad
- Memoria libre en el servidor antes de iniciar el servidor MySQL
- Salida de iostat antes y en el momento de mysqldump.
- ¿Cuáles son los parámetros que utiliza para crear el archivo de volcado en primer lugar?
y muchos más.
Así que trataré de adivinar que su problema está en los discos porque tengo 150 instancias de MySQL que administro con 3 TB de datos en una de ellas y, por lo general, el problema es el disco
Ahora a la solución:
En primer lugar, su MySQL no está configurado para obtener el mejor rendimiento.
Puede leer acerca de las configuraciones más importantes para configurar en la publicación del blog de Percona:http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/
Verifique especialmente los parámetros:
innodb_buffer_pool_size
innodb_flush_log_at_trx_commit
innodb_flush_method
Si su problema es el disco, leer el archivo desde la misma unidad está empeorando el problema.
Y si su servidor MySQL comienza a cambiar porque no tiene suficiente RAM disponible, su problema se vuelve aún mayor.
Debe ejecutar diagnósticos en su máquina antes y en el momento del procedimiento de restauración para averiguarlo.
Además, puedo sugerirle que use otra técnica para realizar la tarea de reconstrucción, que funciona más rápido que mysqldump.
Es Percona Xtrabackup - http://www.percona.com/doc/percona-xtrabackup/2.2/
Tendrá que crear la copia de seguridad con él y restaurar desde él, o reconstruir desde el servidor en ejecución directamente con la opción de transmisión.
Además, la versión de MySQL a partir de 5.5:InnoDB funciona más rápido que MyISAM. Considere cambiar todas sus tablas a él.