在配置文件中添加 lower_case_table_names=2 后，mysql 8.0 无法启动

Question

我已将 mysql 服务器(版本:8.0)安装到 Linux 服务器中。

MySQL 数据库在 Linux 环境中是区分大小写的。

我在[mysqld]下添加“lower_case_table_names=2 ”，并使用systemctl restart mysqld.service重新启动服务器，但Mysql无法启动

这是我的 my.cnf 配置

[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
lower_case_table_names=1
bind-address=192.168.1.25
# 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

这是日志文件/var/log/mysqld.log

2019-02-22T08:50:44.849482Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 6819
2019-02-22T08:50:45.112952Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').

如何解决这个问题

谢谢

Answer 1

Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case-sensitive! InnoDB table names and view names are stored in lowercase, as for lower_case_table_names=1.

请查看第二句，因为您将其从 1 切换为 2 ..您使用的是 Linux，因此区分大小写，这仅适用于 Windows，不适用于您的情况。另外，如果你把它放在 Windows 上，你必须备份、转换表和 View 名称为小写。

更多内容请阅读https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html