MariaDB增量备份生成大文件问题排查与方案咨询

数据老陈

2026-8-11

MariaDB增量备份过大问题排查与解决方案咨询

跨站重复问题（尚无解答）。

我需为MariaDB搭建增量备份系统，所有服务运行在Docker容器中，尝试结合wal-g（类似PostgreSQL的WAL归档配置方式）与mariabackup实现。备份任务在基于mariadb:10.8.3-jammy镜像的独立Docker容器中执行（已添加wal-g二进制文件，执行wal-g backup-push命令），调试时将备份导出到挂载为Docker卷的/bak目录。

核心问题：无数据变更时增量备份体积过大

在禁用所有数据库客户端、无任何数据变更的情况下连续执行备份，初始备份约30MB，但后续所有增量备份也均为30MB左右。

# Initial
$ sudo docker compose run --rm db_backup wal-g backup-push
[+] Running 1/0
 ⠿ Container reporting-db-1  Running                                                                                                                                                                                                   0.0s
INFO: 2022/11/20 19:24:01.865762 FILE PATH: stream_20221120T192400Z/stream.lz4                                                                                                                                                              
INFO: 2022/11/20 19:24:01.865984 Backup sentinel: {"StartLocalTime":"2022-11-20T19:24:00.366507Z","StopLocalTime":"2022-11-20T19:24:01.86589Z","UncompressedSize":350375370,"CompressedSize":227476734,"Hostname":"b08f12bbbb68"}

# Incremental
$ sudo docker compose run --rm db_backup wal-g backup-push
[+] Running 1/0
 ⠿ Container reporting-db-1  Running                                                                                                                                                                                                   0.0s
INFO: 2022/11/20 19:24:07.109202 FILE PATH: stream_20221120T192405Z/stream.lz4                                                                                                                                                              
INFO: 2022/11/20 19:24:07.109509 Backup sentinel: {"StartLocalTime":"2022-11-20T19:24:05.639259Z","StopLocalTime":"2022-11-20T19:24:07.109321Z","UncompressedSize":28104928,"CompressedSize":14609954,"Hostname":"3d9854550254"}

若数据源为空数据库，现象更明显：初始备份与增量备份大小几乎一致（仅差几KB）。

我推测原因是mariabackup会备份使用Aria存储引擎的系统表，而该引擎不支持增量备份。但手动验证结果不符：我尝试将所有mysql.*表转换为InnoDB（执行ALTER TABLE ... engine=innodb）后重新备份，增量备份未变小反而增大至原来的1.5倍（仅压缩后大小略有优化）。

# Initial
$ sudo docker compose run --rm db_backup wal-g backup-push
[+] Running 1/0
 ⠿ Container reporting-db-1  Running                                                                                                                                                                                                   0.0s
INFO: 2022/11/20 18:56:06.254782 FILE PATH: stream_20221120T185602Z/stream.lz4                                                                                                                                                              
INFO: 2022/11/20 18:56:06.254984 Backup sentinel: {"StartLocalTime":"2022-11-20T18:56:02.328882Z","StopLocalTime":"2022-11-20T18:56:06.254911Z","UncompressedSize":366630153,"CompressedSize":229793575,"Hostname":"843e27ceff9e"}

# Incremental 1
$ sudo docker compose run --rm db_backup wal-g backup-push
[+] Running 1/0
 ⠿ Container reporting-db-1  Running                                                                                                                                                                                                   0.0s
INFO: 2022/11/20 18:56:15.123822 FILE PATH: stream_20221120T185610Z/stream.lz4                                                                                                                                                              
INFO: 2022/11/20 18:56:15.124035 Backup sentinel: {"StartLocalTime":"2022-11-20T18:56:10.974406Z","StopLocalTime":"2022-11-20T18:56:15.12395Z","UncompressedSize":44359711,"CompressedSize":13798326,"Hostname":"26a6e38afb59"}

# Incremental 2
$ sudo docker compose run --rm db_backup wal-g backup-push
[+] Running 1/0
 ⠿ Container reporting-db-1  Running                                                                                                                                                                                                   0.0s
INFO: 2022/11/20 19:06:57.626672 FILE PATH: stream_20221120T190653Z/stream.lz4
INFO: 2022/11/20 19:06:57.626904 Backup sentinel: {"StartLocalTime":"2022-11-20T19:06:53.667772Z","StopLocalTime":"2022-11-20T19:06:57.626823Z","UncompressedSize":44360402,"CompressedSize":13799019,"Hostname":"184342c39610"}

我无法要求客户更换数据库管理系统，因此需要解决该问题，避免每次备份都存储30MB的冗余数据。现咨询以下问题：

我的推理是否正确？还有哪些原因会导致该异常现象？
是否可以将所有系统表转换为InnoDB？我了解在MySQL 5.7中这样做可能有风险，但未找到新版本的相关参考。若可行，理论上可让所有数据支持增量备份，解决问题。（疑似重复问题，但未得到有效解答）
是否有更适合该场景的替代备份方案？
是否可以禁止mariabackup备份系统表？我怀疑这不是可行方案（备份越完整越易维护），但可能有误。

附加问题

如何查看mariabackup输出的二进制流，以确认系统表是否为问题根源，甚至定位具体是哪些表？
_{多次连续执行增量备份时，压缩与未压缩大小略有波动，经排查是wal-g导致，`mariabackup`单独执行时大小稳定，该问题已无关注必要。}

上述现象在仅使用mariabackup时也可复现，增量备份文件约为27MB。

mariabackup包装脚本

last_lsns=$(ls /bak/lsns/ | sort -rn | head -n1)

