# 配置
- 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 服务器重启时才会生效）
# 免密模式
如果忘记了密码，可以按以下步骤找回：
在 mysqld 的配置文件中加入以下参数，然后重启 mysqld ：
[mysqld] skip-grant-tables # 跳过权限验证，此时不需要密码就能访问所有数据库 skip-networking # 禁止本机以外的客户端进行 TCP 连接或者通过命令行选项开启免密模式：
mysqld --skip-grant-tables --skip-networking用客户端登录 MySQL ，在免密模式下修改密码。
重新按正常的方式启动 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）
- 有的变量同时存在 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 是按二进制值存储每个字符，因此会区分大小写。
- ：每个字符最多占 3 字节，不完全支持标准的 utf-8 字符。
- utf8mb4
- ：每个字符最多占 4 字节，完全支持标准的 utf-8 字符。
- 其默认字符序为 utf8mb4_general_ci 。
- 建议采用这种字符集。
- MySQL 中，数据库名、表名要区分大小写，而字段名不区分大小写。
- 如果要让字段的内容区分大小写，可以采用字符序 utf8_bin ，也可以在执行 SELECT 命令时加上关键字 binary 。如下：
SELECT * FROM tb1 WHERE binary name = 'Aa';
- 如果要让字段的内容区分大小写，可以采用字符序 utf8_bin ，也可以在执行 SELECT 命令时加上关键字 binary 。如下：
# 配置
- 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 中的数据。
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%'; -- 累计存储到磁盘的临时表数