[Mysql]prompt= [\\[email protected]\\h][\\d]>\\_[mysqld]# Basic Settings #user=Mysqlsql_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=utf8mb4transaction_isolation= read-Committedexplicit_defaults_for_timestamp=1Max_allowed_packet=16777216Event_scheduler=1 #5.1 version is on, 5.7 is off by default, cycle task scheduling thread. # connection #interactive_timeout=1800Wait_timeout=1800Lock_wait_timeout=1800 # #元数据锁等待超时时间Skip_name_resolve=1 # #只允许IP授权, skip domainmax_connections= +max_connect_errors=1000000 # #连接请求被阻塞, flush hosts resolved, default value of# Table Cache performance Settingstable_open_cache=4096 # #show status like "Open_tables"; value big, big. Table_definition_cache=4096 #table_open_cache_instances=#涉及到锁# Session Memory settings #read_buffer_size=16mread_rnd_buffer_size=32msort_buffer_size=32mtmp_table_size=64mjoin_buffer_size=128mthread_cache_size=#有大量新连接, you can increase this value to improve performance. # Log Settings #log_error=Error.logslow_query_log=1Slow_query_log_file=slow.loglog_queries_not_using_indexes=1log_slow_admin_statements=1 #管理命令执行慢的也放到慢日志里log_slow_slave_statements=1 #从库上执行的慢查询也放到慢日志log_throttle_queries_not_using_indexes=Ten #1分钟不走索引的只记录10条expire_logs_days= -Long_query_time=2 #可以设置成0.5Min_examined_row_limit=# #低于100行的慢日志不记录Binlog-rows-query-log-events =1 # #binlog记录执行语句Log-bin-trust-function-creators =1 # #设置为0, the data is more secure, the UUID function can be executed without error, but Binlog is the row format, set to 1 to avoid error messages. expire-logs-days = -Log-slave-updates =1# InnoDB Settings #innodb_page_size=16384innodb_buffer_pool_size=160ginnodb_buffer_pool_instances=#提高并发, reducing contention on memory for different threads. Innodb_buffer_pool_load_at_startup=1 #开启加载热数据, 5.7.7 is turned on by defaultInnodb_buffer_pool_dump_at_shutdown=1 #关闭数据库备份热数据innodb_lru_scan_depth=4096Innodb_lock_wait_timeout=5 #锁超时innodb_io_capacity=10000 #根据情况设置, SSD 4000+Innodb_io_capacity_max=20000Innodb_flush_method=O_directinnodb_file_format=Barracuda #innodb文件格式, the default is also Barracuda, later this parameter will be discarded Innodb_file_format_max=Barracudainnodb_undo_logs=#回滚段, innodb_rollback_segments的别名。默认128
innodb_undo_tablespaces=3 #减小undo表空间的大小innodb_flush_neighbors=0 #ssd防止性能抖动, the mechanical disk is set to 1innodb_log_file_size=17179869184 #16GInnodb_log_files_in_group=2innodb_log_buffer_size=16777216 #16Minnodb_purge_threads=4Innodb_large_prefix=1innodb_thread_concurrency=#根据cpu设置最优性能Innodb_print_all_deadlocks=1 #打印死锁信息Innodb_strict_mode=1 #严格模式, similar to Sql_modeinnodb_sort_buffer_size=67108864innodb_write_io_threads= -innodb_read_io_threads= -innodb_file_per_table=1innodb_stats_persistent_sample_pages= -Innodb_autoinc_lock_mode=2innodb_online_alter_log_max_size=1G #online ddlinnodb_open_files=4096# Replication Settings #master_info_repository=tablerelay_log_info_repository=Tablesync_binlog=1Gtid_mode=onenforce_gtid_consistency=1Log_slave_updatesbinlog_format=rowbinlog_rows_query_log_events=1Relay_log=Relay.logrelay_log_recovery=1slave_skip_errors=Ddl_exist_errorsslave-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=1Rpl_semi_sync_master_timeout= therpl_semi_sync_slave_enabled=1# password Plugin #validate_password_policy=strongvalidate-password=Force_plus_permanent[mysqld-5.6]# Metalock Performance settingsmetadata_locks_hash_instances= -[Mysqld-5.7]# NewInnoDB Settings #loose_innodb_numa_interleave=1innodb_buffer_pool_dump_pct= +innodb_page_cleaners= -innodb_undo_log_truncate=1innodb_max_undo_log_size=2ginnodb_purge_rseg_truncate_frequency= -# NewReplication Settings #slave-parallel-type =Logical_clockslave-parallel-workers = -Slave_preserve_commit_order=1slave_transaction_retries= -# Other Change settings #binlog_gtid_simple_recovery=1Log_timestamps=systemshow_compatibility_56=on
Just reference, to set parameters according to the business.
Https://github.com/jdaaaaaavid/mysql_best_configuration/blob/master/my.cnf
Daniel's MySQL5.7 configuration file explanation, easy to view