MySQL 8.x重新初始化报错[MY-010069] [Server] Failed to set up SSL because of the following SSL library error

一、问题描述

数据库在删掉datadir后，执行mysqld --initialize重新初始化时出现 SSL_CTX_set_default_verify_paths failed报错，导致数据库无法启动。

2023-11-29T12:34:08.528662Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2023-11-29T12:34:08.528692Z 0 [Warning] [MY-000081] [Server] option 'max_binlog_size': unsigned value 2147483648 adjusted to 1073741824.
2023-11-29T12:34:08.528727Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2023-11-29T12:34:08.528834Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.21) initializing of server in progress as process 8725
2023-11-29T12:34:08.538078Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-11-29T12:34:17.577211Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-11-29T12:34:18.351246Z 0 [Warning] [MY-013595] [Server] Failed to initialize TLS for channel: mysql_main. See below for the description of exact issue.
2023-11-29T12:34:18.351779Z 0 [Warning] [MY-010069] [Server] Failed to set up SSL because of the following SSL library error: SSL_CTX_set_default_verify_paths failed
2023-11-29T12:34:18.432643Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ma>ldnxI=97O

二、分析原因

根据日志信息判断应该是初始化SSL相关文件报错。我们检查自定义配置文件my.cnf，可以看到ssl-ca/ssl-cert/ssl-key的配置信息，是因为初始化时会根据my.cnf去配置数据库，初始化时ssl相关的证书文件是不存在(还未自动生成)，导致报错。

二、解决办法

在初始化前去掉my.cnf里面SSL相关的配置，待初始化后自动生成证书文件， 数据库正常后，然后再去my.cnf文件配置SSL就可以了。

### SSL settings ###
#ssl-ca = /var/lib/mysql/ca.pem
#ssl-cert = /var/lib/mysql/server-cert.pem
#ssl-key = /var/lib/mysql/server-key.pem

三、初始化的过程

注意初始化是不会保留数据的，需要保留数据，请先将数据库备份出来，最后再恢复

# 注意初始化是不会保留数据的，需要保留数据，请先将数据库备份出来，最后再恢复

# 停止数据库

systemctl stop mysqld

# 进入Mysql默认数据目录

cd /var/lib/

# 移除数据目录(直接改名备份)

mv mysql mysql_bak

# 执行初始化

mysqld --initialize  --user=mysql

# 查看数据库日志

cat /var/log/mysqld.log

# 启动数据库

systemctl start mysqld

# 查看数据库状态

systemctl status mysqld

四、参考my.cnf

系统CentOS7.4

CPU数量2个

内存16GB

数据库版本MySQL 8.0.21

/etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



### SSL settings ###
ssl-ca = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/server-cert.pem
ssl-key = /var/lib/mysql/server-key.pem

server-id = 001

skip_name_resolve = 1
character-set-server = utf8mb4

key_buffer_size 	 = 16M
max_allowed_packet	 = 32M
table_open_cache	 = 64
tmp_table_size 		 = 256M
max_heap_table_size 	 = 256M
# used for order by group by
sort_buffer_size	 = 52K
# used for inner left right join
join_buffer_size 	 = 8M
net_buffer_length	 = 16K
read_buffer_size	 = 4M
sort_buffer_size 	 = 8M
read_rnd_buffer_size	 = 8M
max_connections		 = 2048
max_connect_errors 	 = 600
myisam_sort_buffer_size	 = 8M
#lower_case_table_names	 = 1
# Turn off ONLY_FULL_GROUP_BY
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

thread_stack = 512K
external-locking = FALSE

interactive_timeout = 7200
wait_timeout = 7200

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

### slow query log settings ###
#slow_query_log = 1
#slow_query_log_file = /var/lib/mysql/slow.log
#long_query_time = 5
#log_queries_not_using_indexes = 0
#min_examined_row_limit = 5000
#log_slow_admin_statements = 0
#log_slow_slave_statements = 1

### Bin-Log settings ###
sync_binlog = 1
log-bin=mysql-bin
binlog_format = mixed
binlog_row_image = FULL
# Reserved 7 days
binlog_expire_logs_seconds = 604800
binlog_cache_size = 2M
max_binlog_cache_size = 2G
max_binlog_size = 2G

### innodb settings ###

# buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 10G
innodb_data_home_dir	 = /var/lib/mysql
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_sort_buffer_size = 1048576
innodb_lock_wait_timeout = 60
innodb_print_all_deadlocks = 1
innodb_file_per_table    = 1
innodb_flush_log_at_trx_commit = 1

### slave settings ###
#relay-log=mysql-slave-relay-bin

# enable the event scheduler
event_scheduler=ON

# client SSL settings
[client]
ssl-ca = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/client-cert.pem
ssl-key = /var/lib/mysql/client-key.pem
# TLS version
tls_version=TLSv1.2,TLSv1.3

[mysqldump]
quick
max_allowed_packet = 32M