经常会有一些小伙伴咨询生产环境Mysql的配置文件参数,我们生产环境Mysql一般选择的是5.7的GA版本,现贴出标准化的参数文件my.cnf如下,目前批量化安装的时候已经做到自动调整,供参考。

port = 3306

socket = /data/mysql/datanode1/mysql.sock

default-character-set = utf8mb4

server-id = 582 ## 我们一般截取IP

port = 33069

user = mysql

basedir = /usr/local/mysql

datadir =/data/mysql/datanode1

tmpdir = /tmp

socket = /data/mysql/datanode1/mysql.sock

pid-file = /data/mysql/datanode1/mysql.pid

default-storage-engine = INNODB

lower_case_table_names = 1

transaction_isolation = READ-COMMITTED

secure_file_priv =

optimizer_switch = 'derived_merge=off'

# connection

back_log = 400

max_connections = 2000

interactive_timeout = 3600

wait_timeout = 3600

slave_net_timeout = 300

skip_name_resolve = on      #禁止DNS解析

log-bin = mysql-bin

log-bin-index = mysql-bin

relay-log = relay-bin

relay-log-index = relay-bin

expire_logs_days = 7

binlog_cache_size = 8M

binlog_format = ROW

innodb_locks_unsafe_for_binlog = 1

log_slave_updates = 1

relay_log_purge = 1

log-bin-trust-function-creators = 1

#slave_parallel_workers = 4

# caches & limits

bulk_insert_buffer_size = 8M

innodb_buffer_pool_size = 18G    ## 适当调整:内存60%

innodb_log_buffer_size = 32M

innodb_sort_buffer_size = 4M

join_buffer_size = 4M

sort_buffer_size = 4M

key_buffer_size = 16M

innodb_max_dirty_pages_pct = 15

max_allowed_packet = 16M

max_heap_table_size = 16M

query_cache_size = 0

query_cache_limit = 8M

query_cache_type = off

read_buffer_size = 512K

read_rnd_buffer_size = 512K

open-files-limit = 10240

table_definition_cache = 4096

table_open_cache = 4096

table_open_cache_instances = 16

thread_cache_size = 300

innodb_thread_concurrency = 0

innodb_io_capacity = 500

innodb_io_capacity_max = 1000

# innodb log

innodb_flush_method = O_DIRECT

innodb_log_file_size = 1G

innodb_autoextend_increment = 128

innodb_flush_log_at_trx_commit = 2

innodb_log_files_in_group = 3

innodb_log_group_home_dir = /data/mysql/datanode1/

innodb_print_all_deadlocks

sync_binlog = 1000

# innodb file

innodb_data_home_dir = /data/mysql/datanode1/

innodb_data_file_path = ibdata:2G:autoextend

innodb_file_per_table = 1                          #设置独立表空间

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_large_prefix = 1

tmp_table_size = 4M

innodb_autoinc_lock_mode = 1

auto_increment_increment = 3        ## 适当调整

auto_increment_offset = 1           ## 适当调整

max_prepared_stmt_count = 65530

# 默认字符集

character-set-server = utf8mb4

# 慢查错误日志配置