MySQL Export Data

Overview

Exporting data from MySQL is essential for backup, migration, and data sharing. MySQL provides multiple methods to export data in various formats.

Export Methods

mysqldump: Complete database export
SELECT INTO OUTFILE: Direct CSV
MySQL Workbench: GUI export
Programming APIs: Application-level export

mysqldump Export

Basic Usage

# Export single database
mysqldump -u root -p database_name > backup.sql

# Export all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Export specific tables
mysqldump -u root -p database_name table1 table2 > tables.sql

# Export with structure only
mysqldump -u root -p --no-data database_name > structure.sql

# Export with data only
mysqldump -u root -p --no-create-info database_name > data.sql

Common Options

# Export with routines (procedures, functions)
mysqldump -u root -p --routines database_name > backup.sql

# Export with triggers
mysqldump -u root -p --triggers database_name > backup.sql

# Export without triggers
mysqldump -u root -p --skip-triggers database_name > backup.sql

# Export with events
mysqldump -u root -p --events database_name > backup.sql

# Export with extended insert
mysqldump -u root -p --extended-insert database_name > backup.sql

# Export with lock tables
mysqldump -u root -p --lock-tables database_name > backup.sql

# Export with single transaction
mysqldump -u root -p --single-transaction database_name > backup.sql

Compressed Export

# Export and compress with gzip
mysqldump -u root -p database_name | gzip > backup.sql.gz

# Export and compress with bzip2
mysqldump -u root -p database_name | bzip2 > backup.sql.bz2

# Export with different compression levels
mysqldump -u root -p database_name | gzip -9 > backup.sql.gz

SELECT INTO OUTFILE / SELECT INTO OUTFILE

CSV Export

-- Export to CSV
SELECT * FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Export with headers
SELECT 
    'id' AS id,
    'name' AS name,
    'email' AS email
UNION ALL
SELECT id, name, email FROM users
INTO OUTFILE '/tmp/users_with_headers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Custom Delimiter

-- Export with tab delimiter
SELECT * FROM products
INTO OUTFILE '/tmp/products.txt'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

-- Export with pipe delimiter
SELECT * FROM orders
INTO OUTFILE '/tmp/orders.txt'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n';

Secure File Privilege

-- Check secure file priv
SHOW VARIABLES LIKE 'secure_file_priv';

-- If ON, can only export to specific directory
-- Usually /var/lib/mysql-files/ or similar

-- Export to allowed directory
SELECT * FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

Export Formats

CSV Format

-- Standard CSV export
SELECT id, name, email, created_at
FROM users
INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

JSON Format

-- Export as JSON (MySQL 5.7.8+)
SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', id,
        'name', name,
        'email', email
    )
) AS users_json
FROM users
INTO OUTFILE '/tmp/users.json';

XML Format

-- Use mysqldump for XML
mysqldump -u root -p --xml database_name > backup.xml

-- Export with specific XML format
mysqldump -u root -p --xml --skip-triggers database_name > backup.xml

Partial Exports

Filtered Export

-- Export with WHERE clause
SELECT * FROM orders
INTO OUTFILE '/tmp/recent_orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
WHERE order_date >= '2024-01-01';

-- Export specific columns
SELECT id, name, email
FROM users
INTO OUTFILE '/tmp/users_subset.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

-- Export with JOIN
SELECT 
    u.name,
    u.email,
    o.order_number,
    o.total
FROM users u
JOIN orders o ON u.id = o.user_id
INTO OUTFILE '/tmp/user_orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

mysqldump Filters

# Export with WHERE clause
mysqldump -u root -p --where="created_at > '2024-01-01'" \
    database_name > recent_data.sql

# Export ignoring specific tables
mysqldump -u root -p --ignore-table=database_name.logs \
    --ignore-table=database_name.temp_data \
    database_name > partial_export.sql

# Export excluding specific columns (requires --no-create-info)
mysqldump -u root -p --no-create-info \
    database_name > no_structure.sql

Large Data Export

Batch Export

-- Export in chunks to avoid memory issues
-- Method 1: LIMIT and OFFSET
SELECT * FROM large_table
INTO OUTFILE '/tmp/chunk1.csv'
FIELDS TERMINATED BY ','
LIMIT 100000;

SELECT * FROM large_table
INTO OUTFILE '/tmp/chunk2.csv'
FIELDS TERMINATED BY ','
LIMIT 100000 OFFSET 100000;

-- Method 2: Date range
SELECT * FROM orders
INTO OUTFILE '/tmp/orders_q1.csv'
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

SELECT * FROM orders
INTO OUTFILE '/tmp/orders_q2.csv'
WHERE order_date BETWEEN '2024-04-01' AND '2024-06-30';

Progress Monitoring

# Use pv to monitor progress
mysqldump -u root -p database_name | pv -p -s $(mysql -u root -p -e "SELECT SUM(data_length+index_length)/1024/1024 FROM information_schema.tables WHERE table_schema='database_name'"M) | gzip > backup.sql.gz

# Count exported rows
wc -l backup.sql

Export from Replication

Consistent Export

# Export from slave to reduce load on master
# Ensure slave is consistent
STOP SLAVE;

# Export data
mysqldump -u root -p --master-data=2 \
    --single-transaction database_name > backup.sql

# Restart slave
START SLAVE;

Export Best Practices

Security Considerations

# Encrypt exported data
mysqldump -u root -p database_name | gpg --encrypt > backup.sql.gpg

# Set file permissions
chmod 600 backup.sql

# Export to secure location
mysqldump -u root -p database_name > /secure/backup/backup.sql

# Remove sensitive data before export
SELECT id, name, email, created_at
-- Exclude password, credit_card, etc.
FROM users
INTO OUTFILE '/tmp/users_anonymized.csv'
FIELDS TERMINATED BY ',';

Export Scheduling

# Create cron job for daily backup
# Edit crontab
0 2 * * * mysqldump -u root -p --all-databases | gzip > /backups/daily_$(date +\%Y\%m\%d).sql.gz

# Weekly full backup
0 3 * * 0 mysqldump -u root -p --all-databases --routines --triggers | gzip > /backups/weekly_$(date +\%Y\%m\%d).sql.gz

Summary

MySQL data export methods include:

mysqldump: Complete database dumps
SELECT INTO OUTFILE: Direct file export
Multiple Formats: SQL, CSV, JSON, XML
Filtered Export: Partial data export
Compressed Export: Save space

Choose appropriate method based on your needs: complete backup, partial export, or format requirements.

Previous: SQL Injection

Next: Import Data

#MySQL Export Data

#Overview

#Export Methods

#mysqldump Export

#Basic Usage

#Common Options

#Compressed Export

#SELECT INTO OUTFILE / SELECT INTO OUTFILE

#CSV Export

#Custom Delimiter

#Secure File Privilege

#Export Formats

#CSV Format

#JSON Format

#XML Format

#Partial Exports

#Filtered Export

#mysqldump Filters

#Large Data Export

#Batch Export

#Progress Monitoring

#Export from Replication

#Consistent Export

#Export Best Practices

#Security Considerations

#Export Scheduling

#Summary