my.cnf 通用配置【5.7/8.0】
2020-10-14 本文已影响0人
醉红尘丶
5.6/5.7/8.0通用配置
注意：可能需要根据实际情况作调整，以下参数仅为建议值。
- 地址路径可能需要更改
- innodb_buffer_pool_size
在服务器为数据库专用情况下，建议设置为物理内存的 70%左右【60% -75%】 - table_open_cache/table_definition_cache
内存 < 16g : 1024
内存 >= 16g : 2048 - join_buffer_size/sort_buffer_size/read_buffer_size
内存 <= 4G ：1M
内存 > 4g : 4M - table_open_cache_instances
16个或更多内核的系统上，建议将值设置为8或16
内存 < 16G : 8
内存 >= 16g : 16 - innodb_buffer_pool_instances
内存 <= 1G : 1
内存 <= 16g : 4
内存 >=16g : 8
# 创建数据文件路径
mkdir -p /data/{mysql3306,tmpdir}
chown -R myqsl.mysql /data/mysql3306
[client]
port = 3306
socket = /tmp/mysql3306.sock
[mysql]
prompt="\u [\d]> "
no-auto-rehash
[mysqld]
# 路径配置
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql3306
socket = /tmp/mysql3306.sock
log-error = /data/mysql3306/error.log
log-bin = /data/mysql3306/mybinlog
tmpdir = /data/tmpdir
slow_query_log_file = /data/mysql3306/slow.log
# 基础配置 设置值 默认值
max_connections = 512 # 151
pid-file = initnode.pid # host名
interactive_timeout = 1200 # 28800
wait_timeout = 1200 # 28800
transaction_isolation = READ-COMMITTED # REPEATABLE-READ
innodb_buffer_pool_size = 717M # 134217728 = 128M
innodb_buffer_pool_instances = 4 # 8 ，内存<1G时为1
default_time_zone = "+8:00" # SYSTEM
character-set-server = utf8mb4 # 5.6/5.7 --> latin1 | 8.0 --> utf8mb4
skip_name_resolve = 1 # OFF
open_files_limit = 65535 # 5000
back_log = 1024 # 151
max_connect_errors = 1000000 # 100
table_open_cache = 1024 # -1 自动调整大小
table_definition_cache = 1024 # -1 自动调整大小
table_open_cache_instances = 8 # 16,通常使用16个或更多内核的系统上，建议将值设置为8或16。
thread_stack = 512K # 286720
max_allowed_packet = 32M # 67108864
thread_cache_size = 768 # -1 自动调整大小,大于 max_connections 一些
tmp_table_size = 32M # 16777216
max_heap_table_size = 32M # 16777216
lock_wait_timeout = 3600 # 31536000
explicit_defaults_for_timestamp = 1 # 5.6/5.7 --> OFF | 8.0 --> ON
lower_case_table_names = 1 # 0
log_bin_trust_function_creators = 1 # OFF
event_scheduler = 1 # 5.6/5.7 --> OFF | 8.0 --> ON
#看情况设置，设置了可使用 load 和 output #
secure_file_priv = '' # #
sort_buffer_size = 4M # 262144
join_buffer_size = 4M # 262144
log_timestamps = SYSTEM # UTC
#
# 慢查询 #
slow_query_log = 1 # OFF
long_query_time = 1 # 10
log_slow_admin_statements = 1 # OFF
#log_slow_slave_statements = 1 #
#
# 复制 #
server-id = 3306 #
sync_binlog = 1 #
binlog_cache_size = 4M # 32768
max_binlog_cache_size = 2G # 18446744073709551615
max_binlog_size = 1G # 1073741824
expire_logs_days = 7 # 0 | 8.0 --> binlog_expire_logs_seconds
master_info_repository = TABLE # 5.6/5.7 --> FILE | 8.0 --> TABLE
relay_log_info_repository = TABLE # 5.6/5.7 --> FILE | 8.0 --> TABLE
gtid_mode = on # OFF
enforce_gtid_consistency = 1 # OFF
log_slave_updates = 1 # 5.6/5.7 --> OFF | 8.0 --> ON
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN' # 5.6/5.7 --> TABLE_SCAN,INDEX_SCAN | 8.0 --> INDEX_SCAN,HASH_SCAN
binlog_format = row # ROW
binlog_checksum = 1 # CRC32
relay_log_recovery = 1 # OFF
relay-log-purge = 1 # ON
## 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,在8.0环境能进一步调小 #
key_buffer_size = 32M # 8388608
read_buffer_size = 8M # 131072
read_rnd_buffer_size = 4M # 262144
bulk_insert_buffer_size = 64M # 8388608
myisam_sort_buffer_size = 128M # 8388608
myisam_max_sort_file_size = 5G # 9223372036854775807
myisam_repair_threads = 1 # 1
# innodb #
innodb_buffer_pool_load_at_startup = 1 # ON
innodb_buffer_pool_dump_at_shutdown = 1 # ON
innodb_data_file_path = ibdata1:200M:autoextend # ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1 # 1
innodb_log_buffer_size = 32M # 16777216
innodb_log_file_size = 2G # 50331648
innodb_log_files_in_group = 3 # 2
# 根据服务器IOPS能力适当调整 #
innodb_io_capacity = 2000 # 200
innodb_io_capacity_max = 4000 # 2000
# innodb_flush_neighbors参数，SSD 禁用，非SSD请启用 #
innodb_flush_neighbors = 1 # 0
innodb_write_io_threads = 8 # 4
innodb_read_io_threads = 8 # 4
innodb_purge_threads = 4 # 4
innodb_open_files = 65535 # -1
innodb_flush_method = O_DIRECT # NULL
innodb_checksum_algorithm = crc32 # crc32
innodb_lock_wait_timeout = 10 # 50
innodb_rollback_on_timeout = 1 # OFF
innodb_file_per_table = 1 # ON
innodb_online_alter_log_max_size = 4G # 134217728
## undo log #
innodb_max_undo_log_size = 2G #
## innodb_undo_directory、innodb_undo_tablespaces 在5.6环境不建议开启 #
innodb_undo_tablespaces = 3 #
# 遵守innodb_io_capacity设置定义的I / O速率 #
innodb_flush_sync = 0 #
innodb_page_cleaners = 4 #
#
# performance_schema #
performance_schema = 1 # ON
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.7]
query_cache_size = 0 # 1048576
query_cache_type = 0 # 0
#索引767限制 #
innodb_large_prefix = ON # 5.6/5.7 --> OFF | 8.0.0 已删除
#
[mysqld-8.0] #
log_error_verbosity = 3 #
innodb_print_ddl_logs = 1 #
binlog_expire_logs_seconds = 604800 #
#
[mysqldump] #
quick #
max_allowed_packet = 32M #
