由于mysql8不支持修改lower_case_table_names,需要重新初始化数据库。并导入数据
(1)备份数据
参考:
mysqldump -uroot -p'密码' --databases 库名 --complete-insert --single-transaction -E -R --triggers --default-character-set=UTF8 --flush-privileges=TRUE >文件名_2022-03-31.sql
(2)备份用户名,密码
mysqldump -uroot -p'密码' mysql user > /tmp/user.sql
(3)备份权限
SHOW GRANTS FOR 用户名;
(4)备份data目录,新建data目录
mv mysql mysql_20230713
mkdir mysql
chown -R mysql.mysql ./mysql
(5)修改/etc/my.cnf增加
lower_case_table_names=1
(6)重新初始化
systemctl start mysqld
(7)查看日志
2023-07-13T14:31:43.814558Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: dkoMsVQDN6*E
(8)用临时密码登录修改密码:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';
查看
mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+
1 row in set (0.01 sec)
mysql>
(9)建库
create database 数据库名称;
(10)创建用户,授权
参照 user.sql
参照SHOW GRANTS FOR 用户名;
(11)导数
use 数据库名称;
source /root/数据库名称.sql