MySQL导出数据

概述

从MySQL导出数据对于备份、迁移和数据共享至关重要。MySQL提供了多种方法以不同格式导出数据。

导出方法

mysqldump：完整数据库导出 Text export / SELECT INTO OUTFILE：直接CSV/文本导出 MySQL Workbench：GUI导出 编程API：应用程序级导出

mysqldump导出

基本用法

# Export single database

# Export all databases

# Export specific tables

# Export with structure only

# Export with data only

常用选项

# Export with routines (procedures, functions)

# Export with triggers

# Export without triggers

# Export with events

# Export with extended insert

# Export with lock tables

# Export with single transaction

压缩导出

# Export and compress with gzip

# Export and compress with bzip2

# Export with different compression levels

SELECT INTO OUTFILE / SELECT INTO OUTFILE

CSV导出

-- Export to CSV

-- Export with headers

自定义分隔符

-- Export with tab delimiter

-- Export with pipe delimiter

安全文件权限

-- Check secure file priv

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

-- Export to allowed directory

导出格式

CSV格式

-- Standard CSV export

JSON格式

-- Export as JSON (MySQL 5.7.8+)

XML格式

-- Use mysqldump for XML

-- Export with specific XML format

部分导出

过滤导出

-- Export with WHERE clause

-- Export specific columns

-- Export with JOIN

mysqldump过滤器

# Export with WHERE clause

# Export ignoring specific tables
    --ignore-table=database_name.temp_data \

# Export excluding specific columns (requires --no-create-info)

大数据导出

批量导出

-- Export in chunks to avoid memory issues
-- Method 1: LIMIT and OFFSET

-- Method 2: Date range

进度监控

# Use pv to monitor progress

# Count exported rows

从复制导出

一致导出

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

# Export data
    --single-transaction database_name > backup.sql

# Restart slave

导出最佳实践

安全考虑

# Encrypt exported data

# Set file permissions

# Export to secure location

# Remove sensitive data before export
-- Exclude password, credit_card, etc.

导出计划

# Create cron job for daily backup
# Edit crontab

# Weekly full backup

小结

MySQL数据导出方法包括：

mysqldump：完整数据库转储 SELECT INTO OUTFILE：直接文件导出 多种格式：SQL、CSV、JSON、XML 过滤导出：部分数据导出 压缩导出：节省空间

根据您的需求选择适当的方法：完整备份、部分导出或格式要求。

---

上一个：SQL注入

下一个：导入数据