MySQL 之 my.cnf 详解

文章

林里克斯

试验平台：CentOS Linux release 7.6.1810

MySQL Version：mysql 5.7.31

    - client options

[client]
port = 3306
#指定客服端连接端口
socket =  /data/mysql/mysql.sock
#指定客服端连接的 sock 的文件路径
default-character-set = utf8mb4
#指定客服端默认的连接的字符集


    - mysql server options

[mysqld]
user = mysql
#MySQL 启动用户
bind-address = 127.0.0.1
#MySQL 服务监听的 IP
port = 3306
#指定服务所用的端口
socket =  /data/mysql/mysql.sock
#指定服务的 sock 文件存放路径
basedir =  /data/mysql/
#MySQL 服务的根目录（安装目录）
datadir = /data/mysql/data
#数据文件存放的目录
tmpdir = /data/mysql/tmp
#用于存放 MySQL 临时文件的目录
character_set_server = utf8mb4
#指定服务的默认字符集（数据库级别）
pid-file = /data/mysql/mysql.pid
#指定服务的 pid 文件存放路径

binlog_cache_size = 32K
#一个事务中 binlog 为了记录 SQL 状态所持有的 cache 大小
#如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能
#所有从事务来的状态都将被缓冲在 binlog 缓冲中然后在提交后一次性写入到 binlog 中
#如果事务比此值大, 会使用磁盘上的临时文件来替代
#此缓冲在每个连接的事务第一次更新状态时被创建.session 级别
sync_binlog = 1000
#使 binlog 在每 1000 次 binlog 写入后与硬盘同步，MySQL 将进行一次 fsync 之类的磁盘同步指令来将 binlog_cache 中的数据强制写入磁盘
thread_stack = 256K
#设置 MYSQL 线程使用的堆大小，此容量的内存在每次连接时被预留
#MySQL 本身常不会需要超过 64K 的内存
#如果你使用你自己的需要大量堆的 UDF 函数
#或者你的操作系统对于某些操作需要更多的堆
#你也许需要将其设置的更高一点

#InnoDB Options
default_storage_engine = InnoDB
#设置默认存储引擎 (InnoDB|MyISAM)
innodb_data_home_dir = /data/mysql/data
#设置 InnoDB 表空间文件存放路径.默认保存在 MySQL 的 datadir 中
innodb_data_file_path = ibdata1:10M:autoextend
#设置数据存储的名字 ibdata1 与大小
innodb_log_group_home_dir = /www/server/data
#InnoDB 的日志文件所在位置. 默认是 MySQL 的 datadir.
innodb_buffer_pool_size = 32M
#InnoDB 用来高速缓冲数据和索引内存缓冲大小。更大的值可以使访问数据时减少磁盘 I/O，包括数据页、索引页、插入缓存、锁信息、自适应哈希
innodb_log_file_size = 32M
#日志组中的每个日志文件的大小(单位 MB)，请注意一个大的日志文件大小会增加恢复进程所需要的时间。建议设置日志文件总合大小到你缓冲池大小的 25%~100%
innodb_log_buffer_size = 8M
#InnoDB 将日志写入日志磁盘文件前的缓冲大小。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。如果有大的事务处理，设置大的日志缓冲可以减少磁盘 I/O。　　　　　
innodb_flush_log_at_trx_commit = 1
#每次commit 日志缓存中的数据刷到磁盘中。
#设置为 1,InnoDB 会在每次提交后刷新(fsync)事务日志到磁盘上,这提供了完整的 ACID 行为
#设置为 2,代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上
innodb_lock_wait_timeout = 120
#在回滚(rooled back)之前，InnoDB 事务将等待超时的时间(单位 秒),InnoDB 在它自己的锁定表中自动检测事务死锁并且回滚事务
innodb_max_dirty_pages_pct = 90
#在InnoDB 缓冲池中最大允许的脏页面的比例,值在范围1-100.当 Innodb 的内存分配过大，致使 swap 占用严重时，可以适当的减小调整这个值，使达到 swap 空间释放出来。建议这个值最大在 90%，最小在 15%。
# 如果达到限额, InnoDB会开始刷新他们防止他们妨碍到干净数据页面.这是一个软限制,不被保证绝对执行.
innodb_read_io_threads = 1
innodb_write_io_threads = 1
#innodb 读写的线程，根据 CPU 核数来确认值
innodb_open_files = 2048
#限制 Innodb 能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300
innodb_log_files_in_group = 3
#配置日志组中有多少个文件总数，建议 2~3
innodb_file_format = Barracuda
#Innodb Plugin 引擎开始引入多种格式的行存储机制，(Antelope|Barracuda)两种 Barracuda 兼容 Antelope 格式。
innodb_purge_threads = 1
#开始碎片回收线程。这个应该能让碎片回收得更及时而且不影响其他线程的操作
innodb_support_xa = FALSE　
#分布式事务
innodb_buffer_pool_instances = 4 
#与 innodb_buffer_pool_size 一致可以开启多个内存缓冲池，把需要缓冲的数据 hash 到不同的缓冲池中，这样可以并行的内存读写。
innodb_io_capacity = 500　 
#控制 Innodb checkpoint 时的 IO 能力
innodb_file_per_table = 1 
#InnoDB 为独立表空间模式，每个数据库的每个表都会生成一个数据空间
#独立表空间优点：
#1．每个表都有自已独立的表空间。
#2．每个表的数据和索引都会存在自已的表空间中。
#3．可以实现单表在不同的数据库中移动。
#4．空间可以回收（除drop table操作处，表空不能自已回收）
#缺点：单表增加过大，如超过100G
innodb_change_buffering = inserts
#当更新/插入的非聚集索引的数据所对应的页不在内存中时（对非聚集索引的更新操作通常会带来随机IO），会将其放到一个 insert buffer 中，
#当随后页面被读到内存中时，会将这些变化的记录merge到页中。当服务器比较空闲时，后台线程也会做merge操作
innodb_adaptive_flushing = 1
#该值影响每秒刷新脏页的操作，开启此配置后，刷新脏页会通过判断产生重做日志的速度来判断最合适的刷新脏页的数量；
transaction-isolation = READ-COMMITTED
#数据库事务隔离级别 ，读取提交内容
innodb_flush_method = O_DIRECT
#InnoDB用来刷新日志的方法，表空间总是使用双重写入刷新方法(fdatasync|O_DSYNC)
innodb_use_sys_malloc = 1
#设置为0: 表示 Innodb 使用自带的内存分配程序；设置为1：表示 InnoDB 使用操作系统的内存分配程序,默认设置值为1
skip-innodb 
#如果 MySQL 服务包含 InnoDB 支持但是并不打算使用的话,使用此选项会节省内存以及磁盘空间,并且加速某些部分
innodb_status_file = 1 
#启用 InnoDB 的 status file，便于管理员查看以及监控等
innodb_additional_mem_pool_size = 100M 
#设置 InnoDB 存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。 
innodb_file_io_threads = 4 
#用来同步 IO 操作的 IO 线程的数量. 此值在 Unix 下被硬编码为 4,但是在 Windows 磁盘 I/O 可能在一个大数值下表现的更好.
innodb_thread_concurrency = 16
#在 InnoDB 核心内的允许线程数量,InnoDB 试着在 InnoDB 内保持操作系统线程的数量少于或等于这个参数给出的限制,最优值依赖于应用程序,硬件以及操作系统的调度方式.过高的值可能导致线程的互斥颠簸.默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量
innodb_force_recovery=1
# 如果发现 InnoDB 表空间损坏, 设置此值为一个非零值可能帮助你导出你的表.从1 开始并且增加此值知道你能够成功的导出表.
innodb_fast_shutdown 
# 加速 InnoDB 的关闭. 这会阻止 InnoDB 在关闭时做全清除以及插入缓冲合并.这可能极大增加关机时间, 但是取而代之的是 InnoDB 可能在下次启动时做这些操作.

#MyISAM Options
key_buffer_size = 32M
#指定用于索引的缓冲区大小,不要将其设置大于你可用内存的30%,因为一部分内存同样被OS用来缓冲行数据
#可通过 show global status like ‘%key_blocks_unused%' 查看是否还有剩余，如果剩余很多，就不需要再加大key_buffer_size了
read_buffer_size = 768K
#读查询操作所能使用的缓冲区大小,如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能
read_rnd_buffer_size = 256K
#MySql的随机读（查询操作）缓冲区大小
bulk_insert_buffer_size = 16M
#批量插入数据缓存大小，可以有效提高批量插入效率，默认为8M,设置为 0 会关闭此优化,为了最优化不要将此值设置大于 "key_buffer_size"
myisam_sort_buffer_size = 8M
#当对 MyISAM 表执行 repair table 或创建索引时，用以缓存排序索引；设置太小时可能会遇到 "myisam_sort_buffer_size is too small"
myisam_max_sort_file_size = 1G
#MySQL 重建索引时所允许的临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE),如果临时文件大于此值,索引会通过键值缓冲创建(更慢)
myisam_max_extra_sort_file_size = 10G
#如果被用来更快的索引创建索引所使用临时文件大于制定的值,那就使用键值缓冲方法,这主要用来强制在大表中长字串键去使用慢速的键值缓冲方法来创建索引.
myisam_repair_threads = 1
#如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
myisam_recover
# 自动检查和修复没有适当关闭的 MyISAM 表.
skip-federated
#默认关闭 Federated

