运行环境:centos 7.5+MySQL8.0.12
背景:
使用YUM或者RPM按照默认的参数安装后要修改不区分大小写参数报错
[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
解决办法:
1.若已经运行了一段时间产生了数据则需要备份数据
2.若是新安装没多久没有产生重要数据则可重新配置参数重新安装。
我这里由于是体验mysql8.0.12版本没有产生数据。
1.重新配置参数文件my.cnf,在MySQL启动之前将参数lower_case_table_names设置为不区分大小写:
# cat /etc/my.cnf | grep -v ^#
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log_timestamps = SYSTEM
lower_case_table_names = 1
default-authentication-plugin =mysql_native_password
2.停掉运行的MySQL进程删除默认datadir下的文件,清空错误日志文件便于重启查看:
# systemctl stop mysqld
# rm -rf /var/lib/mysql/*
# echo '' > /var/log/mysqld.log
3.重启MySQL 查看临时密码 登录数据库:
# systemctl start mysqld
# cat /var/log/mysqld.log | grep -i password
2018-07-29T00:29:19.851620+08:00 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !U5uL5VU1e4g
# mysql -p -S /var/lib/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.12
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> show variables like '%valid%password%';
登录进去之后首先要求修改root用户的密码:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@'localhost' identified by 'Oracle@2018';
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'valid%pass%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
按照密码强度设置用户的密码。
4.若不想设置太复杂的密码 可以首先设置复杂的密码之后卸载密码强度认证的组件:
mysql> UNINSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.10 sec)
mysql> show variables like 'valid%pass%';
Empty set (0.00 sec)
-- 删除之后再把密码修改为简单的,卸载之后重启mysql也没有密码强度认证了。
mysql> alter user root@'localhost' identified by 'oracle';
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
5.设置可以远程登录的用户:
mysql> create user root@'%' identified by 'oracle';
Query OK, 0 rows affected (0.06 sec)
mysql> grant all privileges on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.04 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
6.查看配置的区分大小写的参数是否生效:
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
+------------------------+-------+
2 rows in set (0.01 sec)
7.查看8.0版本默认的字符集和排序规则:
mysql> show variables like '%char%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)
mysql> show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name | Value |
+-------------------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12 |
+-----------+
1 row in set (0.00 sec)