哪些情况下 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，否则可能导致一些异常的情况。
补充
什么情况会遇到最后一行没有换行符？
根据测试以下两种情况会导致此现象：
echo -n "xx" >> my.cnfprintf "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
