#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工具、mysqldump工具等）
wait_timeout = 600 ##非交互式连接超时时间。指连接mysql的api程序，jdbc连接等。
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
