Ubuntu 20+安装MySQL8及表名大小写处理_ubuntu22.04 lower_case_table_names=1

1、安装
#命令1
sudo apt-get update
#命令2
sudo apt-get install mysql-server

2、配置
初始化
sudo mysql_secure_installation

3、检查
检查服务状态
systemctl status mysql.service

4、问题
Access denied for user 'root'@'localhost'`
解决：
sudo mysql
use mysql
mysql>update user set plugin='mysql_native_password' where User='root';
mysql8
ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';
mysql5.7
ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';

出现：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>set global validate_password_length=1;
mysql>set global validate_password_policy=0;
MySQL 8.0 调整密码验证规则：
mysql> set global validate_password.policy=0;
mysql> set global validate_password.length=1;
重新执行
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';
mysql8
ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';
mysql> flush privileges;
mysql>exit;

5、大小写问题（mysql8需要在初始化之前修改，否则可能无法启动mysql）
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下添加
lower_case_table_names=1
修改后无法启动解决
eg: /var/lib/mysql
#备份好数据后，删除/var/lib/mysql 目录

#重新创建/var/lib/mysql文件夹（注意给mysql用户授权）
#重新初始化mysql，并制定策略
sudo mysqld --initialize --user=mysql --lower-case-table-names=1

重启mysql
service mysql restart #注意mysql8的服务名时mysql而MySQL7的服务名是mysqld

密码处理
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下添加
skip-grant-tables
service mysql restart
sudo mysql
mysql>update user set plugin='mysql_native_password' where User='root';
update user set authentication_string='' where user='root';
mysql>exit
去掉skip-grant-tables
service mysql restart
mysql -uroot -p
mysql>ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';
mysql>flush privileges;
mysql>exit
mysql -uroot -p'新密码'

6、卸载MySQL
sudo rm /var/lib/mysql/ -R
sudo rm /etc/mysql/ -R
sudo apt-get autoremove mysql* --purge