MySQL5.7和MySQL8.0通用配置文件

MySQL5.7 my.cnf配置

[client]

port=3306

socket=/log/mysql/mysql.sock

[mysql]

socket=/log/mysql/mysql.sock

[mysqladmin]

socket=/log/mysql/mysql.sock

#user=zabbix

#password=Paswwrod

[mysqld_safe]

log-error = /log/mysql/error.log

[mysqld]

port=3306

skip_name_resolve = 1

skip_symbolic_links = yes

lower_case_table_names = 1

explicit_defaults_for_timestamp = ON

character_set_server = utf8

collation_server = utf8_general_ci

datadir = /data/mysql/

socket = /log/mysql/mysql.sock

log_error = /log/mysql/mysqld.log

pid_file = /log/mysql/mysqld.pid

# binlog

server_id = 100

log_bin = /log/mysql/mysql-bin

binlog_cache_size = 32768

binlog_checksum = CRC32

binlog_format = ROW

# [IGNORE_ERROR|ABORT_SERVER]

binlog_error_action = ABORT_SERVER

binlog_group_commit_sync_delay = 0

binlog_transaction_dependency_tracking = COMMIT_ORDER

binlog_order_commits = ON

binlog_row_image = FULL

binlog_rows_query_log_events = OFF

# 0~99

expire_logs_days = 10

binlog_expire_logs_seconds = 864000

sync_binlog = 1

local_infile = 0

# innodb

innodb_file_per_table = ON

innodb_checksum_algorithm = NONE

# stores all data twice, first to the doublewrite buffer, then to the actual data files

innodb_doublewrite = OFF

# larger is wide range of workloads, particularly for DML operations involving bulk updates

innodb_file_per_table = ON

innodb_max_dirty_pages_pct = 50

innodb_use_native_aio = ON

# specifies whether InnoDB index statistics are persisted to disk

innodb_stats_persistent = ON

# The maximum delay between polls for a spin lock

innodb_spin_wait_delay = 96

# pool buffer

# Keeping the InnoDB page size close to the storage device block size

# minimizes the amount of unchanged data that is rewritten to disk.

innodb_page_size = 16K

innodb_buffer_pool_chunk_size = 128M

innodb_buffer_pool_instances = 4

innodb_buffer_pool_size = 6G

innodb_buffer_pool_dump_at_shutdown = ON

innodb_buffer_pool_dump_now = off

innodb_buffer_pool_dump_pct = 15

# ibdata file, system tablespace

# innodb_data_file_path=datafile_spec1[;datafile_spec2]...

# file_name:file_size[:autoextend[:max:max_file_size]]

innodb_data_home_dir = /log/mysql/

# innodb_data_file_path = ibdata1:4G;ibdata2:32M:autoextend:max:4G

innodb_data_file_path = ibdata1:128M:autoextend:max:4G

innodb_autoextend_increment = 64M

# redo logs

# innodb_log_file_size * innodb_log_files_in_group

innodb_log_group_home_dir = /log/mysql/

innodb_log_files_in_group = 2

innodb_log_file_size = 4G

# before the transactions commit. update, insert, or delete many rows, making the log buffer larger saves disk I/O

innodb_log_buffer_size = 64M

innodb_undo_directory = /log/mysql/

# maximum number of .ibd files that MySQL can keep open at one time

innodb_open_files = 5000

# Defines the method used to flush data to InnoDB data files and log files, which can affect I/O throughput

# O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system file system cache

innodb_flush_method = O_DIRECT

# Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent

innodb_flush_neighbors = 0

innodb_thread_concurrency = 0

innodb_write_io_threads = 8

innodb_read_io_threads = 8

# sets an upper limit on the number of I/O operations performed per second by InnoDB background tasks

innodb_io_capacity = 3000

# causes the innodb_io_capacity setting to be ignored during bursts of I/O activity that occur at checkpoints

innodb_flush_sync = ON

# [0|1|2]

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 30

# pref special

# whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload

innodb_adaptive_flushing = ON

innodb_purge_threads = 2

# optimization for InnoDB tables that can speed up lookups using = and IN operators, by constructing a hash index in memory

# A hash index can be partial; the whole B-tree index does not need to be cached in the buffer pool

innodb_adaptive_hash_index = OFF

# buffer size

# Index blocks for MyISAM tables are buffered and are shared by all threads

key_buffer_size = 2G

sort_buffer_size = 8M

read_buffer_size = 8M

join_buffer_size = 64M

tmp_table_size = 2G

max_heap_table_size = 64M

# OFF/ON/DEMAND

#query_cache_type = OFF

#query_cache_size = 32M

# connections

max_connections = 5000

max_connect_errors = 100000

wait_timeout = 86400

max_execution_time = 0

interactive_timeout = 7200

# UPDATE wait until there is no pending SELECT or LOCK TABLE READ on the affected table

low_priority_updates = 0

# number of outstanding connection requests MySQL can have

# 50 + (max_connections / 5)

# this value is the size of the listen queue for incoming TCP/IP connections

back_log = 1500

thread_stack = 256K

# 8 + (max_connections / 100)

thread_cache_size = 64

max_allowed_packet = 10M

# number of open tables for all threads

table_open_cache = 10000

# value of 8 or 16 is recommended on systems that routinely use 16 or more cores.

table_open_cache_instances = 16

# number of files that the operating system permits mysqld to open

# max_connections * 5 / 10 + max_connections + (table_open_cache * 2)

open_files_limit = 15000

# slow sql

slow_launch_time = 4

slow_query_log = ON

slow_query_log_file = /log/mysql/slow.log

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

