Cómo hacer una copia de seguridad y restaurar bases de datos mysql con mysqldump

Este tutorial explica cómo hacer una copia de seguridad y restaurar bases de datos MySQL o MariaDB desde la línea de comandos utilizando la utilidad mysqldump.

Los archivos de respaldo creados por la utilidad mysqldump son básicamente un conjunto de instrucciones SQL que pueden usarse para recrear la base de datos original. El comando mysqldump también puede generar archivos en formato CSV y XML.

También puede usar la utilidad mysqldump para transferir su base de datos MySQL a otro servidor MySQL.

Sintaxis del comando Mysqldump

Antes de comenzar a usar el comando mysqldump, comencemos por revisar la sintaxis básica.

Las expresiones de utilidad mysqldump toman la siguiente forma:

mysqldump > file.sql

• options : el archivo de opciones file.sql : el archivo de volcado (copia de seguridad)

Para usar el comando mysqldump, el servidor MySQL debe estar accesible y en ejecución.

Copia de seguridad de una sola base de datos MySQL

El caso de uso más común de la herramienta mysqldump es hacer una copia de seguridad de una sola base de datos.

Por ejemplo, para crear una copia de seguridad de la base de datos llamada database_name usando la root del usuario y guardarla en un archivo llamado database_name.sql , debe ejecutar el siguiente comando:

mysqldump -u root -p database_name > database_name.sql

Se le pedirá que ingrese la contraseña de root. Después de una autenticación exitosa, se iniciará el proceso de volcado. Dependiendo del tamaño de la base de datos, el proceso puede llevar algún tiempo.

mysqldump database_name > database_name.sql

Copia de seguridad de múltiples bases de datos MySQL

Para hacer una copia de seguridad de múltiples bases de datos MySQL con un comando, debe usar la opción --database seguida de la lista de bases de datos que desea respaldar. Cada nombre de base de datos debe estar separado por espacio.

mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql

El comando anterior creará un archivo de volcado que contiene ambas bases de datos.

Copia de seguridad de todas las bases de datos MySQL

Use la opción --all-databases para hacer una copia de seguridad de todas las bases de datos MySQL:

mysqldump -u root -p --all-databases > all_databases.sql

Igual que con el ejemplo anterior, el comando anterior creará un único archivo de volcado que contiene todas las bases de datos.

Copia de seguridad de todas las bases de datos MySQL para separar archivos

La utilidad mysqldump no proporciona una opción para hacer una copia de seguridad de todas las bases de datos para separar los archivos, pero lo logramos fácilmente con un simple bash FOR loop:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done

El comando anterior creará un archivo de volcado separado para cada base de datos utilizando el nombre de la base de datos como nombre de archivo.

Crear una copia de seguridad de la base de datos MySQL comprimida

Si el tamaño de la base de datos es muy grande, es una buena idea comprimir la salida. Para hacerlo, simplemente canalice la salida a la utilidad gzip y redirija a un archivo como se muestra a continuación:

mysqldump database_name | gzip > database_name.sql.gz

Crear una copia de seguridad con marca de tiempo

mysqldump database_name > database_name-$(date +%Y%m%d).sql

El comando anterior creará un archivo con el siguiente formato database_name-20180617.sql

Restaurar un volcado de MySQL

Puede restaurar un volcado de MySQL utilizando la herramienta mysql . La sintaxis general del comando es la siguiente:

mysqld database_name < file.sql

En la mayoría de los casos, deberá crear una base de datos para importar. Si la base de datos ya existe, primero debe eliminarla.

En el siguiente ejemplo, el primer comando creará una base de datos llamada database_name y luego importará el volcado database_name.sql :

mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql

Restaurar una sola base de datos MySQL desde un volcado completo de MySQL

mysql --one-database database_name < all_databases.sql

Exportar e importar una base de datos MySQL en un comando

En lugar de crear un archivo de volcado de una base de datos y luego importar la copia de seguridad a otra base de datos MySQL, puede usar el siguiente one-liner:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

El comando anterior canalizará la salida a un cliente mysql en el host remoto y lo importará a una base de datos llamada remote_database_name . Antes de ejecutar el comando, asegúrese de que la base de datos ya exista en el servidor remoto.

Automatizar copias de seguridad con Cron

Automatizar el proceso de copia de seguridad de las bases de datos es tan simple como crear un trabajo cron que ejecutará el comando mysqldump en el momento especificado.

Para configurar copias de seguridad automáticas de una base de datos MySQL usando cronjob, siga los pasos a continuación:

1. Cree un archivo llamado .my.cnf en su directorio de inicio de usuario:

   sudo nano ~/.my.cnf

   Copie y pegue el siguiente texto en el archivo.my.cnf.

   user = dbuser password = dbpasswd

   No olvide reemplazar dbuser y dbpasswd con el usuario de la base de datos y la contraseña del usuario.

   Restrinja los permisos del archivo de credenciales para que solo su usuario tenga acceso a él:

   chmod 600 ~/.my.cnf

   Cree un directorio para almacenar las copias de seguridad:

   mkdir ~/db_backups

   Abra su archivo de usuario crontab:

   crontab -e

   Agregue el siguiente trabajo cron que creará una copia de seguridad de un nombre de base de datos mydb todos los días a las 3 am:

   0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql

   No olvide reemplazar el username con su nombre de usuario real.

También puede crear otro cronjob para eliminar cualquier copia de seguridad anterior a 30 días:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Por supuesto, debe ajustar el comando de acuerdo con la ubicación de la copia de seguridad y los nombres de los archivos. Para obtener más información sobre el comando find, consulte nuestra guía Cómo encontrar archivos en Linux con la guía de línea de comandos.

Conclusión

Este tutorial cubre solo los conceptos básicos, pero debería ser un buen comienzo para cualquiera que quiera aprender cómo crear y restaurar bases de datos MySQL desde la línea de comandos utilizando la utilidad mysqldump.

También puede consultar el tutorial sobre cómo restablecer una contraseña de root de MySQL en caso de que la haya olvidado.

mysql mariadb backup mysqldump