Exportación de datos MySQL

En MySQL puedes usarSELECT...INTO OUTFILEla declaración para exportar datos fácilmente a un archivo de texto.


Usar la declaración SELECT ... INTO OUTFILE para exportar datos

SELECT...INTO OUTFILEEs la sintaxis en MySQL para exportar el resultado de una consulta a un archivo.

SELECT...INTO OUTFILEPermite escribir el resultado de tu consulta en un archivo de texto. La forma básica de uso es:

SELECT column1, column2, ...
INTO OUTFILE 'file_path'
FROM your_table
WHERE your_conditions;

Descripción de parámetros:

  • column1, column2, ...: las columnas a seleccionar.
  • 'file_path': especifica la ruta y el nombre del archivo de salida.
  • your_table: la tabla a consultar.
  • your_conditions: las condiciones de la consulta.

A continuación se muestra un ejemplo sencillo:

Ejemplo

SELECT id, name, email
INTO OUTFILE '/tmp/user_data.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
FROM users;

En la declaración SQL anterior, seleccionamos las columnas id, name y email de la tabla users y escribimos el resultado en el archivo /tmp/user_data.csv. FIELDS TERMINATED BY ',' especifica el delimitador entre columnas (coma) y LINES TERMINATED BY '\n' especifica el delimitador entre líneas (salto de línea).

Ten en cuenta que para ejecutarSELECT...INTO OUTFILEse necesitan los permisos correspondientes, y el directorio del archivo de salida debe ser un lugar donde el servidor MySQL pueda escribir.

En el siguiente ejemplo exportamos los datos de la tabla ejemplo_tbl al archivo /tmp/ejemplo.txt:

mysql> SELECT * FROM ejemplo_tbl 
    -> INTO OUTFILE '/tmp/ejemplo.txt';

Puedes configurar el formato de salida de los datos mediante las opciones del comando. El siguiente ejemplo exporta en formato CSV:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/ejemplo.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';

En el siguiente ejemplo se genera un archivo con los valores separados por comas. Este formato puede ser utilizado por muchos programas.

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

La declaración SELECT ... INTO OUTFILE tiene las siguientes propiedades:

  • LOAD DATA INFILE es la operación inversa de SELECT ... INTO OUTFILE, sintaxis SELECT. Para escribir los datos de una base de datos en un archivo, usa SELECT ... INTO OUTFILE; para leer el archivo de vuelta a la base de datos, usa LOAD DATA INFILE.
  • La forma SELECT ... INTO OUTFILE 'file_name' de SELECT puede escribir las filas seleccionadas en un archivo. El archivo se crea en el host del servidor, por lo que debes tener el privilegio FILE para usar esta sintaxis.
  • La salida no puede ser un archivo ya existente, para evitar la manipulación de los datos del archivo.
  • Necesitas una cuenta con acceso al servidor para recuperar el archivo. De lo contrario, SELECT ... INTO OUTFILE no tendrá ningún efecto.
  • En UNIX, el archivo se crea legible, y los permisos son propiedad del servidor MySQL. Esto significa que, aunque puedes leer el archivo, es posible que no puedas eliminarlo.

Exportar tablas como datos sin procesar con mysqldump

mysqldump es una herramienta de línea de comandos proporcionada por MySQL para hacer copias de seguridad y exportar bases de datos.

mysqldumpEs una utilidad de mysql para volcar bases de datos. Principalmente produce un script SQL que contiene los comandos necesarios para recrear la base de datos desde cero, como CREATE TABLE, INSERT, etc.

UsarmysqldumpExportar datos requiere usar--tabla opción para especificar el directorio del archivo de exportación; dicho destino debe ser escribible.

Uso básico de mysqldump:

mysqldump -u username -p password -h hostname database_name > output_file.sql

Descripción de parámetros:

  • -u: especifica el nombre de usuario de MySQL.
  • -p: solicita ingresar la contraseña.
  • -h: especifica el nombre del host de MySQL.
  • database_name: el nombre de la base de datos a exportar.
  • output_file.sql: el archivo donde se guardarán los datos exportados.

El siguiente ejemplo exporta la tabla ejemplo_tbl al directorio /tmp:

$ mysqldump -u root -p --no-create-info \
            --tab=/tmp EJEMPLO ejemplo_tbl
password ******

Ejemplo de mysqldump

1. Exportar toda la base de datos

Exportar la base de datos mydatabase al archivo mydatabase_backup.sql:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

2. Exportar una tabla específica

Si solo quieres exportar una tabla de la base de datos, puedes usar el siguiente comando:

mysqldump -u username -p password -h hostname database_name table_name > output_file.sql

o:

mysqldump -u root -p mydatabase mytable > mytable_backup.sql

3. Exportar la estructura de la base de datos

Si solo quieres exportar la estructura de la base de datos sin incluir los datos, puedes usar--no-data la opción:

mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

4. Exportar archivo comprimido

Puedes comprimir los datos exportados para reducir el tamaño del archivo. Por ejemplo, usando gzip:

mysqldump -u username -p password -h hostname database_name | gzip > output_file.sql.gz

Exportar datos en formato SQL

Exporta los datos en formato SQL a un archivo específico, como se muestra a continuación:

$ mysqldump -u root -p EJEMPLO ejemplo_tbl > dump.txt
password ******

El contenido del archivo creado por el comando anterior es el siguiente:

-- MySQL dump 8.23
--
-- Host: localhost    Database: EJEMPLO
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `ejemplo_tbl`
--

CREATE TABLE ejemplo_tbl (
  ejemplo_id int(11) NOT NULL auto_increment,
  ejemplo_title varchar(100) NOT NULL default '',
  ejemplo_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (ejemplo_id),
  UNIQUE KEY AUTHOR_INDEX (ejemplo_author)
) TYPE=MyISAM;

--
-- Dumping data for table `ejemplo_tbl`
--

INSERT INTO ejemplo_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO ejemplo_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO ejemplo_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

Si necesitas exportar los datos de toda la base de datos, puedes usar el siguiente comando:

$ mysqldump -u root -p EJEMPLO > database_dump.txt
password ******

Si necesitas hacer una copia de seguridad de todas las bases de datos, puedes usar el siguiente comando:

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

La opción --all-databases se añadió en MySQL 3.23.12 y versiones posteriores.

Este método se puede utilizar para implementar una estrategia de copia de seguridad de bases de datos.


Copiar tablas y bases de datos a otro host

Si necesitas copiar datos a otro servidor MySQL, puedes especificar el nombre de la base de datos y la tabla en el comando mysqldump.

Ejecuta el siguiente comando en el host de origen para hacer una copia de seguridad de los datos en el archivo dump.txt:

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

Si haces una copia de seguridad completa de la base de datos, no es necesario usar un nombre de tabla específico.

Si necesitas importar la base de datos respaldada a un servidor MySQL, puedes usar el siguiente comando. Con este comando debes confirmar que la base de datos ya ha sido creada:

$ mysql -u root -p database_name < dump.txt
password *****

También puedes usar el siguiente comando para importar los datos exportados directamente a un servidor remoto, pero asegúrate de que los dos servidores estén conectados y puedan acceder entre sí:

$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name

En el comando anterior se utiliza una tubería (pipe) para importar los datos exportados al host remoto especificado.

Otras extensiones