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

bash

# 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

bash

# 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

bash

# 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

sql

-- 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

sql

-- 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

sql

-- 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

sql

-- 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

sql

-- 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

sql

-- 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

sql

-- 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

bash

# 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

sql

-- 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

bash

# 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

bash

# 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

bash

# 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

bash

# 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 ​

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