Mysql 修改大小写敏感问题

注意此流程会清理所有数据, 请先备份

MySQL 8.0

1. 添加配置

vim /etc/mysql/mysql.conf.d/mysqld.cnf
添加以下属性

[mysqld]
# 大小写
lower_case_table_names=1

2. 删除数据

rm -rf /var/lib/mysql/*

3. 初始化数据库

/usr/sbin/mysqld --initialize --user=mysql --lower-case-table-names=1 --log_bin_trust_function_creators=1

3.1 检查默认密码

cat /var/log/mysql/error.log | grep password

日志中检索出来的默认 root 密码是 59ixadiZVt_&

root@VM-4-10-ubuntu:~# cat /var/log/mysql/error.log | grep password
... A temporary password is generated for root@localhost: 59ixadiZVt_&

3.2 重启 MySQL

systemctl restart mysql

4. 进入MYSQL并修改密码

mysql -uroot -p 输入刚才检索到的密码 59ixadiZVt_&

root@l1:/var/lib/mysql# systemctl restart mysql
root@l1:/var/lib/mysql# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.28-0ubuntu0.20.04.3

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
No connection. Trying to reconnect...
Enter password: 
Connection id:    13
Current database: *** NONE ***

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> ^DBye
root@l1:/var/lib/mysql#

5. 大功告成

使用 navicat 测试。
image.png

MySQL 5.7

编辑配置文件 vi /etc/my.cnf

[mysqld]
...
＃1表示对大小写不敏感，0表示对大小写敏感
lower_case_table_names=1
...

之后重启一下mysql服务

MySQL 5.7 Docker 版

  1. 拉取镜像 mysql/mysql-server:5.7
  2. 启动 mysql docker run -p 23140:3306 --name mysql57 -d mysql/mysql-server:5.7
  3. 进入容器 docker exec -it mysql57 bash
  4. 写入配置 echo 'lower_case_table_names = 1' >> /etc/my.cnf
  5. 重启容器 docker restart mysql57

备注

  1. 如果有遇到初始化数据库后无法进入mysql终端的行为, 请在 /etc/mysql/mysql.conf.d/mysqld.cnf 下加入
[mysqld]
skip-grant-tables = 1
plugin-load-add = auth_socket.so

参考 https://stackoverflow.com/questions/41984956/cant-reset-root-password-with-skip-grant-tables-on-ubuntu-16

  1. 在配置完密码和远程访问后, 需要删除掉 1 新增的属性. 否则远程访问时无法访问

注意：lower_case_table_names=1只对修改完成之后的操作有效，修改之前的表仍然是大小写敏感的，对于这个问题的最简单办法就是把它们删了重建。

评论栏