MySQL8.0 my.cnf配置文件

[client]

port=3306

socket=/log/mysql/mysql.sock

[mysql]

socket=/log/mysql/mysql.sock

[mysqladmin]

socket=/log/mysql/mysql.sock

[mysqld_safe]

log-error = /log/mysql/error.log

[mysqld]

port=3306

user=mysql

datadir=/data/mysql

socket=/log/mysql/mysql.sock

log_error=/log/mysql/mysqld.log

pid_file=/log/mysql/mysqld.pid

skip_name_resolve=1

skip_symbolic_links=yes

explicit_defaults_for_timestamp=ON

log_timestamps=SYSTEM

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci

# binlog

server_id = 100

log_bin = /log/mysql/mysql-bin

binlog_cache_size = 32768

binlog_checksum = CRC32

binlog_format = ROW

# [IGNORE_ERROR|ABORT_SERVER]

binlog_error_action = ABORT_SERVER

binlog_group_commit_sync_delay = 0

binlog_transaction_dependency_tracking = COMMIT_ORDER

binlog_order_commits = ON

binlog_row_image = FULL

binlog_rows_query_log_events = OFF

# 0~99

expire_logs_days = 10

binlog_expire_logs_seconds = 864000

sync_binlog = 1

local_infile = 0

# innodb

innodb_file_per_table = ON

innodb_checksum_algorithm = NONE

# stores all data twice, first to the doublewrite buffer, then to the actual data files

innodb_doublewrite = OFF

# larger is wide range of workloads, particularly for DML operations involving bulk updates

innodb_file_per_table = ON

innodb_max_dirty_pages_pct = 50

innodb_use_native_aio = ON

# specifies whether InnoDB index statistics are persisted to disk

innodb_stats_persistent = ON

# The maximum delay between polls for a spin lock

innodb_spin_wait_delay = 96

# pool buffer

# Keeping the InnoDB page size close to the storage device block size

# minimizes the amount of unchanged data that is rewritten to disk.

innodb_page_size = 16K

innodb_buffer_pool_chunk_size = 128M

innodb_buffer_pool_instances = 4

innodb_buffer_pool_size = 2G

innodb_buffer_pool_dump_at_shutdown = ON

innodb_buffer_pool_dump_now = off

innodb_buffer_pool_dump_pct = 15

# ibdata file, system tablespace

# innodb_data_file_path=datafile_spec1[;datafile_spec2]...

# file_name:file_size[:autoextend[:max:max_file_size]]

#innodb_data_home_dir=/log/mysql/

# innodb_data_file_path=ibdata1:4G;ibdata2:32M:autoextend:max:4G

#innodb_data_file_path=ibdata1:128M:autoextend:max:4G

#innodb_autoextend_increment=64M

# redo logs

# innodb_log_file_size * innodb_log_files_in_group

#innodb_log_group_home_dir = /log/mysql/

#innodb_log_files_in_group = 2

#innodb_log_file_size = 4G

# before the transactions commit. update, insert, or delete many rows, making the log buffer larger saves disk I/O

#innodb_log_buffer_size = 64M

#innodb_undo_directory = /log/mysql/

# maximum number of .ibd files that MySQL can keep open at one time

#innodb_open_files = 5000

# Defines the method used to flush data to InnoDB data files and log files, which can affect I/O throughput

# O_DIRECT can help to avoid double buffering between the InnoDB buffer pool and the operating system file system cache

innodb_flush_method = O_DIRECT

# Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent

innodb_flush_neighbors = 0

innodb_thread_concurrency = 0

innodb_write_io_threads = 8

innodb_read_io_threads = 8

# sets an upper limit on the number of I/O operations performed per second by InnoDB background tasks

innodb_io_capacity = 3000

# causes the innodb_io_capacity setting to be ignored during bursts of I/O activity that occur at checkpoints

innodb_flush_sync = ON

# [0|1|2]

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 30

# pref special

# whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload

innodb_adaptive_flushing = ON

innodb_purge_threads = 2

# optimization for InnoDB tables that can speed up lookups using = and IN operators, by constructing a hash index in memory

# A hash index can be partial; the whole B-tree index does not need to be cached in the buffer pool

innodb_adaptive_hash_index = OFF

# buffer size

# Index blocks for MyISAM tables are buffered and are shared by all threads

key_buffer_size = 1G

sort_buffer_size = 8M

read_buffer_size = 8M

join_buffer_size = 64M

tmp_table_size = 1G

max_heap_table_size = 64M

# OFF/ON/DEMAND

#query_cache_type = OFF

#query_cache_size = 32M

# connections

max_connections = 5000

max_connect_errors = 100000

wait_timeout = 86400

max_execution_time = 0

interactive_timeout = 7200

# UPDATE wait until there is no pending SELECT or LOCK TABLE READ on the affected table

low_priority_updates = 0

# number of outstanding connection requests MySQL can have

# 50 + (max_connections / 5)

# this value is the size of the listen queue for incoming TCP/IP connections

back_log = 1500

thread_stack = 256K

# 8 + (max_connections / 100)

thread_cache_size = 64

max_allowed_packet = 10M

# number of open tables for all threads

table_open_cache = 10000

# value of 8 or 16 is recommended on systems that routinely use 16 or more cores.

table_open_cache_instances = 16

# number of files that the operating system permits mysqld to open

# max_connections * 5 / 10 + max_connections + (table_open_cache * 2)

open_files_limit = 15000

# slow sql

slow_launch_time = 4

slow_query_log = ON

slow_query_log_file = /log/mysql/slow.log

sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION