Backups en MySql desde el terminal con mysqldump

Estas son las opciones de backup que vamos a ver:

• Backup de una sola base de datos
• Backup de tablas concretas
• Backup de todas las bases de datos
• Backup de todas las bases de datos menos las de sistema
• Backup de múltiples bases de datos seleccionadas
• Backup solo de la estructura de la base de datos (sin datos)
• Backup solo de los datos (sin la estructura)
• Backup con compresión
• Backup excluyendo ciertas tablas
• Backup de base de datos con datos en formato CSV
• Backup con especificación de un conjunto de caracteres
• Backup de una base de datos con procedimientos almacenados, funciones y triggers
• Backup limitando y filtrando el número de registro de una tabla
• Backup dividiendo en multiples archivos
• Backup sin bloquear tablas

Antes de nada recordar que la base de la instrucción mysqldump es la siguiente

mysqldump -u usuario -p ... > /ruta/fichero.sql

También podemos poner la IP / nombre del servidor con el parámetro -h

mysqldump -h 127.0.0.1 -u usuario -p ...

Y el password, cuando automatizamos los backups lo podemos poner seguido del parámetro -p

mysqldump -u usuario -pContraseña ...

Después de los tres puntos van las opciones que queramos poner dependiendo del tipo de backup que queramos realizar.

Hay opciones, aunque aquí no lo comento al detalle que se pueden combinar entre ellas, por ejemplo, realizar backups de multiples tablas a csv y comprimirlo o con un juego de caracteres específico ... pero esto ya os lo dejo un poco mas a vosotros.

Y por último, comentar que los backups que se programen vía cron, supervisor, etc ... tienen que llevar la ruta completa donde se quiere dejar el backup.

Y ya por último de verdad cuando se realiza un backup es buena práctica poner la fecha y hora para saber cuando se realizan los backups. Y esto lo podéis hacer poniendo este nombre de fichero

nombre_de_backup_$(date +%F_%H%M%S).sql

Y dará un formato com este: nombre_de_backup_2024-08-11_143727.sql

Backup de una sola base de datos

mysqldump -u usuario -p nombre_base_datos > backup.sql

El fichero backup.sql se queda en el directorio desde donde ejecuteis el backup, si se quiere dejar el fichero en otra ruta hay que especificarla, por ejemplo

mysqldump -u usuario -p nombre_base_datos > /home/USUARIO/backups/backup.sql

Backup de tablas concretas

También podemos realizar backups de una tabla:

mysqldump -u usuario -p nombre_base_datos nombre_tabla > backup_tabla.sql

O de varias tablas:

mysqldump -u usuario -p nombre_base_datos tabla1 tabla2 tabla3 > backup_tabla.sql

Backup de todas las bases de datos

Con esto realizamos el backup de todo el servidor de MySql

mysqldump -u usuario -p --all-databases > backup_todas_las_bases.sql

Backup de todas las bases de datos menos las de sistemas

Es decir, eliminando del backup las bases de datos mysql, information_schema, performance_schema y sys y que no se guarden en el fichero. El comando no es directo y hay que hacer una consulta a la base de datos para que excluya las bases de datos de sistema.

mysql -u usuario -p -e "SHOW DATABASES WHERE \`Database\` NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');" > bases_de_datos.txt

De esta forma que excluimos las bases de datos de sistema podemos excluir cualquier otra base de datos que no queramos en el backup.

Recordar que esto hace backup de todo estructura y datos.

Backup de múltiples bases de datos seleccionadas

Igual que podemos seleccionar una o todas las bases de datos podemos también elegir de que bases de datos queremos hacer backup

mysqldump -u usuario -p --databases base_datos1 base_datos2 > backup_varias_bases.sql

Recordar que esto hace backup de todo estructura y datos.

Backup solo de la estructura de la base de datos (sin datos)

Y si solo queremos la estructura de la base de datos y no los datos es muy sencillo con el parámetro --no-data

mysqldump -u usuario -p --no-data nombre_base_datos > backup_estructura.sql

Backup solo de los datos (sin la estructura)

Y si lo queremos al revés solo los datos con el parámetro --no-create-info

mysqldump -u usuario -p --no-create-info nombre_base_datos > backup_datos.sql

Backup con compresión

Si la base de datos es muy grande una buena opción es comprimir el fichero de backup.

