Linux Mysql8.0修改配置文件区分大小写

mysql8.0

修改my.cnf文件，[mysqld]块最后一行追加lower_case_table_names=1(0区分1不区分)

systemctl start mysqld.service启动服务【报错】

查看MySQL官方文档，有记录：

lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

只有在初始化(重装)的时候设置 lower_case_table_names=1才有效，比如：

/usr/sbin/mysqld --initialize --user=mysql --lower-case-table-names=1

 

如果你不在意数据的话直接删除数据（注：之前的数据库会不见删掉。要做好备份）

1. 停止MySQL
2. 删除 MySQL的数据 /var/lib/mysql
3. 修改该 lower_case_table_names = 1
4. 启动 mysql
5. 搞定

 

数据库初始化

查看默认初始密码：cat /var/log/mysqld.log | grep password

进入数据库mysql -uroot -p，填写对应的初始密码

修改登陆账户初始密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '147852963' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)

刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

进入mysql库

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> select host, user, authentication_string, plugin from user;
--------表格展示-----

创建账户

mysql> create user root@'%' identified  by '123456';
Query OK, 0 rows affected (0.01 sec)

授权

mysql> grant all privileges on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

修改远程连接用户的加密规则

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

亲测有效！