仅供参考,实际配置应以实际业务为准进行调整。不正之处欢迎指正。存放路径与以下参数可能需要根据服务器配置进行修改
port 端口
max_connections
interactive_timeout,wait_timeout 根据业务需求设置,没特殊要求默认即可
transaction_isolation 默认设置为了RC,有需求可以设置为RR
pid-file 一般根据主机名,可设置成自定义的值
innodb_buffer_pool_size 服务器只跑MySQL的话,建议设置为内存 70% 左右
innodb_buffer_pool_instances,innodb_page_cleaners 根据CPU核数来设置
innodb_flush_sync ,innodb_io_capacity ,innodb_io_capacity_max 根据磁盘设置
table_open_cache,table_definition_cache 根据数据表的多少进行调整
expire_logs_days,binlog_expire_logs_seconds 日志保留时间
[client]
port= 3306
socket= /data/mysql/mysql.sock
[mysql]
prompt="\u [\d]> "
no-auto-rehash
[mysqld]
# 可能需要修改
# innodb_buffer_pool_size | innodb_buffer_pool_instances | innodb_page_cleaners | innodb_flush_sync
# innodb_page_cleaners 设置等于 innodb_buffer_pool_instances 即可
# SSD需要修改 innodb_flush_sync = 0,innodb_flush_neighbors = 0,innodb_io_capacity 和 innodb_io_capacity_max 为磁盘对应IOPS值
# tmpdir 可以修改为比较大的空间的路径
user= mysql
port= 3306
basedir= /usr/local/mysql
datadir= /data/mysql
socket= /data/mysql/mysql.sock
log-error = /data/mysql/error.log
log-bin = /data/mysql/mybinlog
tmpdir = /tmp
max_connections = 512
pid-file = initnode.pid
interactive_timeout = 1200
wait_timeout = 1200
transaction_isolation = READ-COMMITTED
innodb_buffer_pool_size = 717M
innodb_buffer_pool_instances = 4
default_time_zone = "+8:00"
# 基础配置
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 8
thread_stack = 512K
max_allowed_packet = 32M
thread_cache_size = 768
tmp_table_size = 32M
max_heap_table_size = 32M
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
lower_case_table_names=1
log_bin_trust_function_creators = 1
event_scheduler = 1
#索引767限制
innodb_large_prefix = ON
#看情况设置,设置了可使用 load 和 output
secure_file_priv=''
# 缓存
sort_buffer_size = 4M
join_buffer_size = 4M
# 慢查询
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
long_query_time = 1
log_slow_admin_statements = 1
#log_slow_slave_statements = 1
# 复制
server-id = 3306
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
## replication
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#plugin_load = "validate_password.so"
#loose_rpl_semi_sync_master_enabled = 1
#loose_rpl_semi_sync_slave_enabled = 1
#loose_rpl_semi_sync_master_timeout = 3000
#slave-parallel-type = LOGICAL_CLOCK
#slave-parallel-workers = 4
#slave_preserve_commit_order = 1
#binlog_gtid_simple_recovery = 1
# MyISAM
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 5G
myisam_repair_threads = 1
# innodb
#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
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
# 根据服务器IOPS能力适当调整
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
# innodb_flush_neighbors参数,SSD 禁用,非SSD请启用
innodb_flush_neighbors = 1
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_open_files = 65535
innodb_flush_method = O_DIRECT
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
# performance_schema
performance_schema = 1
performance_schema_instrument = '%lock%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
[mysqld-5.6]
query_cache_size = 0
query_cache_type = 0
[mysqld-5.7]
log_timestamps = SYSTEM
## undo log
innodb_max_undo_log_size = 2G
## innodb_undo_directory、innodb_undo_tablespaces 在5.6环境不建议开启
innodb_undo_directory = /data/mysql/undolog
innodb_undo_tablespaces = 3
# 遵守innodb_io_capacity设置定义的I / O速率
innodb_flush_sync = 0
innodb_page_cleaners = 4
query_cache_size = 0
query_cache_type = 0
[mysqld-8.0]
log_timestamps = SYSTEM
innodb_max_undo_log_size = 2G
innodb_undo_directory = /data/mysql/undolog
### 8.0.14 已废弃此参数,添加需要使用 CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';
## 官方文档:https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html
# innodb_undo_tablespaces = 3
innodb_flush_sync = 0
innodb_page_cleaners = 4
log_error_verbosity = 3
innodb_print_ddl_logs = 1
binlog_expire_logs_seconds = 604800
[mysqldump]
quick
max_allowed_packet = 32M