# 配置

  • MySQL 常见的几种配置方式如下:
    • 启动 mysqld 时读取配置文件。
    • 启动 mysqld 时加上命令行选项。
    • 在 mysqld 运行时,通过客户端登录,修改系统变量。
      • 但这样修改的变量在 mysqld 重启时不会保存,因此建议通过配置文件永久修改。
  • 同一个配置项,对应的命令行选项、配置文件参数、系统变量名不一定相同。
  • 配置参数参考列表 (opens new window)

# 配置文件

  • mysqld 启动时,会读取以下位置的配置文件,用它们覆盖默认配置:
    /etc/my.cnf
    /etc/mysql/my.cnf
    /usr/etc/my.cnf
    ~/.my.cnf
    
    • 配置文件采用 INI 格式,而且扩展名必须为 .cnf 才会被读取。

# 示例

[mysqld]                                    # 这部分配置会被 mysqld 命令读取
# user       = mysql                        # 指定运行 mysqld 进程的系统用户,以 root 用户启动时必须配置该参数
bind_address = 0.0.0.0
port         = 3306
datadir      = /var/lib/mysql               # 数据文件的保存目录
# socket     = /var/lib/mysql/mysql.sock
# pid_file   = /var/lib/mysqld/mysqld.pid

default_storage_engine    = InnoDB          # 设置 MySQL 默认使用的引擎
default_time_zone         = +8:00           # 设置时区,默认采用主机的时区
symbolic-links            = 0               # 在数据目录中禁止使用符号链接
# lower_case_table_names  = 0               # Unix 系统上默认为 0 ,使得表名在比较时区分大小写;设置为 1 时,表名在创建时先转换成小写,比较时不区分大小写;设置为 2 时,表名在比较时先转换成小写
# open_files_limit        = 1048576         # 限制服务器打开的文件描述符数
# performance-schema-instrument='memory/%=ON'   # 启用对内存占用的监控,MySQL 8.0 开始默认启用

character_set_server      = utf8mb4             # 服务器的默认字符集
collation_server          = utf8mb4_general_ci  # 服务器的默认字符序
# character_set_database  = utf8mb4             # 数据库的默认字符集,默认继承 character_set_server
# collation_database      = utf8mb4_general_ci
# init_connect            = 'SET autocommit=0;SET NAMES utf8mb4' # 指定一些 SQL 命令,让非 Super 用户每次连接时执行

# 关于客户端
# max_connect_errors  = 100                 # 限制客户端的错误连接数,超过该值则禁止连接
# max_connections     = 151                 # 限制客户端的连接数,超过该值则禁止连接,会报错:Too many connections
# max_allowed_packet  = 4194304             # 限制客户端请求包的最大大小,默认为 4M 。写入很大的 blob 字段时需要调大该参数
# connect_timeout     = 10                  # 客户端建立连接时,需要 TCP 3 次握手、MySQL 3 次握手。这是配置服务器等待握手的超时时间,单位为秒
# interactive_timeout = 28800               # 对于建立交互式连接的客户端,如果超过该时长未活动,则服务器会关闭其连接。单位为秒
# wait_timeout        = 28800               # 与 interactive_timeout 类似,但针对非交互式连接
# net_read_timeout    = 30                  # 服务器在客户端连接中读每个数据包的超时时间
# net_write_timeout   = 60                  # 服务器在客户端连接中写每个数据包的超时时间

# !includedir /etc/my.cnf.d/                # 可以用 !includedir 导入指定目录下的所有配置文件

[client]                                    # 这部分配置会被 mysql、mysqldump 等客户端命令读取
# port     = 3306                           # 设置连接服务器的端口
# socket   = /var/lib/mysql/mysql.sock
# user     = root                           # 设置用户名
# password = ******                         # 设置密码

# 访问控制

# 管理用户

SELECT * FROM mysql.user;                   -- 显示所有用户的信息

CREATE USER root@'127.0.0.1';               -- 创建用户 root ,只允许从该 IP 地址登录,且不需要输入密码
CREATE USER root@'10.0.%';                  -- 创建用户 root ,允许从该 IP 网段登录,且不需要输入密码
CREATE USER root@'%' identified by '123';   -- 创建用户,允许从任何 IP 地址登录,密码是 123

ALTER USER root@'%' identified by '******'; -- 修改用户的密码
UPDATE mysql.user SET authentication_string=password('******') WHERE user='root' AND host='%';   -- 直接修改 mysql.user 表

DROP USER root@'%';                 -- 删除用户
  • 所有用户的配置存储在 mysql.user 表。
  • 特别的是:'%' 不匹配 'localhost' 这个环回地址。
    • 例如 MySQL 默认会创建两个账号 root@'localhost'root@'%' ,两个账号的密码可以不同。如果从本地登录 MySQL ,则采用前一个账号进行认证。

