MySQL configuration file for 5.6 and 5.7

According to inside's configuration file to organize.

[mysql]port            = 3306socket           = /data/mysql/3306/mysql_3306. Sockdefault-character-set=utf8prompt = [\\[email protected]\\h][\\d]>\\_[mysqld]# basic  settings  #user =nobody                    #调整为实际usersql_mode  =  "Strict_trans_tables,no_engine_substitution,no_ Zero_date,no_zero_in_date,error_for_division_by_zero,no_auto_create_user "Autocommit = 1character_set _server=utf8transaction_isolation = read-committedexplicit_defaults_for_timestamp = 1max_ allowed_packet = 16mevent_scheduler = 1server-id = 123383306          #在主从环境中需要调整datadir =/data/mysql/3306     # Adjust to the actual data directory Default_storage_engine=innodblower_case_table_names = 1federatedport            =  3306socket          = /data/mysql/3306/mysql_ 3306.sockback_log = 150bulk_insert_buffer_size = 64mmax_heap_table_size = 64m#  connection  #interactive_timeout  = 1800wait_timeout = 1800lock_wait_timeout  = 1800skip_name_resolve = 1max_connections = 3000max_connect_errors =  100000# session memory setting  #read_buffer_size  = 16mread_rnd_buffer_size  = 32Msort_buffer_size = 32Mtmp_table_size = 64Mjoin_buffer_size =  128m###***thread_buffers***table_open_cache = 2048thread_cache_size = 8thread_stack =  192kquery_cache_size = 512mquery_cache_limit = 2m###***binlog parameters***log_ Error = /data/mysql/3306/error.logslow_query_log=1slow_query_log_file = /data/mysql/3306/slow.loglog_queries_not_using_ Indexes = 1log_slow_admin_statements = 1log_slow_slave_statements = 1log_ throttle_queries_not_using_indexes = 10expire_logs_days       =  7long_query_time = 1min_examined_row_limit = 100log_bin_trust_function_creators= 1binlog_cache_size = 1mmax_binlog_cache_size   = 16mlog-bin=mysql-binmax_binlog_ size         = 128m     #这个可以根据业务增加, But it doesn't feel like more than 1g#binlog-ignore-db = test#binlog-ignore-db = performance_schema###***myisam.  parameters***key_buffer_size = 32mmyisam_sort_buffer_size = 16mmyisam_max_sort_file_ size = 16mmyisam_repair_threads = 1###***innodb storage engine parameters** *innodb_page_size = 16384innodb_buffer_pool_size = 8g         #这个根据内存来调整innodb_buffer_ Integer multiples of Pool_instances=1 (70?75% for memory) Innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_ Shutdown = 1innodb_lru_scan_depth = 4096innodb_lock_wait_timeout = 2innodb_io_ Capacity = 4000innodb_io_capacity_max = 8000innodb_file_format = barracudainnodb _file_format_max = barracudainnodb_flush_method=o_directinnodb_flush_neighbors = 0        ----? Innodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_concurrency = 64       #见意采用默认值0innodb_print_all_deadlocks  = 1innodb_strict_mode =  1innodb_sort_buffer_size = 64minnodb_write_io_threads = 16innodb_read_io_threads =  16 innodb_file_per_table=1innodb_stats_persistent_sample_pages = 64innodb_autoinc_lock_mode = 2innodb_data_home_dir = /data/mysql/3306innodb_data_file_path  = ibdata1:100m:autoextendinnodb_max_dirty_pages_pct = 85      #默认为90, See Italy 85innodb_log_buffer_size = 64minnodb_log_group_home_dir = /data/mysql/3306innodb_log_ file_size = 128minnodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 2# Innodb_undo_tablespaces = 3#innodb_undo_directory#innodb_undo_logs = 128#innodb_file_io_ threads = 4   #文件读写io数, this parameter only works on Windows. On Linux will only be equal to 4#innodb_use_native_aio = 0   #使用linux的异步i/O, enabled by default ###***master-slave  replication parameters*** #enforce-gtid-consistency=true#gtid-mode=onbinlog_gtid_simple_recovery =  1master_info_repository= "table" relay_log_info_repository= "table" sync_binlog              = 1binlog_format = rowbinlog_rows_query_log_events = 1relay_log_recovery=1skip_slave_startlog_slave_updates= 1relay_log=mysql-relay-bin#slave_skip_errors = ddl_exist_errors#slave_rows_search_algorithms =   ' Index_scan,hash_scan ' # semi sync replication settings # #plugin_load  =   "Validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #rpl _semi_sync_master_enabled = 1#rpl_semi_sync_master_timeout = 3000#rpl_semi_sync_slave_ enabled = 1# password plugin # #validate_password_policy =strong#validate-password= Force_plus_permanent# new innodb setting in 5.7#loose_innodb_numa_interleave=1innodb_ buffer_pool_dump_pct = 40innodb_page_cleaners = 16innodb_undo_log_truncate =  1innodb_max_undo_log_size = 2ginnodb_purge_rseg_truncate_frequency = 128# new  replication setting # #sLave_parallel_type = logical_clock#slave_parallel_workers = 16#slave_preserve_commit_order =1#slave_transaction_retries=128# other change setting # #binlog_gtid_simple_recovery =1#log _timestamps=system#show_compatibility_56=on[mysqld_safe]open-files-limit = 8192[client]  Default-character-set= utf8

This article is from the "just out of the shell of the Birds" blog, please be sure to keep this source http://qhd2004.blog.51cto.com/629417/1871576

MySQL configuration file for 5.6 and 5.7