mysql的設定檔適用5.6與5.7

標籤：settings   設定檔   nobody   mysql   

根據inside君的設定檔整理而來。

[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     #調整為實際的資料目錄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    #這個可以根據業務增加，但是感覺最大不要超過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_pool_instances=1的整數倍（為記憶體的70?75%）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，見意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數，這個參數只在Windows上起作用。在linux上只會等於4#innodb_use_native_aio = 0  #使用linux的非同步i/o，預設啟用###***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

本文出自 “剛剛出殼的小鳥” 部落格，請務必保留此出處http://qhd2004.blog.51cto.com/629417/1871576

mysql的設定檔適用5.6與5.7