Home

深色模式

MySQL 配置 ​

MySQL 加载配置文件的顺序 ​

输入命令：

sh
mysql --help | grep 'my.cnf'

输出：

                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

可以看到 MySQL 搜寻配置文件的顺序：

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. /usr/etc/my.cnf
  4. ~/.my.cnf

如果在以上4个位置都没有my.cnf，MySQL 启动时不加载配置文件，会使用默认配置。

配置文件结构 ​

MySQL 配置文件使用 INI 格式，可以包含多个配置段：

  • [client] - 客户端程序配置
  • [mysql] - mysql 命令行客户端配置
  • [mysqld] - MySQL 服务器配置
  • [mysqld_safe] - mysqld_safe 启动脚本配置
  • [server] - 所有服务器相关程序的配置

常用配置项 ​

基础配置 ​

ini
[mysqld]
# 数据目录
datadir = /var/lib/mysql

# socket 文件路径
socket = /var/run/mysqld/mysqld.sock

# 端口号
port = 3306

# 绑定地址，0.0.0.0 表示监听所有网络接口
bind-address = 0.0.0.0

# 进程 ID 文件
pid-file = /var/run/mysqld/mysqld.pid

# 错误日志文件
log-error = /var/log/mysql/error.log

字符集配置 ​

ini
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'

连接配置 ​

ini
[mysqld]
# 最大连接数
max_connections = 200

# 最大错误连接数，超过后阻止连接
max_connect_errors = 10

# 连接超时时间（秒）
connect_timeout = 10

# 等待超时时间（秒），非交互式连接
wait_timeout = 28800

# 交互式连接超时时间（秒）
interactive_timeout = 28800

缓存配置 ​

ini
[mysqld]
# InnoDB 缓冲池大小，建议设置为物理内存的 50-70%
innodb_buffer_pool_size = 1G

# 查询缓存大小（MySQL 5.7 及以下）
query_cache_size = 64M
query_cache_type = 1

# 表缓存大小
table_open_cache = 2000

# 临时表大小限制
tmp_table_size = 64M
max_heap_table_size = 64M

InnoDB 配置 ​

ini
[mysqld]
# InnoDB 日志文件大小
innodb_log_file_size = 256M

# InnoDB 日志缓冲大小
innodb_log_buffer_size = 16M

# InnoDB 刷新日志方式
innodb_flush_log_at_trx_commit = 1

# InnoDB 文件格式
innodb_file_format = Barracuda

# InnoDB 文件每表空间
innodb_file_per_table = 1

日志配置 ​

ini
[mysqld]
# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 二进制日志
log-bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M

# 通用查询日志
general_log = 0
general_log_file = /var/log/mysql/general.log

安全配置 ​

ini
[mysqld]
# 禁止使用 LOAD DATA LOCAL INFILE
local-infile = 0

# SQL 模式
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 禁止符号链接
symbolic-links = 0

字符集和字符序 ​

概念 ​

字符集定义了字符如何编码，比如：utf8、utf8mb4。

字符序定义了该字符集内字符之间的比较规则，比如大小写字母判断相等。

一个字符集对应一或多个字符序，其中有一个是默认字符序。

字符序只会属于某一个字符集，它的名称以该字符集名作为前缀。

四个等级 ​

MySQL 字符集、字符序的设置，分为4个等级：

  1. server 级
  2. database 级
  3. table 级
  4. column 级

低层级会默认使用上一层级的字符集、字符序设置。即，如果创建一个 database 时指定了字符集是 utf8mb4，那么该 database 中所有表以及表中的列都默认使用 utf8mb4。

查看支持的字符集、字符序 ​

查看 MySQL 支持的字符集 ​

方式一：

sh
SHOW CHARACTER SET;

加条件查询：

sh
SHOW CHARACTER SET LIKE "utf8%";

方式二：

sh
use information_schema;
select * from CHARACTER_SETS;

加条件查询：

sh
select * from CHARACTER_SETS WHERE CHARACTER_SET_NAME like '%utf8%';

查看 MySQL 支持的字符序 ​

与上面类似，有两种方式：

sh
SHOW COLLATION WHERE Charset = 'utf8mb4';
sh
USE information_schema;
SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8mb4";

server 的字符集 ​

查看 ​

方式一：通过变量查看

分别对应character_set_server、collation_server两个系统变量。

默认的字符序分别为latin1、latin1_swedish_ci。

sh
SHOW VARIABLES LIKE "character_set_server";
SHOW VARIABLES LIKE "collation_server";

修改 ​

方式一：启动服务时指定

可以在MySQL服务启动时，指定server字符集、字符序。

sh
mysqld --character-set-server=utf8mb4 \
       --collation-server=utf8mb4_unicode_ci

可以仅指定server字符集，此时，字符序是utf8mb4的默认字符序。

sh
mysqld --character-set-server=utf8mb4

方式二：配置文件指定

除了在命令行参数里指定，也可以在配置文件里指定，如下所示。

ini
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

方式三：运行时修改

重启后会失效，如果想要重启后保持不变，需要写进配置文件里。

sh
SET character_set_server = utf8mb4;

方式四：编译时指定

character_set_server、collation_server的默认值，可以在MySQL编译时，通过编译选项指定：

sh
cmake . -DDEFAULT_CHARSET=utf8mb4 \
           -DDEFAULT_COLLATION=utf8mb4_unicode_ci

database 的字符集 ​

查看 ​

有3种方式可以查看数据库的字符集/字符序。

假设数据库名是test_schema：

方式一：通过变量查看

sh
use test_schema;
SELECT @@character_set_database, @@collation_database;

方式二：information_schema.SCHEMATA

sh
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME  FROM information_schema.SCHEMATA WHERE schema_name="test_schema";

方式三：SHOW CREATE DATABASE

sh
SHOW CREATE DATABASE test_schema;

修改 ​

方式一：创建数据库时指定

sh
CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8mb4;

方式二：运行时修改

sh
ALTER DATABASE `test_schema` DEFAULT CHARACTER SET utf8mb4;

table 的字符集 ​

假设数据库名是test_schema，表名是test_table：

查看 ​

方式一：SHOW TABLE STATUS

sh
SHOW TABLE STATUS FROM test_schema \G;

方式二：information_schema.TABLES

sh
USE test_schema;
SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table";

方式三：SHOW CREATE TABLE

sh
SHOW CREATE TABLE test_table;

修改 ​

方式一：创建表时指定

sh
CREATE TABLE `test_schema`.`test_table` (
  `id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '')
DEFAULT CHARACTER SET = utf8mb4;

column 的字符集 ​

查看 ​

方式一：information_schema.COLUMNS

sh
SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column";

修改 ​

方式一：创建列时指定

sh
ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8mb4;

实践建议 ​

在实践中，最好在 server 级别设置字符集。因为 server 默认字符集不支持中文，会导致用一些工具查看表时，表中的注释显示乱码。所以，在 server 级设置字符集为 utf8mb4 就可以解决中文乱码的问题。

关于utf8 与 utf8mb4 的区别，可以参考Mysql的UTF-8

查看和修改配置 ​

查看当前配置 ​

查看所有配置变量：

sh
SHOW VARIABLES;

查看特定配置：

sh
SHOW VARIABLES LIKE 'max_connections';

运行时修改配置 ​

某些配置可以在运行时修改：

sh
SET GLOBAL max_connections = 300;

或者仅对当前会话生效：

sh
SET SESSION max_connections = 300;

注意：运行时修改的配置在 MySQL 重启后会失效，需要将配置写入配置文件才能永久生效。

参考 ​