GNU/Linux, Open Source, Cloud Computing, DevOps y más...

Cómo hacer backups de MySQL/MariaDB con el comando mysqldump

21 comentarios

MySQL/MariaDB mysqldump commandAunque existen diferentes métodos para realizar copias de seguridad de bases de datos MySQL o MariaDB, el más común y eficiente se basa en el uso de una herramienta nativa que tanto MySQL como MariaDB ponen a nuestra disposición para este cometido: el comando mysqldump. Como su propio nombre indica, se trata de un programa ejecutable desde la línea de comandos que permite realizar una exportación completa (dump) de todo el contenido de una base de datos o incluso de todas las bases de datos presentes en una instancia de MySQL o MariaDB en ejecución. Por supuesto también permite realizar copias de seguridad parciales, es decir, sólo de algunas tablas concretas.

El comando mysqldump ofrece multitud de parámetros distintos que lo hacen muy potente y flexible. Dado que disponer de tal cantidad de opciones puede llegar a ser confuso, en este post voy a recoger varios de los ejemplos de uso más frecuente usando los parámetros más habituales y que resultan de más utilidad en el día a día del administrador de sistemas.

Al ser un programa ejecutable, mysqldump precisa de un acceso por línea de comandos al servidor MySQL o que éste nos permita el acceso desde la red a través de su puerto 3306. Sin embargo, por seguridad en muchas ocasiones no disponemos ni de una cosa ni de la otra y no es posible utilizar este comando. Descubre cómo hacer un backup de MySQL de forma alternativa cuando no podemos valernos del comando mysqldump:

  Backup de bases de datos MySQL con PHP

1.- Backup completo de una base de datos

Imaginemos que disponemos de un servidor de base de datos MySQL o MariaDB con varias bases de datos y que queremos hacer una copia de seguridad de todo el contenido de una de ellas. En este sentido mysqldump nos ofrece la posibilidad de exportar por separado la estructura, los datos, los triggers y los procedimientos o rutinas, o todo ello en su conjunto. Veamos en primer lugar lo que sería el ejemplo estrella, es decir, cómo realizar una exportación completa de toda la información de una base de datos que incluya estructura, datos, eventos, procedimientos, triggers y vistas:

$ mysqldump -v --opt --events --routines --triggers --default-character-set=utf8 -u your_username -p your_db_name > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql

Si además queremos que se genere directamente un fichero comprimido con gzip o bzip2 sin necesidad de que se escriba en disco primero el fichero SQL resultante de la ejecución del comando mysqldump, el cual tendrá un tamaño mucho mayor, para luego realizar la compresión del mismo en un segundo paso, ejecutaremos el comando añadiendo | gzip -c después del nombre de la base de datos a exportar.

Mysqldump database backup workflow

Esto es muy importante sobre todo si queremos automatizar nuestros backups, ya que no hacerlo podría provocar el llenado del sistema de ficheros antes de completarse el respaldo, que resultaría fallido y además no se nos permitiría realizar más backups hasta que se liberara de nuevo espacio en disco. Así, el mismo comando del ejemplo anterior pero adaptado a un entorno desatendido de realización de copias de seguridad automáticas quedaría así:

$ mysqldump --opt --events --routines --triggers --default-character-set=utf8 -u your_username --password=your_password your_db_name | gzip -c > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql.gz

Si en lugar de especificar una base de datos queremos hacer una copia de todas las bases de datos presentes en una instancia o servidor MySQL/MariaDB, entonces emplearemos el parámetro –all-databases en sustitución del nombre de la base de datos, en nuestro ejemplo your_db_name.