mysqldump -u usuario -p nombre_base_datos | gzip > backup_comprimido.sql.gz

Backup excluyendo ciertas tablas

A veces no queremos copiar toda la base de datos y necesitamos excluir tablas por que son muy grandes o no necesarias por ejemplo las de logs, pues también podemos hacerlo

mysqldump -u usuario -p nombre_base_datos --ignore-table=nombre_base_datos.tabla1 --ignore-table=nombre_base_datos.tabla2 > backup_sin_tablas.sql

Backup de base de datos con datos en formato CSV

Con esta opción el backup se realiza en dos ficheros de texto llamados como la tabla de la que se hace backup. Un fichero con extensión .sql donde se guarda la estructura de los datos y otro con extensión .txt donde se guardan los datos en formato csv

mysqldump -u usuario -p --tab=/ruta/destino --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' nombre_base_datos nombre_tabla

Si no se especifica el nombre de la tabla se hace backup de toda la base de datos, cada tabla tendras sus dos ficheros.

Backup con especificación de un conjunto de caracteres

Con el parámetro --default-character-set podemos espeficicar el juego de caracteres que necesitamos en el backup.

mysqldump -u usuario -p --default-character-set=utf8 nombre_base_datos > backup_utf8.sql

Backup de una base de datos con procedimientos almacenados, funciones y triggers

Por defecto un backup de toda la base de datos no exporta procedimientos almacenados, funciones y triggers tenemos que especificarlo con el parámetro --routines y para los triggers --triggers

mysqldump -u usuario -p --routines --triggers nombre_base_datos > backup_completo.sql

También podemos hacerlo especificando los que queremos por separado, por ejemplo, hacer backup solo de vistas y no de lo procedimientos y funciones ni datos.

mysqldump -u usuario -p --no-data --routines=false --triggers=false --single-transaction nombre_base_datos > backup_solo_vistas.sql

Backup limitando y filtrando el número de registro de una tabla

Nos puede interesar que en determinadas ocasiones y para pruebas por lo general no necesitemos todos los datos de una tabla que es muy grande y solo necesitemos un conjunto de ellos.

Coger un subconjunto de datos y limitando la cantidad de estos que exportamos.

mysqldump -u usuario -p --where="1 order by id desc limit 1000" nombre_base_datos nombre_tabla > backup_segmento.sql

Para usar algún filtro solo tenemos que quitar el 1 y poner nuestro filtro. Por ejemplo suponemos que la tabla tiene un campo llamado pais y un codigo que es USA y quiero filtrar los mil 1000 registros de USA

mysqldump -u usuario -p --where="pais='USA' order by id desc limit 1000" nombre_base_datos nombre_tabla > backup_segmento.sql

Backup dividiendo en multiples archivos

Si el archivo que va a salir del backup de una base de datos es muy grande a lo mejor es buena idea dividirlo en varios archivos.

mysqldump -u usuario -p nombre_base_datos | split -b 100m - backup_parte_

Generará ficheros de 100megas y el ultimo del resto como una letra al final del fichero de la siguiente forma backup_parte_aa, backup_parte_ab pero si queremos cambiar las letras por numeros tenemos que añadir el modificador -d

mysqldump -u usuario -p nombre_base_datos | split -b 100m -d - backup_parte_

Para restaurar este tipo de backups utilizaremos esta instrucción

cat backup_parte_* | mysql -u usuario -p nombre_base_datos

Backup sin bloquear tablas

Para bases de datos basadas en el motor MyISAM se puede utilizar el parámetro --lock-tables=false

mysqldump -u usuario -p --lock-tables=false nombre_base_datos > backup_sin_bloqueo.sql

Para bases de datos InnoDB podemos utilizar el parámetro --single-transaction que lo que hace es crear un volcado de la bbdd y mientras se realiza el backup nada de lo que ocurra en la base de datos ira al backup

mysqldump -u usuario -p --single-transaction nombre_base_datos > backup.sql

Conclusión

Y con esto creo que hay suficientes opciones de backup para bases de datos MySql

En la mayoría de los casos restaurar una base de datos creada con mysqldump se realiza con el comando mysql

mysql -u usuario -p nombre_base_datos < backup.sql

Y esto es todo, feliz programming
Saludos
Alex
/ :-)

Si te ha gustado el artículo compartelo en:      

---

---

---

---

---