18. Import and Export Basics

Import and Export Basics

Data engineering often starts with moving data in and out of databases. MySQL supports CSV-style imports and command-line backups.

Prepare a table for CSV import

CREATE TABLE customers_import (
  customer_id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  city VARCHAR(100)
);

Example CSV shape:

customer_id,first_name,last_name,email,city
1,John,Doe,john.doe@example.com,Austin
2,Jane,Smith,jane.smith@example.com,Metropolis

LOAD DATA

LOAD DATA imports a file into a table.

LOAD DATA LOCAL INFILE "/path/to/customers.csv"
INTO TABLE customers_import
FIELDS TERMINATED BY ","
ENCLOSED BY '"'
LINES TERMINATED BY "\n"
IGNORE 1 ROWS
(customer_id, first_name, last_name, email, city);

IGNORE 1 ROWS skips the header row.

Export with SELECT INTO OUTFILE

Some MySQL setups allow exporting query results to a file on the server.

SELECT customer_id, first_name, last_name, email, city
FROM customers
INTO OUTFILE "/tmp/customers_export.csv"
FIELDS TERMINATED BY ","
ENCLOSED BY '"'
LINES TERMINATED BY "\n";

This depends on server permissions and the secure_file_priv setting.

Backup with mysqldump

mysqldump is a command-line tool, not a SQL statement.

mysqldump -u root -p companydb > companydb_backup.sql

Back up one table:

mysqldump -u root -p companydb customers > customers_backup.sql

Restore from a dump

mysql -u root -p companydb < companydb_backup.sql

Import/export habits

• Check column order before importing.
• Import into a staging table first when the file is messy.
• Validate row counts after import.
• Keep backups before destructive migrations or bulk updates.
• Never commit real credentials or sensitive dumps to source control.