#binlog Options
log-bin=mysql-bin
#打开二进制日志功能.在复制(replication)配置中,建议作为 MASTER 主服务器必须打开此项.如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志.这些路径相对于 datadir
log_slave_updates = 1 
#表示slave将复制事件写进自己的二进制日志
log-bin-index = mysql-bin.index 
#二进制的索引文件名
relay-log = relay-log 
#定义relay_log的位置和名称，如果值为空，则默认位置在数据文件的目录，文件名为host_name-relay-bin.nnnnnn（By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory）；
relay_log_index = relay-log.index  
#relay-log的索引文件名
log-warnings = 1 
#将警告打印输出到错误 log 文件.如果对于 MySQL 有任何问题，你应该打开警告 log 并且仔细审查错误日志,查出可能的原因.
log-error = /data/mysql/logs/mysql.err 
#指定错误日志路径
log_output = FILE 
#参数 log_output 指定了慢查询输出的格式，默认为 FILE，你可以将它设为 TABLE，然后就可以查询 mysql 架构下的 slow_log 表了
log_slow_queries 
#指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)
slow_query_log = 1 
# 指定是否开启慢查询日志. 慢查询是指消耗了比 "long_query_time" 定义的更多时间的查询.如果 log_long_format 被打开,那些没有使用索引的查询也会被记录.如果你经常增加新查询到已有的系统内的话. 一般来说这是一个好主意,
slow_query_log_file =  /data/mysql/logs/mysql-slow.log 
#指定慢日志文件存放路径，可以为空，系统会给一个缺省的文件 host_name-slow.log


long-query-time = 1 
#设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s.所有的使用了比这个时间(以秒为单位)更多的查询会被认为是慢速查询.不要在这里使用”1″, 否则会导致所有的查询,甚至非常快的查询页被记录下来(由于MySQL 目前时间的精确度只能达到秒的级别).
log_long_format 
# 在慢速日志中记录更多的信息.一般此项最好打开，打开此项会记录使得那些没有使用索引的查询也被作为到慢速查询附加到慢速日志里
log-queries-not-using-indexes 
#如果运行的SQL语句没有使用索引，则mysql数据库同样会将这条SQL语句记录到慢查询日志文件中。
min_examined_row_limit=1000　　　　
#记录那些由于查找了多余1000次而引发的慢查询
long-slow-admin-statements　　　　
#记录那些慢的optimize table，analyze table和alter table语句
log-slow-slave-statements 
#记录由Slave所产生的慢查询
general_log = 1 
#将所有到达MySQL Server的SQL语句记录下来,默认关闭 
general_log_file =  /usr/local/mysql/log/mysql.log 
#general_log路径
max_binlog_size = 1G 
#如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。如果你正使用大的事务，二进制日志还会超过max_binlog_size
max_relay_log_size = 1G 
#标记relaylog允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小；
relay-log-purge = 1 
#是否自动清空不再需要中继日志时。默认值为1(启用)
expire_logs_days = 30 
#超过 30 天的 binlog 删除
replicate-wild-ignore-table = mysql.% 
#复制时忽略数据库及表
slave_skip_errors=all 
#定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。
slave_skip_errors选项有四个可用值，分别为：off，all，ErorCode，ddl_exist_errors。
默认情况下该参数值是off，我们可以列出具体的error code，也可以选择all，mysql5.6及MySQL Cluster NDB 7.3以及后续版本增加了参数ddl_exist_errors，该参数包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）
一些error code代表的错误如下：
1007：数据库已存在，创建数据库失败
1008：数据库不存在，删除数据库失败
1050：数据表已存在，创建数据表失败
1051：数据表不存在，删除数据表失败
1054：字段不存在，或程序文件跟数据库有冲突
1060：字段重复，导致无法插入
1061：重复键名
1068：定义了多个主键
1094：位置线程ID
1146：数据表缺失，请恢复数据库
1053：复制过程中主服务器宕机
1062：主键冲突 Duplicate entry '%s' for key %d

未完待续~