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

    评论栏