Importing & Exporting Databases

Introduction

Data rarely lives in just one place. You might need to hand a dataset to a colleague, load a spreadsheet of new products into your store's database, or move data between two separate MySQL servers. All of this comes down to importing and exporting.

This lesson covers the two core SQL commands for moving data through CSV files — SELECT ... INTO OUTFILE and LOAD DATA INFILE — as well as MySQL Workbench's friendlier, GUI-based wizard for the same job.

Why Import and Export Data?

CSV (Comma-Separated Values) is one of the most universal data formats — it can be opened by Excel, Google Sheets, most programming languages, and virtually every database system. Exporting to CSV, or importing from it, is how MySQL exchanges data with the rest of the world.

Exporting Data with SELECT INTO OUTFILE

The SELECT ... INTO OUTFILE statement runs a normal SELECT query, but instead of returning the results to your screen, it writes them straight into a CSV file on the MySQL server's disk.

SELECT id, name, email
FROM customers
INTO OUTFILE '/var/lib/mysql-files/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

"1","Alex Turner","alex@example.com"
"2","Priya Shah","priya@example.com"
"3","Sam Lee","sam@example.com"

FIELDS TERMINATED BY sets the delimiter between columns (a comma, for a standard CSV). ENCLOSED BY wraps each value in quotes, which helps if any value itself contains a comma. LINES TERMINATED BY marks the end of each row.

Importing Data with LOAD DATA INFILE

LOAD DATA INFILE is the reverse operation: it reads a CSV file from disk and inserts its rows directly into a table, in bulk. It is dramatically faster than inserting rows one at a time with individual INSERT statements.

Suppose a supplier sends you a CSV of new products to add to your catalog.

"101","Wireless Mouse","19.99"
"102","USB-C Cable","9.50"
"103","Laptop Stand","34.00"

LOAD DATA INFILE '/var/lib/mysql-files/new_products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, name, price);

SELECT * FROM products WHERE id >= 101;

+-----+-----------------+-------+
| id  | name            | price |
+-----+-----------------+-------+
| 101 | Wireless Mouse  | 19.99 |
| 102 | USB-C Cable     | 9.50  |
| 103 | Laptop Stand    | 34.00 |
+-----+-----------------+-------+

The FIELDS and LINES clauses must describe the file's actual format so MySQL can correctly split it back into columns — they should match whatever was used to create the file in the first place.

Using the Workbench Wizard

Writing INTO OUTFILE and LOAD DATA INFILE by hand works well for automation and scripts, but MySQL Workbench also offers a friendlier, entirely mouse-driven alternative: the Table Data Export/Import Wizard.

To use it in Workbench: right-click any table in the schema list on the left, and choose "Table Data Export Wizard" to save its contents to a CSV or JSON file, or "Table Data Import Wizard" to load a CSV or JSON file into that table. The wizard walks you through selecting the file, matching columns, and confirming the import.

Common Use Cases

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Moving data in and out of MySQL is a routine part of real-world database work, whether through SQL commands or Workbench's visual wizard.

In this lesson, you learned how to export data to CSV with SELECT INTO OUTFILE, import it back with LOAD DATA INFILE, and use MySQL Workbench's wizard as a friendlier alternative. Next, you will learn how to protect your entire database with proper backups.