MySQL8 设置大小写敏感

拼搏现实的明天。 2024-04-22 22:05 61阅读 0赞

## 问题描述 ##

今天对我本地的数据库迁移服务器上，完成之后启动项目报错

![在这里插入图片描述][548b209c52464631b9b7c36b15292b2c.png]

说数据库中不存在 `quartz_LOCKS` 这张表

![在这里插入图片描述][98e1ae9da3e04225ae0bcbe1e122e254.png]

我打开服务器上面的数据上面展示的表名是 `quartz_LOCKS`，然后通过查询 `lower_case_table_names` 配置可知

show variables like 'lower_case_table_names';

![在这里插入图片描述][d9ca5eb751744aa78fd4344071250260.png]

`lower_case_table_names=0`，表示 `表名区分大小写，且按照用户指定存储`

> lower\_case\_table\_names 各参数含义：
> 
>  *  0：表名区分大小写，且按照用户指定存储
>  *  1：表不区分大小写，使用小写存储
>  *  2：表不区分大小写，按照用户指定存储

按理说只要将 `lower_case_table_names` 的值设置为 `1` 或者 `2` 就行了

接着我去修改了服务器上 `my.cnf` （或者 `my.ini`）文件

![在这里插入图片描述][972ca792044f44e08174cc9b6cc98871.png]

添加以下配置：

[mysqld]  
    lower_case_table_names = 1

![在这里插入图片描述][ac777753d6694e4b9863d7a7a42e34af.png]

接着再重启 `mysql`

# 重启 mysql
    systemctl restart mysqld

![在这里插入图片描述][e8cdf559dcf24ed48130cceb4a12ceea.png]

`mysql` 启动失败

--------------------

## 原因分析： ##

根据提示使用 `systemctl status mysqld.service` 命令显示 `mysqld.service` 的状态信息，

![在这里插入图片描述][30a06923c3db489fb3085704bdc7e01e.png]

退出状态码为 `1`，这通常意味着在启动过程中遇到了某种错误，为了确定具体的错误原因，查看更详细的日志

# 查询 mysql 日志
    sudo tail -n 100 /var/log/mysqld.log

![在这里插入图片描述][fa7fa742439347d1829606f69a6d1001.png]

从日志上说明 `MySQL` 服务器启动失败的原因是 `lower_case_table_names` 的设置不一致。`MySQL` 的 `lower_case_table_names` 配置项决定了表名在存储和比较时是否应该转换为小写，这个设置必须在服务器和数据字典之间保持一致，否则服务器将无法正确启动

`[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').`

指出服务器期望 `lower_case_table_names` 设置为 `1`（意味着表名在存储和比较时应转换为小写），但是数据字典（即已有的数据库表元数据）的 `lower_case_table_names` 设置是 `0`（意味着表名区分大小写）

从 `mysqld.log` 日志文件打印的信息来看，导致 `Mysql` 启动失败的原因就是因为设置了 `lower_case_table_names=1` 这个参数，之前在本地使用的 `Mysql` 是 `5.7` 版本的，而服务器上面的 `Mysql` 版本确是 `8.0`

查看 `Mysql` 官方文档可知

> lower\_case\_table\_names can only be configured when initializing the server.  
> Changing the lower\_case\_table\_names setting after the server is initialized is prohibited.

在 `Mysql-8.0` 版本时，`lower_case_table_names` 变量是一个系统变量，它只能在 `MySQL` 服务器初始化时设置，并且一旦服务器初始化完成，就不应该再更改这个设置。这是因为在 `MySQL` 的数据字典和文件系统层面，这个设置影响到了表名和目录名的大小写敏感性和转换规则。如果在服务器运行时改变这个设置，可能会导致数据不一致和其他严重问题

--------------------

## 解决方案： ##

通过分析可知 `Mysql8` 在初始化之后是不能修改 `lower_case_table_names` 这个参数的

那要解决这个问题有两种方案，要么就是更改现有的表明，要么就初始化数据库，更改配置

*  方案一

如果你的表比较少的话，可以通过 `RENAME TABLE` 语句来更改它们，例如：将 `my_table` 更新为 `MY_TABLE`

RENAME TABLE my_table TO MY_TABLE;

但是，如果你有很多表需要更改，这种方法可能会很繁琐

也可以编写一个脚本来查询数据库中的所有表名，并自动为它们生成 `RENAME TABLE` 语句，然后执行这些语句来更改表名

*  方案二

初始化数据库，再更改 `lower_case_table_names` 配置，在初始化数据时，需要清空数据库中所有的数据并恢复到初始状态，所以务必做好数据备份

