lower_case_table_names 

简介： lower_case_table_names 是mysql设置大小写是否敏感的一个参数。

• lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
• lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
• lower_case_table_names=2 表名存储为给定的大小写但是比较的时候是小写的

注意：  这个需要在初始化数据的时候就加上去，安装之后改my.cnf 是不行的。Mysql5.7 可以在安装之后改参数。mysql8.0不行。

datadir=/data/db_mysql/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

##
innodb_buffer_pool_size=8G
#max_connections=1000

## 不区分大小写
lower_case_table_names=1

字符集设置

mysql8.0 默认是ut8mb4。

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| 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     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

修改成utf-8的方法如下:

[client]
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqld]

datadir=/data/db_mysql/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

innodb_buffer_pool_size=8G
max_connections=1000

lower_case_table_names=1

init-connect='SET NAMES utf8'
character-set-server = utf8
collation-server = utf8_unicode_ci

验证

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb3                        |
| character_set_connection | utf8mb3                        |
| character_set_database   | utf8mb3                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb3                        |
| character_set_server     | utf8mb3                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb3_general_ci |
| collation_database   | utf8mb3_unicode_ci |
| collation_server     | utf8mb3_unicode_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

创建用户和授权

创建用户和5.x不一样。5.x的命令无法使用

CREATE USER 'knight'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON db_devops.* TO 'knight'@'%';