使用LOAD DATA INFILE导入大CSV时MariaDB数据回滚问题求助

1. 定位问题根源 #

你的核心问题是MariaDB默认将临时文件（包括ALTER操作的frm临时文件、LOAD DATA时的临时表数据）存储在datadir所在磁盘，而该磁盘已无剩余空间，导致大文件导入过程中因磁盘不足触发事务回滚，最终表数据为空。

2. 调整临时文件存储路径 #

临时生效配置（重启后失效） #

先在MariaDB命令行中切换临时目录到空闲的/mnt/HD/HD_b2/Public2/tmp（需提前创建该目录并执行chown mysql:mysql /mnt/HD/HD_b2/Public2/tmp赋予权限）：

SET GLOBAL tmpdir = '/mnt/HD/HD_b2/Public2/tmp';
SET GLOBAL innodb_tmpdir = '/mnt/HD/HD_b2/Public2/tmp';

永久生效配置（修改配置文件） #

找到MariaDB的配置文件（通常为/etc/my.cnf或/etc/mysql/my.cnf），在[mysqld]段添加以下配置：

[mysqld]
# 全局临时文件存储目录
tmpdir = /mnt/HD/HD_b2/Public2/tmp
# InnoDB临时文件存储目录
innodb_tmpdir = /mnt/HD/HD_b2/Public2/tmp
# 限制临时表空间最大大小，避免占满磁盘
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:100G

修改后重启MariaDB服务，确保配置生效。

3. 优化大文件导入流程 #

配合临时目录调整，优化导入参数减少磁盘压力：

# 关闭自动提交，减少事务日志写入
SET AUTOCOMMIT=0;
# 禁用唯一键检查，加速导入
SET UNIQUE_CHECKS=0;

LOAD DATA LOCAL
INFILE '/mnt/HD/HD_b2/Public2/raw_data/DEUIDXEUR_01.csv'
INTO TABLE raw_data_index_germany_at_eur
FIELDS TERMINATED BY '|'
LINES TERMINATED BY ';'
(timestamp_, bid_price_, ask_price_, bid_volume_, ask_volume_);

# 提交事务
COMMIT;
# 恢复唯一键检查
SET UNIQUE_CHECKS=1;

4. 清理默认datadir磁盘空间 #

针对默认磁盘满的问题，可执行以下清理操作：

• 删除过期二进制日志（若开启binlog）：PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
• 停止MariaDB服务后，删除datadir下#sql-*开头的临时文件
• 清理datadir下无用的备份、日志文件

验证配置 #

重启MariaDB后，执行SHOW VARIABLES LIKE '%tmp%';确认临时目录已更新，再尝试导入或ALTER TABLE操作即可。

内容的提问来源于stack exchange，提问作者Jimmy Westberg