Mysql(四)配置文件my.cnf详解及参数调优设置

2022-11-22 13:24:23

Mysql配置文件详解

[client]   # 客户端设置，即客户端默认的连接参数
# 端口号
port = 3306
# 用于本地连接的socket套接字
socket=/tmp/mysql.sock


[mysql]  
# mysql的客户端是否命令自动补全，默认是不补齐
no-auto-rehash
# 数据库或数据表的默认字符集,等同于character_set_server参数
default-character-set=utf8mb4
#服务端默认的比对规则，排序规则
collation_server = utf8mb4_bin


[mysqld]  #服务端基本设置
# 给服务器分配一个唯一的ID编号，区分主从
server-id = 1
# 数据文件存放目录
datadir = /mysql
# 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件
socket= /tmp/mysql.sock
# mysql的日志
log-error = /logs/mysqld.log
user = mysql
# mysql绑定ip
bind-address = 0.0.0.0

# 超出最大连接数max_connections，可以存放的连接数量，把多余的连接放在堆栈中。
back_log = 600
# 当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)；可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能
thread_cache_size = 38
# 查询缓存大小
query_cache_size = 32M

# 表文件描述符的缓存大小( 当打开一个表后 会把这个表的文件描述符缓存下来）
table_open_cache = 512
# sort_buffer_size是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。官方文档推荐范围为256KB~2MB
sort_buffer_size = 16M
# 在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度(默认设置是128K)。
join_buffer_size = 2M

# 规定了内部内存临时表的最大值，每个线程都要分配
tmp_table_size = 96M
# 如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下
max_heap_table_size = 96M
# 据库事务隔离级别 ，读取提交内容 避免脏读，不可重复读。不能避免虚读。
transaction-isolation = REPEATABLE-READ
# 默认存储引擎InnoDB
default-storage-engine = InnoDB
# 建表字符集
character-set-server = utf8mb4
# 排序字符集
collation-server = utf8mb4_bin
# sql_mode,定义了mysql应该支持的sql语法，数据校验等!
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#禁用SSL，ssl是数据传输过程中，加密的功能。
disable_ssl

# 同一时间并发最大连接数
#  show status like 'Max_used_connections'，一般设置为这个值的平均值的1.5倍。
max_connections = 3000

#最大错误连接数
#max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码# 的情况。max_connect_errors的值与性能并无太大关系。
#当此值设置为10时，意味着如果某一客户端尝试连接此MySQL服务器，但是失败（如密码错误等等）10次，则MySQL会无# 条件强制阻止此客户端连接。
max_connect_errors = 100000

#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true

#SQL数据包发送的大小
# 一个sql语句的最大大小。
max_allowed_packet = 32M

#慢查询配置
# 开启慢查询
slow_query_log = on
# 超过的时间为2s；MySQL能够记录执行时间超过参数 long_query_time 设置值的SQL语句，默认是不记录的
long_query_time = 2
# 慢查询记录存放位置
slow_query_log_file = /logs/slow.log

#binlog配置
# 开启二进制日志功能，binlog数据存储位置
log-bin = /logs/mysql-bin
# 二进制日志缓冲大小。InnoDB存储引擎是支持事务的，实现事务需要依赖于日志技术，为了性能，日志编码采用二进制格式。直接将日志写磁盘的效率很低。因此需要加一个缓冲区，提升性能，但是如果宕机数据就会丢失，需要权衡缓冲的大小。
binlog_cache_size = 4M
# 为每个session 最大可分配的内存，在事务过程中用来存储二进制日志的缓存。
max_binlog_cache_size = 4096M
# 如果二进制日志写入的内容超出给定值，日志就会发生滚动。最大1GB
max_binlog_size = 1024M
# binlog的格式
binlog_format = MIXED 
# binlog日志存储时间周期，过期自动清理
expire_logs_days = 7

#Innodb配置
# InnoDB 用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘 I/O。
innodb_buffer_pool_size = 2G
# 单独指定数据文件的路径与大小。自动扩容
innodb_data_file_path=ibdata1:1G:autoextend
# InnoDB驱动程序能够同时使用的最大线程个数(默认设置是8)。
innodb_thread_concurrency=16
# 个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写(术语称为”同步”)到硬盘上。
设置值0的意思是每隔一秒写一次日志并进行 同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 
设置值1(设置设置)的意思是在每执行完一条COMMIT命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 
设置值2是一般折衷的办法，即每执行完一条COMMIT命令写一次日志，每隔一秒进行一次同步。
innodb_flush_log_at_trx_commit=1
# 事务日志文件写操作缓存区的最大长度(默认设置是1MB)。commit之前的事务缓存起来，大的事务可以减少磁盘IO
innodb_log_buffer_size=16M
# 日志组中的每个日志文件的大小(单位 MB)
innodb_log_file_size=512M
# 日志文件组
innodb_log_files_in_group=2

# 控制Innodb的脏页在缓冲中在那个百分比之下，值在范围1-100,默认为90.这个参数的另一个用处：
#当Innodb的内存分配过大，致使swap占用严重时，可以适当的减小调整这个值，使达到swap空间释放出来。建义：这个值最大在90%，最小在15%。
#太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。
innodb_max_dirty_pages_pct=75
# 设置一下恢复Buffer Pool中多少数据；
innodb_buffer_pool_dump_pct=50
# 在回滚(rooled back)之前，InnoDB 事务将等待超时的时间(单位 秒)
innodb_lock_wait_timeout=50
# #作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。
#分配原则：只有使用不使用。但ＤＢ还需要有一个公共的表空间。
innodb_file_per_table=OFF

参数优化

Max_connection参数：

  • 默认是151
  • 测试：设置并发数为200，会报最大连接数超过限制的错误。
  • 单机的数据库，最大的连接数，一般为1000-1500.一个用户连接（会话）会占用一个独立的内存。
    Mysql(四)配置文件my.cnf详解及参数调优设置
  • 判断依据： show status like ‘Max_used_connections’，一般设置为这个值的平均值的1.5倍。
  • 修改/etc/my.cnf Max_connections=1024

back_log参数

  • 超出最大连接数，把多余的连接放在堆栈中。
  • 一般设置为back_log=200-600

wait_timeout和interactive_timeout

  • wait_timeout:默认28800s=8h 一个非交互的连接等待的秒数。一般可以设置得短一点，可以降低内存得压力。例如=120S.(清除一些空闲得会话)
  • interactive_timeout:默认28800s=8h 。正在做操作得sql会话。这个参数不建议修改。

key_buffer_size参数

  • 指定索引缓冲区得大小，它决定索引处理的速度，尤其是索引读的速度。此参数与myisam的索引有关
  • 和(myisam和innodb)临时表的创建有关（多表连接，子查询，union联合查询），以上语句，都需要创建临时表，用完之后丢弃。临时表有两种创建方式：
    • 内存中： key_buffer_size
    • 磁盘上：ibdata1(5.6) ibtmp1(5.7)
  • 默认值：
    • 临时表：show variables liek ‘key_buffer_size’ ; 8388608=8M
    • 索引缓存：show status like ‘key_read’,返回值两个参数
      • key_read_requests = 10
      • key_reads = 2
      • 10个需要索引的请求，其中有2个再内存中没有找到，需要控制再5%以内。（这个需要调整）
  • 一般可以设置为
    • key_buffer_size=64M（官网：机器总内存的25%可接受）
上一篇：MySQL-找回数据库用户密码


下一篇：mysql 启动失败问题