📦 Lesson 20: Importing & Exporting Data

The Big Picture: Why Import/Export?

Here are the most common scenarios where you'll need to move data in or out of MySQL:

Exporting with mysqldump

mysqldump is the most important backup tool in the MySQL toolkit. It creates a .sql file containing all the SQL statements needed to recreate your database — CREATE TABLE, INSERT INTO, indexes, constraints, everything. Run it from your terminal (not inside the MySQL prompt).

Dump an Entire Database

# Dump the entire bookstore database to a file
mysqldump -u root -p bookstore > bookstore_backup.sql
                

This creates a file called bookstore_backup.sql in your current directory. The file contains:

• DROP TABLE IF EXISTS statements (so the restore can replace existing tables)
• CREATE TABLE statements with all columns, constraints, and indexes
• INSERT INTO statements with all row data
• View definitions, triggers, and routines (if present)

Dump Specific Tables

# Dump only the books and authors tables
mysqldump -u root -p bookstore books authors > books_authors_backup.sql
                

Dump Multiple Databases

# Dump two databases into one file
mysqldump -u root -p --databases bookstore school > multi_db_backup.sql
                

Dump All Databases

# Dump every database on the server (full server backup)
mysqldump -u root -p --all-databases > full_server_backup.sql
                

Structure Only (No Data)

# Dump table definitions only — useful for creating empty copies
mysqldump -u root -p --no-data bookstore > bookstore_schema.sql
                

Data Only (No Structure)

# Dump row data only — tables must already exist at the destination
mysqldump -u root -p --no-create-info bookstore > bookstore_data.sql
                

Useful Options

Production-Quality Dump

# A robust backup command for InnoDB databases
mysqldump -u root -p \
    --single-transaction \
    --routines \
    --triggers \
    --databases bookstore \
    > bookstore_$(date +%Y%m%d_%H%M%S).sql
                

The $(date +%Y%m%d_%H%M%S) part adds a timestamp to the filename, producing something like bookstore_20260416_143000.sql. This makes it easy to identify when each backup was taken.

Restoring from a Dump File

A .sql dump file is just a script full of SQL statements. To restore it, you feed it into the mysql client.

Restore an Entire Database

# Create the database first (if the dump doesn't include CREATE DATABASE)
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS bookstore;"

# Restore the dump into the bookstore database
mysql -u root -p bookstore < bookstore_backup.sql
                

Restore from a Multi-Database Dump

# If the dump used --databases or --all-databases, it includes
# CREATE DATABASE statements — no need to specify a database name
mysql -u root -p < multi_db_backup.sql
                

Restore a Single Table

If you only need one table from a full database dump, you can't directly extract it with the mysql client. Instead, use grep or a text editor to pull out the relevant section, or use a purpose-built approach:

# Dump just the one table you need (easier than extracting from a full dump)
mysqldump -u root -p bookstore books > books_only.sql

# Then restore it
mysql -u root -p bookstore < books_only.sql
                

Test Your Backups!

A backup you've never restored is a backup you can't trust. Periodically test your dumps:

# Create a temporary test database
mysql -u root -p -e "CREATE DATABASE bookstore_test;"

# Restore the backup into the test database
mysql -u root -p bookstore_test < bookstore_backup.sql

# Verify the data
mysql -u root -p -e "SELECT COUNT(*) FROM bookstore_test.books;"

# Clean up
mysql -u root -p -e "DROP DATABASE bookstore_test;"
                

Exporting to CSV

CSV (Comma-Separated Values) files are the universal data exchange format. Everyone can open them — Excel, Google Sheets, Python, R, other databases. MySQL offers two main ways to export CSV.

Method 1: SELECT ... INTO OUTFILE

Run this inside the MySQL prompt:

-- Export all books to a CSV file
SELECT id, title, price, publication_year
INTO OUTFILE '/tmp/books_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM books;
                

This creates a clean CSV file at /tmp/books_export.csv:

"1","The Great Gatsby","12.99","1925"
"2","To Kill a Mockingbird","14.99","1960"
"3","1984","11.99","1949"
                    

Adding a Header Row