Error: Your Requested widget " ai_widget-6" is not in the widget list.
  • [do_widget_area above-nav-left]
    • [do_widget_area above-nav-right]
      • [do_widget_area footer-1]
        • [do_widget id="wpp-4"]
      • [do_widget_area footer-2]
        • [do_widget id="recent-posts-4"]
      • [do_widget_area footer-3]
        • [do_widget id="recent-comments-3"]
      • [do_widget_area footer-4]
        • [do_widget id="archives-4"]
      • [do_widget_area logo-bar]
        • [do_widget id="oxywidgetwpml-3"]
      • [do_widget_area menu-bar]
        • [do_widget id="search-3"]
      • [do_widget_area sidebar]
        • [do_widget id="search-4"]
        • [do_widget id="ai_widget-2"]
        • [do_widget id="categories-5"]
        • [do_widget id="ai_widget-3"]
        • [do_widget id="ai_widget-4"]
        • [do_widget id="ai_widget-5"]
      • [do_widget_area sub-footer-1]
        • [do_widget id="text-4"]
      • [do_widget_area sub-footer-2]
        • [do_widget_area sub-footer-3]
          • [do_widget_area sub-footer-4]
            • [do_widget_area upper-footer-1]
              • [do_widget id="search-2"]
              • [do_widget id="recent-posts-2"]
              • [do_widget id="recent-comments-2"]
              • [do_widget id="archives-2"]
              • [do_widget id="categories-2"]
              • [do_widget id="meta-2"]
            • [do_widget_area upper-footer-2]
              • [do_widget_area upper-footer-3]
                • [do_widget_area upper-footer-4]
                  • [do_widget_area widgets_for_shortcodes]
                    • [do_widget id="search-5"]
                    • [do_widget id="ai_widget-6"]
                  • [do_widget_area wp_inactive_widgets]
                    • [do_widget id="wpp-2"]
                    • [do_widget id="text-1"]
                    • [do_widget id="recent-posts-3"]
                    • [do_widget id="categories-3"]
                    • [do_widget id="archives-3"]
                    • [do_widget id="icl_lang_sel_widget-3"]

                  1.1. Exportación sólo de la estructura de nuestra base de datos

                  En este caso usaremos el parámetro –no-data para generar un fichero SQL únicamente con sentencias CREATE TABLE que nos permitan reproducir la estructura de nuestra base de datos, pero creandola vacía de contenido:

                  $ mysqldump -v --opt --no-data --default-character-set=utf8 -u your_username -p your_db_name > db_structure_your_db_name_`date +%Y%m%d_%H%M%S`.sql

                  1.2. Exportación sólo del contenido de las distintas tablas

                  Este sería el caso contrario al del ejemplo anterior, es decir, sólo queremos exportar el contenido de las tablas de nuestra base de datos generando un fichero SQL que incluye básicamente sentencias INSERT. Para ello usaremos los parámetros –no-create-info y –skip-triggers:

                  $ mysqldump -v --opt --no-create-info --skip-triggers --default-character-set=utf8 -u your_username -p your_db_name > db_data_your_db_name_`date +%Y%m%d_%H%M%S`.sql

                  2.- Backup parcial de una base de datos

                  Si sólo nos interesa hacer una copia de seguridad de un subconjunto de tablas tenemos dos opciones: especificar uno a uno el nombre de las distintas tablas que queremos respaldar, o usar el parámetro –ignore-table para que se exporten todas las tablas excepto aquellas que indiquemos expresamente que sean ignoradas:

                  2.1. Incluir sólo un subconjunto de tablas

                  $ mysqldump -v --opt --default-character-set=utf8 -u your_username -p your_db_name table1 table2 table3 > db_tables_your_db_name_`date +%Y%m%d_%H%M%S`.sql

                  2.2. Excluir un subconjunto de tablas

                   $ mysqldump -v --opt --default-character-set=utf8 -u your_username -p --ignore-table=table1 --ignore-table=table2 --ignore-table=table3 your_db_name > db_tables_your_db_name_`date +%Y%m%d_%H%M%S`.sql

                  3.- Backups remotos

                  Si necesitamos realizar una copia de seguridad de una base de datos en un servidor remoto, a continuación ofrezco un par de formas distintas de conseguirlo:


                  Error: Your Requested widget " ai_widget-6" is not in the widget list.
                  • [do_widget_area above-nav-left]
                    • [do_widget_area above-nav-right]
                      • [do_widget_area footer-1]
                        • [do_widget id="wpp-4"]
                      • [do_widget_area footer-2]
                        • [do_widget id="recent-posts-4"]
                      • [do_widget_area footer-3]
                        • [do_widget id="recent-comments-3"]
                      • [do_widget_area footer-4]
                        • [do_widget id="archives-4"]
                      • [do_widget_area logo-bar]
                        • [do_widget id="oxywidgetwpml-3"]
                      • [do_widget_area menu-bar]
                        • [do_widget id="search-3"]
                      • [do_widget_area sidebar]
                        • [do_widget id="search-4"]
                        • [do_widget id="ai_widget-2"]
                        • [do_widget id="categories-5"]
                        • [do_widget id="ai_widget-3"]
                        • [do_widget id="ai_widget-4"]
                        • [do_widget id="ai_widget-5"]
                      • [do_widget_area sub-footer-1]
                        • [do_widget id="text-4"]
                      • [do_widget_area sub-footer-2]
                        • [do_widget_area sub-footer-3]
                          • [do_widget_area sub-footer-4]
                            • [do_widget_area upper-footer-1]
                              • [do_widget id="search-2"]
                              • [do_widget id="recent-posts-2"]
                              • [do_widget id="recent-comments-2"]
                              • [do_widget id="archives-2"]
                              • [do_widget id="categories-2"]
                              • [do_widget id="meta-2"]
                            • [do_widget_area upper-footer-2]
                              • [do_widget_area upper-footer-3]
                                • [do_widget_area upper-footer-4]
                                  • [do_widget_area widgets_for_shortcodes]
                                    • [do_widget id="search-5"]
                                    • [do_widget id="ai_widget-6"]
                                  • [do_widget_area wp_inactive_widgets]
                                    • [do_widget id="wpp-2"]
                                    • [do_widget id="text-1"]
                                    • [do_widget id="recent-posts-3"]
                                    • [do_widget id="categories-3"]
                                    • [do_widget id="archives-3"]
                                    • [do_widget id="icl_lang_sel_widget-3"]

                                  3.1. Accediendo al servidor MySQL a través de TCP/IP

                                  Si tenemos acceso al servidor MySQL a través de TCP/IP atacando su puerto 3306, podemos hacer backups de la misma forma que en los ejemplos anteriores, pero especificando con los parámetros -h y -P el nombre host y el puerto donde escucha el servidor MySQL. Es recomendable también indicar el parámetro –compress para comprimir la comunicación entre cliente y servidor, con lo cual ganaremos en velocidad y ancho de banda:

                                  $ mysqldump -v --opt -h mysql.example.com -P 3306 --compress --events --routines --triggers --default-character-set=utf8 -u your_username -p your_db_name > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql

                                  3.2. Accediendo a través de SSH

                                  Si por el contrario nuestro servidor MySQL sólo es accesible mediante socket desde localhost o algún cortafuegos nos impide el acceso TCP/IP, podemos usar SSH para realizar la copia de seguridad de una máquina a otra así:

                                  $ ssh ssh_username@server "mysqldump -v --opt --events --routines --triggers --default-character-set=utf8 -u your_username --password=your_password your_db_name | gzip -c" > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql.gz

                                  4.- Restauración de bases de datos

                                  Para restaurar los backups tomados con cualquiera de los ejemplos anteriores simplemente redireccionaremos el contenido de alguno de los ficheros .sql hacia el comando mysql y así todo su contenido será importado:

                                  $ mysql -u your_username --password=your_password your_db_name < db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql

                                  En el caso de que el fichero SQL contenga información de más de una base de datos (por ejemplo al haberlo generado con el parámetro –all-databases visto anteriormente) no es necesario especificar ningún nombre de base de datos en el comando.

                                  Podemos restaurar un backup comprimido sin necesidad de tener que descomprimirlo antes así:

                                  $ gunzip -c db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql.gz | mysql -u your_username --password=your_password your_db_name

                                  5.- Sincronizar base de datos entre dos servidores

                                  Por último, podemos ir un poco más allá con el uso de tuberías y redirecciones y conseguir sincronizar el contenido completo de una base de datos en otra sin crear ningún fichero intermedio, ya estén éstas en el mismo servidor o en servidores remotos. A continuación os dejo un comando muy útil de una sola línea que os permitirá hacer esto rápidamente:

                                  $ ssh ssh_username@server "mysqldump -v --opt --events --routines --triggers --default-character-set=utf8 -u your_username --password=your_password your_db_name | gzip -c" | gunzip | mysql --password=your_password -u your_username your_db_name

                                  En el ejemplo anterior se realiza un backup completo de una base de datos localizada en un servidor remoto (origen) desde el servidor de destino al que queremos copiar dicha base de datos. Ambos servidores no tienen por qué compartir los mismos nombres de usuario, base de datos y contraseñas y bastará con ajustar los de un lado y los del otro adecuadamente.

                                  Mediante el mismo mecanismo se podría hacer también al contrario, es decir, ejecutar un comando desde el propio servidor origen que copie el contenido de la base de datos en un servidor remoto que en este caso sería el de destino. Te dejo a ti ponerlo en práctica a modo de ejercicio, aunque si tienes cualquier problema no dudes en contactar conmigo y te echaré una mano gustosamente.

                                   

                                  Sobre el autor

                                  Daniel López Azaña
                                  Arquitecto de soluciones Cloud AWS & Linux Sysadmin Freelance

                                  Emprendedor, generador de ideas y mente inquieta. Apasionado de las nuevas tecnologías, especialmente de los sistemas Linux y del software libre. Me gusta escribir además sobre actualidad tecnológica, Cloud Computing, AWSi, DevOps, DevSecOps, seguridad, desarrollo web y programación, SEO, ciencia, innovación, emprendimiento, etc.

                                  DanielCómo hacer backups de MySQL/MariaDB con el comando mysqldump

                                  Artículos relacionados

                                  21 comentarios

                                  Unirte a la conversación
                                  • Gabriel Bedón - 20/11/2019 responder

                                    Hola Daniel, saludos desde Ecuador, te felicito por el artículo!

                                    Tengo una duda, deseo hacer una sincronización en tiempo real de toda la data en MaríaBD (1 instancia, 5 BDs) ubicado en un servidor origen, hacia otro servidor destino (remoto), con el fin de manejar contingencia, en el caso de que suceda algo con el servidor origen.

                                    ¿Tienes alguna de idea de cómo hacerlo?

                                    Agradeceré mucho tu respuesta y estaré atento a tus comentarios.

                                    Saludos,

                                    GB

                                    Daniel - 21/11/2019 responder

                                    Hola Gabriel, para hacer una sincronización en tiempo real no sirve el enfoque de este artículo. En su lugar debes optar por hacer uso de la función de replicación de MySQL/MariaDB: https://dev.mysql.com/doc/refman/8.0/en/replication.html / https://mariadb.com/kb/en/library/replication-overview/

                                  • Javier - 04/12/2019 responder

                                    Hola Daniel,
                                    Un saludo y felicidades por el artículo.
                                    Me surge una duda con el apartado «1.2. Exportación sólo del contenido de las distintas tablas» …
                                    Si yo en su día hice un insert para introducir datos, y al tiempo modifique alguno de ellos, en esta exportación ¿Que dato guardo?¿el insertado inicialmente o la modificación posterior?

                                    Un saludo y muchas gracias
                                    Javi

                                    Daniel - 05/12/2019 responder

                                    Gracias Javier. Siempre se exporta la información que haya en el momento de ejecutar el mysqldump, no la que se insertó inicialmente en la base de datos. Es decir, si se asignó un valor de 5 a un campo con un INSERT inicial y luego se hace un UPDATE y se cambia a 7, el valor que se exportará es el de 7.

                                  • Daniel - 21/03/2020 responder

                                    Hola Tocallo buenos dias, estoy intentando hacer el backup de una db de un serrvidor en mi computadora personal, exporto la db completa, pero no me trae nada de regreso, sabras porque me aparece en 0
                                    la linea usada es
                                    echo off
                                    mysqldump -v –opt -h xxxx.xxxx.net -P 3306–events –routines –triggers -u myuser-p mypass mydb> backup_%Date:~8,6%%Date:~5,2%%Date:~0,4%.sql
                                    exit

                                    Alex Trellez - 02/07/2021 responder

                                    Lo mas probable es que el motor no reciba conexiones externas por lo que puedes probar la opcion 3.2. Accediendo a través de SSH. Saludos

                                  • Omar - 25/03/2020 responder

                                    Hola Daniel, una consulta, como se establece un a conexion SSL a MariaDB desde un cliente windows10 y la BD en un WindowsServer 2012

                                  • Misael de Leon - 18/04/2020 responder

                                    Buen Dia Daniel. Saludos de México.
                                    Una duda, al restaurar una DB de un gzip, se vuelve a integrar el la DB Original? o de que forma podria mandarla a una DB nueva que identifique que es un backup, por ejemplo, agredarle la fecha en el nombre.

                                    Daniel - 20/04/2020 responder

                                    Puedes restaurar un dump comprimido con gzip en cualquier base de datos, ya sea en la original, en otra base de datos del mismo servidor, o en otro servidor remoto. El nombre del fichero es indiferente, lo importante es el contenido del dump y los parámetros que se pasen al comando mysql a la hora de procesarlo.

                                  • Isaac - 03/06/2020 responder

                                    Hola, Mi grano de arena: puesto que los dumps suelen bloquear las tablas, especialmente con tablas grandes, reducir el tiempo de backup al máximo resulta interesante. En vez de comprimir con gzip se puede usar el programa «pigz», normalmente disponible en los repos de la distribución. Este puede usar varios cores de la maquina para la compresión, acelerándolo todo bastante sobretodo en servidores dedicados con muchos cores disponibles. Al usar compresión la CPU trabaja de más pero se le mete menos ‘castaña’ al disco, que suele ser el cuello de botella.

                                    Un saludo

                                    Daniel - 03/06/2020 responder

                                    ¡Gracias Isaac, muy interesante tu aportación!

                                  • Daniel - 06/07/2020 responder

                                    Hola Daniel,
                                    Enhorabuena por el artículo puesto que me ha aclarado bastantes dudas.
                                    Supongo que es sencillo pero me gustaría preguntarte si para realizar el backup de mi bd Mariadb a un servidor remoto (192.168.x.x) bastaría con hacer lo que has comentado. ¿Podría implementarlo en un script #!/bin/sh y programarlo para que lo hiciera todos los días a una hora? Es decir, incluir la línea que has indicado en el script:
                                    ssh ssh_mi_usuario@192.168.x.x «mysqldump -v –opt –events –routines –triggers –default-character-set=utf8 -u mi_usuario –password=mi_password mi_db_name | gzip -c» > /ruta_remota/carpeta1/db_backup_mi_db_name_`date +%Y%m%d_%H%M%S`.sql.gz

                                    Muchas gracias,
                                    Saludos.

                                    Daniel - 06/07/2020 responder

                                    Así es, puedes incluso lanzar el comando directamente desde cron sin necesidad de script.

                                  • Alejandro Ibarra - 10/04/2021 responder

                                    Muchas Gracias,
                                    Es un excelente artículo, muchas opciones que a pesar de mis años de experiencia no conocía y me facilitaran el trabajo.
                                    Mi granito de arena será para todos los que quieren sincronizar bases de datos en tiempo real en mariadb existe Galera que te permite crear un cluster de bases de datos de una forma sencilla (probado en centos 8), solo como consejo respalden las bases de datos antes de configurar ya que se corre el riesgo que algún error te borre una base de datos.

                                  • Xavi - 18/05/2021 responder

                                    Hola, solo quería darte las gracias por tu artículo, soy profesor de Ciclos de Grado Superior y mis apuntes para mis alumnos han quedado muy completos gracias a tu artículo/»post».
                                    Saludos desde Barcelona

                                    Daniel - 18/05/2021 responder

                                    ¡Muchas gracias Xavi, me alegro de que te haya resultado útil!

                                  • Valentin - 21/08/2021 responder

                                    Hola Daniel, buenísimo el artículo y gracias por las respuestas que das. Un par de consultas, es posible obtener un backup diferencial de la BD con mysqldump ??? Si no lo fuese, que herramienta opensource o de paga recomiendas ??? de preferencia open source 🙂

                                    Daniel - 31/10/2021 responder

                                    Es posible hacer backups incrementales con la propia herramienta mysqldump de MySQL y activando el binlog de MySQL. Se llaman PITR (Point-In-Time-Recovery). Si buscas por esa clave encontrarás tutoriales sobre como configurar este tipo de backups.

                                  • Jose - 10/08/2022 responder

                                    Hola
                                    Estoy empezando en este mundo… siguiendo tus indicaciones entro en mysql con «mysql -u root -p» y ejecuto el comando que tienes más arriba «$ mysqldump –opt –events –routines –triggers –default-character-set=utf8 -u your_username –password=your_password your_db_name | gzip -c > db_backup_your_db_name_`date +%Y%m%d_%H%M%S`.sql.gz»
                                    Y me aparece «->» es decir como esperando algún input… Ya no sé seguir (siento ser tan novel)

                                    Daniel - 11/09/2022 responder

                                    Hola, el $ del principio no tienes que ponerlo, sólo indica el prompt de un usuario genérico (no root). Quizás también te está copiando mal el carácter `, así que prueba a ejecutarlo así: mysqldump –opt –events –routines –triggers –default-character-set=utf8 -u your_username –password=your_password your_db_name | gzip -c > db_backup_your_db_name_$(date +%Y%m%d_%H%M%S).sql.gz

                                  • maria - 21/10/2022 responder

                                    Buenos dias Daniel, estoy con ustedes, ya que intento ejecutar un query con varios sub querys, nota importante cuando son and, los ejecuta sin problema, pero al cambiarlos al OR, ahi inicia mi problema ya que se desconecta de mysql, tratando de resolver me encontre con mysqldump, me puedes apoyar, con el asunto de ejecución, para que lo soporte y no se desconecte.
                                    Gracias

                                  Deja una respuesta

                                  Tu dirección de correo electrónico no será publicada.