主业    

MySQL 命令行操作完整帮助文档

2 次访问
SoftwareSciencer
Scroll Down

MySQL 命令行操作完整帮助文档

📖 目录

  1. 快速导出CSV
  2. 数据导入MySQL
  3. 执行大文件SQL
  4. 常用维护命令
  5. 性能优化建议

1. 快速导出CSV

1.1 基础导出命令

# 基本格式
mysql -u用户名 -p密码 -D数据库名 -e "查询语句" > 输出文件.csv

# 示例：导出整个表
mysql -uroot -p123456 -Dmydb -e "SELECT * FROM users" > users.csv

# 示例：导出特定字段
mysql -uroot -p123456 -Dmydb -e "SELECT id, name, email FROM customers" > customers.csv

1.2 格式化CSV导出

# 方法1：使用sed格式化（标准CSV格式）
mysql -uroot -p123456 -Dmydb -e "SELECT * FROM table" | 
  sed 's/\t/","/g; s/^/"/; s/$/"/; s/\r//g;' > output.csv

# 方法2：使用tr替换制表符（简单CSV）
mysql -uroot -p123456 -Dmydb -e "SELECT * FROM table" | 
  tr '\t' ',' > output.csv

# 方法3：包含表头的格式化
mysql -uroot -p123456 -Dmydb --batch -e "SELECT * FROM table" | 
  sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > output.csv

1.3 使用INTO OUTFILE（高性能导出）

# 需要FILE权限，数据保存在MySQL服务器上
mysql -uroot -p123456 -Dmydb -e "
SELECT * FROM orders
INTO OUTFILE '/tmp/orders_export.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"'
LINES TERMINATED BY '\n';
"

# 导出到远程服务器的本地
mysql -uroot -p123456 -h远程IP -Dmydb -e "
SELECT * FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
" && scp user@远程IP:/tmp/orders.csv ./local/

1.4 复杂查询导出

# 多表关联导出
mysql -uroot -p123456 -Dmydb -e "
SELECT u.id, u.name, o.order_date, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
" > user_orders.csv

# 带条件导出
mysql -uroot -p123456 -Dmydb -e "
SELECT * FROM logs 
WHERE create_time > '2024-01-01' 
ORDER BY create_time DESC
" > recent_logs.csv

1.5 压缩导出

# 直接压缩
mysql -uroot -p123456 -Dmydb -e "SELECT * FROM large_table" | gzip > data.csv.gz

# 分步压缩
mysql -uroot -p123456 -Dmydb -e "SELECT * FROM large_table" > data.csv && gzip data.csv

# 使用pigz多线程压缩（更快）
mysql -uroot -p123456 -Dmydb -e "SELECT * FROM large_table" | pigz > data.csv.gz

1.6 分块导出大数据

#!/bin/bash
# 分页导出大表
LIMIT=100000
OFFSET=0
PAGE=1

while true; do
  mysql -uroot -p123456 -Dmydb -e "
    SELECT * FROM huge_table 
    LIMIT $LIMIT OFFSET $OFFSET
  " > "data_part_$PAGE.csv"
  
  # 检查是否还有数据
  if [ $(wc -l < "data_part_$PAGE.csv") -le 1 ]; then
    rm "data_part_$PAGE.csv"
    break
  fi
  
  OFFSET=$((OFFSET + LIMIT))
  PAGE=$((PAGE + 1))
done

2. 数据导入MySQL

2.1 使用LOAD DATA INFILE（高性能导入）

# 基本格式
mysql -uroot -p123456 -Dmydb -e "
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE target_table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;  -- 忽略标题行
"

# 示例：导入用户数据
mysql -uroot -p123456 -Dmydb -e "
LOAD DATA INFILE '/tmp/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, name, email, created_at);
"

2.2 命令行管道导入

# CSV导入（使用sed处理格式）
cat data.csv | sed 's/","/\t/g; s/^"//; s/"$//' | 
mysql -uroot -p123456 -Dmydb -e "LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE mytable"

# TSV文件直接导入
cat data.tsv | mysql -uroot -p123456 -Dmydb -e "
LOAD DATA LOCAL INFILE '/dev/stdin' 
INTO TABLE mytable
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
"

