lower_case_table_names 设置为 1 时启动报错_lower-case-table-names=1加上之后不能启动了

os: centos 7.4.1708
db: mysql 8.0.20

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# 
# yum list installed |grep -i mysql80
mysql-community-client.x86_64              8.0.20-1.el7                @mysql80-community
mysql-community-common.x86_64              8.0.20-1.el7                @mysql80-community
mysql-community-devel.x86_64               8.0.20-1.el7                @mysql80-community
mysql-community-libs.x86_64                8.0.20-1.el7                @mysql80-community
mysql-community-libs-compat.x86_64         8.0.20-1.el7                @mysql80-community
mysql-community-server.x86_64              8.0.20-1.el7                @mysql80-community
mysql-community-test.x86_64                8.0.20-1.el7                @mysql80-community
mysql80-community-release.noarch           el7-3                       installed

# mysql -e "select version();"
+-----------+
| version() |
+-----------+
| 8.0.20    |
+-----------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

lower_case_table_names

lower_case_table_names 默认为0，当设置为 1，重启 mysql 报错

# vi /etc/my.cnf

lower_case_table_names = 1

1
2
3
4

# systemctl restart mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2020-06-29 10:24:17 CST; 18s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 17220 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 17192 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 17220 (code=exited, status=1/FAILURE)
   Status: "Server startup in progress"

Jun 29 10:24:13 xxoo systemd[1]: Starting MySQL Server...
Jun 29 10:24:17 xxoo systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Jun 29 10:24:17 xxoo systemd[1]: Failed to start MySQL Server.
Jun 29 10:24:17 xxoo systemd[1]: Unit mysqld.service entered failed state.
Jun 29 10:24:17 xxoo systemd[1]: mysqld.service failed.


# cat /var/log/mysqld.log

2020-06-29T02:10:14.118784Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 8  user: 'root'.
2020-06-29T02:10:15.652568Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.
2020-06-29T02:10:17.485225Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 17010
2020-06-29T02:10:17.529925Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-29T02:10:20.164989Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-29T02:10:20.772214Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '0.0.0.0' port: 33060
2020-06-29T02:10:20.979796Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-29T02:10:21.142790Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.20'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2020-06-29T02:24:11.754455Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 8  user: 'root'.
2020-06-29T02:24:12.825751Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.
2020-06-29T02:24:14.473992Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 17220
2020-06-29T02:24:14.505559Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-06-29T02:24:16.635771Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-06-29T02:24:16.824448Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2020-06-29T02:24:16.825072Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-06-29T02:24:16.825532Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-06-29T02:24:17.371067Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.20)  MySQL Community Server - GPL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

最后几行可以看到 ERROR

主要是 mysql 8.0 开始做了调整

After initialization, is is not allowed to change this setting.So “lower_case_table_names” needs to be set together with --initialize .

所以就需要再初始化时指定 --lower-case-table-names=1

# /usr/bin/mysqld --user=mysql --lower-case-table-names=1 --initialize-insecure --datadir=/var/lib/mysql

1
2

# vi /etc/my.cnf

[mysqld]
lower_case_table_names        = 1

1
2
3
4
5

参考:
https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names