mysql my.cnf缓存池配置

# 内存分配（核心优化）
innodb_buffer_pool_size = 18G           # InnoDB缓冲池，缓存数据和索引，分配约56%内存给InnoDB缓冲池，默认值：128M；OLTP（高并发读写）需增大，OLAP（分析型）可适当降低
innodb_buffer_pool_instances = 8        # 缓冲池实例数（建议与80%CPU核数匹配），高并发或缓冲池≥64GB时需增加
key_buffer_size = 16M                   # MyISAM表索引缓存（备用）默认值：8M；建议范围：若使用MyISAM表可增大，否则保持默认；场景：纯InnoDB场景可设为16M节省内存

 

 

[client]
port            = 3306
socket          = /tmp/mysql.sock
default-character-set = utf8mb4

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
datadir   = /usr/local/mysql/data
log-error = /usr/local/mysql/mysqld.log
pid-file  = /usr/local/mysql/mysqld.pid
explicit_defaults_for_timestamp = true
default_authentication_plugin = mysql_native_password
lower_case_table_names = 1
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 跳过连击时DNS 域名检查
skip-name-resolve 

# 解决排序编码的问题
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake

# binlog 的配置项
log-bin=mysql-bin
binlog_format=mixed
# 二进制日志缓存大小(4096的倍数)
binlog_cache_size = 256K
binlog_expire_logs_seconds = 600000

# 慢日志查询的配置项
slow_query_log=1
slow-query-log-file=/usr/local/mysql/data/mysql-slow.log
long_query_time=3
early-plugin-load = ""

# 性能配置项
# 用于索引的缓冲区大小
key_buffer_size = 1024M
# 临时表缓存大小
tmp_table_size = 2048M
# 每个线程排序的缓冲大小
sort_buffer_size = 4096K
# 读入缓冲区大小
read_buffer_size = 4096K
# 随机读取缓冲区大小
read_rnd_buffer_size = 2048K
# 关联表缓存大小
join_buffer_size = 8192K
# 每个线程的堆栈大小
thread_stack = 512K
# 线程池大小
thread_cache_size = 256
# 最大连接数
max_connections = 1000
# 表缓存
table_open_cache = 2048

max_heap_table_size = 2048M
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
max_allowed_packet = 100G
net_buffer_length = 4K
myisam_sort_buffer_size = 32M
max_connect_errors = 100
open_files_limit = 65535



# innode 配置项 
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
# Innodb缓冲区大小
innodb_buffer_pool_size = 4096M
# Innodb日志缓冲区大小
innodb_log_buffer_size = 64M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 4
innodb_write_io_threads = 4

[mysqldump]
quick
max_allowed_packet = 500M
default-character-set = utf8mb4

[mysql]
no-auto-rehash
default-character-set = utf8mb4


[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout