mysql 安装完成后需要做的配置

前言

• CentOS 7
• MySQL 5.7

1. 设置 mysql 默认的编码格式

推荐设置为：utf8mb4

修改mysql的配置文件

shell> vim /etc/my.cnf
-----------------------------
# 打开文件后，添加下面的配置。注意对应节点
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

重启mysql

shell> systemctl restart mysqld

查看编码格式是否修改

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| 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               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

优化和修复所有的表

shell> mysqlcheck -u root -p --auto-repair --optimize --all-databases

2. 设置 table name 不区分大小写

查看 table name 是否区分大小写：

mysql> show variables like '%table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

Linux下的MYSQL默认是要区分表名大小写的 ，而在windows下表名不区分大小写。

修改mysql的配置文件，让 mysql table name 不区分大小写：

shell> vim /etc/my.cnf
-----------------------------
# 打开文件后，添加下面的配置。注意对应节点
[mysqld]
lower_case_table_names = 1

重启mysql

shell> systemctl restart mysqld

再次查看 table name 是否区分大小写：

mysql> show variables like '%table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.00 sec)