mysql database backup innodb

¿Cuál es la forma más rápida de volcar y cargar una base de datos MySQL InnoDB usando mysqldump?



mysqldump mysql backup (5)

Tápelo directamente a otra instancia, para evitar la sobrecarga del disco. No se moleste en --compress menos que esté ejecutando una red lenta, ya que en una LAN rápida o en un bucle invertido la red no tiene importancia.

Me gustaría crear una copia de una base de datos con aproximadamente 40 tablas InnoDB y alrededor de 1.5GB de datos con mysqldump y MySQL 5.1.

¿Cuáles son los mejores parámetros (es decir: --single-transaction) que darán como resultado el volcado y carga más rápidos de los datos?

Además, al cargar los datos en el segundo DB, es más rápido:

1) canalice los resultados directamente a la segunda instancia del servidor MySQL y use la opción --compress

o

2) cargarlo desde un archivo de texto (es decir: mysql <my_sql_dump.sql)

Para innodb, --order-by-primary --extended-insert es generalmente el mejor combo. Si después de cada último bit de rendimiento y el cuadro de destino tiene muchos núcleos de CPU, es posible que desee dividir el archivo de volcado resultante y hacer inserciones paralelas en muchos subprocesos, hasta innodb_thread_concurrency / 2.

Además, modifique innodb_buffer_pool_size en el destino al máximo que puede permitirse, e incremente innodb_log_file_size a 128 o 256 MB (teniendo cuidado con esto, debe eliminar los viejos archivos de registro antes de reiniciar el daemon mysql, de lo contrario no se reiniciará)

Use la herramienta mk-parallel-dump de Maatkit.

Al menos eso sería probablemente más rápido. Confiaría en mysqldump más.

¿Con qué frecuencia estás haciendo esto? ¿Es realmente un problema de rendimiento de la aplicación? Tal vez deberías diseñar una forma de hacerlo que no necesite volcar toda la información (¿replicación?)

Por otro lado, 1.5G es una base de datos bastante pequeña, por lo que probablemente no sea un gran problema.

DEVOLUCIÓN RÁPIDA de una base de datos inactiva:

El uso de la opción "-T" con mysqldump genera muchos archivos .sql y .txt en el directorio especificado. Esto es ~ 50% más rápido para volcar tablas grandes que un solo archivo .sql con instrucciones INSERT (toma 1/3 menos de tiempo de reloj de pared).

Además, hay una gran ventaja al restaurar si puede cargar varias tablas en paralelo y saturar múltiples núcleos. En una caja de 8 núcleos, esto podría ser tanto como una diferencia de 8 veces en el tiempo del reloj de pared para restaurar el volcado, además de las mejoras de eficiencia proporcionadas por "-T". Debido a que "-T" hace que cada tabla se almacene en un archivo separado, cargarlas en paralelo es más fácil que dividir un archivo .sql masivo.

Llevando las estrategias anteriores a su extremo lógico, se podría crear una secuencia de comandos para volcar una base de datos ampliamente en paralelo. Bueno, eso es exactamente lo que son las herramientas Maakit mk-parallel-dump (ver http://www.maatkit.org/doc/mk-parallel-dump.html ) y mk-parallel-restore; scripts Perl que realizan múltiples llamadas al programa mysqldump subyacente. Sin embargo, cuando traté de usarlos, tuve problemas para completar la restauración sin duplicar los errores clave que no ocurrieron con los volcados vainilla, así que tenga en cuenta que su kilometraje puede variar.

Volcado de datos desde una base de datos EN VIVO (sin interrupción del servicio):

El modificador --single-transaction es muy útil para tomar un volcado de una base de datos en vivo sin tener que desactivarlo o tomar un volcado de una base de datos esclava sin tener que dejar de esclavizar.

Tristemente, -T no es compatible con --single transaction, por lo que solo obtienes uno.

Usualmente, tomar el botadero es mucho más rápido que restaurarlo. Todavía hay espacio para una herramienta que toma el archivo de volcado monolítico entrante y lo divide en varias piezas que se cargarán en paralelo. Que yo sepa, tal herramienta aún no existe.

La transferencia del volcado sobre la red suele ser un triunfo

Para escuchar un volcado entrante en una ejecución de host:

nc -l 7878 > mysql-dump.sql

Luego, en su host DB, ejecute

mysqldump $OPTS | nc myhost.mydomain.com 7878

Esto reduce la contención de que los husos de disco en el maestro escriban el volcado en el disco acelerando ligeramente su volcado (suponiendo que la red es lo suficientemente rápida como para mantenerse al día, una suposición bastante segura para dos hosts en el mismo centro de datos). Además, si está construyendo un nuevo esclavo, ahorra el paso de tener que transferir el archivo de volcado una vez que ha finalizado.

Advertencias: obviamente, debe tener suficiente ancho de banda de red para no ralentizar insoportablemente las cosas, y si la sesión TCP se rompe, debe comenzar de nuevo, pero para la mayoría de los vertederos esto no es una preocupación importante.

Por último, quiero aclarar un punto de confusión común.

A pesar de la frecuencia con la que ve estas banderas en los ejemplos y tutoriales de mysqldump, son superfluas porque están activadas de manera predeterminada:

  • --opt
  • --add-drop-table
  • --add-locks
  • --create-options
  • --disable-keys
  • --extended-insert
  • --lock-tables
  • --quick
  • --set-charset .

De http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html :

El uso de --opt es lo mismo que especificar --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, - rápido, y --set-charset. Todas las opciones que --opt significa también están activadas por defecto porque --opt está activado por defecto.

De esos comportamientos, "--quick" es uno de los más importantes (saltea el conjunto de resultados de caché en mysqld antes de transmitir la primera fila), y puede estar con "mysql" (que NO se enciende --ejecutar por defecto) acelerar drásticamente las consultas que devuelven un gran conjunto de resultados (por ejemplo, volcar todas las filas de una gran tabla).