MySQL 5.7 my.cnf configuration file detailed

[Client]
Default-character-set = Utf8mb4

[MySQL]
#开启 Tab Completion
#auto-rehash
Default-character-set = Utf8mb4

[Mysqld]
port=3306
basedir=/data/server/mysql57/
datadir=/data/server/mysql57/data/
Socket=/data/server/mysql57/data/mysql.sock
Symbolic-links=0
Log-error=/data/logs/mysql57/mysqld.log
Pid-file=/data/server/mysql57/data/mysqld57.pid

Disabling host name resolution

Skip-name-resolve

The default database engine

Default-storage-engine = InnoDB
Innodb-file-per-table=1innodb_force_recovery = # some pits
Group_concat_max_len = 10240sql_mode=expire_logs_days = 7memlock

Character Set Configuration

Character-set-client-handshake = FALSE
Character-set-server = Utf8mb4
Collation-server = Utf8mb4_unicode_ci
init_connect= ' SET NAMES utf8mb4 ' # # # GTID
server_id = 330759# to ensure stable GTID replication, row-level logging
Binlog_format = row

Turn on the Gtid function

Gtid_mode = On

Secure GTID transaction security when the Enforce_gtid_consistency feature is enabled, MySQL only allows transaction security and can be executed by the SQL statements that are logged, like CREATE TABLE ... select and create temporarytable statements, and SQL statements or transactions that update both the transaction table and the non-transactional table are not allowed to execute

Enforce-gtid-consistency = true# The following two configurations are primary from switchover, database high availability must be configured

Turn on the Binlog log function

Log_bin = Mysql57-bin

Turn on Update binlog log from library

Log-slave-updates = On
#slave复制进程不随mysql启动而启动
skip_slave_start=1### Slow Query log

Turn on the Slow query log feature

Slow_query_log = # More than 2 seconds of query logging down
Long_query_time = a query with no index in the record
Log_queries_not_using_indexes = 0slow_query_log_file =/data/logs/mysql57/slow.log
#log =/data/logs/mysql57/all.log

Automatic repair of records relay.info to data tables

Relay_log_info_repository = TABLE

Record Master.info to data table

Master_info_repository = TABLE

Enable Relaylog auto-Repair feature

Relay_log_recovery = On

Automatic deletion after the SQL thread finishes executing a relaylog

Relay_log_purge = 1### Data Security Configuration

Wei to turn off master creation function

Log_bin_trust_function_creators = On

Force writes to disk for every transaction executed

Sync_binlog = # timestamp column supports the Null property if it is not explicitly defined as NOT NULL

The column value of set TIMESTAMP is null and will not be set to current timestamp

explicit_defaults_for_timestamp=true### optimized configuration

Optimizing Chinese full-text fuzzy index

Ft_min_word_len = # Default library name Table name is saved in lowercase, not case sensitive
Lower_case_table_names = maximum size limit for a single record write

Too small may cause write (import) data to fail

Max_allowed_packet = 256M

Semi-synchronous replication turned on

#rpl_semi_sync_master_enabled = 1#rpl_semi_sync_slave_enabled = # semi-synchronous replication time-out setting
#rpl_semi_sync_master_timeout = 1000# Copy mode (keep system default)
#rpl_semi_sync_master_wait_point = After_sync

Back end as long as there is one to receive the log and write Relaylog even if successful

#rpl_semi_sync_master_wait_slave_count = # multi-threaded replication

Method of parallel replication based on group submission

Slave_parallel_type = Logical_clock
#并行的SQL线程数量, this parameter will only start with n threads for SQL Redo if the 1<n is set.
#经过测试对比发现, if the connection thread of the main library is M, only M < n, the delay of the repository can be completely avoided.
Slave_parallel_workers = 4### number of connections limit
max_connections = 1500# authentication password exceeds 20 deny connection
Max_connect_errors = 200# Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request

That is, if the number of MySQL connections reaches Max_connections, the new request will be present on the stack waiting for a connection to release the resource, the number of that stack is back_log, and if the number of waiting connections exceeds back_log the connection resource will not be granted

Back_log = 500open_files_limit = number of seconds the 65535# server waits for activity before closing an interactive connection
Interactive_timeout = number of seconds that the 3600# server waits for activity before closing a non-interactive connection
Wait_timeout = 3600### memory allocation

Specifies the size of the table cache. Whenever MySQL accesses a table, the table is opened and put into it if there is room in the table buffer, which allows for faster access to the table contents

Table_open_cache = 1024# The memory allocated for each session, which is used to store the cache of the binary log during the transaction
Binlog_cache_size = 4M

The maximum size of the temporary table in memory

Tmp_table_size = 128M

Create the maximum size of the memory table (keep the system default, do not allow the creation of oversized memory tables) if there is a need to use as a cache, you can adjust this value appropriately

Max_heap_table_size = 16M

Sequential read, read into buffer size set the number of full table scans, you can increase this value

Read_buffer_size = 1M

Random read, read in buffer size setting

Read_rnd_buffer_size = 8M

In case of high concurrency, this value needs to be reduced to 64k-128k

Sort_buffer_size = 1M

The maximum cache size per query is 1M and the maximum cache 64M data

Query_cache_size = 64M
Query_cache_limit = 1M

Mention the efficiency of join

Join_buffer_size = 16M

Thread-Join re-use

thread_cache_size = 64### InnoDB optimization

Setting data buffers for memory utilization

innodb_buffer_pool_size=2g

Log aspect set transaction log size

Innodb_log_file_size = 256M

Log buffer size

Innodb_log_buffer_size = 4M

Buffering of transactions in memory

Innodb_log_buffer_size = 3M

The main library keeps the system default, transactions are written to disk immediately, and no one transaction is lost

Innodb_flush_log_at_trx_commit = # mysql data file setup, initial 100, 10M auto-expansion
#innodb_data_file_path = Ibdata1:100m:autoextend

To improve performance, MySQL can write log files to multiple files in a circular manner

Innodb_log_files_in_group = 3# #其他设置

If the tables in the library are particularly numerous, increase this value

#innodb_open_files = 800# Allocates a separate tablespace for each InnoDB table
innodb_file_per_table = # InnoDB Use a background thread to process the number of write I/O (input) requests on the data page
Innodb_write_io_threads = 8# InnoDB use a background thread to process the number of read I/O (output) requests on the data page
Innodb_read_io_threads = 8# Enable separate threads to reclaim useless data
innodb_purge_threads = # dirty data brush into the disk (first to keep the system default, swap too much to use, to reduce this value, after small, and disk interaction increased, performance degradation)
innodb_max_dirty_pages_pct = maximum time the 90# transaction waits for a resource to be fetched
Innodb_lock_wait_timeout = 120# Open InnoDB Strict check mode, no warning, direct error 1 on 0 off
innodb_strict_mode=1# allow column indexes up to 3072
Innodb_large_prefix = On

[Mysqldump]

Turn on Quick export

Quick
Default-character-set = Utf8mb4
Max_allowed_packet = 256M

MySQL 5.7 my.cnf configuration file detailed