注释:lower_case_table_names = 0
测试:
mysql> create database data1;
Query OK, 1 row affected (0.00 sec)
mysql> use data1;
Database changed
mysql>
mysql> create table Table1(id int);
Query OK, 0 rows affected (0.10 sec)
mysql>
mysql> select * from Table1;
Empty set (0.00 sec)
mysql>
mysql> select * from table1;
ERROR 1146 (42S02): Table 'data1.table1' doesn't exist
mysql>
############
############
修改MySQL的主配置文件,忽略表名的英文字母的大小写:
[root@redhat8 ~]# vi /etc/my.cnf
在[mysqld]下添加:
lower_case_table_names = 1
效果如下:
[root@redhat8 ~]# cat /etc/my.cnf |grep -v "^#" |grep -v "^$" |grep -A4 "mysqld]"
[mysqld]
lower_case_table_names = 1
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
[root@redhat8 ~]#
注释:MySQL数据目录为 /var/lib/mysql
重启MySQL:(重启失败了)
[root@redhat8 ~]# 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.
[root@redhat8 ~]#
解决方法:
先删除MySQL的数据目录:
[root@redhat8 ~]# rm -rf /var/lib/mysql
[root@redhat8 ~]#
再初始化MySQL:
[root@redhat8 ~]# find / -name mysqld
/run/mysqld
/usr/sbin/mysqld
[root@redhat8 ~]#
[root@redhat8 ~]# /usr/sbin/mysqld --initialize --user=mysql --datadir=/var/lib/mysql --lower-case-table-names=1
2022-07-10T06:18:51.608547Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.26) initializing of server in progress as process 1858
2022-07-10T06:18:51.639963Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-10T06:18:52.372930Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-07-10T06:18:53.236022Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-07-10T06:18:53.236270Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-07-10T06:18:53.275457Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 7h?)1PPBwyKa
[root@redhat8 ~]#
注释:
MySQL初始化后,会出现数据库管理员root@localhost新的初始密码:7h?)1PPBwyKa
重启MySQL:(重启成功了)
[root@redhat8 ~]# systemctl restart mysqld
[root@redhat8 ~]#
使用数据库管理员root@localhost登录MySQL:
[root@redhat8 ~]# mysql -u"root" -h"localhost" -p"7h?)1PPBwyKa"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26
Copyright (c) 2000, 2021, 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>
mysql> show variables like '%low%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
必须要修改数据库管理员root@localhost的密码:(密码要符合复杂性要求)
mysql> ALTER USER root@'localhost' IDENTIFIED BY 'P@ssw7rd';
Query OK, 0 rows affected (0.00 sec)
mysql>
使用数据库管理员root@localhost重新登录MySQL:
[root@redhat8 ~]# mysql -u"root" -h"localhost" -p"P@ssw7rd"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 MySQL Community Server - GPL
Copyright (c) 2000, 2021, 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>
更改已经生效: