参考文献

  • MySQL是咋样运行的

MySQL获取配置信息路径

  • 命令行参数 mysqld_safe --datadir=/data/sql_data

  • 配置文件

    • 确定配置的文件路径

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      -- 方法一
      root@2dd900ce424a:/# which mysqld
      /usr/sbin/mysqld
      root@2dd900ce424a:/# /usr/sbin/mysqld --verbose --help |grep -A 1 'Default options'
      Default options are read from the following files in the given order:
      /etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
      -- 方法二
      [root@holelin ~]# mysql --help |grep my.cnf
                            order of preference, my.cnf, $MYSQL_TCP_PORT,
      /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
      • 从左到右,MySQL数据库是从/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf 的顺序读取配置文件的.

    • 配置文件通常分成多个部分,每个部分的开头是一个方括号括起来的分段名称.MySQL程序通常读取跟它同名的分段部分,很多客户端程序还会读取client部分,这是一个存放共用设置的地方.服务器通常读取mysqld这一段.一定要确认配置项放在了文件正确的分段中,否则配置是不会生效的.

配置文件的路径

  • 在类UNIX操作系统中,MySQL会按照以下路径来依次寻找配置文件

    路径 备注
    /etc/my.cnf
    /etc/mysql/my.cnf
    SYSCONFDIR/my.cnf
    $MYSQL_HOME/my.cnf 特定于服务器的选项(仅限服务器)
    defaults-extra-file 命令行指定的额外配置文件路径
    ~/.mycnf 特定于用户的选项
    ~/.mylogin.cnf 特定于用户的登录路径选项(仅限客户端)
    • SYSCONFDIR表示使用CMake构建MySQL时使用SYSCONFDIR选项指定的目录

配置文件的内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[server]
[具体的启动选项...]

[mysqld]
[具体的启动选项...]

[mysql_safe]
[具体的启动选项...]

[cilent]
[具体的启动选项...]

[mysql]
[具体的启动选项...]

[mysqladmin]
[具体的启动选项...]

[mysqldump]
[具体的启动选项...]

[mysqlcheck]
[具体的启动选项...]

[mysql_upgrade]
[具体的启动选项...]

[mysqlimport]
[具体的启动选项...]

  • 在配置文件中指定启动选项的语法类似于命令行语法,但是在配置文件中只能使用长形式的选项,而且在配置文件中指定的启动选项不允许加--前缀,并且每行只指定一个选项,等号=周围可以有空白字符(在命令行中,选项名,=,选项值之间不允许有空白字符).

  • 在配置文件中,可以使用#来添加注释,从#出现直至行尾的内容都属于注释内容

  • 在配置文件中,不同的选项组是给不同的程序使用的.如果选项组名称与程序名称相同,则组中选项将专门应用于该程序.例如,[mysqld]和[mysql]组分别应用于mysqld服务器程序和mysql客户端程序.不过有两个选项组比较特别:

    • [server]组项目的启动选项将作用于所有的服务器程序
    • [client]组下面的启动选项将作用于所有的客户端程序
    程序名 类别 能读取的组
    mysqld 启动服务器 [mysqld],[server]
    mysqld_safe 启动服务器 [mysqld],[server],[mysqld_safe]
    mysql_server 启动服务器 [mysqld],[server],[mysqld_server]
    mysql 启动客户端 [mysqld],[client]
    mysqladmin 启动客户端 [mysqladmin],[client]
    mysqldump 启动客户端 [mysqldump],[client]

特定MySQL版本的专用选项组

  • 可以使用选项组的名称后加上特定的MySQL版本号.比如对于[mysqld]选项组来说,可以定义一个[mysqld-5.7],它的含义和[mysqld]一样,只不过只有版本号为5.7的mysqld程序才能使用这个选项组中的选项

配置文件的优先级

  • MySQL将按照上述配置文件的表格中的顺序依次读取各个配置文件.如果配置文件不存在,则忽略.值得注意的是,如果我们在多个配置文件中设置了相同的启动选项,则以最后一个配置文件中的为准.

同一个配置文件中多个组的优先级

  • 如果在同一个配置文件中(比如~/.my.cnf),在[mysqld],[server]组里出现了同样的启动选项,将以最后一个出现在组中的启动选项为准.

    1
    2
    3
    4
    5
    [server]
    default-storage-engine=InnoDB

    [mysqld]
    default-storage-engine=MyISAM