# 用户权限

SHOW grants;                                            -- 查看当前用户的权限
SHOW grants for root@'%';                               -- 查看指定用户的权限

grant select,insert,update,delete on db1.* to root@'%'; -- 授予用户对于数据库 db1 中所有数据表的查询、修改、插入、删除权限
grant  all on *.* to root@'%';                          -- 授予用户对于所有数据库的全部权限,包括 Super 权限
revoke all on *.* FROM root@'%';                        -- 撤销权限

flush privileges;                                       -- 刷新权限表(否则要等到 MySQL 服务器重启时才会生效)

# 免密模式

如果忘记了密码,可以按以下步骤找回:

  1. 在 mysqld 的配置文件中加入以下参数,然后重启 mysqld :

    [mysqld]
    skip-grant-tables     # 跳过权限验证,此时不需要密码就能访问所有数据库
    skip-networking       # 禁止本机以外的客户端进行 TCP 连接
    

    或者通过命令行选项开启免密模式:

    mysqld --skip-grant-tables --skip-networking
    
  2. 用客户端登录 MySQL ,在免密模式下修改密码。

  3. 重新按正常的方式启动 mysqld 。

# 会话

  • MySQL 服务器会为每个建立 TCP 连接的客户端创建一个线程,用于身份认证、执行命令、保持会话。
    • 这个线程是 MySQL 内部的对象,并不是操作系统的线程。
  • 管理会话的相关命令:
    SHOW STATUS LIKE 'thread%';   -- 显示全部客户端的连接数
    
    SHOW processlist;   -- 列出 MySQL 服务器上的所有客户端会话,包括用户名、IP、连接的数据库、执行的命令等信息。普通用户只能看到自己的会话
    kill query <id>;    -- 终止某个会话当前执行的命令。如果正在执行事务,则通过 undo log 进行回滚
    kill <id>;          -- 终止某个会话
    

# 变量

# 作用域

  • 变量的作用域分为两种:
    • 全局变量(global)
      • :作用于 MySQl 服务器,影响所有客户端会话。
      • 每次 mysqld 启动时,会根据命令行选项、配置文件初始化全局变量。
      • 当 mysqld 运行时,只有 root 用户有权修改全局变量的值。
    • 会话变量(session)
      • :只作用于某个客户端会话。
      • 每次客户端建立会话时,会根据全局变量初始化会话变量。
      • 客户端可以修改自己的会话变量,不会影响其它客户端。
  • 有的变量同时存在 global、session 作用域,可以分别操作。

# 系统变量

:system variable ,用于记录 MySQL 的配置信息。

  • 用法:
    SHOW variables;                     -- 查看所有系统变量
    SHOW global  variables;             -- 查看 global 作用域的
    SHOW session variables;
    SHOW variables LIKE '%time_zone%';  -- 查看指定名称的
    SELECT @@global.time_zone;          -- 查看指定作用域、指定名称的
    
    SET global   time_zone = '+8:00';   -- 修改系统变量
    SET @@global.time_zone = '+8:00';
    

# 状态变量

:status variable ,用于记录 MySQL 的运行状态。

  • 只能读取,不能修改。
  • 用法:
    SHOW STATUS;            -- 查看所有状态变量
    SHOW GLOBAL  STATUS;
    SHOW SESSION STATUS;
    SHOW SESSION STATUS LIKE '%time_zone%';
    

# 用户变量

:由用户定义的变量,变量名必须加上 @ 前缀。

  • 作用域属于会话变量。
  • 例:
    mysql> SET @x = 1, @y = 'Hello';      -- 创建用户变量并赋值
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SELECT @x, @y;                 -- 读取用户变量的值
    +------+-------+
    | @x   | @y    |
    +------+-------+
    |    1 | Hello |
    +------+-------+
    1 row in set (0.00 sec)
    
    mysql> SELECT x;        -- 用户变量如果不加上 @ 前缀,则会被当作列名进行查询
    ERROR 1054 (42S22): Unknown column 'x' in 'field list'
    
    mysql> SELECT @z;       -- 如果读取一个不存在的用户变量,则返回的值为 NULL
    +------+
    | @z   |
    +------+
    | NULL |
    +------+
    1 row in set (0.00 sec)
    
  • 也可用 INTO 关键字给变量赋值:
    SELECT 2 INTO @x;
    SELECT @x;
    
  • = 赋值时可能被当作比较运算符处理,此时可以改用 := 赋值。

# 局部变量

:由用户在函数、存储过程等语句块内定义的变量,且变量名没有加上 @ 前缀。

  • 例:
    DECLARE a int default 0;
    SET a = 1;
    

# 字符集

  • 字符集(character set):指字符的编码格式。
  • 字符序(collation):指字符的排序方式。
    • 每种字符集有多个配套的字符序。

# 常见类型

  • latin1
    • :MySQL v5.7 的默认字符集。
  • utf8
    • :每个字符最多占 3 字节,不完全支持标准的 utf-8 字符。
      • 因为 MySQL 引入该字符集时,utf8 标准尚未统一。
    • 其默认字符序为 utf8_general_ci ,不区分大小写。
      • 另一种字符序 utf8_bin 是按二进制值存储每个字符,因此会区分大小写。
  • utf8mb4
    • :每个字符最多占 4 字节,完全支持标准的 utf-8 字符。
    • 其默认字符序为 utf8mb4_general_ci 。
    • 建议采用这种字符集。
  • MySQL 中,数据库名、表名要区分大小写,而字段名不区分大小写。
    • 如果要让字段的内容区分大小写,可以采用字符序 utf8_bin ,也可以在执行 SELECT 命令时加上关键字 binary 。如下:
      SELECT * FROM tb1 WHERE binary name = 'Aa';
      

# 配置

  • MySQL 服务器、客户端、数据库、数据表、字段可以分别设置字符集、字符序。
    • 创建数据库、数据表时,如果没有指定字符集、字符序,则使用 MySQL 服务器的默认字符集,以及该字符集的默认字符序。
    • 如果 MySQL 客户端使用的字符集与服务器存储的字符集不一致,查询到的数据就可能乱码。
  • 相关命令:
    SHOW variables LIKE 'character%';                       -- 显示 MySQL 服务器、客户端的字符集
    SHOW variables LIKE 'collation%';
    SELECT @@character_set_database, @@collation_database;  -- 显示当前数据库的字符集、字符序
    
    SET NAMES utf8mb4                                       -- 设置当前客户端与服务器通信的字符集(只作用于当前会话)
        [COLLATE utf8mb4_general_ci]
    
    CREATE DATABASE db1 CHARACTER SET utf8mb4               -- 在创建数据库时设置字符集
                        [COLLATE utf8mb4_general_ci]        -- 设置字符序
    ALTER  DATABASE db1 CHARACTER SET utf8mb4               -- 修改数据库的默认字符集(只会影响新建的数据表)
    
    CREATE TABLE tb1 CHARACTER SET utf8mb4                  -- 在创建数据表时设置字符集
    ALTER  TABLE tb1 CHARACTER SET utf8mb4                  -- 修改数据表的默认字符集(只会影响新增的字段)
                     [COLLATE utf8mb4_general_ci]           -- 设置字符序
    ALTER  TABLE tb1 CONVERT TO CHARACTER SET utf8mb4       -- 转换数据表的字符集(会影响已有的所有字段)
    
    ALTER  TABLE tb1 ADD COLUMN name varchar(25) CHARACTER SET utf8mb4  -- 在新增字段时设置字符集
    ALTER  TABLE tb1 MODIFY name varchar(25) CHARACTER SET utf8mb4      -- 修改已有字段的字符集
    

# SQL Mode

:SQL 模式,用于在执行 SQL 时进行一些语法检查。

  • sql_mode 举例:
    ERROR_FOR_DIVISION_BY_ZERO  # 在算术运算中,除以 0 时会报错。禁用该模式时,除以 0 的结果为 NULL
    
    NO_AUTO_CREATE_USER         # 执行 grant 语句时,如果用户不存在,也没有指定密码,则不会自动创建用户。禁用该模式时,无密码也会自动创建用户
    NO_AUTO_VALUE_ON_ZERO       # 给一个 AUTO_INCREMENT 类型的字段赋值为 0 时,不会自动转换成下一个自增值。禁用该模式时,赋值为 0 或 NULL 都会自动转换
    NO_ENGINE_SUBSTITUTION      # 创建数据表时,如果指定的存储引擎不可用,则中断执行并报错。禁用该模式时,会使用默认的存储引擎
    NO_ZERO_DATE                # 给一个日期字段赋值时,不允许值为 0 。禁用该模式时,赋值为 0 时会保存为 0000-00-00
    NO_ZERO_IN_DATE             # 给一个日期字段赋值时,不允许月份或日期为 0 。比如不允许 2010-00-01、2010-01-00
    
    ONLY_FULL_GROUP_BY          # group by 子句必须包含 SELECT 选中的所有字段,否则拒绝执行。这是 SQL 的旧版标准,建议禁用该模式
    
    STRICT_TRANS_TABLES         # 为事务性存储引擎启用严格模式。比如如果向一个事务表写入值时失败,则中断执行
    STRICT_ALL_TABLES           # 为所有存储引擎启用严格模式
    
  • 相关命令:
    SELECT @@global.sql_mode;       -- 查询全局的 sql_mode
    SELECT @@session.sql_mode;      -- 查询会话的 sql_mode
    
    SET global  sql_mode = '...';   -- 设置全局的 sql_mode ,这需要 root 权限,会一直生效直到 MySQL 重启
    SET session sql_mode = '...';   -- 设置会话的 sql_mode
    
  • MySQL 默认启用了多个 sql_mode 。用户可以修改 my.cnf 文件,只启用必要的几个 sql_mode ,然后重启 MySQL :
    [mysqld]
    sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
    

# undo log

:用于撤销最近提交的一些事务。

  • 每次提交事务时,InnoDB 引擎会用 undo log 记录相应的逆操作。
    • 例如执行一个 delete 操作时,就记录恢复数据的 insert 操作。
  • 一个事务包含的操作越多,记录的 undo log 就越大。
  • redo log 处理底层的数据页,而 undo log 处理逻辑层的命令操作。

# 内存

  • InnoDB 引擎存储数据的基本单位为 page ,默认为 16K 。
    • 每个 page 拥有一个递增的数字序号(Log Sequence Number ,LSN)。

# Buffer

  • InnoDB 引擎通过 redo log buffer 实现写缓冲,通过 innodb_buffer_pool 实现读缓存。

    • 查询数据时,先尝试查询 innodb_buffer_pool 中的数据。
      • 如果未命中缓存,则从磁盘加载数据页(page),并缓存。
    • MySQL 刚启动时,innodb_buffer_pool 为空。经过一些查询操作之后,innodb_buffer_pool 会变大,从而增加缓存命中率,提高查询速度。
    • 当 innodb_buffer_pool 变满时,会根据 LRU 算法删除较少使用的 page 。
  • innodb_buffer_pool 分为以下部分:

    • data page
    • index page
    • insert buffer :插入操作的缓冲区。当索引为二级索引,且非 unique 时,才会缓冲。
    • adaptive hash index :自适应哈希索引。
    • lock info :锁信息。
    • data dictionary :数据字典,包括数据库、表、索引等对象的元数据。
  • 相关配置:

    innodb_buffer_pool                  = 134217728      # innodb_buffer_pool 的总内存上限。默认为 128M ,会四舍五入为 innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances 的整数倍
    innodb_buffer_pool_chunk_size       = 134217728      # 组成 innodb_buffer_pool 的块大小。默认为 128M
    innodb_buffer_pool_instances        = 1              # 允许运行多个 innodb_buffer_pool 实例,支持并发读取。建议每个实例的内存不超过 1G
    
    innodb_buffer_pool_dump_at_shutdown = ON             # 当 MySQL 停止时,是否将 innodb_buffer_pool 中 pages 的 LSN 保存到磁盘,便于重启时进行恢复
    innodb_buffer_pool_dump_pct         = 25             # 保存的 pages 百分比,默认为 25%
    innodb_buffer_pool_filename         = ib_buffer_pool # 保存的文件名
    innodb_buffer_pool_load_at_startup  = ON             # 当 MySQL 启动时,是否从磁盘恢复 innodb_buffer_pool
    
    • 建议将 innodb_buffer_pool 容量最多设置为主机内存的 80% 。

# Query Cache

  • :用于将 SELECT 命令的整个查询结果缓存起来,之后如果执行相同哈希值的 SELECT 命令,则返回缓存的数据。
  • 如果原数据经常变化,则 Query Cache 会经常失效。
  • MySQL 5.7 开始,不推荐使用 Query Cache ,并在 MySQL 8.0 移除。
    • 建议在 MySQL 外部实现缓存机制,比如用 Redis 。

# Temporary Table

  • MySQL 在执行某些 SQL 时会自动创建内部临时表,比如 group by 。

    • 用户主动创建的临时表默认采用 InnoDB 引擎,而内部临时表采用 TempTable 引擎,存储在内存中。
    • 如果临时表超出内存限制,则会改用 InnoDB 引擎,写入磁盘 tmpdir 目录的临时文件中。
  • 相关配置:

    tmp_table_size      = 16777216    # 限制单个临时表占用的内存,默认为 16M
    max_heap_table_size = 16777216    # 限制用户创建的单个临时表占用的内存,默认为 16M
    temptable_max_ram   = 1073741824  # 限制全部临表占用的内存,默认为 1G
    temptable_use_mmap  = on          # 临时表超出内存限制时,是否采用 MMAP ,而不是改用 InnoDB 引擎写入磁盘
    temptable_max_mmap  = 1073741824  # 显示 MMAP 映射的内存容量,默认为 1G 。超过则改用 InnoDB 引擎写入磁盘
    
  • 相关命令:

    SHOW STATUS LIKE '%Created_tmp_tables%';        -- 查询累计创建的临时表数
    SHOW STATUS LIKE '%Created_tmp_disk_tables%';   -- 累计存储到磁盘的临时表数