2.3 使用mysqlimport工具

# 基本格式
mysqlimport -u用户名 -p密码 --local 数据库名 文件名.csv

# 示例
mysqlimport -uroot -p123456 --local --fields-terminated-by=, --ignore-lines=1 mydb /tmp/users.csv

# 常用参数
mysqlimport -uroot -p123456 \
  --local \
  --fields-terminated-by=, \
  --fields-enclosed-by='"' \
  --lines-terminated-by='\n' \
  --ignore-lines=1 \
  --columns="id,name,email" \
  mydb /tmp/data.csv

2.4 导入压缩文件

# 解压并导入
gunzip -c data.csv.gz | mysql -uroot -p123456 -Dmydb -e "
LOAD DATA LOCAL INFILE '/dev/stdin'
INTO TABLE mytable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
"

# 一步完成
zcat data.csv.gz | sed '1d' | mysql -uroot -p123456 -Dmydb -e "
LOAD DATA LOCAL INFILE '/dev/stdin'
INTO TABLE mytable
FIELDS TERMINATED BY ','
"

2.5 导入时数据转换

# 导入时修改数据
mysql -uroot -p123456 -Dmydb -e "
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(@col1, @col2, @col3)
SET 
  id = NULL,  -- 自增ID
  name = UPPER(@col1),
  email = LOWER(@col2),
  age = @col3 * 1,  -- 类型转换
  created_at = NOW();
"

2.6 导入前预处理脚本

#!/bin/bash
# 预处理并导入
INPUT_FILE="data.csv"
TEMP_FILE="$(mktemp)"

# 清理数据
cat "$INPUT_FILE" | \
  sed 's/\"\"/\\\"\"/g' | \  # 处理双引号
  iconv -f GBK -t UTF-8//IGNORE | \  # 转换编码
  grep -v '^#' > "$TEMP_FILE"  # 移除注释行

# 导入清理后的数据
mysql -uroot -p123456 -Dmydb -e "
LOAD DATA LOCAL INFILE '$TEMP_FILE'
INTO TABLE mytable
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
"

# 清理临时文件
rm "$TEMP_FILE"

3. 执行大文件SQL

3.1 基础执行方式

# 方法1：直接执行
mysql -uroot -p123456 -Dmydb < large_file.sql

# 方法2：使用source命令
mysql -uroot -p123456
mysql> USE mydb;
mysql> SOURCE /path/to/large_file.sql;

# 方法3：带进度显示
pv large_file.sql | mysql -uroot -p123456 -Dmydb

3.2 分割大文件执行

# 按文件大小分割
split -l 10000 large_file.sql chunk_

# 依次执行分割文件
for file in chunk_*; do
  echo "Processing $file..."
  mysql -uroot -p123456 -Dmydb < "$file"
  rm "$file"
done

3.3 使用mysqlslap测试性能

# 测试SQL文件执行性能
mysqlslap -uroot -p123456 \
  --concurrency=50 \
  --iterations=10 \
  --query=large_file.sql \
  --create-schema=mydb \
  --verbose

3.4 分批次执行大事务

#!/bin/bash
# 分批次执行大更新
BATCH_SIZE=10000
MAX_ID=$(mysql -uroot -p123456 -Dmydb -N -e "SELECT MAX(id) FROM huge_table")
BATCHES=$(( (MAX_ID + BATCH_SIZE - 1) / BATCH_SIZE ))

for ((i=0; i<BATCHES; i++)); do
  OFFSET=$((i * BATCH_SIZE))
  echo "Processing batch $((i+1))/$BATCHES..."
  
  mysql -uroot -p123456 -Dmydb <<EOF
    START TRANSACTION;
    UPDATE huge_table 
    SET status = 'processed'
    WHERE id BETWEEN $OFFSET AND $((OFFSET + BATCH_SIZE - 1))
      AND status = 'pending';
    COMMIT;
EOF
  
  sleep 1  # 避免锁竞争
done

3.5 执行时监控进度

#!/bin/bash
# 带进度监控的执行
SQL_FILE="large_update.sql"
TOTAL_LINES=$(wc -l < "$SQL_FILE")
PROGRESS_FILE="/tmp/mysql_progress"

