# 配置

  • 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 'aaa';   -- 创建用户,允许从任何 IP 地址登录,密码是 aaa

alter user root@'%' identified by '******'; -- 修改用户的密码
update mysql.user set authentication_string=password('******') where user='root' and host='%';   -- 直接修改 mysql.user 表

drop user root@'%';                 -- 删除用户
  • 用户的账号信息存储在 mysql.user 表 中。

# 用户权限

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)
    
  • = 赋值时可能被当作比较运算符处理,此时可以改用 := 赋值。

# 局部变量

:由用户在某个语句块内定义的变量,且变量名没有加上 @ 前缀。

  • 例:
    drop function if exists test_print; -- 删除已存在的同名函数
    
    delimiter //                        -- 声明 SQL 命令的结束符,默认为分号 ;
    create function test_print (x int)  -- 创建一个函数
    returns int DETERMINISTIC           -- 声明函数的返回值类型
    begin
        declare a int default 0;        -- 创建一个用户变量 x ,类型为 int ,默认值为 0
        set a = 1;
        return a + x;
    end//
    delimiter ;
    
    select test_print(3);               -- 调用函数
    

# 字符集

  • 字符集(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 模式举例:
    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 选中的所有字段
    
    STRICT_TRANS_TABLES         # 为事务性存储引擎启用严格模式。比如如果向一个事务表写入值时失败,则中断执行
    STRICT_ALL_TABLES           # 为所有存储引擎启用严格模式
    
  • 相关命令:
    select @@global.sql_mode;       -- 查询全局的 SQL 模式
    select @@session.sql_mode;      -- 查询会话的 SQL 模式
    
    set global  sql_mode = '...';   -- 设置全局的 SQL 模式,这需要 root 权限,会一直生效直到 MySQL 重启
    set session sql_mode = '...';   -- 设置会话的 SQL 模式
    
  • 可以在 MySQL 的配置文件中持久地配置 SQL 模式:
    [mysqld]
    sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
    

# 日志

mysqld 可以产生多种日志,不过默认只启用了错误日志。

# Error Log

:错误日志,包括 mysqld 启动、停止、报错的日志。

  • 相关配置:
    [mysqld]
    log_error    = /var/lib/mysql/mysql-error.log   # 错误日志的保存路径。默认为 log_error=stderr ,即输出到终端
    
  • 日志内容示例:
    2020-01-12T09:09:17.376824Z 0 [Note] Server socket created on IP: '0.0.0.0'.                  # 创建 Socket
    2020-01-12T09:09:17.385746Z 0 [Note] Event Scheduler: Loaded 0 events
    2020-01-12T09:09:17.385880Z 0 [Note] mysqld: ready for connections.                           # mysqld 启动成功,允许被连接
    Version: '5.7.26-29-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 29, Revision 11ad961
    2020-01-12T09:34:51.166819Z 5 [Note] Access denied for user root@'localhost' (using password: YES)    # 客户端连接出错(连接成功则不会在该日志中记录)
    
  • 相关命令:
    flush logs;           -- 关闭并重新打开所有类型的日志文件,这会将内存中的日志缓冲立即写入磁盘
    flush binary  logs;   -- 关闭当前打开的 binlog 文件,并创建一个新的 binlog 文件供写入
    flush engine  logs;
    flush error   logs;
    flush general logs;
    flush slow    logs;
    flush replay  logs;
    

# General Query Log

:通用查询日志。用于记录 mysqld 执行过的所有 SQL 命令。

  • 相关配置:
    [mysqld]
    general_log       = on                          # 是否启用。默认禁用,因为会拖慢 MySQL 的处理速度
    general_log_file  = /var/lib/mysql/mysql.log
    
  • 日志内容示例:
    Time                            Id  Command   Argument
    2020-01-12T09:10:00.371447Z     2   Connect   root@localhost on  using Socket     # 客户端建立连接
    2020-01-12T09:10:00.371642Z     2   Query     select @@version_comment limit 1
    2020-01-12T09:10:24.049055Z     2   Query     show variables like '%time_zone%'   # 执行一条 SQL 命令
    2020-01-12T09:34:34.644737Z     2   Query     show tables
    2020-01-12T09:34:43.278005Z     2   Quit                                          # 客户端断开连接
    
    • Id 表示这是 mysql 重启之后,第几次建立的客户端连接。

# Slow Query Log

:慢查询日志。用于记录耗时较久的查询操作,供用户定位问题,进行优化。

  • 相关配置:

    [mysqld]
    slow_query_log=on                     # 是否启用慢查询日志。默认禁用,因为会拖慢 MySQL 的处理速度
    long_query_time=3                     # 慢查询的阈值,默认为 10 秒。超过该时长的查询操作才会被记录
    log_queries_not_using_indexes=off     # 是否记录未使用索引的查询操作
    # slow_query_log_file='/var/lib/mysql/mysql-slow.log'   # 日志文件的保存路径(MySQL 5.6 版本开始支持该参数)
    
  • 也可以在 MySQL 终端中配置,当 MySQL 重启时就会失效:

    mysql> set global slow_query_log=on;
    mysql> set global long_query_time=3;
    
  • 直接查看慢查询日志文件比较麻烦,可以用 mysqldumpslow 命令进行筛选:

    mysqldumpslow <slow.log>
                  -s            # 指定排序方式,默认为 at
                      c         # count ,访问次数
                      l         # lock time ,锁定时间
                      r         # rows sent ,返回的记录数
                      t         # query time ,查询时间
                      al        # average rows sent
                      ar        # average query time
                      at        # average lock time
                  -r            # 倒序排列
                  -t 10         # 只显示前 10 条
                  -g PATTERN    # 通过正则匹配进行筛选
    

    例:

    mysqldumpslow -s r -t 10 slow.log
    

# Binary Log

:二进制日志。用于记录 MySQL 执行的所有事务,默认禁用。

  • 常用于拷贝数据、备份数据。
  • mysqld 可以保存多个 binlog 文件,每个文件记录多个事务。
    • 一个事务可能包含一组事件(event),比如 INSERT、UPDATE 。因此 binlog 记录数据的基础单位是 event 。
    • binlog 文件中,每个 event 有确定的字节偏移量 start-position、stop-position ,而事务也是根据 position 来定位。

# 相关配置

[mysqld]
server_id         = 1                 # 启用 binlog 时,必须指定服务器的唯一 ID ,以支持主从同步
log_bin           = mysql-bin         # 启用 binlog ,并指定其保存路径,默认在 datadir 目录下。实际保存时会加上时间字符串作为后缀
# log_bin_index   = mysql-bin.index   # binlog 的索引文件的保存路径,默认为 ${log_bin}.index

# binlog_format   = row         # binlog 的记录格式
# binlog_cache_size = 32k       # binlog 的内存缓冲区大小,单位为 bytes 。超出缓冲区的事务会存储到磁盘的临时文件中
sync_binlog       = 0           # 每提交几次事务就写入 binlog 文件。默认为 1 ,安全性最高,但性能最差。取值为 0 则由文件系统自动写入,性能最好
expire_logs_days  = 7           # binlog 文件的保存天数,过期后会自动删除。默认为 0 ,即不自动删除
# max_binlog_size = 1G          # 每个 binlog 文件的最大大小,超过则创建一个新的文件,文件编号递增。不过一个事务总是会整个写入一个 binlog 文件中,因此一个 binlog 文件可能超过最大大小
gtid_mode         = on          # 是否启用 gtid ,默认为 off
enforce-gtid-consistency = on   # 是否只允许 master 执行支持 gtid 的事务,默认为 off

# binlog-do-db    = db1         # 记录该数据库(其它的默认不记录)
# binlog-do-db    = db2         # 可以多次配置该参数
# binlog-ignore-db= db1         # 不记录该数据库(其它的默认记录)
# binlog-ignore-db= db2
  • binlog 有三种记录格式:
    • statement
      • :记录 MySQL 执行的每条写操作的 SQL 命令.
      • 优点:日志量较少。
      • 缺点:还原数据时,SQL 命令的执行结果不一定与原来一致。比如重复执行 update tb1 set datetime=now() where id=1; 时,now() 函数的返回值不同。
    • row
      • :记录 MySQL 对每行数据做出的实际修改。
      • 优点:还原数据时,能保证与原来的数据一致。
      • 缺点:日志量较多。
    • mixed
      • :一般的操作用 statement 方式记录,状态可能变化的操作用 row 方式记录。
  • binlog 根据 position 定位每个事务,因此主从复制时,各个事务必须按顺序传输。
    • 启用 gtid 时,会给每个已提交事务分配一个全局事务 ID(Global Transaction ID ,GTID),格式为 server_uuid:transaction_id
      • 此时,不需要根据 position 定位事务,因此不需要按顺序传输事务,还可以避免 slave 重复执行同一事务。

# 相关命令

  • 显示 binlog 的配置:

    mysql> show variables like '%log_bin%';
    +---------------------------------+--------------------------------+
    | Variable_name                   | Value                          |
    +---------------------------------+--------------------------------+
    | log_bin                         | ON                             |    -- 说明是否启用了 binlog
    | log_bin_basename                | /var/lib/mysql/mysql-bin       |
    | log_bin_index                   | /var/lib/mysql/mysql-bin.index |
    | log_bin_trust_function_creators | OFF                            |
    | log_bin_use_v1_row_events       | OFF                            |
    | sql_log_bin                     | ON                             |    -- 是否为当前 session 启用 binlog
    +---------------------------------+--------------------------------+
    
  • 显示 binlog 的内容:

    mysql> show binlog events in 'mysql-bin.000060' limit 7;
    +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
    | Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                 |
    +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
    | mysql-bin.000060 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.25-15, Binlog ver: 4                                 |  -- binlog 固定格式的开头
    | mysql-bin.000060 | 125 | Previous_gtids |         1 |         196 | f75d3723-1c3e-11ec-a666-0242c0a8f002:1-7754                          |
    | mysql-bin.000060 | 196 | Gtid           |         1 |         275 | SET @@SESSION.GTID_NEXT= 'f7343723-8di7-11ec-aj66-0r36c0a8f002:7455' |
    | mysql-bin.000060 | 275 | Query          |         1 |         370 | BEGIN                                                                |  -- 开始一个事务
    | mysql-bin.000060 | 370 | Table_map      |         1 |         449 | table_id: 85 (db1.tb1)                                               |
    | mysql-bin.000060 | 449 | Update_rows    |         1 |         619 | table_id: 85 flags: STMT_END_F                                       |
    | mysql-bin.000060 | 619 | Xid            |         1 |         650 | COMMIT /* xid=351 */                                                 |  -- 结束一个事务
    +------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------------+
    
  • 其它命令:

    show binary logs;                           -- 显示当前所有 binlog 文件
    show binlog events in 'mysql-bin.000001'    -- 显示一个 binlog 的内容
                          from 0 limit 2        -- 显示从 position=0 开始的最多 2 个 event
    show master status;                         -- 显示 master 的状态
    
    reset master;                               -- 删除所有 binlog 及其索引,重新开始记录
    purge master logs to 'mysql-bin.000001';    -- 删除指定的 binlog
    
  • 在 shell 中,可以用官方提供的 mysqlbinlog 命令解析 binlog 的内容。如下:

    mysqlbinlog mysql-bin.000001
                --start-position=0
                --stop-position=177
                --start-datetime='2019-12-01 12:00:00'
                --stop-datetime='2019-12-02 12:00:00'
                --database=db1      # 只显示指定数据库的记录
    
  • 将 binlog 转换成.sql 文件之后,便可以导入数据库,还原数据。如下:

    mysqlbinlog mysql-bin.000001 > tmp.sql
    mysql -u root -p < tmp.sql
    

# Transaction Log

  • InnoDB 引擎默认启用了事务日志(Transaction Log),分为两种:
    • 重做日志(redo log)
    • 回滚日志(undo log)

# redo log

:属于预写日志(WAL),用于在数据库崩溃之后重启时,重新提交最近一些尚未保存到磁盘的事务。

  • InnoDB 引擎每次提交事务时,会用 redo log 记录相应数据页的变化内容。

    • 新增的事务先记录到内存的 redo log buffer ,缓冲了一定数量才写入磁盘的 redo log file ,并记录此时的 LSN 。
      • redo log file 缓冲了一定数量,才 flush 到磁盘,实际完成事务。
    • redo log file 的大小固定,会从头到尾循环写入。
    • MySQL 重启时,会恢复 redo log file 中尚未 flush 到磁盘的数据页。
  • InnoDB 引擎通过二阶段提交确保该事务同时记录到 redo log 和 binary log 。

    • 先将该事务记录到 redo log buffer ,标记为 prepare 状态,表示预提交。
    • 再记录到 binary log 。
    • 最后在 redo log buffer 中将该事务标记为 commit 状态,表示已提交。实际上不一定写入了磁盘。
    • 返回响应给客户端。
  • 相关配置:

    innodb_log_buffer_size         = 16777216   # redo log buffer 的大小,默认为 16M
    innodb_log_file_size           = 50331648   # redo log file 的大小,默认为 48M
    innodb_log_files_in_group      = 2          # redo log file 的数量,默认为 2 个,名为 ib_logfile0、ib_logfile1
    innodb_log_group_home_dir      = ./         # redo log file 的保存目录,默认在数据目录下
    innodb_flush_log_at_trx_commit = 1          # redo log 的 flush 策略,取值如下:
        # 0 :每隔一秒,就将 redo log buffer 新增的事务写入 redo log file ,并 flush 到磁盘
        # 1 :默认值。redo log buffer 每次新增事务时,都写入 redo log file ,并 flush 到磁盘,实现严格的事务
        # 2 :redo log buffer 每次新增事务时,都写入 redo log file ,但每隔一秒才 flush 到磁盘
    
  • 关闭 redo log :

    ALTER INSTANCE DISABLE INNODB REDO_LOG;             -- 关闭 redo log
    SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';  -- 查看是否启用
    ALTER INSTANCE ENABLE INNODB REDO_LOG;              -- 启用 redo log
    

# 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 容量设置成几 GB ,最多占用主机内存的 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%';   -- 累计存储到磁盘的临时表数