<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/kdoc_html_views-1a98987dfd.css">
<link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/ck_htmledit_views-25cebea3f9.css">
<div id="content_views" class="htmledit_views">
<p></p>
由于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