mysql8卸载重新安装并配置lower_case_table_names=1

# 卸载
root@iZuf6afyp0j8anyom0ro8zZ:~# apt purge mysql-* -y
# 查看是否还有残余依赖
root@iZuf6afyp0j8anyom0ro8zZ:~# dpkg --list|grep mysql
# 删除数据目录以及配置文件目录
root@iZuf6afyp0j8anyom0ro8zZ:~# rm -rf /var/lib/mysql /etc/mysql/

apt-get install mysql-server -y

cd /etc/mysql/mysql.conf.d/

[mysqld]
character_set_server = utf8mb4
lower_case_table_names = 1
# 注释掉 bind-address		= 127.0.0.1

root@iZuf6afyp0j8anyom0ro8zZ:~# rm -rf /var/lib/mysql
root@iZuf6afyp0j8anyom0ro8zZ:~# mkdir /var/lib/mysql
root@iZuf6afyp0j8anyom0ro8zZ:~# chown mysql:125 /var/lib/mysql
# 初始化数据库
root@iZuf6afyp0j8anyom0ro8zZ:/etc/mysql/mysql.conf.d# /usr/sbin/mysqld --initialize --user=root --lower-case-table-names=1
# 重启mysql看看有没有报错
root@iZuf6afyp0j8anyom0ro8zZ:~# systemctl restart mysql

root@iZuf6afyp0j8anyom0ro8zZ:/var/lib/mysql# grep "A temporary password" /var/log/mysql/error.log
2022-11-24T02:35:31.584059Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: =mg&eZ8sA%TV

root@iZuf6afyp0j8anyom0ro8zZ:/var/lib/mysql# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 811
Server version: 8.0.31-0ubuntu0.22.04.1

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> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password by 'mysql数据库root密码';
Query OK, 0 rows affected (0.00 sec)

root@iZuf6afyp0j8anyom0ro8zZ:~# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.31-0ubuntu0.22.04.1 (Ubuntu)

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> 

mysql> CREATE USER 'mysql'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.01 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET host = '%' WHERE user = 'mysql';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> create database test default charset utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)

# 赋予'mysql'@'%'数据库为test的所有权限
mysql> GRANT ALL ON test.* TO 'mysql'@'%';
Query OK, 0 rows affected (0.01 sec)
# 从MySQL系统授权表中重新读取权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

iptables -A INPUT -p tcp --dport 3306 -j ACCEPT