En el trabajo de mantenimiento diario, a menudo se requieren operaciones de exportación de datos, y mysqldump es una herramienta frecuentemente utilizada para exportar datos. Cuenta con muchos parámetros funcionales integrados, y este artículo listará algunas operaciones comúnmente usadas.
¿Qué es mysqldump?
mysqldump es un programa cliente de MySQL utilizado para volcar bases de datos. Principalmente genera una serie de sentencias SQL que pueden encapsularse en un archivo. Este archivo contiene todos los comandos SQL necesarios para reconstruir tu base de datos, como CREATE DATABASE, CREATE TABLE, INSERT, etc. Puede utilizarse para migraciones o recuperaciones rápidas y ligeras de bases de datos. Es un método para realizar copias de seguridad lógicas en bases de datos MySQL.
¿Cómo exportar una base de datos MySQL usando mysqldump?
1. Exportar todas las bases de datos
Este comando exporta todas las bases de datos, incluidas las bases de datos del sistema.
mysqldump -uroot -proot --all-databases > /tmp/all.sql
2. Exportar todos los datos de las bases de datos db1 y db2
mysqldump -uroot -proot --databases db1 db2 > /tmp/user.sql
Exporta múltiples bases de datos. Todos los parámetros subsiguientes después de la opción se consideran nombres de bases de datos.
Agregue una instrucción DROP DATABASE antes de crear cada base de datos.
mysqldump -uroot -p --databases test --add-drop-database
3. Exportar las tablas a1 y a2 desde db1
Nota: la exportación de tablas específicas solo puede realizarse para una sola base de datos, y el contenido exportado es diferente al de la exportación de una base de datos. El texto exportado para tablas específicas no incluye instrucciones de creación de la base de datos; solo incluye instrucciones DROP TABLE, CREATE TABLE y INSERT.
mysqldump -uroot -proot --databases db1 --tables a1 a2 > /tmp/db1.sql
Anule el parámetro --databases y especifique las tablas que desea exportar.
mysqldump -uroot -p --host=localhost --databases test --tables test
Exportar todos los tablespace.
mysqldump -uroot -p --databases test --all-tablespaces
Excluir toda la información de tablespace de la exportación.
mysqldump -uroot -p --databases test --no-tablespaces
Agregue una instrucción DROP TABLE antes de crear cada tabla (habilitado por defecto, use --skip-add-drop-table para deshabilitar).
mysqldump -uroot -p --databases test
mysqldump -uroot -p --databases test --skip-add-drop-table
4. Exportación condicional: Exportar registros con id=1 de la tabla a1 en db1
La exportación condicional solo se puede realizar en una sola tabla.
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql
Volcar solo los registros seleccionados por la condición WHERE dada. Si la condición contiene intérpretes de comandos, espacios o caracteres especiales, debe ir entre comillas.
mysqldump -uroot -p --host=localhost --databases db1 --where="user='root'"
5. Exportar solo las estructuras de las tablas, sin datos
mysqldump -uroot -proot --no-data --databases db1 > /tmp/db1.sql
Exportar solo los datos, sin agregar instrucciones CREATE DATABASE.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-db
Exportar solo datos, sin agregar instrucciones CREATE TABLE.
mysqldump -uroot -p --host=localhost --databases db1 --no-create-info
Excluir tablas específicas. Para ignorar varias tablas, repita la opción para cada tabla. Cada tabla debe especificar tanto la base de datos como el nombre de la tabla, por ejemplo, --ignore-table=database.table1 --ignore-table=database.table2 ...
mysqldump -uroot -p --host=localhost --databases db1 --ignore-table=mysql.user
Utilice el modo de inserción diferida (INSERT DELAYED) al exportar datos.
mysqldump -uroot -p --databases db1 --delayed-insert
Utilice sentencias INSERT con múltiples cláusulas VALUES. Esto reduce el tamaño del archivo de exportación y acelera la importación. Habilitado por defecto; use --skip-extended-insert para desactivarlo.
mysqldump -uroot -p --databases db1
mysqldump -uroot -p --databases db1 --skip-extended-insert
6. Exportar e importar datos entre servidores
mysqldump --host=h1 -uroot -proot --databases db1 | mysql --host=h2 -uroot -proot db2
Esto significa exportar todos los datos de db1 en el servidor h1 e importarlos a db2 en h2. La base de datos db2 debe existir; de lo contrario, se producirá un error.
Utilice una tubería con nombre para conectarse a MySQL.
mysqldump -uroot -p --host=localhost --all-databases --pipe
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
Habilite la compresión para la comunicación entre el cliente y el servidor.
mysqldump -uroot -p --all-databases --compress
7. Anexar la posición del binlog y el nombre de archivo de la base de datos maestra al archivo exportado
Esta opción, cuando se ejecuta en un servidor esclavo, equivale a ejecutar SHOW SLAVE STATUS.
Establecerlo en 1 genera un comando CHANGE MASTER en el archivo de datos.
Establecerlo en 2 agrega comentarios antes de CHANGE MASTER.
Esta opción habilita --lock-all-tables a menos que se especifique --single-transaction.
Después de la ejecución, --lock-tables se desactiva automáticamente. El valor predeterminado para --dump-slave es 1.
mysqldump -uroot -proot --dump-slave=1 --database db1 > /tmp/db1.sql
mysqldump -uroot -proot --dump-slave=2 --database db1 > /tmp/db1.sql
Exportar eventos.
mysqldump -uroot -p --all-databases --events
8. Vaciar registros antes de exportar
Nota: Si se exportan varias bases de datos (--databases o --all-databases), los registros se vacían por base de datos. Si se utiliza --lock-all-tables o --master-data, los registros se vacían una sola vez, bloqueando todas las tablas. Para exportar y vaciar los registros simultáneamente, use --lock-all-tables o --master-data junto con --flush-logs.
mysqldump -uroot -p --all-databases --flush-logs
9. Asegúrese de un estado consistente durante la exportación
Antes de exportar los datos, esta opción emite una instrucción SQL BEGIN. BEGIN no bloquea ninguna aplicación y garantiza la consistencia de la base de datos durante la exportación. Solo es aplicable a motores de almacenamiento multiversión (por ejemplo, InnoDB). Esta opción es mutuamente excluyente con --lock-tables, ya que LOCK TABLES implícitamente confirma cualquier transacción pendiente. Para exportar tablas grandes, úsela junto con la opción --quick.
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
10. Exportar procedimientos almacenados y funciones
mysqldump -uroot -p --host=localhost --all-databases --routines
Otro método: usar SELECT...INTO OUTFILE para exportar datos de MySQL
SELECT...INTO OUTFILE es una sintaxis de MySQL utilizada para exportar los resultados de una consulta a un archivo. En MySQL, puedes usar esta instrucción para exportar fácilmente los datos a un archivo de texto.
Uso básico:
SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;
Ejemplo:
SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;
En la sentencia SQL anterior, seleccionamos las columnas id, nombre y correo electrónico de la tabla usuarios y escribimos los resultados en el archivo /tmp/user_data.csv. FIELDS TERMINATED BY ',' especifica el separador de columnas (coma), y LINES TERMINATED BY '\n' especifica el separador de filas (salto de línea).
Notas importantes:
La ejecución de SELECT...INTO OUTFILE requiere los permisos adecuados, y el directorio del archivo de salida debe ser una ubicación en la que el servidor MySQL pueda escribir.
En el siguiente ejemplo, exportamos datos de la tabla runoob_tbl al archivo /tmp/runoob.txt:
mysql> SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/runoob.txt';
Puede especificar el formato de salida utilizando opciones de comando. El siguiente ejemplo exporta datos en formato CSV:
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
Propiedades de SELECT ... INTO OUTFILE:
LOAD DATA INFILE es la operación inversa de SELECT ... INTO OUTFILE. Para escribir datos de la base de datos en un archivo, use SELECT ... INTO OUTFILE, y para leer el archivo nuevamente en la base de datos, use LOAD DATA INFILE.
Una sentencia SELECT en la forma SELECT...INTO OUTFILE 'file_name' escribe las filas seleccionadas en un archivo. Dado que el archivo se crea en el servidor host, debe tener el privilegio FILE para usar esta sintaxis.
El archivo de salida no debe existir previamente, para evitar la manipulación de datos.
Necesitas una cuenta con acceso de inicio de sesión al servidor para recuperar el archivo. De lo contrario, SELECT ... INTO OUTFILE no funcionará.
En sistemas UNIX, después de que se crea el archivo, es legible y sus permisos son propiedad del servidor MySQL. Esto significa que, aunque puede leer el archivo, es posible que no pueda eliminarlo.
Protección definitiva para su ecosistema de bases de datos
En una era en la que la resistencia de los datos es fundamental, elegir una solución de copia de seguridad con escalabilidad global y agilidad multi-nube es clave. Vinchin Backup & Recovery destaca por su sólida protección de MySQL diseñada para empresas internacionales:
✅ Estrategias completas de copia de seguridad
Compatible con estrategias de copia de seguridad en caliente, copia de seguridad completa, copia de seguridad incremental y copia de seguridad del registro de transacciones;
✅ Soporte universal para bases de datos
Unifique la protección en MySQL, Oracle, SQL Server y más en entornos híbridos (físicos/nube/VM).
Simplifique la gestión con una única consola para políticas de copia de seguridad multi-database.
✅ Fortalecimiento contra ransomware
Almacenamiento cifrado de datos de copia de seguridad + función antiransomware para resistir ataques maliciosos.
✅ Soporte Global, Eficiencia Local
Asistencia técnica las 24 horas, los 7 días de la semana y licencias optimizadas en costos, ideal para pymes y equipos distribuidos.
Para crear trabajos de copia de seguridad de bases de datos MySQL con Vinchin, vaya a la página Physical Backup > Database Backup > Backup:
1. Seleccione las bases de datos que deben respaldarse.
2. Seleccione un nodo de copia de seguridad en el que desea que se procesen y almacenen los datos de copia de seguridad.
3. Configure estrategias de copia de seguridad según sus necesidades.
4. Revise y confirme la configuración.
Vinchin es confiable para instituciones financieras e infraestructuras de TI multinacionales en más de 60 países. Haga clic en el botón de abajo para probar la prueba gratuita de 60 días de Vinchin y experimentar una solución de copia de seguridad de datos eficiente y confiable.
Conclusión
En este artículo, hemos explorado varias opciones de mysqldump para exportar eficientemente datos de la base de datos MySQL, desde exportaciones completas de bases de datos hasta exportaciones más detalladas, como tablas individuales o registros específicos. Comprender estos comandos y su funcionalidad ayuda a agilizar los procesos de copia de seguridad, migración y recuperación de la base de datos.
