Mysql 修改大小写敏感问题预计阅读时间 3 分钟

    .

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

    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只对修改完成之后的操作有效，修改之前的表仍然是大小写敏感的，对于这个问题的最简单办法就是把它们删了重建。

    评论栏