垦荒学园
  • 中文

      • 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注入

      下一个：导入数据