#This configration file opts for MySQL 8.0.20 #Let us make an optimal MySQL configuration file template for product enviroment. # #I assume the MySQL Server as followings. You should tune the variables according to your server. # #* 32 CPU core #* 256G Memory #* SSD storage with 20000 IOPS in 16K page size [client] ##client标签设置mysql、mysqldump、mysqladmin的变量。 user = root password = 1111aaA_ [mysql] ##mysql标签设置mysql客户端的变量。 prompt = [\\u@\\p][\\d]>\\_ tee = "/tmp/tee.log" ##设置客户端的查询日志 pager = "less -i -n -S" ##分页显示查询出的数据，方便搜索查询到的数据 no-auto-rehash ##关闭预读取元数据 [mysqld_safe] [mysqldump] single-transaction [mysqld] # basic settings # innodb_temp_data_file_path ##研究一下这个参数设置成多少合适。 default_authentication_plugin=mysql_native_password sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" ##server_id = 8888 ##datadir = /mysql_data/data transaction_isolation = READ-COMMITTED event_scheduler = OFF open_files_limit = 327675 ##设置MySQL运行过程中可以打开的文件数，避免出现“too many open files”报错。建议设置值使用公式：10+max_connections+(table_open_cache*2)。 secure_file_priv = /tmp # connection # interactive_timeout = 600 ##服务器在关闭交互连接之前等待活动的秒数。交互客户端定义为使用mysql_real_connect（）的client_interactive选项的客户端。另请参见等待超时。 wait_timeout = 600 ##连接在没有交互多少秒后关闭 lock_wait_timeout = 1800 ##DDL操作的锁等待时间 skip_name_resolve = ON max_connections = 1024 max_user_connections = 256 max_connect_errors = 1000000 # table cache performance settings # table_open_cache = 4096 table_definition_cache = 4096 table_open_cache_instances = 64 # session memory settings # read_buffer_size = 16M read_rnd_buffer_size = 32M sort_buffer_size = 32M tmp_table_size = 64M join_buffer_size = 128M thread_cache_size = 64 # optimizer switch settings # optimizer_switch = ‘index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on‘ ##优化器开关设置。 # log settings # log_timestamps = system ##general_log = ON ##general_log_file = /usr/local/mysql/data/MySQL01-general.log slow_query_log = ON long_query_time = 1 ##slow_query_log_file = /usr/local/mysql/data/MySQL01-slow.log log_queries_not_using_indexes = ON log_slow_admin_statements = ON ##log_slow_slave_statements = ON log_throttle_queries_not_using_indexes = 10 expire_logs_days = 90 min_examined_row_limit = 100 log_bin_trust_function_creators = ON # innodb settings # innodb_buffer_pool_size = 160G innodb_buffer_pool_instances = 16 innodb_page_cleaners = 16 ##设置page clean线程的个数。这个值推荐和innodb_buffer_poool_instances相等。该值大于innodb_buffer_poool_instances时，自动设置为与innodb_buffer_poool_instances相同的值。 innodb_lru_scan_depth = 2048 ##控制LRU列表中可用页的数量。默认值1024。写密集型负载使IO饱和，或者使用机械盘时建议使用默认值。调整innodb_lru_scan_depth时，从一个较低的值开始，向上配置设置，目标是很少看到零空闲页面。另外，在更改缓冲池实例的数量时，考虑调整innodb_lru_scan_depth，因为innodb_lru scan_depth*innodb_buffer_pool_instances定义了页面清理线程每秒执行的工作量。推荐使用默认值。 innodb_fast_shutdown = 0 ##设置innodb关机模式。有效值0、1、2。0表示执行慢关机，即在关闭前彻底清除和更改缓冲区合并，这种模式下开机是最快的；1表示关机时跳过上述操作，但是在开机时会执行上述操作，称为快关机；2表示刷新其日志并冷关闭，不会丢失任何已提交的事务下，获得最快的关机，这种模式是开机最慢的。 innodb_max_dirty_pages_pct = 80 ##设置当缓冲池中脏页达到百分之80时，强制进行checkpoint,将一部分脏页刷新到磁盘上。默认值90。推荐值80。这个值不建议设置太小，设置太小会加重IO的写操作。 innodb_buffer_pool_dump_pct = 80 ##正常关机时将innodb_buffer_pool中百分之八十的数据保存到磁盘中 innodb_lock_wait_timeout = 5 ##innodb dml锁的等待超时时间，单位秒。 innodb_rollback_on_timeout = ON ##等待超时的事务进行回滚。 innodb_io_capacity = 400 ##设置后台线程每秒操作IO的次数。默认值200。理想情况下尽可能低，如果太大会从缓冲池中清除过多的数据，反而使缓冲池失去了“缓冲”的作用。对于SSD磁盘默认值200已经足够。15000转的机械磁盘建议设置为200。15000转以下的机械磁盘建议设置为100。 innodb_io_capacity_max = 2000 ##设置在刷新落后时，后台任务执行的最大IOPS数。默认值2000。最小值也是2000。这个值在使用SSD磁盘时也无需调大。 innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 ##对于SSD盘将这个值设置为0。由于该功能会使不怎么脏的页进行了写入，而后该页又会很快变成脏页，而固态硬盘有超高的IOPS，因此要置为0。可选值1表示刷新相同范围内连续的脏页，可选值2表示刷新相同范围内的脏页。 innodb_undo_tablespaces = 3 innodb_log_file_size = 1900MB ##这个值是百度公司统一的。 innodb_log_files_in_group = 2 innodb_log_buffer_size = 64MB innodb_checksum_algorithm = strict_crc32 ##设置innodb存储引擎每次读取页时，使用checksum函数的哪个算法。 innodb_thread_concurrency = 32 innodb_print_all_deadlocks = ON ##将死锁信息打印到mysql错误日志中 innodb_sort_buffer_size = 64M innodb_write_io_threads = 16 innodb_read_io_threads = 16 innodb_stats_persistent_sample_pages = 64 innodb_online_alter_log_max_size = 1G innodb_open_files = 4096 # replication settings # sync_binlog = 1 ##当有多个从库，并且开启了半同步复制这个值可以设置成1000。百度公司在半同步复制时统一设置成1000。 ##gtid_mode = ON ##enforce_gtid_consistency = ON ##binlog_gtid_simple_recovery = ON log_slave_updates = OFF binlog_rows_query_log_events = ON relay_log_recovery = ON ##slave_skip_errors = ddl_exist_errors # replication settings for slave # ##slave_parallel_workers = 16 ##设置多线程SQL线程数量 ##slave_parallel_type = LOGICAL_CLOCK ##设置多线程复制的模式 ##slave_preserve_commit_order = ON ##slave_transaction_retries = 128 # semi sync replication settings # ##rpl_semi_sync_master_enabled = ON ##rpl_semi_sync_master_timeout = 1000 # semi sync replication settings for slave # ##rpl_semi_sync_slave_enabled = ON # perforamnce_schema settings performance_schema_digests_size = 40000 performance_schema_max_table_handles = 40000 performance_schema_max_table_instances = 40000 performance_schema_max_sql_text_length = 4096 performance_schema_max_digest_length = 4096 # group replication settings # ##plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so" ##transaction-write-set-extraction = XXHASH64 ##report_host = 127.0.0.1 # optional for group replication ##binlog_checksum = NONE # only for group replication ##loose_group_replication = FORCE_PLUS_PERMANENT ##loose_group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" ##loose_group_replication_compression_threshold = 100 ##loose_group_replication_flow_control_mode = 0 ##loose_group_replication_single_primary_mode = 0 ##loose_group_replication_enforce_update_everywhere_checks = 1 ##loose_group_replication_transaction_size_limit = 10485760 ##loose_group_replication_unreachable_majority_timeout = 120 ##loose_group_replication_start_on_boot = 0