在初始化 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 = 0nmem_limit_mb = 0ncpu_quota_percentage = 0nquota_limit_mb = 0nscsi_pr_level = 0nu"..., 4096) = 4096 <0.000007>
11:43:17.274997 read(3, "e-schema-consumer-events-stages-history=ONnperformance-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 = 0nmem_limit_mb = 0ncpu_quota_percentage = 0nquota_limit_mb = 0nscsi_pr_level = 0nu"..., 4096) = 4096 <0.000006>
11:48:40.550560 read(3, "e-schema-consumer-events-stages-history=ONnperformance-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 = 0nmem_limit_mb = 0ncpu_quota_percentage = 0nquota_limit_mb = 0nscsi_pr_level = 0nu"..., 4096) = 4096 <0.000009>
12:18:10.341897 read(3, "e-schema-consumer-events-stages-history=ONnperformance-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 = 0nmem_limit_mb = 0ncpu_quota_percentage = 0nquota_limit_mb = 0nscsi_pr_level = 0nu"..., 4096) = 4096 <0.000006>
12:24:16.055747 read(3, "e-schema-consumer-events-stages-history=ONnperformance-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.cnf
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