¿Cómo puedo optimizar un mysqldump de una gran base de datos?

Por fin luego de mucho trabajar ya encontramos el arreglo de este atascamiento que agunos lectores de nuestro sitio web han presentado. Si deseas compartir alguna información no dejes de aportar tu información.

Solución:

El principal cuello de botella en el volcado como este es la E / S de la unidad. Estás leyendo una gran cantidad de datos y escribiéndolos de nuevo. Puede acelerar esto de varias formas:

• Asegúrese de que su salida vaya a una (s) unidad (es) diferente (s) a la (s) en que están almacenados los archivos de la base de datos; esto hará una gran diferencia con los discos giratorios, ya que los cabezales de las unidades no se moverán constantemente entre la ubicación que se está leyendo. y la ubicación a la que se está escribiendo.
• La salida de mysqldump será muy comprimible, por lo que si no puede separar la salida de la entrada como se mencionó anteriormente, canalice la salida a través de gzip o similar. Esto reducirá la cantidad de escritura que se está haciendo (por lo tanto, reduzca la carga de E / S total y la cantidad de movimiento de la cabeza) a expensas de algo de tiempo de CPU (que de todos modos puede tener mucho de sobra en estos momentos).
• Además, (también o en lugar de la compresión) pase la salida a través de una utilidad de tubería (como pv) que admita grandes búferes de escritura para agrupar más bloques escritos en las unidades, nuevamente para reducir el efecto de la latencia del movimiento de la cabeza; esto hará una gran diferencia si se usa el --quick opción para reducir el impacto de la RAM al realizar copias de seguridad de tablas grandes).
• Solo ejecute su proceso de copia de seguridad cuando la carga de E / S sea baja.

Sin embargo, es posible que esté solucionando el problema incorrecto: podría ser más fácil abordar las caídas de conexión (aunque reducir la carga de E / S impuesta por sus copias de seguridad ayudará a reducir el efecto que tiene en otros usuarios, por lo que vale la pena intentarlo de todos modos). ¿Podría ejecutar sus copias de seguridad manuales a través de la pantalla (o herramientas similares como tmux)? De esa forma, si su conexión con el servidor se interrumpe, puede volver a conectarse y volver a conectarse al screen sesión sin que se interrumpa ningún proceso.

Si está enviando los datos directamente a través de la conexión (es decir, está ejecutando mysqldump en su máquina local contra una base de datos remota, por lo que el volcado aparece localmente), es mejor que ejecute el volcado en el servidor primero, comprimiendo según sea necesario y luego transfiriendo los datos a través de la red utilizando una herramienta (como rsync) que admite transferencias parciales para que pueda reanudar la transferencia (en lugar de reiniciar) si una caída de conexión la interrumpe.

Como parte de su “reducción del tamaño de la base de datos general para resolver este problema”, supongo que una gran parte de sus datos no cambia. Es posible que pueda mover una gran parte de los 1,2 Gb de esa tabla principal a otra y eliminarla de las que copia el mysqldump llama. No es necesario hacer una copia de seguridad de estos datos cada vez que nunca cambien. La división de datos entre tablas y bases de datos de esta manera generalmente se conoce como partición de datos y también puede permitirle distribuir los datos y la carga de E / S en varias unidades. La base de datos de alta gama tiene soporte integrado para particiones automáticas, aunque en mysql probablemente tendrá que hacerlo manualmente y alterar su capa de acceso a datos para tener en cuenta.

Desviarse del tema de este sitio (por lo que probablemente debería pasar a ServerFault o SuperUser para preguntar si necesita más detalles): si parece que está perdiendo conexiones debido a la inactividad, verifique las opciones en su servidor SSH y cliente SSH para hacer Asegúrese de que los paquetes Keep-Alive estén habilitados y se envíen con la suficiente frecuencia. Si ve caídas incluso si la conexión está activa, también puede intentar usar OpenVPN o similar para ajustar la conexión; debería manejar una caída corta, incluso una caída completa si toda su conexión está caida durante unos segundos, de modo que el cliente SSH y el servidor no se da cuenta.

VISTAZO A HACER BACKUPS CON mysqldump

En mi humilde opinión, hacer copias de seguridad se ha convertido en una forma de arte si sabes cómo abordarlo.

Tienes opciones

Opción 1: mysqldump una instancia completa de mysql

Esta es la más fácil, la obviedad !!!

mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz

Todo escrito en un archivo: estructuras de tablas, índices, disparadores, procedimientos almacenados, usuarios, contraseñas cifradas. Otras opciones de mysqldump también pueden exportar diferentes estilos de comandos INSERT, archivo de registro y coordenadas de posición de registros binarios, opciones de creación de base de datos, datos parciales (opción –where), etc.

Opción 2: mysqldump separa las bases de datos en archivos de datos separados

Comience creando una lista de bases de datos (2 técnicas para hacer esto)

Técnica 1

mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

Técnica 2

mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

La técnica 1 es la forma más rápida. La técnica 2 es la más segura y la más segura. La técnica 2 es mejor porque, a veces, los usuarios crean carpetas para propósitos generales en / var / lib / mysql (datadir) que no están relacionadas con la base de datos. Information_schema registraría la carpeta como una base de datos en la tabla information_schema.schemata. La técnica 2 evitaría las carpetas que no contienen datos mysql.

