DataEase 使用外接数据库版本为8.0.27，解决修改 lower_case_table_names=1 不生效问题，实测有效

这篇文章，是在安装开源数据可视化工具 DataEase 时，使用了外接数据库，且数据库版本为8.0.27版本，修改 lower_case_table_names=1 时，不生效，其实这里我们需要注意的是，MySQL 8 版本是不支持在安装后修改这个参数的，需要进行文件夹的删除后才可生效，需要慎重衡量一下是否要如此操作，刚安装的数据库可以如此操作，否则建议重新安装数据库，或者试用内置的 MySQL 数据库容器。

首先，我们来看一下数据库直接修改时的报错，即我们将在配置文件里直接修改或增加 lower_case_table_names=1 时，重新启动 MySQL 的命令行报错：

[root@mysql-2 etc] systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

截图如下：

执行 以下命令查看 MySQL 的运行日志报错如下：

#查看MySQL执行日志
tail -100f /var/log/mysqld.log

报错日志命令和截图如下：

2023-04-04T07:12:03.785027Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.32) initializing of server in progress as process 29970
2023-04-04T07:12:03.795964Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-04T07:12:04.946892Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-04T07:12:07.144007Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: BBazr6Aiye+t
2023-04-04T07:12:11.086208Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 30013
2023-04-04T07:12:11.099433Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-04T07:12:11.485713Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-04T07:12:11.850071Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2023-04-04T07:12:11.850130Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2023-04-04T07:12:11.882547Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2023-04-04T07:12:11.882870Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2023-04-04T07:57:48.914660Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user <via user signal>. Shutting down mysqld (Version: 8.0.32).
2023-04-04T07:57:49.530871Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32)  MySQL Community Server - GPL.
2023-04-04T07:57:50.041531Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2023-04-04T07:57:50.045338Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 30114
2023-04-04T07:57:50.047383Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2023-04-04T07:57:50.059896Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-04T07:57:50.581239Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-04T07:57:50.596524Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2023-04-04T07:57:50.596811Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-04-04T07:57:50.596847Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-04-04T07:57:51.159019Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32)  MySQL Community Server - GPL.
2023-04-04T07:58:12.533340Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2023-04-04T07:58:12.535947Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 30182
2023-04-04T07:58:12.537438Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2023-04-04T07:58:12.544640Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-04-04T07:58:12.900111Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-04-04T07:58:12.910727Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2023-04-04T07:58:12.910964Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-04-04T07:58:12.910994Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-04-04T07:58:13.496717Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32)  MySQL Community Server - GPL.

解决方案如下：

1. 清空 /var/lib/mysql 目录下的内容
2. 修改 my.cnf 配置文件，增加 lower_case_table_names=1 配置
3. 重启服务 systemctl restart mysqld
4. 因为清空了目录下的内容，需要重新设置用户密码等

        4.1 执行命令查看初始密码：grep 'temporary password' /var/log/mysqld.log 

        4.2 根据需要修改密码即可