mysql5.7 参数配置_MySQL5.7配置参数

MySQL5.7的参数调整配置，参考例子，某些具体参数的设置还需要根据实际来调整。

[client]

port                            = 3306

socket                          = /tmp/mysql3306.sock

default_character_set           = utf8mb4

[mysqld]

lower_case_table_names  = 1

port                            = 3306

basedir                         = /usr/local/mysql

datadir                         = /home/mysql3306/mysql3306

socket                          = /tmp/mysq3306.sock

pid_file                        = /home/mysql3306/mysql5711.pid

tmpdir                          = /tmp/

slave_load_tmpdir               = /tmp/

# *** dns resolve *** #

skip_name_resolve               = 1

external_locking

explicit_defaults_for_timestamp= true

character_set_client_handshake = FALSE

character_set_server            = utf8mb4

collation_server                = utf8mb4_unicode_ci

init_connect                    = 'SET NAMES utf8m2b4'

# *** connection *** #

max_connections                 = 512

max_connect_errors              = 100000

back_log                        = 102

interactive_timeout             = 600

wait_timeout                    = 600

table_definition_cache          = 5000

table_open_cache                = 2048

#query_cache_size                = 64M

#query_cache_limit               = 2M

thread_cache_size               = 51

ft_min_word_len                = 4

#performance_schema              = OFF

##If large data transfer problems,enlarge max_allowed_packet

thread_stack = 192K

# *** global memory parameter *** #

max_allowed_packet              = 16M

tmp_table_size                  = 256M

max_tmp_tables                  = 128

max_heap_table_size             = 96M

#close query cache

#query_cache_type                = 0

# *** session memory parameter *** #

sort_buffer_size                = 4M

read_buffer_size                = 4M

join_buffer_size                = 4M

read_rnd_buffer_size            = 8M

bulk_insert_buffer_size         = 64M

# *** engine *** #

default_storage_engine          = InnoDB

#ignore_builtin_innodb

#plugin_load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

# *** log *** #

log_timestamps                  = system

log_bin                         = /home/mysql3306/logs/mysql-bin

expire_logs_days                = 10

binlog_format                   = mixed

#binlog_gtid_simple_recovery     = 1

#binlog_do_db

#binlog_ignore_db

log_slave_updates=1

relay_log                       = /home/mysql3306/logs/mysql5711_relay_bin

relay_log_index                = /home/mysql3306/logs/mysql5711_relay_bin.index

relay_log_info_file             = /home/mysql3306/logs/mysql5711_relay_bin.info

relay_log_recovery              = 0

log_error                       = /home/mysql3306/logs/mysql5711.err

slow_query_log                  = 1

slow_query_log_file             = /home/mysql3306/logs/mysql5711_slow.log

long_query_time                 = 1

log_output                      = FILE

log_queries_not_using_indexes   = 1

log_slow_admin_statements       = 1

log_slow_slave_statements       = 1

log_throttle_queries_not_using_indexes = 10

min_examined_row_limit          = 100

#general_log                     = off

general_log_file                = /home/mysql3306/logs/mysql5711_general.log

#log_queries_not_using_indexes  = /mysqllog/mysql5711/mysql5711_not_useing_index.log

max_binlog_cache_size           = 8M

max_binlog_size                 = 1024M

binlog_cache_size               = 4M

sync_binlog                     = 1

server_id                       = 28703306

#read_only                       = 0

#slave_skip_errors               = all

# *** MyISAM Specific options *** #

#key_buffer_size                 = 128M

#bulk_insert_buffer_size         = 64M

#myisam_sort_buffer_size         = 128M

#myisam_max_sort_file_size       = 10G

#myisam_repair_threads           = 1

#myisam_recover_options          = BACKUP,FORCE

# *** INNODB Specific options *** #

innodb_data_home_dir            = /home/mysql3306/mysql3306/

innodb_data_file_path           = ibdata1:1024M:autoextend

innodb_log_group_home_dir       = /home/mysql3306/mysql3306/

innodb_buffer_pool_instances    = 1

innodb_buffer_pool_size         = 1024MB

innodb_buffer_pool_dump_pct     = 40

innodb_page_cleaners            = 4

innodb_log_file_size            = 256M

innodb_log_files_in_group       = 2

innodb_log_buffer_size          = 32M  #default 16M

innodb_flush_log_at_trx_commit  = 1

innodb_lock_wait_timeout        = 30

innodb_undo_directory           = /home/mysql3306/mysql3306/

innodb_undo_logs                = 128  #must >=35.default 128

innodb_undo_tablespaces         = 3    #must >=2

innodb_undo_log_truncate        = 1

innodb_max_undo_log_size        = 256M

innodb_purge_rseg_truncate_frequency = 128

innodb_print_all_deadlocks      = 1

innodb_sort_buffer_size         = 64M

disabled_storage_engines=MyISAM

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_file_format_max=Antelope

innodb_file_format=Antelope

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

innodb_open_files               = 4000

innodb_file_per_table           = 1

innodb_status_file              = 1

innodb_lock_wait_timeout        = 30

innodb_read_io_threads          = 8

innodb_write_io_threads         = 8

innodb_io_capacity              = 200

innodb_thread_concurrency       = 32

innodb_commit_concurrency       = 0

#innodb_flush_neighbors          = 0  #for ssd

#innodb_flush_neighbors          = 1  #for other

innodb_purge_batch_size         = 1000

innodb_flush_log_at_trx_commit  = 0

innodb_flush_method             = O_DIRECT

innodb_purge_threads            = 4

#innodb_file_format              = barracuda

innodb_strict_mode              = 1

innodb_support_xa               = 1

skip_innodb_adaptive_hash_index

innodb_force_recovery           =0

#innodb_fast_shutdown

innodb_change_buffering        = all

#innodb_use_native_aio          = 1

innodb_max_dirty_pages_pct      =50

##replication

#replicate_do_db

#replicate_ignore_db             = mysql

#replicate_ignore_db             = test

#replicate_do_table

#replicate_ignore_table

#replicate_wild_do_table

#replicate_wild_ignore_table

#gtid_mode                       = on

#enforce_gtid_consistency        = true

#open mts

master_info_repository          = TABLE

relay_log_info_repository       = TABLE

sync_master_info                = 1

slave-parallel-type=LOGICAL_CLOCK #default databases,group rep

slave_parallel_workers          = 16

relay_log_recovery=ON

binlog_checksum                 = CRC32 #default crc32

master_verify_checksum          = 1     #default off

slave_sql_verify_checksum       = 1

#binlog_rows_query_log_events    = 1    #

##semi sync replication settings

#plugin_dir=/usr/local/mysql57/lib/plugin

#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

#loose_rpl_semi_sync_master_enabled = 1

#loose_rpl_semi_sync_slave_enabled  = 1

#loose_rpl_semi_sync_master_timeout = 10000 # 10 second

##transaction

transaction_isolation          = REPEATABLE-READ

#transaction_write_set_extraction= MURMUR32 #open --single-tra no use

[mysqldump]

quick

max_allowed_packet              = 16M

[mysql]

no_auto_rehash

default_character_set           = utf8mb4

[myisamchk]

#key_buffer_size                 = 20M

#sort_buffer_size                = 4M

#read_buffer                     = 4M

#write_buffer                    = 2M

[mysqlhotcopy]

interactive_timeout

[mysqld_safe]

open_files_limit                = 8192

总结：参数的调整，能对RW的性能有帮助，调整不好也是个坑......