MySQL mariadb_11.3.2性能优化(宝塔面板)

重要提示:先备份！先备份！先备份！

服务器:Debian12 2H4G

代码

# 2H4G服务器 MariaDB 11.3.2 启动兼容版（解决启动失败+保留优化）
# 适配：宝塔面板+PHP8.4+OpenResty，优先保障启动稳定
[client]
port                  = 3306
socket                = /tmp/mysql.sock
default-character-set = utf8mb4
password              = your_password

[mysqld]
# 一、内存分配（保守值，避免内存不足）
innodb_buffer_pool_size = 800M                # 从1G下调至800M，更适配4G内存（减少与其他服务冲突）
innodb_buffer_pool_instances = 1
innodb_log_buffer_size = 32M                  # 从64M下调，兼容低配置环境
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 32M
key_buffer_size = 64M

# 二、连接管理（保守值，避免线程占用过高）
max_connections = 150                         # 从200下调，减少启动内存占用
thread_cache_size = 30
wait_timeout = 240
interactive_timeout = 240
back_log = 100
table_open_cache = 800                        # 从1200下调，兼容低内核
table_definition_cache = 800

# 三、InnoDB优化（删除不兼容参数，保留核心优化）
# innodb_flush_method = O_DIRECT              # 注释：部分云服务器存储不支持O_DIRECT，导致启动失败
innodb_flush_log_at_trx_commit = 2
innodb_flush_neighbors = 0
innodb_read_io_threads = 2                    # 从4下调至2，兼容2核CPU低负载
innodb_write_io_threads = 2
innodb_max_dirty_pages_pct = 70
innodb_adaptive_flushing = 1
innodb_adaptive_hash_index = 1
innodb_adaptive_hash_index_parts = 2
innodb_read_ahead_threshold = 56
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
# innodb_corrupt_table_action = assert_clear_pages  # 注释：部分旧数据目录不兼容该参数
innodb_lock_wait_timeout = 30
innodb_large_prefix = 1
innodb_stats_on_metadata = 0
innodb_print_all_deadlocks = 1

# 四、查询性能优化（保留核心，删除冗余）
query_cache_type = 0
query_cache_size = 0
long_query_time = 2                           # 从1.2秒上调至2秒，减少日志冗余
slow_query_log = 1
slow-query-log-file = /www/server/data/mysql-slow.log
log_queries_not_using_indexes = 0
performance_schema = 1
performance_schema_max_table_instances = 800

# 五、会话级缓存（保守值，避免内存叠加）
join_buffer_size = 512K
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K

# 六、宝塔默认核心配置（100%保留，删除重复sql_mode！）
thread_stack = 256K
binlog_cache_size = 64K
port                  = 3306
socket                = /tmp/mysql.sock
datadir               = /www/server/data
default_storage_engine = InnoDB
skip-external-locking
net_buffer_length     = 4K
myisam_sort_buffer_size = 16M
# 修复重复sql_mode：之前配置重复声明，导致语法错误
sql-mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#skip-name-resolve
max_connect_errors    = 1000
open_files_limit      = 65535

# 七、二进制日志优化（保留默认，避免冲突）
log-bin               = mysql-bin
binlog_format         = row
server-id             = 1
expire_logs_days      = 7
binlog_row_image      = minimal

# 八、InnoDB日志与数据文件（禁止修改！）
innodb_data_home_dir  = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_log_file_size  = 128M
innodb_log_buffer_size = 32M
tmpdir                = /tmp

# 九、安全与稳定性（保留核心）
skip_symbolic_links   = 1
character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci

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

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

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

[mysqlhotcopy]
interactive-timeout

# 宝塔预留配置（保留注释）
# bt_mysql_set = None
# bt_mem_size = None
# bt_query_cache_size = 64
# bt_mysql_set = None
# bt_mem_size = None