在初始化数据库时需要先将数据库关闭

# 关闭 mysql
    systemctl stop mysqld

`Mysql` 数据库的数据库存储在 `/var/lib/mysql` 下，在重新初始化数据库之前，需要删除现有的数据目录

sudo rm -rf /var/lib/mysql

![在这里插入图片描述][f32117e676d849c0b1fff90cb454331b.png]

注意：此操作不可逆，务必做好数据备份

再重新创建一个新的数据目录

sudo mkdir /var/lib/mysql

![在这里插入图片描述][ef39361d4b5e438a8f992204de903c86.png]

授权 `Mysql` 对于 `/var/lib/mysql` 目录的所有权

sudo chown -R mysql:mysql /var/lib/mysql

![在这里插入图片描述][b2f882eb3b2646f0a40e2db4e6b0adb3.png]

初始化数据库

sudo mysqld --initialize

![在这里插入图片描述][6ea8298fb254456fbd8a7c8a5281ac2e.png]

到此为止初始化的步骤已经完成，接着就去修改 `/etc/my.cnf` 文件中的 `lower_case_table_names` 配置

![在这里插入图片描述][f710be2d064e43aaa3c0233f1c550f42.png]

在 /var/log/mysqld.log 这个文件中查找 `Mysql` 的密码

cat /var/log/mysqld.log

![在这里插入图片描述][207e0a892dc24e8d8679e9d38bca7272.png]

rtAVCTaNg0\*b

再启动 `Mysql`

# 启动 mysql
    systemctl start mysqld

![在这里插入图片描述][3fa6ae2585264d698f4e8e1386eef8ae.png]

连接 `Mysql`

# 连接 MySQL 
    mysql -u root -p

![在这里插入图片描述][c076d86582884f7bbd862e14720ab202.png]

修改 `root` 密码

ALTER  USER  'root'@'localhost'  IDENTIFIED BY 'password';

![在这里插入图片描述][249433538f08413e9b14795623c19ad6.png]

再通过以下命令查看下 `lower_case_table_names` 的值是否为你所设置的

show variables like 'lower_case_table_names';

![在这里插入图片描述][8837f9aef7d249d9b7599b6f5eea57d0.png]

可以看到已经成功修改为 `1` 了

[548b209c52464631b9b7c36b15292b2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/55da424550f145ee85d512bed01e1e36.png
[98e1ae9da3e04225ae0bcbe1e122e254.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/7ffd47c0adac4a8ea562eb6cd54274ba.png
[d9ca5eb751744aa78fd4344071250260.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/e0731f1b9318480f8489c8f7571a26c2.png
[972ca792044f44e08174cc9b6cc98871.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/81bbe458ba824e12b5c75174b5d645de.png
[ac777753d6694e4b9863d7a7a42e34af.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/077ef7943e1e49d3a98276cf90e2f4bc.png
[e8cdf559dcf24ed48130cceb4a12ceea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/6a077e2b50324036a6830d7eac9a39a7.png
[30a06923c3db489fb3085704bdc7e01e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/9a7ead21300a4d2e8c891fe113a751b8.png
[fa7fa742439347d1829606f69a6d1001.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/f2d56d69708744a693274e88d24d6138.png
[f32117e676d849c0b1fff90cb454331b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/bc411a88c57d41b9b3aafbddd00b8f95.png
[ef39361d4b5e438a8f992204de903c86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/aed6c1ad5e394560b37944ed152f9bb3.png
[b2f882eb3b2646f0a40e2db4e6b0adb3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/c3a1aacdf7484b6d9dec1bc28c4626d2.png
[6ea8298fb254456fbd8a7c8a5281ac2e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/b2a68d7f92ea4205b27ea3aeb722aa24.png
[f710be2d064e43aaa3c0233f1c550f42.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/bd89ba795847413096321342feee12a8.png
[207e0a892dc24e8d8679e9d38bca7272.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/dda23ada8c594e09a0d9dc543051e72e.png
[3fa6ae2585264d698f4e8e1386eef8ae.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/b37278fbd3d0418f980f1de0eb43d9fe.png
[c076d86582884f7bbd862e14720ab202.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/bafd143ee65e4cd6bfee2ae9839f3f31.png
[249433538f08413e9b14795623c19ad6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/a419a70da14946c1a03b70e94e9cb9b6.png
[8837f9aef7d249d9b7599b6f5eea57d0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/c6ba11f31ded4edea54d7b38424ce500.png