一、概述
作為DBA或系統運維人員在安裝或配置MySQL的過程中，經常遇到mysql的配置文件參數不知如何設置？哪些參數需要設置？參數值設置為多少比較合理。下面分享一個在線生成mysql的生成器，幫你解決以上煩憂。
二、過程
2.1 填寫相關配置
1、填寫相關參數，點擊生成。
2、填寫參數說明
- CPU：CPU邏輯核數
- 內存大小：部署mysql機器內存大小
- 磁盤類型：SSD或HDD
- MYSQL版本：5.6、5.7、8.0
- 架構：單機、主從、MGR
- basedir：程序目錄
- datadir：數據目錄的父目錄
- 監聽端口：3306
2.2 生成配置文件展示
配置說明：
- CPU:4核
- 內存：16G
- 磁盤：SSD
- MYSQL版本:5.7
- 架構：單機
- base_dir:/usr/local/mysql/
- datadir:/data/mysql/data/
[mysql] auto-rehash socket =/tmp/mysql.sock # /tmp/mysql.sock ? ? [mysqld] ####: for global user =mysql # mysql basedir =/usr/local/mysql/ # /usr/local/mysql/ datadir =/data/mysql/data/3306/ # /usr/local/mysql/data server_id =1980 # 0 port =3306 # 3306 character_set_server =utf8 # latin1 log_bin_trust_function_creators =ON # 0 max_prepared_stmt_count =1048576 # log_timestamps =system # utc socket =/tmp/mysql.sock # /tmp/mysql.sock read_only =OFF # off skip_name_resolve =1 # 0 auto_increment_increment =1 # 1 auto_increment_offset =1 # 1 lower_case_table_names =1 # 0 secure_file_priv = # null open_files_limit =65536 # 1024 max_connections =512 # 151 thread_cache_size =64 # 9 table_open_cache =4000 # 2000 table_definition_cache =2000 # 1400 table_open_cache_instances =32 # 16 ? ####: for binlog binlog_format =ROW # row log_bin =mysql-bin # off binlog_rows_query_log_events =ON # off log_slave_updates =ON # off expire_logs_days =7 # 0 binlog_cache_size =96k # 65536(64k) binlog_checksum =none # CRC32 sync_binlog =1 # 1 slave-preserve-commit-order =ON # ? ####: for error-log log_error =err.log # /usr/local/mysql/data/localhost.localdomain.err ? ####: for general-log general_log =OFF # off general_log_file =general.log # hostname.log ? ####: for slow query log slow_query_log =ON # off slow_query_log_file =slow.log # hostname.log log_queries_not_using_indexes =OFF # off long_query_time =2.0 # 10.000000 ? ####: for gtid gtid_executed_compression_period =1000 # 1000 gtid_mode =ON # off enforce_gtid_consistency =ON # off ? ? ####: for replication skip_slave_start =0 # master_info_repository =table # file relay_log_info_repository =table # file slave_parallel_type =logical_clock # database | LOGICAL_CLOCK slave_parallel_workers =4 # 0 rpl_semi_sync_master_enabled =1 # 0 rpl_semi_sync_slave_enabled =1 # 0 rpl_semi_sync_master_timeout =1000 # 1000(1 second) plugin_load_add =semisync_master.so # plugin_load_add =semisync_slave.so # binlog_group_commit_sync_delay =4000 # 0 4000(4ms) binlog_group_commit_sync_no_delay_count = 10 # 0 binlog_transaction_dependency_tracking = WRITESET # COMMIT_ORDER | WRITESET transaction_write_set_extraction = XXHASH64 ? ? ####: for innodb default_storage_engine =innodb # innodb default_tmp_storage_engine =innodb # innodb innodb_data_file_path =ibdata1:256M;ibdata2:256M:autoextend # ibdata1:12M:autoextend innodb_temp_data_file_path =ibtmp1:64M:autoextend # ibtmp1:12M:autoextend innodb_buffer_pool_filename =ib_buffer_pool # ib_buffer_pool innodb_log_group_home_dir =./ # ./ innodb_log_files_in_group =8 # 2 innodb_log_file_size =128M # 50331648(48M) innodb_file_per_table =ON # on innodb_online_alter_log_max_size =128M # 134217728(128M) innodb_open_files =64000 # 2000 innodb_page_size =16k # 16384(16k) innodb_thread_concurrency =0 # 0 innodb_read_io_threads =4 # 4 innodb_write_io_threads =4 # 4 innodb_purge_threads =4 # 4(garbage collection) innodb_page_cleaners =4 # 4(flush lru list) innodb_print_all_deadlocks =ON # off innodb_deadlock_detect =ON # on innodb_lock_wait_timeout =50 # 50 innodb_spin_wait_delay =6 # 6 innodb_autoinc_lock_mode =2 # 1 innodb_flush_sync =OFF # on innodb_io_capacity =4000 # 200 innodb_io_capacity_max =20000 # 2000 #--------Persistent Optimizer Statistics innodb_stats_auto_recalc =ON # on innodb_stats_persistent =ON # on innodb_stats_persistent_sample_pages =20 # 20 innodb_buffer_pool_instances =10 # 1 innodb_adaptive_hash_index =ON # on innodb_change_buffering =all # all innodb_change_buffer_max_size =25 # 25 innodb_flush_neighbors =0 # 1 innodb_flush_method =O_DIRECT # innodb_doublewrite =ON # on innodb_log_buffer_size =256M # 16777216(16M) innodb_flush_log_at_timeout =1 # 1 innodb_flush_log_at_trx_commit =1 # 1 innodb_buffer_pool_size =10G # 128M autocommit =ON # 1 #--------innodb scan resistant innodb_old_blocks_pct =37 # 37 innodb_old_blocks_time =1000 # 1000 #--------innodb read ahead innodb_read_ahead_threshold =56 # 56 (0..64) innodb_random_read_ahead =off # OFF #--------innodb buffer pool state innodb_buffer_pool_dump_pct =50 # 25 innodb_buffer_pool_dump_at_shutdown =ON # ON innodb_buffer_pool_load_at_startup =ON # ON ? ? ? ? ? #### for performance_schema performance_schema =on # on performance_schema_consumer_global_instrumentation =on # on performance_schema_consumer_thread_instrumentation =on # on performance_schema_consumer_events_stages_current =on # off performance_schema_consumer_events_stages_history =on # off performance_schema_consumer_events_stages_history_long =off # off performance_schema_consumer_statements_digest =on # on performance_schema_consumer_events_statements_current =on # on performance_schema_consumer_events_statements_history =on # on performance_schema_consumer_events_statements_history_long =off # off performance_schema_consumer_events_waits_current =on # off performance_schema_consumer_events_waits_history =on # off performance_schema_consumer_events_waits_history_long =off # off performance-schema-instrument ='memory/%=COUNTED'
