本文主要介绍在数据库启动的时候，参数文件通过什么样的方式去确认参数，以及数据库中的重要参数的介绍。
1环境配置
- CPU：Intel(R) Core(TM) i5-8300H CPU
- 内存：8GB
- MySQL：mysql-5.7.27-linux-glibc2.12-x86_64
- 操作系统：CentOS 7.3
- 内核：3.10.0-957
2my.cnf概述
my.cnf是数据库在Linux下的默认参数文件名，存放了数据库的配置参数，在数据库启动时，mysqld进程会读取该文件，并根据文件中的参数设置来配置数据库，如InnoDB内存池的分配，允许打开的进程数和会话数等。另外数据库相关的工具运行时，也会读取参数文件，并根据参数文件中的参数设置来配置工具，如命令行的显示等。
默认参数文件的位置为: /etc/my.cnf、/etc/mysql/my.cnf、/usr/local/mysql/etc/
my.cnf、~/.my.cnf。也可以在数据库启动或者客户端工具使用时，通过--defaults-file=/path/my.cnf的方式手动指定一个参数文件，而不使用默认的参数文件。
数据库启动时使用配置参数有以下三种情况：
数据库启动时未手动指定参数文件，并且系统中不存在默认参数文件，数据库将使用数据库默认参数。
数据库启动时未手动指定参数文件，并且系统中存在默认参数文件时，数据库将使用默认参数文件中的配置，若默认文件中参数未指定，则使用数据默认参数。默认参数文件之间也会存在读取顺序，参数读取顺序先是/etc/my.cnf，然后是/etc/mysql/my.cnf，之后再是/usr/local/mysql/etc/my.cnf，最后才是~/.my.cnf，多个参数文件中存在相同参数，则以最后一个参数文件中值为准。
数据库启动时手动指定参数文件，数据库将不会使用默认位置下参数文件中的配置，只会使用指定参数文件中的参数，若参数未指定，则使用数据库默认参数。
实验一：未手动指定参数文件并在删除默认位置参数文件情况下，初始化并启动数据库。
# 默认位置参数文件已删除
[root@hells ~]# ls /etc/my.cnf; ls /etc/mysql/my.cnf; ls /usr/local/mysql/etc/my.cnf; ls ~/.my.cnf
ls: cannot access /etc/my.cnf: No such file or directory
ls: cannot access /etc/mysql/my.cnf: No such file or directory
ls: cannot access /usr/local/mysql/etc/my.cnf: No such file or directory
ls: cannot access /root/.my.cnf: No such file or directory
# 软件包的位置
[root@hells ~]# ls -l /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 53 Apr 11 2019 /usr/local/mysql -> /data/ddata/dfile/mysql-5.7.25-linux-glibc2.12-x86_64
# 调用mysqld初始化数据库：
[root@hells ~]# /usr/local/mysql/bin/mysqld --user=root --initialize-insecure
2019-12-22T11:31:04.459418Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-12-22T11:31:04.650918Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-12-22T11:31:04.679683Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-12-22T11:31:04.736762Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 8a0ef772-24ae-11ea-b6f5-0050563d0f79.
2019-12-22T11:31:04.737987Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-12-22T11:31:04.738500Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
# 虽然不存在默认参数文件，也没有手动指定参数文件，但成功初始化数据库，数据库文件创建在数据库安装包下的data目录
[root@hells data]# ls /data/ddata/dfile/mysql-5.7.27-linux-glibc2.12-x86_64/data
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema sys
# 成功启动
[root@hells data]# /usr/local/mysql/bin/mysqld --user=root &
…
Version: '5.7.27' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
[root@hells data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
结论：innodb_buffer_pool_size最终为128M，也就是默认值。数据库在不存在默认参数文件，也没有手动指定参数文件时，默认使用数据库默认配置参数，去进行初始化和启动数据库。
实验二：未手动指定参数文件，多个默认参数文件相同参数设置不同参数值，验证默认参数文件的读取顺序。
# 前三个默认位置参数文件中参数innodb_buffer_pool_size设置不同的值，最后一个默认位置参数文件中该值不进行配置。
[root@hells ~]# cat /etc/my.cnf|grep innodb_buffer_pool_size
innodb_buffer_pool_size = 100M
[root@hells ~]# cat /etc/mysql/my.cnf|grep innodb_buffer_pool_size
innodb_buffer_pool_size = 120M
[root@hells ~]# cat /usr/local/mysql/etc/my.cnf|grep innodb_buffer_pool_size
innodb_buffer_pool_size = 80M
[root@hells ~]# cat ~/.my.cnf|grep innodb_buffer_pool_size
[root@hells ~]#
# 启动数据库
[root@hells ~]# /usr/local/mysql/bin/mysqld --user=root &
# 查看innodb_buffer_pool_size参数的值
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| innodb_buffer_pool_size | 83886080 |
+-------------------------+----------+
1 row in set (0.00 sec)
结论：innodb_buffer_pool_size最终为80M，也就是/usr/local/mysql/etc/my.cnf中的参数生效了，说明多个参数文件中存在相同参数，则以最后一个参数文件中值为准。
实验三：手动指定参数文件，默认参数文件相同参数设置不同参数值，验证参数使用方式。
# 数据库启动时指定参数文件，手动指定的参数文件中innodb_buffer_pool_size的值不设置，/etc/my.cnf中的值设置为100M
[root@hells ~]# cat /etc/my.cnf|grep innodb_buffer_pool_size
innodb_buffer_pool_size = 100M
[root@hells ~]# ls /etc/mysql/my.cnf
ls: cannot access /etc/mysql/my.cnf: No such file or directory
[root@hells ~]# ls /usr/local/mysql/etc/my.cnf
ls: cannot access /usr/local/mysql/etc/my.cnf: No such file or directory
[root@hells ~]# ls ~/.my.cnf
ls: cannot access /root/.my.cnf: No such file or directory
[root@hells ~]# cat /data/mysql/my3306.cnf|grep innodb_buffer_pool_size
[root@hells ~]#
# 启动数据库
[root@hells ~]# /usr/local/mysql/bin/mysqld \
--defaults-file=/data/mysql/my3306.cnf --user=root &
# 查看innodb_buffer_pool_size参数的值
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| innodb_buffer_pool_size | 134217728|
+-------------------------+----------+
1 row in set (0.00 sec)
说明：数据库启动时指定参数文件，mysqld读取innodb_buffer_pool_size的过程为，先读取手动指定的参数文件但未找到该参数值，然后使用数据库默认设置128M，未读取默认参数文件/etc/my.cnf中100M 。
参数文件示例：
[client]
loose_default_character_set = utf8
port = 3306
[mysqldump]
max_allowed_packet = 2G
default_character_set = utf8
[mysql]
prompt = \\u@\\h:\\d\\r:\\m:\\s>
default_character_set = utf8
[mysqld_safe]
user = mysql
open_files_limit = 65535
[mysqld]
default_storage_engine = InnoDB
default_character_set = utf8
collation_server = utf8_bin
log_timestamps = SYSTEM
user = mysql
port = 3306
socket = /home/mysql/data/mysqldata1/sock/mysql.sock
pid_file = /home/mysql/data/mysqldata1/sock/mysql.pid
datadir = /home/mysql/data/mysqldata1/mydata
tmpdir = /home/mysql/data/mysqldata1/tmpdir
......
参数文件是以键值对的方式设置相关的参数，并且根据不同范围可设置各类参数，如[client]下面的参数为客户端相关的配置，这里设置的字符集为utf8以及端口为3306。[mysqldump]备份为mysqldump工具相关的配置，[mysql]为mysql客户端工具的配置，最后[mysqld]为数据库的配置。
3重要参数介绍
MySQL中常见的参数简单介绍，详细参数解释见知识库文档：《3-MySQL关键配置参数和状态变量说明》。
[mysqld]
#*******************common parameters******************************
default_storage_engine = InnoDB
# 设置默认存储引擎为InnoDB
# 查看哪些存储引擎可用，使用SHOW ENGINES语句或查询information_schema.engines表
character_set_server = utf8
# 设置server的默认字符集为utf8
# 该变量的值会被character_set_client、character_set_connection、character_set_database、character_set_results、character_set_system参数继承
collation_server = utf8_bin
# 设置server的默认校对规则
# 该变量的值会被collation_database参数继承
log_timestamps = SYSTEM
# 设置错误、查询和慢查询日志文件的时间戳与时区，SYSTEM表示与操作系统时间相同时区
optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on'
# 设置查询优化器优化行为的参数，此变量的值是一组标签，每个标签（子选项）具有on或off值，以指示相应的优化程序行为是启用还是禁用。
# 建议启用MRR（多范围读取）和开启BKA连接算法
port = 3306
# 设置TCP/IP连接监听的端口号
socket = /home/mysql/data/mysqldata1/sock/mysql.sock
# 设置socket文件位置
# socket文件用于监听创建它的进程，即mysqld进程，用于其与MySQL实例进程本地通信
pid_file = /home/mysql/data/mysqldata1/sock/mysql.pid
# 设置pid文件位置
# pid文件文件内容为mysqld进程的进程号
datadir = /home/mysql/data/mysqldata1/mydata
# 设置数据文件存放目录
# 假如CREATE DATABASE test则会在该目录下创建一个test的目录，在开启参数innodb_file_per_table的情况下，在test库创建表，会在test目录下创建表相关的文件，*.idb文件存放表的数据和索引，*.frm文件存放表的元数据。
tmpdir = /home/mysql/data/mysqldata1/tmpdir
# 设置临时文件存放目录
skip_name_resolve = 1
# 设置是否跳过域名解析，只使用IP和localhost作为主机地址
lower_case_table_names = 1
# 数据库名和表名称将转换为小写存储在磁盘上，且比较时不区分大小写，建议在Linux下设置为1
default_time_zone = '+8:00'
# 设置数据库时区为东8区
wait_timeout = 172800
# 等待关闭无活动非交互连接的时间
interactive_timeout = 172800
# 等待关闭无活动交互连接的时间
gtid_mode = on
# 开启gtid模式，启用全局唯一事务ID（GTIDS）来唯一识别一个事务
enforce_gtid_consistency = true
# 启用强制GTID一致性，服务器通过只执行可以以事务安全方式记录的那些语句来强制执行GTID一致性。
# 开启该值，将不支持以下操作：
* 不支持CREATE TABLE ... SELECT语句
* 不支持在事务内使用CREATE TEMPORARY TABLE语句(5.7开始支持临时表)
* 不支持在一个事务或语句内同时更新事务表和非事务表的语句
#************** Logs related settings ***************************
log_error = /home/mysql/data/mysqldata1/log/error.log
# 设置错误日志位置，数据库排错可以通过该日志检查
slow_query_log = 1
# 设置慢查询日志的开启
slow_query_log_file = /home/mysql/data/mysqldata1/slowlog/slow-query.log
# 设置慢日志位置，记录慢SQL
long_query_time = 1
# 设置执行时间超过该值为慢语句，慢语句将被存放到慢日志中
#************** Replication related settings **********************
#### For Master
server_id = 330614
# 复制架构中全局唯一标识实例的数字编号，主从需不同
# 建议设置为端口号加上IP的最后一段，以方便区分
log_bin = /home/mysql/data/mysqldata1/binlog/mysql-bin
# 设置binlog位置
binlog_format = ROW
# 设置binlog模式，有STATEMENT、MIXED、ROW 三种格式
# STATEMENT格式，记录语句的原始格式，存在安全隐患，可能导致主从不一致，如日期函数
# MIXED格式，过渡格式，可能存在一些bug，导致主从不一致
# ROW格式，记录改变行的原始数据和新数据，包含每一行的数据
binlog_checksum = CRC32
# 设置主库为二进制日志中的每个事件写入校验和
max_binlog_size = 512M
# 设置单个二进制日志文件最大限制，如果超过这个值则进行日志滚动，关闭当前正在使用的binlog文件并产生一个新的日志文件，在正在使用的binlog file后缀编号上加一命名新的二进制文件，并把新日志名记录到*.index文件中
expire_logs_days = 15
# 设置二进制日志文件保留日期，如果超过这个值则进行清理过期日志
sync_binlog = 1
# 设置写了多少个binlog events之后把binlog日志刷新到磁盘，可设置为0或1
# 设置为0，表示二进制日志的落盘操作由操作系统刷新，性能最好，但也是最不安全，发生意外崩溃时没有落盘的binlog日志将丢失
# 设置为1，每次事务提交时就会实时把binlog日志刷新到磁盘，性能不好，但最安全，发生意外崩溃时不会存在binlog日志丢失
master_verify_checksum = 1
# 主库使用二进制日志中的事件长度来验证事件，以便从库从二进制日志读取完整的事件
# 从库也需要同时使用参数slave_sql_verify_checksum=ON来打开校验checksum的功能
master_info_repository = TABLE
# IO线程的对应的master status和master connection信息保存在master.info文件中，或者保存在mysql.slave_master_info表中
# mysql.slave_master_info为InnoDB表，InnoDB表在MySQL崩溃恢复的过程中可以尽量保证不丢失数据
auto_increment_increment = 2
# 自增字段的步长值（增加值）
auto_increment_offset = 1
# 自增字段的偏移量（起始值）
# 常常auto_increment_increment与auto_increment_offset变量一起用于在主主复制环境中避开双写时的主键冲突
# 示例：如果主库一设置参数auto_increment_increment=2，auto_increment_offset=1，那么自增插入值会自动以1，3，5，7，9这样递增，以此类推，主库二设置参数auto_increment_increment=2，auto_increment_offset=2，那么自增插入值会自动以2，4，6，8这样递增。避免了主键冲突
#### For Slave
relay_log = /home/mysql/data/mysqldata1/relaylog/mysql-relay-bin
# 设置relay log位置
relay_log_info_repository = TABLE
# SQL线程对应的master status以及从库relay log 位置保存在relay-log.info文件中，或者保存在mysql.slave_relay_log_info表中
# mysql.slave_relay_log_info为InnoDB表，InnoDB表在MySQL崩溃恢复的过程中可以尽量保证不丢失数据
relay_log_recovery = 1
# 从库重启是否自动开启复制
slave_parallel_type = LOGICAL_CLOCK
# 从库使用多线程复制时，此选项指定用于决定在从库上并行执行事务的策略。可以为LOGICAL_CLOCK和DATABASE
# DATABASE：基于库级别的并行应用更新事务。
# LOGICAL_CLOCK：与事务在主库上相同二进制日志组提交的方式在从库并行应用事务
slave_parallel_workers = 4
# 设置并行复制的工作线程数
log_bin_trust_function_creators = 1
# 设置MySQL对存储函数和触发器创建的限制
log_slave_updates = 1
# 设置从库是否创建binlog
slave_net_timeout = 10
# 备库等待主库发送数据的超时时间
#****************** INNODB Specific options ****************************
#### Data options
innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts
# 设置系统表空间位置
innodb_data_file_path = ibdata1:2048M:autoextend
# 设置共享表空间文件的路径和名称，初始值大小，是否自动扩展
innodb_file_per_table = ON
# 设置是否开启独立表空间，创建表后将创建单独的文件存放数据
innodb_strict_mode = 1
# 设置是否启用InnoDB严格模式，开启后，InnoDB在某些条件下返回错误而不是警告
innodb_flush_method = O_DIRECT
# 设置InnoDB数据文件及redo log的打开、刷写模式
innodb_checksum_algorithm = crc32
# 设置校验和的计算方式
innodb_autoinc_lock_mode = 2
# 设置InnoDB自增长锁模式参数, 有0，1，2三个值
# 0：通过表锁的方式进行，也就是所有类型的insert都用AUTO-inc locking
# 1：默认值，对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作，对于bulk insert 则还是使用表锁的方式进行
# 2：对所有的insert-like 自增长值的产生使用互斥量机制完成，分配自增id时是预分配超过需要的自增范围段，性能最高，并发插入可能导致自增值不连续，可能会导致STATEMENT 的复制出现不一致，使用该模式，需要用 ROW Replication的模式
#### Buffer Pool options
innodb_buffer_pool_size = 160G
# InnoDB存储引擎表的数据和索引的最大缓存区大小，用于缓存数据和索引
# 在保证操作系统和其他程序有足够内存的情况下，这个参数越大越好，缓存命中率越高，访问磁盘IO就越少，性能也就越高
# 建议设置为操作系统内存的50%-80%
innodb_buffer_pool_instances = 40
# buffer pool instance的个数，减少内部对缓存池数据结构的争用
# 建议每个instance为4G大小
innodb_max_dirty_pages_pct = 50
# 允许脏页占用的最大比例
# 读写比例高，不超过50%；读的比例低，可调大
innodb_adaptive_flushing = ON
# 设置自适应hash索引的开启
innodb_flush_neighbors = 0
# 设置刷新脏页是否刷新临近页
# SSD的读写方式为随机读写，数据存放是不连续的，机械磁盘为顺序读写。该功能是顺序读取临近页，对SSD无效，使用SSD时建议不开启
innodb_lru_scan_depth = 4096
# 设置InnoDB缓冲池刷新操作方式
innodb_change_buffering = all
# change buffer优化可以将辅助索引的写入操作延迟，先缓存到change buffer中，以便可以顺序执行I/O操作
# all：默认值，缓冲区缓存inserts，deletes和purges操作
# InnoDB缓存池中有2个区域，一个是sublist of old blocks存放不经常被访问到的数据，另外一个是sublist of new blocks存放经常被访问到的数据。
innodb_old_blocks_pct = 37
# 控制进入到sublist of old blocks区域的数量
innodb_old_blocks_time = 1000
# 控制访问到sublist of old blocks里面数据的时候控制数据不立即转移到sublist of new blocks区域，而是在多少微秒之后再次访问才会真正进入到new区域，这也是防止new区域里面的数据不会立即被踢出。
innodb_buffer_pool_dump_at_shutdown = ON
# 设置数据库关闭时，InnoDB缓冲池中的热数据页列表是否保存到本地硬盘
innodb_buffer_pool_load_at_startup = ON
# 设置数据库启动时，InnoDB缓冲池是否加载ib_buffer_pool文件
#### Redo options
innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log
# 设置redo log的位置
innodb_log_buffer_size = 64M
# 设置redo log buffer的大小
innodb_log_file_size = 2G
# 设置redo log文件大小
innodb_log_files_in_group = 2
# 设置redo log文件个数
innodb_flush_log_at_trx_commit = 1
# 刷新redolog的方式，有效值有0,1,2
# 0：在事务提交时，不会立即触发将缓存日志写入到磁盘日志文件中，而是大约每秒一次由主线程刷新到磁盘文件的操作，并调用操作系统的fsync刷新IO缓存。最不安全，但是性能最好。如果MySQL服务挂掉或者操作系统挂掉，会丢失至少一秒的数据
# 1：在事务提交时，InnoDB立即触发将缓存中的redo日志写回到磁盘日志文件中，并调用系统fsync刷新IO缓存，最安全，但是性能最差。如果MySQL挂掉或者操作系统挂掉也不丢失数据。
# 2：在事务提交时，InnoDB立即将缓存中的redo日志写回到日志文件，但是并不马上调用fsync来刷新IO缓存，而是大约每秒一次磁盘IO缓存刷新操作或者交由操作系统来决定何时刷新磁盘IO缓存。安全性和性能介于0和1之间，如果MySQL服务挂掉而操作系统不挂，不会丢失数据，如果操作系统挂掉，也会丢失至少一秒的数据
# 建议设置innodb_flush_log_at_trx_commit = 1和sync_binlog = 1保证数据不丢失
innodb_fast_shutdown = 1
# 设置数据库shutdown的关闭方式，有效值有0,1,2
# 0：表示慢速关闭，在mysql关闭数据库时，InnoDB需要完成所有的full pruge和merge insert buffer，并且将所有的脏页刷新回磁盘
# 1：是默认的，表示不需要完成full purge和merge insert buffer操作（把这部分内容写进日志里），但是在缓冲池中的一些脏数据还是会刷新到磁盘。下次启动时再读取日志内容进行full purge和插入缓冲合并
# 2：表示不完成full purge和merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件，这样不会有任何的事务丢失，但是下次启动数据库时，会进行恢复操作
innodb_support_xa = ON
# 设置xa事务开启
#### Undo options
innodb_undo_directory = /home/mysql/data/mysqldata1/undo/
# 设置undo文件位置
innodb_undo_tablespaces=4
# 设置undo表空间个数
innodb_undo_log_truncate=ON
# 设置启用undo log自动收缩
innodb_purge_threads = 4
# purge线程数量
innodb_purge_batch_size = 512
# 定义历史列表中一次批量清除的undo log日志页数
innodb_max_purge_lag = 65536
# 设置当PURGE操作滞后时如何延迟INSERT，UPDATE和DELETE操作
#### Transaction options
innodb_thread_concurrency = 64
# 设置同时有多少个线程进入InnoDB内核
innodb_lock_wait_timeout = 120
# InnoDB事务请求行锁的超时时间限制参数
innodb_rollback_on_timeout = 1
# 设置被回滚的事务是否回滚
transaction_isolation = READ-COMMITTED
# 设置事务隔离级别
# read-uncommitted：读未提交，允许脏读
# read-committed：读提交，不允许脏读，但允许不可重复读
# repeatable-read：可重复读，不允许脏读、不可重复读，但允许幻读
# serializable：串行化，以上都不允许
#### IO options
innodb_read_io_threads = 16
# InnoDB内部读的IO线程数
innodb_write_io_threads = 16
# InnoDB内部写的IO线程数
innodb_io_capacity = 20000
# InnoDB后台任务执行的IO活动的上限
# 建议：SAS阵列可以设置为1000，高端的SSD盘或SSD阵列可以设置为4000，PCIE卡可以设置为10000或更多，但不建议超过20000
很多人都觉得，这些配置文件不重要啊，就设置默认值就好了啊，为什么一定要了解这些，其实不然，一定要理解好底层的原理，才能更好的解决问题。现在市面上很多数据库工具其实已经把这些包装成黑盒了，页面上点点就可以设置好了，已经算是帮助我们释放了很多压力了，但是对于我们dba来说，不管工具如何进步，最基本的家伙事不能丢。
1079
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