defaults-file的使用

  • 不想让MySQL到默认的路径下搜索配置文件,则可以在命令指定defaults-file选项

    1
    mysqld --defaults-file=/tmp/myconfig.txt
    • 注意:defaults-extra-file和defaults-file的区别,使用defaults-extra-file可以指定额外的配置文件路径(也就是说那些固定的配置文件路径也会被搜索)

MySQL配置参数的作用域

全局参数

1
2
set global 参数名=参数值;
set @@global.参数名:=参数值;

会话参数

1
2
set [session] 参数名=参数值;
set @@session.参数名:=参数值;

配置

  • 在MySQL8.x中以下配置存在废弃配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
# 数据存放相关
# 数据库所在位置,默认为/usr/local/mysql/data
datadir

# 内存相关
# 确定可以使用的内存的上限
# 确定MySQL的每个连接使用的内存

# 针对每个连接分配内存的
# 排序缓冲区
sort_buffer_size
# 连接缓冲区 
join_buffer_sizes
read_buffer_size
read_rnd_buffer_size

# 缓存池分配内存 若服务器只有MySQL: 总内存-(每个线程所需要的线程*连接数)-系统保留内存
Innodb_buffer_pool_size
key_buffer_size

# I/O相关
Innodb_log_file_size
Innodb_log_files_in_group
# 事务日志总大小=Innodb_log_files_in_group * Innodb_log_file_size
Inondb_log_buffer_size
# 0: 每秒进行一次log写入cache,并flush log到磁盘.设置该值当MySQL崩溃时,会丢失至少一秒的数据
# 1: 默认,在每次事务提交执行log写入cache,并flush log到磁盘
# 2: 每次事务提交,执行到log数据写入到cache,每秒执行一次flush log到磁盘
Innodb_flush_log_at_trx_commit

Innodb_flush_method = O_DIRECT
Innodb_file_per_table = 1
# 双写缓存
Innodb_doublewrite = 1

# 安全相关
# 指定自动清理binlog的天数
expire_logs_days
# 控制MySQL可以接收的包的大小
max_allowed_packet
# 禁止DNS查找
skip_name_resolve
# 确保sysdate()返回确定性日志
sysdate_is_now
# 禁止非super权限的用户写权限
read_only
# 禁止Slave自动恢复
skip_slave_start
# 设置MySQL所使用的SQL模式
sql_mode
#①ONLY_FULL_GROUP_BY
# 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
#②NO_AUTO_VALUE_ON_ZERO
# 该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
#③STRICT_TRANS_TABLES
# 如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制
#④NO_ZERO_IN_DATE
# 不允许日期和月份为零
#⑤NO_ZERO_DATE
# mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告
#⑥ERROR_FOR_DIVISION_BY_ZERO
# 在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL
#⑦NO_AUTO_CREATE_USER
# 禁止GRANT创建密码为空的用户
#⑧NO_ENGINE_SUBSTITUTION
# 如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
#⑨PIPES_AS_CONCAT
# 将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似
#⑩ANSI_QUOTES
# 不能用双引号来引用字符串，因为它被解释为识别符

# 控制MySQL如何向磁盘刷新binlog
sync_binlog
# 控制内存表临时大小,两个参数应该保持一致
tmp_table_size
max_heap_table_size
# 控制允许的最大连接数
max_connections

重要配置说明

innodb_buffer_pool_size

  • MySQL 8.9.39默认值为134217728B(128MB)

  • 它决定了InnoDB存储引擎可以使用多少内存来缓存内存中的数据和索引.

RAM 缓冲池大小(范围)
4GB 1GB-2GB
8GB 4GB-6GB
12GB 6GB-10GB
16GB 10GB-12GB
32GB 24GB-28GB
64GB 45GB-56GB
128GB 108GB-116GB
256GB 220GB-245GB

innodb_buffer_pool_instances

  • MySQL 8.9.39默认值为1

  • 可以将InnoDB缓冲池划分为不同的区域,以便在不同线程读取和写入缓存页面时减少争用,从而提高并发性.

  • 例如,如果缓冲池为64GB,innodb_buffer_pool_instances的值为32,则缓冲区将被分为32个区域,每个区域的大小为2GB

  • 如果缓冲池大小超过16GB,则可以设置实例,以便每个区域至少获得1GB的空间.

innodb_log_file_size

  • MySQL 8.9.39默认值为50331648B(50MB)

  • 它是redo log的空间大小,用于数据库崩溃时重放已提交的事务.