# 使用awk添加进度信息
awk -v total="$TOTAL_LINES" '
{
  # 每1000行输出一次进度
  if (NR % 1000 == 0) {
    printf "Progress: %.2f%% (%d/%d)\n", (NR/total)*100, NR, total > "/dev/stderr"
  }
  print $0
}
' "$SQL_FILE" | mysql -uroot -p123456 -Dmydb --show-warnings

3.6 使用pt-online-schema-change（在线修改大表）

# 安装percona工具包后使用
pt-online-schema-change \
  --user=root \
  --password=123456 \
  --host=localhost \
  --database=mydb \
  --table=huge_table \
  --alter="ADD COLUMN new_column INT NULL" \
  --execute \
  --no-drop-old-table

3.7 并行执行多个SQL文件

#!/bin/bash
# 并行执行脚本
SQL_DIR="/path/to/sql/files"
MAX_JOBS=4  # 最大并行数

# 使用GNU parallel并行执行
ls "$SQL_DIR"/*.sql | parallel -j $MAX_JOBS "
  echo 'Processing {}'
  mysql -uroot -p123456 -Dmydb < {} 2>&1 | tee {}.log
"

4. 常用维护命令

4.1 数据库备份与恢复

# 备份单个数据库
mysqldump -uroot -p123456 --single-transaction --routines mydb > mydb_backup.sql

# 备份所有数据库
mysqldump -uroot -p123456 --all-databases --single-transaction > all_dbs.sql

# 压缩备份
mysqldump -uroot -p123456 mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

# 只备份结构
mysqldump -uroot -p123456 --no-data mydb > mydb_schema.sql

# 只备份数据
mysqldump -uroot -p123456 --no-create-info mydb > mydb_data.sql

# 恢复数据库
mysql -uroot -p123456 -Dmydb < backup.sql
gunzip -c backup.sql.gz | mysql -uroot -p123456 -Dmydb

4.2 数据库状态监控

# 查看进程列表
mysql -uroot -p123456 -e "SHOW PROCESSLIST;"

# 查看锁信息
mysql -uroot -p123456 -e "SHOW ENGINE INNODB STATUS\G"

# 查看表状态
mysql -uroot -p123456 -Dmydb -e "SHOW TABLE STATUS LIKE 'table_name';"

# 查看数据库大小
mysql -uroot -p123456 -e "
SELECT 
  table_schema AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
"

4.3 性能分析

# 开启慢查询日志分析
mysql -uroot -p123456 -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
"

# 使用mysqldumpslow分析慢查询
mysqldumpslow -s t /var/log/mysql/slow.log | head -20

# 使用pt-query-digest（percona工具）
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

5. 性能优化建议

5.1 导出优化

# 1. 使用INTO OUTFILE代替管道（性能最好）
# 2. 大表导出时添加索引条件减少锁表时间
# 3. 在从库上执行导出操作
# 4. 分批导出大数据，使用LIMIT和OFFSET

5.2 导入优化

# 1. 导入前禁用索引和外键约束
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

# 2. 使用LOAD DATA INFILE而不是INSERT
# 3. 导入后重新创建索引
ALTER TABLE table_name ENABLE KEYS;

# 4. 增大缓冲区大小
mysql --max_allowed_packet=1G --net_buffer_length=1000000

5.3 大文件SQL执行优化

# 1. 拆分大文件，分批执行
# 2. 关闭二进制日志
mysql --skip-log-bin

# 3. 增加超时时间
mysql --connect_timeout=3600 --net_read_timeout=3600

# 4. 使用事务包裹（适当大小）
START TRANSACTION;
-- 执行一批SQL
COMMIT;

5.4 配置文件优化

# my.cnf 相关配置
[mysqld]
# 导入导出相关
max_allowed_packet = 1G
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
bulk_insert_buffer_size = 256M

# 连接相关
max_connections = 500
wait_timeout = 600
interactive_timeout = 600

# 查询缓存
query_cache_type = 0  # 对于频繁更新的大表建议关闭

📋 快速参考速查表

# 导出速查
mysql -uuser -p -Ddb -e "SELECT ..." > file.csv                  # 简单导出
mysql -uuser -p -Ddb -e "SELECT ..." | sed 's/\t/,/g' > file.csv # CSV格式化
mysqldump -uuser -p db table > backup.sql                        # 备份表

# 导入速查
mysql -uuser -p db < file.sql                                    # 执行SQL文件
mysqlimport -uuser -p --local db file.csv                        # 导入CSV
mysql -uuser -p -e "LOAD DATA INFILE 'file.csv' INTO TABLE ..."  # 高性能导入

# 实用管道组合
cat data.csv | grep -v 'NULL' | mysql -uuser -p -Ddb -e "LOAD DATA ..."  # 过滤后导入
mysql -uuser -p -Ddb -e "SELECT ..." | gzip > data.csv.gz                # 压缩导出
zcat backup.sql.gz | mysql -uuser -p db                                  # 压缩恢复

⚠️ 注意事项

  1. 权限要求：INTO OUTFILE需要FILE权限，LOAD DATA需要INSERT权限
  2. 路径问题：确保MySQL对目标路径有读写权限
  3. 字符编码：统一使用UTF-8避免乱码
  4. 大文件处理：超过1GB的文件建议分割处理
  5. 生产环境：在从库上执行导出，避免影响主库性能
  6. 事务处理：大事务可能产生大量undo log，适当拆分

🔧 故障排查

# 查看错误日志
tail -f /var/log/mysql/error.log

# 查看当前连接状态
mysqladmin -uroot -p123456 processlist

# 检查表状态
mysqlcheck -uroot -p123456 --auto-repair --optimize mydb

# 查看导入导出错误
mysql -uroot -p123456 -e "SHOW WARNINGS;"

© 本文著作权归作者所有，转载前请务必署名

最新文章
PostgreSQL 命令行操作完整帮助文档

PostgreSQL 命令行操作完整帮助文档📖 目录快速导出CSV数据导入PostgreSQL执行大文件SQL常用维护命令性能优化建议与MySQL的区别1. 快速导出CSV1.1 基础导出命令# 基本格式psql -U用户名 -d数据库名 -c "查询语句" -o 输出文件.c
MySQL 命令行操作完整帮助文档

MySQL 命令行操作完整帮助文档📖 目录快速导出CSV数据导入MySQL执行大文件SQL常用维护命令性能优化建议1. 快速导出CSV1.1 基础导出命令# 基本格式mysql -u用户名 -p密码 -D数据库名 -e "查询语句" > 输出文件.csv# 示例：导出整个
OpenTelemetry 监测框架介绍

第一部分：核心概念澄清我们之前的类比非常准确：OpenTelemetry 是可观测性领域的“SLF4J”。它本身并非一个带界面的监控平台，而是一套开源的、厂商中立的工具、API 和 SDK 集合，其核心作用是统一观测数据（追踪、指标、日志）的生成、收集和导出标准。简单来说，OpenTelemetry
Transformer模型深度解析：从“大模型”定义到向量计算与层数揭秘

Transformer模型深度解析：从“大模型”定义到向量计算与层数揭秘本文档整合了关于大模型、向量计算、召回率以及Transformer层数的核心知识，旨在提供一个系统化的技术解读。第一章：大模型——“大”在何处？大模型的“大”主要体现在三个方面，它们共同构成了其强大能力的基础。1. 参数量巨大参
高并发风控策略模型代码设计思想

在Java多线程编程中，处理风控策略模型这种复杂依赖关系时，需要合理使用线程池、锁机制（如ReentrantLock、ReadWriteLock）以及并发工具类（如ConcurrentHashMap、CountDownLatch）来优化并发性能和避免资源浪费。以下是一个结合风控策略模型的多线程实现方
分类
顶级分类 default
人工智能 mainai
设计方案 scheme
使用手册 使用手册
日记 riji
学习笔记 study
配置管理 07configuration
运维监控 06operations
软件测试 05test
编码开发 04development
系统设计 03design
需求工程 02requirements
项目管理 01project
标签
过程中 inprocess
收尾 end
初始化 init
副业 sideoccupation
主业 mainbusiness
应用容器 application
随笔 informal
总结 summary
DevOps devops
数据库 database
大数据 bigdata
人工智能 ai