lower_case_table_names 设置为 1 时启动报错

淡淡的烟草味﹌ 2023-02-21 06:25 1阅读 0赞

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    |
    +-----------+

# lower\_case\_table\_names #

lower\_case\_table\_names 默认为0，当设置为 1，重启 mysql 报错

# vi /etc/my.cnf
    
    lower_case_table_names = 1

# 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.

最后几行可以看到 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

# vi /etc/my.cnf
    
    [mysqld]
    lower_case_table_names        = 1

参考:  
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