MySQL Import and Export Data

MySQL provides built-in tools to import data from external files into tables and export data from tables into external files. The most common formats used are CSV (Comma-Separated Values) and SQL files. This is essential for data migration, bulk loading, and sharing data between systems.

Exporting Data with SELECT INTO OUTFILE

SELECT INTO OUTFILE writes the result of a SELECT query directly to a file on the MySQL server's file system.

Syntax

SELECT column1, column2
INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

Example: Export Students to CSV

SELECT student_id, student_name, class
INTO OUTFILE '/var/lib/mysql-files/students.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM students;

The exported file looks like:

"1","Riya Sharma","9A"
"2","Anil Joshi","10B"
"3","Meera Pillai","8C"

Importing Data with LOAD DATA INFILE

LOAD DATA INFILE reads data from a file and inserts it into a table. It is significantly faster than running individual INSERT statements for large datasets.

Syntax

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

IGNORE 1 ROWS skips the header row if the CSV file has column names as the first line.

Example: Import Students from CSV

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

LOAD DATA LOCAL INFILE

If the file is on the client machine (not the server), use LOCAL:

LOAD DATA LOCAL INFILE '/home/user/students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Exporting with mysqldump (SQL Format)

For full SQL export (structure + data):

mysqldump -u root -p school_db students > students_export.sql

Importing SQL Files

mysql -u root -p school_db < students_export.sql

Export with Tab-Separated Values

SELECT * FROM employees
INTO OUTFILE '/var/lib/mysql-files/employees.tsv'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

Common Import/Export Options

File Path Note

MySQL's INTO OUTFILE and LOAD DATA INFILE require the file to be in a directory that MySQL has read/write permission to. On most systems this is /var/lib/mysql-files/. The secure file path setting can be checked with:

SHOW VARIABLES LIKE 'secure_file_priv';

Key Points

• SELECT INTO OUTFILE exports query results to a file on the server.
• LOAD DATA INFILE bulk-loads data from a file into a table — much faster than individual INSERTs.
• The file must reside in a directory allowed by MySQL's secure_file_priv setting.
• Use IGNORE 1 ROWS to skip CSV header lines during import.
• mysqldump exports to SQL format; OUTFILE exports to CSV or TSV format.