Una vez que haya compilado la lista de bases de datos, puede proceder a recorrer la lista y realizar mysqldump, incluso en paralelo si así lo desea.

for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers $DB | gzip > $DB.sql.gz &
done
wait

Si hay demasiadas bases de datos para iniciar a la vez, bótelas en paralelo 10 a la vez:

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers $DB | gzip > $DB.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ $COMMIT_COUNT -eq $COMMIT_LIMIT ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ $COMMIT_COUNT -gt 0 ]
then
    wait
fi

Opción 3: tablas separadas de mysqldump en archivos de datos separados

Empiece por crear una lista de tablas

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt

Luego, volca todas las tablas en grupos de 10

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat ListOfTables.txt`
do
    DB=`echo $DBTB | sed 's/./ /g' | awk 'print $1'`
    TB=`echo $DBTB | sed 's/./ /g' | awk 'print $2'`
    mysqldump -h... -u... -p... --hex-blob --triggers $DB $TB | gzip > $DB_$TB.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ $COMMIT_COUNT -eq $COMMIT_LIMIT ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ $COMMIT_COUNT -gt 0 ]
then
    wait
fi

Opción 4: UTILIZA TU IMAGINACIÓN

Pruebe variaciones de las opciones mencionadas anteriormente más técnicas para obtener instantáneas limpias

Ejemplos de

1. Ordene la lista de tablas por el tamaño de cada tabla de forma ascendente o descendente.
2. Usando un proceso separado, ejecute “FLUSH TABLES WITH READ LOCK; SELECT SLEEP (86400)” antes de iniciar mysqldumps. Elimine este proceso después de que se complete mysqldumps. Esto es útil si una base de datos contiene InnoDB y MyISAM
3. Guarde los mysqldumps en carpetas con fecha y gire las carpetas de respaldo antiguas.
4. Cargue mysqldumps de instancia completa en servidores independientes.

CONSIDERACIÓN

Solo la Opción 1 trae todo. El inconveniente es que mysqldumps creado de esta manera solo se puede volver a cargar en la misma versión majot de mysql en la que se generó mysqldump. En otras palabras, un mysqldump de una base de datos MySQL 5.0 no se puede cargar en 5.1 o 5.5. La razón ? El esquema de mysql es totalmente diferente entre las principales versiones.

Las opciones 2 y 3 no incluyen guardar nombres de usuario y contraseñas.

Esta es la forma genérica de volcar SQL Grants para usuarios que se puede leer y es más portátil

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

La opción 3 no guarda los procedimientos almacenados, por lo que puede hacer lo siguiente

mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql &

Otro punto que debe tenerse en cuenta es el relativo a InnoDB. Si tiene un gran grupo de búfer InnoDB, tiene sentido vaciarlo lo mejor que pueda antes de realizar cualquier copia de seguridad. De lo contrario, MySQL pasa el tiempo limpiando tablas con páginas sucias sobrantes del grupo de búfer. Esto es lo que sugiero:

Aproximadamente 1 hora antes de realizar la copia de seguridad, ejecute este comando SQL

SET GLOBAL innodb_max_dirty_pages_pct = 0;

En MySQL 5.5, innodb_max_dirty_pages_pct predeterminado es 75. En MySQL 5.1 y versiones posteriores, innodb_max_dirty_pages_pct predeterminado es 90. Al establecer innodb_max_dirty_pages_pct en 0, esto acelerará el vaciado de páginas sucias al disco. Esto evitará o al menos reducirá el impacto de limpiar cualquier confirmación incompleta de dos fases de datos InnoDB antes de realizar cualquier mysqldump contra cualquier tabla InnoDB.

PALABRA FINAL SOBRE mysqldump

La mayoría de la gente se aleja de mysqldump en favor de otras herramientas y esas herramientas son realmente buenas.

Tales herramientas incluyen

1. MAATKIT (scripts de descarga / restauración paralelos, de Percona [Deprecated but great])
2. XtraBackup (copia de seguridad de instantáneas de TopNotch de Percona)
3. CDP R1Soft (opción de módulo MySQL que toma instantáneas de un momento determinado)
4. MySQL Enterprise Backup (anteriormente InnoDB Hot Backups [commercial])

Si tiene el espíritu de un verdadero DBA de MySQL, puede adoptar mysqldump y dominarlo por completo. Que todas sus copias de seguridad sean un reflejo de sus habilidades como DBA de MySQL.

Plan A: Ver también Xtrabackup de Percona. Esto permite la copia de seguridad en línea de InnoDB, sin ningún bloqueo significativo.

Plan B: un esclavo puede detenerse y puede realizar una copia de seguridad consistente por cualquiera de varios medios (copiar archivos, mysqldump, xtrabackup, etc.)

Plan C: Instantánea de LVM. Después de una configuración críptica, el tiempo de inactividad de una copia de seguridad es de menos de un minuto, independientemente del tamaño de la base de datos. Detiene mysqld, realiza la instantánea, reinicia mysqld y luego copia la instantánea. El último paso puede llevar mucho tiempo, pero MySQL no está inactivo.

Plan D: Instantánea de un esclavo: cero tiempo de inactividad.

valoraciones y comentarios

Recuerda algo, que puedes decir si te fue de ayuda.

---

---

Utiliza Nuestro Buscador