适用范围

建议8.0版本

问题概述

MySQL 8.0.30 进行数据库初始化时报错，提示数据目录不能使用，需要删除

问题分析及解决

1.执行初始化命令

#  /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/3306/data

2.分析运行日志

2024-05-21T01:39:25.702841Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 559892
mysqld: File '/home/mysql/log/mybinlog.index' not found (OS errno 2 - No such file or directory)
2024-05-21T01:39:25.708420Z 0 [ERROR] [MY-013455] [Server] The newly created data directory /home/mysql/3306/data/ by --initialize is unusable. You can remove it.
2024-05-21T01:39:25.708438Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-05-21T01:39:25.708612Z 0 [System] [MY-010910] [Server] /usr/local/mysql/bin/mysqld: Shutdown complete (mysqld 8.0.30)  MySQL Community Server - GPL.

刚开始直接查看的ERROR级别的日志，报错说目录不可用使用，需要remove ；清理data目录后重新执行仍然是上面的报错

3.使用–no-defaults启动

添加–no-defaults 后可以正常初始化成功，所以怀疑是参数文件/etc/my.cnf中参数有问题

#  /usr/local/mysql/bin/mysqld --no-defaults --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/3306/data

4.检查参数文件

逐个查看参数名称及路径，最后发现是 log_bin = /home/mysql/log/mybinlog 路径配置错误，正确的创建好的路径是 log_bin = /home/mysql/3306/log/mybinlog， 路径 /home/mysql/log 没有创建

# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /mysql/data1
socket = /home/mysql/3306/tmp/mysql.sock
log_error = /home/mysql/3306/log/mysql.error
log_bin = /home/mysql/log/mybinlog   
port = 3306

[client]
socket = /home/mysql/3306/mysql/tmp/mysql.sock

修改后

# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /mysql/data1
socket = /home/mysql/3306/tmp/mysql.sock
log_error = /home/mysql/3306/log/mysql.error
log_bin = /home/mysql/3306/log/mybinlog   
port = 3306

[client]
socket = /home/mysql/3306/mysql/tmp/mysql.sock

5.修改后重新初始化成功

去掉 --no-defaults 后，清空 /home/mysql/3306/data 目录后，重新初始化成功

#  /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/home/mysql/3306/data

日志如下：

2024-05-21T01:39:53.017553Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 559924
2024-05-21T01:39:53.027205Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-05-21T01:39:53.235182Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-05-21T01:39:54.146691Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

6.复盘运行日志

刚开始只关注了ERROR级别的日志，忽略了System的日志，现在来看刚开始已经有提示 ‘/home/mysql/log/mybinlog.index’ not found (OS errno 2 - No such file or directory) ，其实mysqld在初始化的过程中会生成 mybinlog.index 文件及mybinlog.000001、mybinlog.000002、mybinlog.000003 3个文件，因为 /home/mysql/log 路径不存在，所以无法成功创建相关的文件

2024-05-21T01:39:25.702841Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.30) initializing of server in progress as process 559892
mysqld: File '/home/mysql/log/mybinlog.index' not found (OS errno 2 - No such file or directory)

7.查看生成的binlog相关文件

# ll /home/mysql/3306/log
总用量 40
-rw-r-----. 1 mysql mysql   180 5月  21 09:37 mybinlog.000001
-rw-r-----. 1 mysql mysql   180 5月  21 09:37 mybinlog.000002
-rw-r-----. 1 mysql mysql   180 5月  21 09:39 mybinlog.000003
-rw-r-----. 1 mysql mysql   111 5月  21 09:39 mybinlog.index

其他说明

运行日志的ERROR过度的关注，忽略的System级别的日志，导致消耗过多的时间，同时也感觉mysql对于mybinlog.index的报错没有定义为ERROR级别影响了自己的判断，建议mysql优化