哪些情况下 MySQL 配置文件会被截断？

在初始化 MySQL 实例（版本：5.7.44）的时候报错。

作者：龚唐杰，爱可生 DBA 团队成员，主要负责 MySQL 技术支持，擅长 MySQL、PG、国产数据库。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1900 字，预计阅读需要 6 分钟。

背景

在初始化 MySQL 实例（版本：5.7.44）的时候报错。

[root@db1 ~]# /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --datadir=/u01/3306 --basedir=/opt/mysql/base/5.7.44/ --initialize
mysqld: Can't read dir of '/etc/my.cnf.' (Errcode: 2 - No such file or directory)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

处理步骤

根据报错信息来看，是找不到 /etc/my.cnf. 这个目录。看着比较奇怪，因为目录最后有一个点。

首先，查看 my.cnf 配置文件，发现跟 my.cnf 相关配置的只有 !includedir /etc/my.cnf.d，然后查看 /etc/my.cnf.d 目录是存在的。

[root@db1 ~]# cat /u01/my.cnf|grep /etc/my.cnf

!includedir /etc/my.cnf.d

[root@db1 ~]# ls -l /etc/my.cnf.d
total 4
-rw-r--r-- 1 root root 232 May 6 2020 mysql-clients.cnf

尝试校验一下配置文件正确性，发现依旧如此。

[root@db1 ~]# /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --help
mysqld: Can't read dir of '/etc/my.cnf.' (Errcode: 2 - No such file or directory)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

由此怀疑是配置文件有问题。尝试把 !includedir /etc/my.cnf.d 删除后，发现能正常读取。

[root@db1 ~]# cp /u01/my.cnf /u01/my.cnf.bak
[root@db1 ~]# vi /u01/my.cnf.bak
[root@db1 ~]# /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf.bak --help
/opt/mysql/base/5.7.44/bin/mysqld Ver 5.7.44-log for linux-glibc2.12 on x86_64 (MySQL Community Server (GPL))
Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Starts the MySQL database server.

Usage: /opt/mysql/base/5.7.44/bin/mysqld [OPTIONS]

For more help options (several pages), use mysqld --verbose --help.

这就很奇怪了，为什么写的 /etc/my.cnf.d，但是报错的是 /etc/my.cnf.？

在对这两个文件进行对比后发现了一个问题，显示：

[root@db1 ~]# diff /u01/my.cnf /u01/my.cnf.bak
169d168
< !includedir /etc/my.cnf.d
\ No newline at end of file

No newline at end of file 表示文件末尾没有换行符，正常来说通过 vi 等编译软件编辑后，最后一行会自动加上换行符。比如刚才改的 /u01/my.cnf.bak 文件中最后一行就存在 newline。通过 tail 命令可直观发现其中的区别。

[root@db1 ~]# tail -1 /u01/my.cnf.bak
#validate_password_policy = MEDIUM
[root@db1 ~]# tail -1 /u01/my.cnf
!includedir /etc/my.cnf.d[root@db1 ~]#

尝试在 /u01/my.cnf 后面手动添加后，初始化成功。

[root@db1 ~]# echo >> /u01/my.cnf
[root@db1 ~]# tail -1 /u01/my.cnf
!includedir /etc/my.cnf.d
[root@db1 ~]# /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --datadir=/u01/3306 --basedir=/opt/mysql/base/5.7.44/ --initialize

分析原因

先恢复原先的配置文件后使用 strace 进行调试。

strace   -T -tt -s 100 -o /tmp/strace.log  /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --datadir=/u01/3306 --basedir=/opt/mysql/base/5.7.44/ --initialize

