MySQL InnoDB 相关参数设置

一、my.conf配置文件

[mysqld]

basedir = /usr/local/mysql

datadir = /data/mysql/

socket = /data/mysql/mysql.sock

character-set-server = utf8

skip_name_resolve = 1                    #禁用域名解析

open_files_limit = 65535                #修改最大文件打开数，默认1000

          

max_connections = 500                    #最大连接数，按需调整，避免oom

max_connect_errors = 1000000             #最大错误连接次数

max_allowed_packet = 32M                #按需调整，避免mysqldump出现max packet问题

sort_buffer_size = 4M                    #按需调整，排序空间

join_buffer_size = 4M                    #按需调整

thread_cache_size = 768                  #线程缓冲，按需调整  show global status like '%thread%' 分析实际情况

interactive_timeout = 600               #服务器关闭交互式连接前等待活动时间，此参数和wait_timeout最好调整为一致,默认8小时

wait_timeout = 600                       #服务器关闭非交互式连接前等待活动时间 和interactive_timeout保持一致,默认8小时  

tmp_table_size = 32M                      #sql语句在排序或者分组时没用到索引，就会使用临时表空间，默认16M，按需调整

max_heap_table_size = 32M

slow_query_log = 1                      #开启慢查询

slow_query_log_file = /data/mysql/slow.log

log-error = /data/mysql/error.log

long_query_time = 0.1                   #慢查询时间默认10s，按需调整

server-id = 3306101                     #配置主从时候一定要设置，且不能和从库一致，否则gg

log-bin = /data/mysql/mysql-binlog         #生产环境必须开启binlog

sync_binlog = 1                         #此参数也非常重要，建议为1，提交一个事务binlog_cache及刷新到磁盘

binlog_cache_size = 4M                 #此参数基于会画，按需调整，需要根据当前服务器状态调整，避免设置过大出现oom

max_binlog_cache_size = 1G            

max_binlog_size = 1G                  #binlog最大大小

expire_logs_days = 7                  #设置binlog过期参数

gtid_mode = on                       #gtid模式下主从必须开启

enforce_gtid_consistency = 1            #gtid模式必须开启，强制事务一致

log_slave_updates=on                 #从库环境开启，接收master binlog即写入relay

binlog_format = row                  #生产环境规范统一必须为ROW

 

relay_log_recovery = 1                #从库异常夯机，自动删除未应用的relay，建议从库开启

relay-log-purge = 1                    #从库开启，自动删除应用过的relay

key_buffer_size = 32M

read_buffer_size = 8M

read_rnd_buffer_size = 4M

bulk_insert_buffer_size = 64M

lock_wait_timeout = 3600              #锁等待时间，默认较大，建议调整小

explicit_defaults_for_timestamp = 1   #此参数用于控制timestamp字段（参考第四部分说明）

innodb_thread_concurrency = 0         #Innodb最大并发线程数，默认值为0，大表不受限制。很多建议调整为cpu核数一致，建议设置为0

innodb_buffer_pool_size = 1024M       #innodb缓冲池调整为物理内存的50%至80%

innodb_buffer_pool_instances = 8      #缓冲池实例，按需调整

innodb_buffer_pool_load_at_startup = 1  #默认开启，请勿关闭（作用参考上述文章）

innodb_buffer_pool_dump_at_shutdown = 1 #默认开启，请勿关闭

innodb_data_file_path = ibdata1:1G:autoextend  #表空间文件大小，建议调大

innodb_flush_log_at_trx_commit = 1      #生产环境必须统一开启

innodb_log_buffer_size = 32M            #生产环境建议按需调大，默认16M，不能调整过大，避免恢复耗费很长时间

innodb_io_capacity = 4000               #生产环境按需调整，官方建议调整为每秒i/o数，默认为200，建议调大

innodb_io_capacity_max = 8000

innodb_flush_neighbors = 0

innodb_write_io_threads = 8             #按需调整默认4

innodb_read_io_threads = 8              #按需调整默认4

innodb_purge_threads = 4

innodb_page_cleaners = 4

innodb_open_files = 65535

innodb_max_dirty_pages_pct = 50        #InnoDB尝试从缓冲池中刷新数据，以便脏页的百分比不超过此值，默认值为75。

innodb_flush_method = O_DIRECT         #生产建议采用O_DIRECT模式    

innodb_file_per_table = 1              #默认值

#从库参数

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log_recovery=ON

log_slave_updates=ON

relay_log_purge=1

slave-parallel-type=LOGICAL_CLOCK  

slave-parallel-workers=16

[mysqldump]

quick

max_allowed_packet = 32M
-----------------------------------

MySQL InnoDB相关参数设置
https://blog.51cto.com/u_15060546/2650692

相关文章：
MySQL InnoDB相关参数设置

为者常成，行者常至