INTO OUTFILE doesn't include column headers by default. Use a UNION to add them:

-- Export with a header row
SELECT 'id', 'title', 'price', 'publication_year'
UNION ALL
SELECT id, title, price, publication_year
INTO OUTFILE '/tmp/books_with_header.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM books;
                

Method 2: mysql Client with Redirect

From the terminal (not inside MySQL), use the -e flag with output redirection:

# Tab-separated output (default)
mysql -u root -p bookstore -e "SELECT id, title, price FROM books;" > books.tsv

# For true CSV, pipe through sed or tr
mysql -u root -p bookstore -e "SELECT id, title, price FROM books;" \
    | tr '\t' ',' > books.csv

# Batch mode with no headers
mysql -u root -p -N bookstore -e "SELECT id, title, price FROM books;" \
    | tr '\t' ',' > books_no_header.csv
                

Check secure_file_priv

MySQL restricts where INTO OUTFILE and LOAD DATA INFILE can read/write files. Check this setting:

SHOW VARIABLES LIKE 'secure_file_priv';
                

+------------------+------------------------+
| Variable_name    | Value                  |
+------------------+------------------------+
| secure_file_priv | /var/lib/mysql-files/   |  -- Can only use this directory
| secure_file_priv |                        |  -- Empty = any directory
| secure_file_priv | NULL                   |  -- Disabled entirely
+------------------+------------------------+
                    

Importing from CSV

Got a spreadsheet full of data? Save it as CSV and load it directly into MySQL with LOAD DATA INFILE.

Basic CSV Import

-- Import a CSV file into the books table
LOAD DATA INFILE '/tmp/new_books.csv'
INTO TABLE books
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;    -- Skip the header row
                

The IGNORE 1 ROWS tells MySQL to skip the first line — which is typically column headers in a CSV.

Map CSV Columns to Table Columns

If the CSV columns don't match the table columns in order (or you only want to import some columns):

-- CSV has: title, author_name, price, year
-- Table has: id (auto_increment), title, author_id, price, publication_year
LOAD DATA INFILE '/tmp/new_books.csv'
INTO TABLE books
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title, @author_name, price, publication_year)
SET author_id = (SELECT id FROM authors WHERE name = @author_name LIMIT 1);
                

The @author_name is a user variable — the CSV value is loaded into it, and the SET clause uses a subquery to look up the matching author_id.

Using LOAD DATA LOCAL INFILE

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

-- Load from the client's local filesystem
LOAD DATA LOCAL INFILE '/home/practicalace/data/new_books.csv'
INTO TABLE books
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
                

# Check if it's enabled
mysql -u root -p -e "SHOW VARIABLES LIKE 'local_infile';"

# Enable it on the server (requires restart or SET GLOBAL)
SET GLOBAL local_infile = 1;

# Connect with LOCAL enabled
mysql -u root -p --local-infile=1 bookstore
                    

Handling Common CSV Issues

Handling NULLs and Empty Values

-- Treat empty strings as NULL for specific columns
LOAD DATA INFILE '/tmp/books.csv'
INTO TABLE books
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title, @price, @pub_year)
SET
    price = NULLIF(@price, ''),
    publication_year = NULLIF(@pub_year, '');
                

mysqlimport (CLI CSV Loading)

mysqlimport is a command-line wrapper around LOAD DATA INFILE. It's convenient for quick imports from the terminal.

Basic Usage

# The filename (minus extension) must match the table name!
# books.csv → imported into the "books" table
mysqlimport -u root -p \
    --local \
    --fields-terminated-by=',' \
    --fields-enclosed-by='"' \
    --lines-terminated-by='\n' \
    --ignore-lines=1 \
    bookstore /tmp/books.csv
                

Useful mysqlimport Options

Import/Export with phpMyAdmin

If you prefer a GUI, phpMyAdmin provides point-and-click import and export. This is especially handy for quick one-off tasks.

Exporting via phpMyAdmin

1. Select your database in the left sidebar
2. Click the Export tab
3. Choose export method:
   • Quick — exports everything with defaults (usually fine)
   • Custom — pick tables, format, options
4. Choose format: SQL (for backups) or CSV (for spreadsheets)
5. Click Go — the file downloads to your computer

Importing via phpMyAdmin

1. Select your database in the left sidebar
2. Click the Import tab
3. Click Choose File and select your .sql or .csv file
4. For CSV imports, set the format options (delimiter, enclosure, escape character)
5. Click Go

Backup Strategies

Having the tools is one thing — using them wisely is another. A good backup strategy answers: How often? Where do I store them? How long do I keep them? How fast can I restore?

The 3-2-1 Rule

A widely-used framework for data protection:

• 3 copies of your data (the original + 2 backups)
• 2 different storage types (e.g., local disk + cloud)
• 1 copy offsite (in case of physical disaster)

Automated Daily Backups (Cron Job)

On Linux, use cron to schedule automatic backups:

# Create a backup script
# File: /home/practicalace/scripts/mysql_backup.sh

#!/bin/bash
BACKUP_DIR="/home/practicalace/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="bookstore"

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

# Dump the database
mysqldump -u backup_user -pBackupPass123 \
    --single-transaction \
    --routines \
    --triggers \
    "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql"

# Compress the backup
gzip "$BACKUP_DIR/${DB_NAME}_${DATE}.sql"

# Delete backups older than 30 days
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +30 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql.gz"
                

# Make the script executable
chmod +x /home/practicalace/scripts/mysql_backup.sh

# Add to crontab (runs daily at 2:00 AM)
crontab -e
# Add this line:
0 2 * * * /home/practicalace/scripts/mysql_backup.sh >> /home/practicalace/logs/backup.log 2>&1
                

# File: ~/.my.cnf (chmod 600!)
[mysqldump]
user=backup_user
password=BackupPass123
                    

Backup Comparison

Common Issues & Troubleshooting

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE my_table
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
                    

Exercises

# 1. Full dump
mysqldump -u root -p bookstore > bookstore_backup.sql

# 2. Single table
mysqldump -u root -p bookstore books > books_only.sql

# 3. Schema only
mysqldump -u root -p --no-data bookstore > bookstore_schema.sql

# 4. Create test DB and restore
mysql -u root -p -e "CREATE DATABASE bookstore_test;"
mysql -u root -p bookstore_test < bookstore_backup.sql

# 5. Verify
mysql -u root -p -e "SELECT COUNT(*) AS total_books FROM bookstore_test.books;"

# 6. Clean up
mysql -u root -p -e "DROP DATABASE bookstore_test;"
                        

-- 1. Check the allowed directory
SHOW VARIABLES LIKE 'secure_file_priv';
-- Let's assume: /var/lib/mysql-files/

-- 2. Export via INTO OUTFILE
SELECT id, title, price
INTO OUTFILE '/var/lib/mysql-files/books_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM books;
                        

# 3. Alternative: mysql client redirect
mysql -u root -p bookstore \
    -e "SELECT id, title, price FROM books;" \
    | tr '\t' ',' > ~/books_export.csv
                        

# 1-2. Create the CSV file
cat > /tmp/new_authors.csv << 'EOF'
name,country,birth_year
"Gabriel García Márquez","Colombia",1927
"Haruki Murakami","Japan",1949
"Chimamanda Ngozi Adichie","Nigeria",1977
"Isabel Allende","Chile",1942
EOF
                        

-- 3. Create the target table
CREATE TABLE imported_authors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(50),
    birth_year INT
);

-- 4. Import the CSV
LOAD DATA INFILE '/tmp/new_authors.csv'
INTO TABLE imported_authors
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(name, country, birth_year);

-- 5. Verify
SELECT * FROM imported_authors;
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — mysqldump
• MySQL Docs — LOAD DATA Statement
• MySQL Docs — SELECT ... INTO OUTFILE
• MySQL Docs — mysqlimport
• MySQL Docs — Backup and Recovery

🚀 What's Next?

In the final lesson, Lesson 21: Next Steps & Best Practices, we'll review everything you've learned across the entire course, highlight key best practices for working with MySQL, and map out where to go from here — from PHP and Python connectors to ORMs and beyond.