11:43:17.274860 stat("/u01/my.cnf", {st_mode=S_IFREG|0644, st_size=4581, ...}) = 0 <0.000005>
11:43:17.274886 open("/u01/my.cnf", O_RDONLY) = 3 <0.000006>
11:43:17.274910 fstat(3, {st_mode=S_IFREG|0644, st_size=4581, ...}) = 0 <0.000004>
11:43:17.274927 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fa11a196000 <0.000005>
11:43:17.274945 read(3, "[universe]\niops = 0\nmem_limit_mb = 0\ncpu_quota_percentage = 0\nquota_limit_mb = 0\nscsi_pr_level = 0\nu"..., 4096) = 4096 <0.000007>
11:43:17.274997 read(3, "e-schema-consumer-events-stages-history=ON\nperformance-schema-consumer-events-stages-history-long=ON"..., 4096) = 485 <0.000004>
11:43:17.275018 read(3, "", 4096) = 0 <0.000004>
11:43:17.275035 openat(AT_FDCWD, "/etc/my.cnf./", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = -1 ENOENT (No such file or directory) <0.000005>
11:43:17.275077 write(2, "mysqld: ", 8) = 8 <0.000009>
11:43:17.275101 write(2, "Can't read dir of '/etc/my.cnf.' (Errcode: 2 - No such file or directory)", 73) = 73 <0.000004>
11:43:17.275120 write(2, "\n", 1) = 1 <0.000004>
11:43:17.275140 close(3) = 0 <0.000005>
11:43:17.275157 munmap(0x7fa11a196000, 4096) = 0 <0.000007>
11:43:17.275179 write(2, "mysqld: ", 8) = 8 <0.000004>
11:43:17.275196 write(2, "[ERROR] Fatal error in defaults handling. Program aborted!", 58) = 58 <0.000004>
11:43:17.275213 write(2, "\n", 1) = 1 <0.000004>
11:43:17.275324 exit_group(1) = ?

配置文件最后加上 newline 后能正常初始化成功，调试的结果如下。

strace   -T -tt -s 100 -o /tmp/strace2.log  /opt/mysql/base/5.7.44/bin/mysqld --defaults-file=/u01/my.cnf --datadir=/u01/3306 --basedir=/opt/mysql/base/5.7.44/ --initialize

11:48:40.550423 stat("/u01/my.cnf", {st_mode=S_IFREG|0644, st_size=4582, ...}) = 0 <0.000005>
11:48:40.550449 open("/u01/my.cnf", O_RDONLY) = 3 <0.000006>
11:48:40.550473 fstat(3, {st_mode=S_IFREG|0644, st_size=4583, ...}) = 0 <0.000004>
11:48:40.550490 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7faf63733000 <0.000005>
11:48:40.550508 read(3, "[universe]\niops = 0\nmem_limit_mb = 0\ncpu_quota_percentage = 0\nquota_limit_mb = 0\nscsi_pr_level = 0\nu"..., 4096) = 4096 <0.000006>
11:48:40.550560 read(3, "e-schema-consumer-events-stages-history=ON\nperformance-schema-consumer-events-stages-history-long=ON"..., 4096) = 486 <0.000004>
11:48:40.550583 openat(AT_FDCWD, "/etc/my.cnf.d/", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4 <0.000005>
11:48:40.550615 brk(NULL) = 0x336d000 <0.000003>
11:48:40.550630 brk(0x338f000) = 0x338f000 <0.000004>
11:48:40.550660 getdents(4, /* 3 entries */, 32768) = 88 <0.000006>
11:48:40.550680 getdents(4, /* 0 entries */, 32768) = 0 <0.000004>
11:48:40.550697 close(4) = 0 <0.000004>
11:48:40.550721 stat("/etc/my.cnf.d/mysql-clients.cnf", {st_mode=S_IFREG|0644, st_size=232, ...}) = 0 <0.000004>
11:48:40.550740 open("/etc/my.cnf.d/mysql-clients.cnf", O_RDONLY) = 4 <0.000004>

当最后一行不是 !includedir 或者 !include 类型并且也没有 newline 时，调试的结果如下，能正常初始化成功。

12:18:10.341731 stat("/u01/my.cnf", {st_mode=S_IFREG|0644, st_size=4599, ...}) = 0 <0.000005>
12:18:10.341756 open("/u01/my.cnf", O_RDONLY) = 3 <0.000006>
12:18:10.341783 fstat(3, {st_mode=S_IFREG|0644, st_size=4599, ...}) = 0 <0.000005>
12:18:10.341807 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f304d65a000 <0.000005>
12:18:10.341827 read(3, "[universe]\niops = 0\nmem_limit_mb = 0\ncpu_quota_percentage = 0\nquota_limit_mb = 0\nscsi_pr_level = 0\nu"..., 4096) = 4096 <0.000009>
12:18:10.341897 read(3, "e-schema-consumer-events-stages-history=ON\nperformance-schema-consumer-events-stages-history-long=ON"..., 4096) = 503 <0.000005>
12:18:10.341923 openat(AT_FDCWD, "/etc/my.cnf.d/", O_RDONLY|O_NONBLOCK|O_CLOEXEC|O_DIRECTORY) = 4 <0.000006>
12:18:10.341948 brk(NULL) = 0x34fb000 <0.000003>
12:18:10.341963 brk(0x351d000) = 0x351d000 <0.000005>
12:18:10.341988 getdents(4, /* 3 entries */, 32768) = 88 <0.000007>
12:18:10.342016 getdents(4, /* 0 entries */, 32768) = 0 <0.000005>
12:18:10.342038 close(4) = 0 <0.000004>
12:18:10.342062 stat("/etc/my.cnf.d/mysql-clients.cnf", {st_mode=S_IFREG|0644, st_size=232, ...}) = 0 <0.000004>
12:18:10.342081 open("/etc/my.cnf.d/mysql-clients.cnf", O_RDONLY) = 4 <0.000005>

当最后一行是 !include 类型并且也没有 newline 时，调试的结果如下：

12:24:16.055588 stat("/u01/my.cnf", {st_mode=S_IFREG|0644, st_size=4596, ...}) = 0 <0.000005>
12:24:16.055614 open("/u01/my.cnf", O_RDONLY) = 3 <0.000006>
12:24:16.055653 fstat(3, {st_mode=S_IFREG|0644, st_size=4596, ...}) = 0 <0.000007>
12:24:16.055676 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7fcb0b65c000 <0.000005>
12:24:16.055695 read(3, "[universe]\niops = 0\nmem_limit_mb = 0\ncpu_quota_percentage = 0\nquota_limit_mb = 0\nscsi_pr_level = 0\nu"..., 4096) = 4096 <0.000006>
12:24:16.055747 read(3, "e-schema-consumer-events-stages-history=ON\nperformance-schema-consumer-events-stages-history-long=ON"..., 4096) = 500 <0.000004>
12:24:16.055768 read(3, "", 4096) = 0 <0.000004>
12:24:16.055788 stat("/etc/my.cnf.d/mysql-clients.cn", 0x7ffd8c379280) = -1 ENOENT (No such file or directory) <0.000004>
12:24:16.055809 read(3, "", 4096) = 0 <0.000004>
12:24:16.055827 close(3) = 0 <0.000005>

根据上面调试显示，每次会以 4096 字节进行读取配置文件，若最后遇到 !includedir 或者 !include 开头的且没有 newline 时，会多进行一次读取 0 字节的操作，由此引发了截断问题。

总结

MySQL 初始化解析配置文件时：

• 当最后一行为 !includedir 类型且没有 newline，会自动截断最后一个字符，所以会报错 Can't read dir of '/etc/my.cnf.'，导致初始化失败。
• 当最后一行为 !include 类型且没有 newline，引用的文件名会被截断一个字符导致找不到该文件，但是不会引起初始化程序 aborted。能正常初始化完成。
• 综上所述，推荐配置文件最后需要添加上 newline，否则可能导致一些异常的情况。

补充

什么情况会遇到最后一行没有换行符？

根据测试以下两种情况会导致此现象：

1. echo -n "xx" >> my.cnf
2. printf "xx" >> my.cnf

若还有其他的情况，可一起交流分享。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle