mysql 导入导出大体积 sql
之前写过一篇 mysql 备份与恢复数据 - 指令操作，在面对几十 M 单位的 sql 文件是游刃有余的，之前使用 Navicat 工具导入过，也没有问题。但是最近一个项目，需要修改其中的定制功能，所以需要拉取线上的数据库。
开发主管指导，可以去掉一些不需要的表，给了我一个 sql ，大概的指令如下：
mysqldump -u root -p root abc \
--ignore-table=abc.a \
--ignore-table=abc.b \ > abc.sql导出的过程没有太大的问题，虽然导出的文件因为时间积累达到了 2G 左右，但速度还是非常快的。
主要问题在导入过程。一开始尝试使用 Navicat 导入新创建的站点数据库，速度相当慢，且过一段时间，Mac 弹出内存不足的提示，让强制清理一些应用进程。可以看到此时的 Navicat 吃了春药一样，竟然占用了 20 多个 G 的内存。这显然不可能，因为 Mac 就 8 G 的内存，应该是使用了 Swap 交换缓存，存储到物理存储中了。但这个数字依然相当的惊人，文件只有 2 个 G 而已，但导入解析竟然占用了 10 多倍的以上的存储。此时显然已经无法继续了，只能强制清理 Navicat 进程。
咨询了 deepseek 了解到，原来命令行导入方式才是最优解。因为命令行导入会使用流式导入，每次取一部分数据导入，这样就不会疯狂爆内存。瞬间感觉自己像个扔了西瓜捡芝麻，明明直接使用命令行才是最优解。
将本地的 sql 压缩包通过 vagrant 上传到虚拟机中，之后再通过 docker cp 复制到 mariadb 中，再执行 mysql 命令行导入指令：
mysql -u root -p root abc < abc.sql
# 后面发现可以直接从虚拟机里导入
sudo cat abc.sql | docker exec -i mariadb mysql -u root -p root abc
# 更优解，使用 pv 工具，可以查看到进度条，apt-get install pv 安装指令
sudo pv abc.sql | docker exec -i mariadb mysql -u root -p root abc结果不出意外还是又问题。一个是看不到导入的进度，一直等待没有结果；另一个是不知道是否因为导入文件太大，数据库连接失败了，包括表之类的都无法访问。且删除数据库的时候会报错 1036 - Table 'billing_account_copy' is read only。此时只能尝试重启 mariadb 容器或者删除重新创建（只读问题按照 deepseek 的指导修改、调整了很多次，最后发现是 innodb-force-recovery=6 参数导致）。
等了近一个多小时后还是没有完成导入，不得不撤销命令行执行。也就是说使用命令行管道进行流式导入至少对于目前状态下的环境是通不过的。
之后问 deepseek，给出的方案就是，一个是调整 mysql 的 my.cnf 中的配置参数，增加缓存大小等；另一个则是切割导出的 sql 文件。这边是两样都做了，结果问题越做越多。
首先是 mysql 的配置修改问题，修改的时候才发现之前做的数据库宕机恢复服务遗留了两个参数给注释掉（不然，就会导致上面的一致提示表只读，导入失败的情况）：
command:
- mysqld
- --log-error=/var/log/mysql/error.log
- --key_buffer_size=16M # 替换旧参数
#- --innodb-force-recovery=6
#- --skip-grant-tablesinnodb_force_recovery 参数值
0 - 默认值，InnoDB 正常启动。
1 - 将 InnoDB 置于“不崩溃恢复”模式。在这种模式下，InnoDB 将尝试启动，但会忽略一些校验和错误。
2 - 启动时跳过 redo 日志的应用。这可能导致一些最近的事务丢失。
3 - 跳过插入缓冲和撤销日志的写入。这可能导致非提交事务的数据丢失和一些索引损坏。
4 - 跳过日志的回滚。这将导致所有未提交的事务都被视为已提交。
5 - 完全跳过日志文件（包括 redo 日志和 undo 日志）。这将导致所有未提交的事务都被视为已提交，并且可能导致索引损坏。
6 - 跳过双写缓冲区。这可能导致数据文件损坏。
数值越大，宕机启动的几率就越大，对于数据文件的破坏也越大。
但这么一改就无法正常启动了，因为整个 mariadb 是宕机的状态，加了这个参数才能正常启动。尝试降低到 3 可以启动，但导入依然会失败，不记得是只读还是其他的什么问题。
这样的矛盾冲突下，选择先恢复到 6，将所有的其他数据库进行备份，之后关闭删除容器，再删除 /var/lib/mysql（mysql data 文件夹）。然后按照公司的项目手动部署流程里的 mariadb 做初始化处理。之后删除容器，统一使用 docker compose 启动。
再次尝试命令行直接导入，依然失败。 df -h 查看磁盘大小，free -h 查看内存使用，docker stats mariadb 实时查看容器的资源占用（CPU、内存等），只有 Blocked I/O 参数有明显的异常，O 输出部分到达了 5、6G，然后导入指令就卡住不动了。
按照 deepseek 的指示尝试修改 mariadb 参数，然而并没有什么软用。
[mysqld]
key_buffer_size = 256M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 2
innodb_buffer_pool_size = 1G # 根据你的宿主机内存调整，设置更大的缓冲池可以提高导入速度
innodb_log_file_size = 64M # 增大重做日志文件大小，减少磁盘I/O
max_allowed_packet = 256M # 增大允许的最大数据包大小，避免大INSERT语句被拒绝
thread_stack = 192K
thread_cache_size = 8
# I/O 优化配置
innodb_flush_log_at_trx_commit = 2 # 设置为0或2，大幅减少日志刷新磁盘的次数，提高速度（导入完成后建议改回1以保证数据安全）
innodb_flush_method = O_DIRECT # 使用直接I/O，减少操作系统缓存
sync_binlog = 0 # 禁用二进制日志同步此时琢磨起另外一个方案，就是切割导出的 sql 文件。基本的方案分为两步，一是分割出多份 sql 文件，第二步就是写一个 sh 脚本，循环遍历文件，执行 mysql 导入操作。
拆分工具一开始是使用 Mac 下自带的工具 split：
# 使用 split 命令按行或大小拆分
split -l 100000 exported_file.sql chunk_ # 每10万行拆分成一个文件，生成 chunk_aa, chunk_ab, ...
# 或者按大小拆分
split -b 100M exported_file.sql chunk_ # 每100MB拆分成一个文件然而这样拆的话，还是会有问题。按照大小拆，看到会从语句中间截断，导致语法错误，执行失败；按照行拆，如果是正好拆在 create 语句的时候，也会导致失败。且之前 mysqldump 下来的 sql，默认会一次 insert 多条数据，甚至所有的数据都在一个 insert 里插入了，如果考虑按照行拆分，且每个文件大小均匀的话，需要将其改成一行 insert 插入一条数据。当然这也有问题，数据量大的情况下，会导致 insert 需要执行几万次，几十万次。
导出时使每条 insert 语句导入一条数据，增加配置参数 --skip-extended-insert。
尝试多次，发现都会有拆分问题，因为本身拆的时候就没有考虑过表的数据的结构。之后找到了一个 python 工具 SQL Dump Splitter，可以按照每个表和导入数据进行拆分。
下载下来是一个 python 文件，需要安装 python3 来使用：
python3 sql_dump_splitter.py --input-file 'path/to/your/dump.sql' --output-dir 'path/to/output' --trigger-count 1 --ignore-blank-lines --report接着是循环脚本（上面的拆分默认文件名从 0 开始的数字）：
#!/bin/bash
# 获取所有.sql文件并按数字顺序排序
files=$(ls *.sql | sort -n)
# 计算总数
total=$(echo "$files" | wc -l)
echo "$total files in total."
count=0
# 循环处理每个文件
for file in $files; do
((count++))
echo "Importing $file ($count/$total)..."
mysql -uroot abc < "$file"
echo "After $file imported...";
# 检查上一个命令的退出状态
if [ $? -ne 0 ]; then
echo "Error importing $file. Aborting."
exit 1
fi
sleep 1
done
echo "All files imported successfully."然后 error.log 最近的日志：
Server version: 10.1.10-MariaDB-1~jessie-log
key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=3
max_threads=1002
thread_count=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2217186 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0x4069339008
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
...
250919 8:46:41 [ERROR] mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.这通常是由于内存不足、配置不当或软件 bug 引起的。
按照这个思路又走了7、8次修改，主要是 mariadb 配置、内存，还有 shell 脚本修改，但没有解决。一直说内存有问题，但虚拟机本身已经分配了 4g，docker 内关键参数 key_buffer_size 改成 1g（1/4）也没有用。只能看到 I/O 比较高。内存在导入的时候并没有太明显的变化。降低 I/O 的参数修改做了没用，本身 mariadb 属于 docker 容器，很多清理 I/O 的方法无法实践。且这个问题每次重启容器就没了，但一导出就会阻塞，然后连接不上。
使用 vmstat 1 可以看到导入时有明显的 I/O 流，过一段时间后，I/O 流消失了，但（某一块）导入并没有结束，一直卡在那边。检测这个块，甚至这个块里包含的表和数据都只有几十行，根本不可能导致阻塞。
昨天 9.23 号（搞了一周了）突然想试试在另一个版本里导入这些分块的 sql，跳出当前的糟糟的环境，看是否可以成功。在本地 Docker Desktop 中添加 Mariadb 10.11（测试环境里是 10.1.x），然后按照之前的做法，将分块 sql 和导入 shell 都复制到容器中，再执行。Amazing！只中间报了一个错误，然后并没有影响，直接成功了。执行的脚本都是最初版，什么都没加：
total=$(ls *.sql | wc -l)
count=0
for file in *.sql; do
((count++))
echo "Importing $file ($count/$total)..."
mysql -uroot abc < "$file"
sleep 1此时考虑的是，如何将其复制和同步到虚拟机中的 mariadb 中。因为有了工具 Navicat，此时最有希望成功的就是工具去执行。首先尝试的是备份还原，不通。然后是工具里的数据传输，卡死。最后尝试先结构同步，再部分表数据同步。一开始很顺利，虽然执行的时候有点太长了。结果今天到这一开，又卡死了。
到了这个时候，已经有点绝望了。想着算逑，干脆直接用别的数据库，反正正常开发也没有什么问题，至于判断最后的数据准确性，再说。突然想到接触的一个站点，数据库没有使用 docker 容器服务，而是单独的一台数据库服务器。如果本地环境也是这样搞，让应用连接 Mac 下的数据库服务，而非虚拟机内的。将完整的构想发给 deepseek，让其出解决方案。
1. 获取 Mac 主机在虚拟机网络中的 IP
# 在虚拟机中执行：
> ip route show default | awk '{print $3}'
10.0.2.2
# 通常 Vagrant 环境中，Mac 主机的 IP 是 10.0.2.2 或 192.168.56.1（具体取决于你的网络配置）。此时检测虚拟机中和虚拟机的应用容器中连接 Mac 地址是否通畅：
# 虚拟机中
telnet 10.0.2.2 33060
# 应用中
apt-get update && apt-get install -y telnet
telnet 10.0.2.2 330602. 修改 docker-compose.yml
移除 mariadb 服务，并修改其他服务的数据库连接配置：
services:
xx:
...
environment:
# 添加数据库连接环境变量，指向 Mac 主机
DB_HOST: 10.0.2.2 # 或使用具体的 IP，如 10.0.2.2
DB_PORT: 33060 # Mac 下的 mariadb 端口3. 在应用配置中修改数据库连接
将其改为 10.0.2.2:33060。
测试OK！
此时创建项目需要的连接用户：
# Mac 下的 mariadb shell
CREATE USER 'xxx'@'%' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON *.* TO 'xxx'@'%';
FLUSH PRIVILEGES;