if [ -n "$last_lsns" ]; then
  ex="/bak/lsns/lsn_$(date +%s)"
  mkdir -p "$ex"
  mariabackup -H"$WEB_DB_HOST" -uroot -p"$MYSQL_ROOT_PASSWORD" --backup \
    --stream=xbstream --datadir=/var/lib/mysql \
    --incremental-basedir=/bak/lsns/$last_lsns --extra-lsndir=$ex
else
  mkdir -p /bak/lsns/initial
  mariabackup -H$WEB_DB_HOST -uroot -p"$MYSQL_ROOT_PASSWORD" --backup \
    --stream=xbstream --datadir=/var/lib/mysql \
    --extra-lsndir=/bak/lsns/initial
fi

该脚本作为WALG_STREAM_CREATE_COMMAND使用，同时配置了：

WALG_MYSQL_DATASOURCE_NAME='root:$MYSQL_ROOT_PASSWORD@tcp($REPORTING_DB_HOST:$REPORTING_DB_PORT)/$REPORTING_DATABASE'
WALG_FILE_PREFIX='/bak/foo'

这些配置（实际写在compose文件中）看似正确，备份可正常创建并写入指定目录。

当前使用的存储引擎信息

> select table_schema, table_name, engine from information_schema.tables where table_schema <> 'performance_schema' and engine <> 'MEMORY';

+--------------------+---------------------------+--------+
| table_schema       | table_name                | engine |
+--------------------+---------------------------+--------+
| information_schema | ALL_PLUGINS               | Aria   |
| information_schema | CHECK_CONSTRAINTS         | Aria   |
| information_schema | COLUMNS                   | Aria   |
| information_schema | EVENTS                    | Aria   |
| information_schema | OPTIMIZER_TRACE           | Aria   |
| information_schema | PARAMETERS                | Aria   |
| information_schema | PARTITIONS                | Aria   |
| information_schema | PLUGINS                   | Aria   |
| information_schema | PROCESSLIST               | Aria   |
| information_schema | ROUTINES                  | Aria   |
| information_schema | SYSTEM_VARIABLES          | Aria   |
| information_schema | TRIGGERS                  | Aria   |
| information_schema | VIEWS                     | Aria   |
| mysql              | slow_log                  | CSV    |
| mysql              | db                        | Aria   |
| mysql              | help_relation             | Aria   |
| mysql              | general_log               | CSV    |
| mysql              | innodb_index_stats        | InnoDB |
| mysql              | servers                   | Aria   |
| mysql              | time_zone_transition_type | Aria   |
| mysql              | gtid_slave_pos            | InnoDB |
| mysql              | time_zone                 | Aria   |
| mysql              | roles_mapping             | Aria   |
| mysql              | transaction_registry      | InnoDB |
| mysql              | procs_priv                | Aria   |
| mysql              | proxies_priv              | Aria   |
| mysql              | global_priv               | Aria   |
| mysql              | func                      | Aria   |
| mysql              | innodb_table_stats        | InnoDB |
| mysql              | help_topic                | Aria   |
| mysql              | time_zone_leap_second     | Aria   |
| mysql              | help_keyword              | Aria   |
| mysql              | time_zone_transition      | Aria   |
| mysql              | event                     | Aria   |
| mysql              | columns_priv              | Aria   |
| mysql              | tables_priv               | Aria   |
| mysql              | time_zone_name            | Aria   |
| mysql              | plugin                    | Aria   |
| mysql              | table_stats               | Aria   |
| mysql              | index_stats               | Aria   |
| mysql              | proc                      | Aria   |
| mysql              | help_category             | Aria   |
| mysql              | column_stats              | Aria   |
| test_reporting     | merchant_configs          | InnoDB |
| test_reporting     | masterdata_prediction     | InnoDB |
| test_reporting     | aggregator_config         | InnoDB |
| test_reporting     | masterdata                | InnoDB |
| test_reporting     | fixed_costs               | InnoDB |
| test_reporting     | timeline                  | InnoDB |
| test_reporting     | migrations                | InnoDB |
| test_reporting     | user_analytics            | InnoDB |
| test_reporting     | affiliate                 | InnoDB |
| test_reporting     | vat_config                | InnoDB |
| sys                | sys_config                | Aria   |
| reporting          | merchant_configs          | InnoDB |
| reporting          | masterdata_prediction     | InnoDB |
| reporting          | aggregator_config         | InnoDB |
| reporting          | masterdata                | InnoDB |
| reporting          | fixed_costs               | InnoDB |
| reporting          | timeline                  | InnoDB |
| reporting          | migrations                | InnoDB |
| reporting          | user_analytics            | InnoDB |
| reporting          | affiliate                 | InnoDB |
| reporting          | vat_config                | InnoDB |
| reporting_web      | record_change             | InnoDB |
| reporting_web      | jwt_expiry                | InnoDB |
| reporting_web      | forecasting               | InnoDB |
| reporting_web      | users                     | InnoDB |
| reporting_web      | alembic_version           | InnoDB |
| reporting_web      | merchant_analytics        | InnoDB |
| reporting_web      | email_config              | InnoDB |
| reporting_web      | user_company              | InnoDB |
| reporting_web      | user_detail               | InnoDB |
+--------------------+---------------------------+--------+

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

MariaDB增量备份生成大文件问题排查与方案咨询

核心问题：无数据变更时增量备份体积过大 #

附加问题 #

mariabackup包装脚本 #

当前使用的存储引擎信息 #

核心问题：无数据变更时增量备份体积过大

附加问题

mariabackup包装脚本

当前使用的存储引擎信息