[client] port = 3306 socket = /var/run/mysqld/mysqld.sock default-set-character = utf8mb4

[mysqld_safe] thp-setting=never socket = /var/run/mysqld/mysqld.sock nice = 0

[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp

local-infile = 0 lc-messages-dir = /usr/share/mysql skip-external-locking skip-name-resolve

bind-address = 127.0.0.1

对timestamp新的行为控制参数，相关文章。

explicit_defaults_for_timestamp = 1

character-set-server = utf8mb4 default-storage-engine=INNODB

thread相关参数:Thread_pool_size X Thread_pool_oversubscribe = MySQL内部同时支持运行的最大任务数，相关文章

MySQL线程调度方式,

thread_handling = pool-of-threads

根据系统的CPU创建出thread_pool_size(该参数不建议调整)threadpool中group数量，默认为cpu核心数，server启动时自动计算

thread_pool_size = auto

每个thread_pool_size里同时可以运行多少个任务，超过限制后，继续创建worker线程会被延迟

thread_pool_oversubscribe = 20

timer线程检测间隔，单位为毫秒，用于防止thread pool里的线程死掉．Thread pool里线程如果超过thread_pool_stall_limit声明的时间没响应降会重新创建

thread_pool_stall_limit = 50

用于声明extra_port最大允许的连接，extra_port 去监听一个和服务端口不一样的端口，用于Thread pool被占满或是被锁着的情况下用于管理使用．

extra_max_connection = 5

线程池的优先级：transactions：使用优先队列和普通队列，对于事务已经开启的statement，放到优先队列中，否则放到普通队列中。statements：只使用优先队列

；none: 只是用普通队列，本质上和statements相同，都是只是用一个队列 thread_pool_high_prio_mode = transactions

当开启了优先队列模式后，每个连接最多允许thread_pool_high_prio_tickets次被放到优先队列中，之后放到普通队列中

thread_pool_high_prio_tickets=4294967295

线程最大空闲时间，单位为秒，超过限制后会退出，默认60

thread_pool_idle_timeout=60

threadpool中最大线程数目，线程总数超过该限制后不能继续创建更多线程

thread_pool_max_threads = 100000

FullText

MyISAM

ft_min_word_len = 1 ft_max_word_len = 84

InnoDB

innodb_ft_min_token_size= 1 innodb_ft_max_token_size=84

MyISAM 停止词存放在文件

ft_stopword_file = /var/lib/mysql/stopword.txt

InnoDB，停止词存放在表

innodb_ft_server_stopword_table = dba_test/ft_stopwords innodb_ft_user_stopword_table = dba_test/ft_stopwords

控制在创建临时表时使用的存储引擎，默认为innodb

default_tmp_storage_engine = innodb

线上可以开启

innodb_undo_directory = /var/lib/mysql/undolog/ innodb_undo_tablespaces = 8 innodb_undo_logs = 128

用于优化in()，以确认是否直接使用索引统计，相关文章

eq_range_index_dive_limit = 200

back_log = 500

thread_stack = 192K

max_allowed_packet = 32M max_connections = 600 max_connect_errors = 2000 max_heap_table_size = 256M max_length_for_sort_data= 2048 tmp_table_size = 256M

read_buffer_size = 512K read_rnd_buffer_size = 1M sort_buffer_size = 512K join_buffer_size = 256K

table_open_cache = 1024

对table cache进行划分，减少table cache的锁竞争，累死innodb_buffer_poll_instances

table_open_cache_instances = 8 thread_cache_size = 64

* Query Cache Configuration

query_cache_type = 0

query_cache_size = 0M

query_cache_limit = 1M

open_files_limit = 5000

* Logging and Replication

log_error = /var/log/mysql/error_3306.log

开启慢查询日志，相关文章1，文章2

slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 0.5

log-queries-not-using-indexes

general_log = 1

慢查询过滤器，用于Percona，相关文章

log_slow_filter = 'string'

可用值session or query

log_slow_rate_type = session

记录每个session/query的日志，如果log_slow_rate_type为session，将不记录replication thread的日志

log_slow_rate_limit = 1

语句执行存储过程(不包括触发器)慢将记录到慢查询日志

log_slow_sp_statements = 1

指定慢查询日志包含多少信息，相关文章

log_slow_verbosity = ''

每个慢查询记录时间戳

slow_query_log_timestamp_always

精度为second或microsecond

slow_query_log_timestamp_precision=second

该变量控制最大slow文件数量，设置slow_query_log_file为没有.log后缀的文件

max_slowlog_files

超过该值，slow日志将自动旋转，生成slow_query_log_file.000001这样的文件

max_slowlog_size

log_bin = mysql-bin-3306.log binlog_cache_size = 1M max_binlog_size = 1024M binlog_format = MIXED

binlog_format = ROW

binlog_row_image = minimal

binlog_rows_query_log_events = 1

sync_binlog = 1000 expire_logs_days = 5

binlog_checksum = NONE

事务提交顺序和binlog记录顺序是相同的，默认打开

binlog_order_commits =1 server-id = 10

relay_log = mysqld-relay-bin

* TokuDB

tokudb_cache_size = 512M tokudb_commit_sync = 0 tokudb_fsync_log_period = 1000 tokudb_data_dir = /var/lib/mysql/tokudb_data tokudb_log_dir = /var/lib/mysql/tokudb_log tokudb_tmp_dir = /tmp

* MyISAM

key_buffer_size = 128M bulk_insert_buffer_size = 8M concurrent_insert = 2

myisam-recover = BACKUP

myisam_recover_options = BACKUP

* InnoDB

打印死锁到errorlog

innodb_print_all_deadlocks = 1

#预读，对于随机读写，推荐关闭 innodb_read_ahead_threshold = 0

预热

innodb_buffer_pool_dump_now = 1 innodb_buffer_pool_load_now = 1

innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1

预热文件

innodb_buffer_pool_filename = ib_buffer_pool

#事务日志记录的大小，默认512，ssd可以设置4096 innodb_log_block_size = 4096 innodb_page_size = 16K

innodb_page_size = 4K #ssd

innodb_flush_neighbors = 0

ssd

innodb_flush_neighbors = 0

影响page cleaner 线程一次扫描LRU/UNZIP_LRU的深度，默认为1024，IO能力强的可以适当调大。 innodb_io_capacity/innodb_buffer_pool_instances

innodb_lru_scan_depth =2000

innodb_strict_mode = 1 innodb_buffer_pool_size = 10000M innodb_buffer_pool_instances = 3 innodb_change_buffering = all innodb_change_buffer_max_size = 20 innodb_autoextend_increment = 64 innodb_additional_mem_pool_size = 15M#5.7中移除，innodb_use_sys_malloc 配置为 ON 时，innodb_additional_mem_pool_size 失效（直接从操作系统分配内存） innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2

自定义刷新日志时间，每隔这么多秒刷一次日志，只有在innodb_flush_log_at_trx_commit=2时才生效

innodb_flush_log_at_timeout = 3

innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 60

innodb_read_io_threads = 8 innodb_write_io_threads = 8

innodb_io_capacity = 800

innodb_io_capacity = 2000 #ssd

innodb_io_capacity_max = 2000

innodb_adaptive_flushing = 1 innodb_adaptive_flushing_lwm = 15

innodb_old_blocks_pct = 20 innodb_old_blocks_time = 1000

online ddl时并发DML产生的row log最大size，超过这个限制会导致DDL回滚

innodb_online_alter_log_max_size = 128M

innodb_flush_neighbors = 1

innodb_concurrency_tickets = 5000 innodb_optimize_fulltext_only = 0

innodb_use_native_aio = 1 innodb_use_sys_malloc = 1#innodb_use_sys_malloc 配置为 ON 时，innodb_additional_mem_pool_size 失效（直接从操作系统分配内存）。5.7中移除 innodb_thread_concurrency = 0#回收undo线程 innodb_purge_threads= 4

5.7 innodb_page_cleaners = 4

每次回收undo页的数量

innodb_purge_batch_size = 300

创建索引做merge-sort时，一个归并block的大小

innodb_sort_buffer_size = 16M

innodb_change_buffering = all

changebuffer占用bp的最大比例

innodb_change_buffer_max_size = 20

innodb_file_format = barracuda innodb_file_format_check = 1 innodb_file_format_max = barracuda innodb_stats_on_metadata = 0

innodb_open_files = 3000

Percona支持ALL_O_DIRECT

innodb_flush_method = ALL_O_DIRECT

innodb_flush_method = O_DIRECT_NO_FSYNC

lock_wait_timeout = 50

transaction-isolation = READ-COMMITTED

* replication

replicate-ignore-db = test replicate-ignore-db = mysql

binlog_do_db = include_database_name

skip-slave-start = 1 log_slave_updates = 1

gtid-mode=on # GTID only

enforce-gtid-consistency=true # GTID only

master_info_repository = table relay_log_info_repository = table

* slave

并行复制线程数

slave_parallel_workers = 4

多线程复制需要关闭

slave_transaction_retries = 0

在并发复制时总共执行这么多次事务后做一次checkpoint，更新show slave status的数据

slave_checkpoint_group = 512

在复制执行这么长时间后做一次checkpoint

slave_checkpoint_period = 300

在多线程复制时，在队列中Pending的事件所占用的最大内存，默认为16M，如果内存富余，或者延迟较大时，可以适当调大;注意这个值要比主库的max_allowed_packet大

Keys	Action
`?`	Open this help
`←`	Previous page
`→`	Next page
`s`	Search