Skip to content
DBAExperts
MySQL Migration & data

How to export and import data in MySQL with LOAD DATA and OUTFILE

To move large volumes of data, delimited formats (CSV) with LOAD DATA INFILE are much faster than the one-by-one INSERTs of a dump. The counterpart for exporting is SELECT ... INTO OUTFILE. Both require attention to server permissions and paths.

  1. 1

    Check where writing and reading files is allowed

    The secure_file_priv variable restricts the folder that OUTFILE and INFILE can use. If it is empty or NULL, the behavior changes; check it first.

    SHOW VARIABLES LIKE 'secure_file_priv';
  2. 2

    Export data to a CSV file

    The file is created on the server, not on your client machine, and inside the folder allowed by secure_file_priv.

    SELECT * FROM clientes
INTO OUTFILE '/var/lib/mysql-files/clientes.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
  3. 3

    Import a CSV into a table

    LOAD DATA INFILE is the fast path for loading millions of rows. The target table must already exist with compatible columns.

    LOAD DATA INFILE '/var/lib/mysql-files/clientes.csv'
INTO TABLE clientes
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
  4. 4

    If the file is on your machine, use LOCAL

    LOAD DATA LOCAL INFILE reads from the client, not the server. It requires the local_infile option to be enabled on both sides.

    LOAD DATA LOCAL INFILE 'C:/datos/clientes.csv'
INTO TABLE clientes
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

// common mistake

By default, LOAD DATA INFILE does not validate integrity the way your application would: malformed rows load with silent warnings and truncated values. Always check SHOW WARNINGS after the load and validate the row count against the source.

// when it's worth an expert

Migrating data between systems with different encodings, types, and keys is where bulk loads get complicated. At dba.mx we plan migrations with data validation and rollback, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.