Introduction to /etc/my.cnf parameters in MySQL 5.7

[client]
port = 3306
default-character-set=utf8mb4
socket=/tmp/mysql.sock

[mysql]
#prompt="\\u@\\h :\\d \\r:\\m:\\s> "
prompt="\\u@\\h \\R:\\m:\\s [\\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8mb4
socket=/tmp/mysql.sock

[mysqld]
##########################
# summary
##########################
skip_name_resolve = on
#skip-grant-tables
#skip-networking

bind-address = 0.0.0.0
port = 3306
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
tmpdir = /tmp
pid-file=/tmp/mysqld.pid

explicit_defaults_for_timestamp=1
lower_case_table_names=1

table_open_cache = 8000
transaction_isolation = 'REPEATABLE-READ'

##########################
# time out
##########################
connect_timeout = 20
wait_timeout = 300
interactive_timeout = 300

##########################
#connection
##########################
max_connections = 2000
max_user_connections = 1900
max_connect_errors = 100000
max_allowed_packet = 1G

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_bin

##########################
# log bin
##########################
server-id = 1
log_bin = mysql-bin
# ROW、STATEMENT、MIXED
binlog_format = row
sync_binlog = 1
expire_logs_days = 7
binlog_cache_size = 128m
max_binlog_cache_size = 512m
max_binlog_size = 256M
master_info_repository=TABLE
log_slave_updates=ON
binlog_checksum=none

#binlog_ignore_db=information_schema
#binlog_ignore_db=mysql
#binlog_ignore_db=performance_schema
#binlog_ignore_db=sys
#binlog_do_do=
replicate_ignore_db=information_schema
replicate_ignore_db=mysql
replicate_ignore_db=performance_schema
replicate_ignore_db=sys
#replicate_do_db=

##########################
#gtid
##########################
gtid_mode = on
enforce_gtid_consistency = on

##########################
# slave parallel
##########################
slave_net_timeout=60
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=4

##########################
# log relay
##########################
relay_log_info_repository=TABLE
relay_log = mysql-relay-bin
relay_log_purge = on
relay_log_recovery = on
max_relay_log_size = 1G

##########################
# log error
##########################
log_error=/data/mysql/mysqld_error.log

##########################
# log slow
##########################
slow_query_log = on
slow_query_log_file = /data/mysql/mysqld_slow.log
long_query_time = 2
log_queries_not_using_indexes = on

##########################
# log general
##########################
general_log = on
general_log_file = /data/mysql/mysqld_gener.log


##########################
# thread pool,Enterprise edition
##########################
#thread_handling=pool-of-threads
#thread_handling=one-thread-per-connection
#thread_pool_oversubscribe=8 

##########################
# innodb
##########################
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=2
innodb_adaptive_flushing=on
innodb_file_per_table=1
innodb_log_file_size=1024M
innodb_log_buffer_size=64M
max_prepared_stmt_count=150000
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=50

##########################
# password policy
# after init 
##########################
#validate_password_policy=0
#validate_password_length=4     
#validate_password_mixed_case_count=0
#validate_password_number_count=0
#validate_password_special_char_count=0

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 # indicates that the serial number of this machine is 1, which generally means master skip-name-resolve
# Disable MySQL from performing DNS resolution for external connections. Using this option can eliminate the time MySQL spends on DNS resolution. But please note that if you turn on this option,
# All remote host connection authorizations must use IP address mode, otherwise MySQL will not be able to process connection requests normally#skip-networking

back_log = 600
# The number of connections MySQL can have. This comes into play when the main MySQL thread gets a lot of connection requests in a short period of time.
# The main thread then takes a moment (albeit a short one) to check the connection and start a new thread. The back_log value indicates how many requests can be stacked before MySQL temporarily stops answering new requests.
# If you expect a lot of connections in a short period of time, you may want to increase this. That is to say, if the MySQL connection data reaches max_connections, the new incoming request will be stored in the stack.
# Waiting for a connection to release resources. The number of the stack is back_log. If the number of waiting connections exceeds back_log, no connection resources will be granted.
# Additionally, this value (back_log) is limited by the size of your operating system's listening queue for incoming TCP/IP connections.
# Your operating system has its own limits on this queue size (check your OS documentation to find out the maximum value of this variable), trying to set back_log higher than your OS limit will have no effect.

max_connections = 1000
# The maximum number of connections for MySQL. If the server has a large number of concurrent connection requests, it is recommended to increase this value to increase the number of parallel connections. Of course, this is based on the machine's ability to support it. Because if there are more connections, MySQL will provide a connection buffer for each connection, which will consume more memory. Therefore, you should adjust this value appropriately and not blindly increase the value. You can use the 'conn%' wildcard to view the number of connections in the current state to determine the size of this value.

max_connect_errors = 6000
# For the same host, if there are more interrupted error connections than the number specified in this parameter, the host will be prohibited from connecting. To unblock the host, execute: FLUSH HOST.

open_files_limit = 65535
# MySQL open file descriptor limit, the default minimum is 1024; when open_files_limit is not configured, compare the value of max_connections*5 and ulimit -n, whichever is larger,
# When open_file_limit is configured, compare the values ​​of open_files_limit and max_connections*5, and use the larger one.

table_open_cache = 128
# Every time MySQL opens a table, it reads some data into the table_open_cache cache. When MySQL cannot find the corresponding information in this cache, it will read it from the disk. Default value: 64
# Assuming that the system has 200 concurrent connections, this parameter needs to be set to 200*N (N is the number of file descriptors required for each connection);
# When table_open_cache is set to a large value, if the system cannot handle so many file descriptors, the client will fail to connect. max_allowed_packet = 4M
# Accepted packet size; it is safe to increase the value of this variable because additional memory will only be allocated when needed. For example, MySQLd allocates more memory only if you issue long queries or if MySQLd has to return large result rows.
# The small default value for this variable is a precaution to catch error packets between client and server and to ensure that memory does not overflow due to accidental use of large packets.

binlog_cache_size = 1M
# When a transaction is not committed, the logs generated are recorded in the cache; when the transaction needs to be committed, the logs are persisted to the disk. The default binlog_cache_size is 32K

max_heap_table_size = 8M
# Defines the size of the memory table that the user can create. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic change of tmp_table_size = 16M
# MySQL heap table buffer size. All joins are done in one DML instruction, and most joins can even be done without temporary tables.
# Most temporary tables are memory-based (HEAP) tables. Temporary tables with large record lengths (the sum of the lengths of all columns) or tables containing BLOB columns are stored on disk.
# If the size of an internal heap table exceeds tmp_table_size, MySQL can automatically change the in-memory heap table to a hard disk-based MyISAM table as needed. You can also increase the size of temporary tables by setting the tmp_table_size option. That is to say, if you increase this value, MySQL will also increase the size of the heap table, which can improve the speed of join queries. read_buffer_size = 2M
# MySQL read buffer size. A request for a sequential scan of a table allocates a read buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer.
# If sequential scan requests for the table are very frequent and you think that frequent scans are too slow, you can improve performance by increasing the value of this variable and the memory buffer size read_rnd_buffer_size = 8M
# MySQL random read buffer size. When rows are read in arbitrary order (for example, in sorted order), a random read buffer is allocated. When performing a sort query,
# MySQL will first scan the buffer to avoid disk search and improve query speed. If you need to sort a large amount of data, you can increase this value appropriately. However, MySQL will allocate this buffer space for each client connection, so you should try to set this value appropriately to avoid excessive memory overhead sort_buffer_size = 8M
# The size of the buffer used by MySQL to perform sorts. If you want to speed up your ORDER BY, first see if you can get MySQL to use indexes instead of an extra sorting phase.
# If not, try increasing the size of the sort_buffer_size variable join_buffer_size = 8M
# The buffer size that can be used for joint query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection thread_cache_size = 8
# This value (default 8) indicates the number of threads that can be reused in the cache. If there is still space in the cache when disconnecting, the client's threads will be put into the cache.
# If the thread is requested again, the request will be read from the cache. If the cache is empty or it is a new request, the thread will be recreated. If there are many new threads,
# Increasing this value can improve system performance. By comparing the variables of the Connections and Threads_created states, you can see the role of this variable. (–> indicates the value to be adjusted)
# According to the physical memory settings, the rules are as follows:
# 1G —> 8
# 2G —> 16
# 3G —> 32
# Greater than 3G —> 64

query_cache_size = 8M
#MySQL query buffer size (MySQL provides a query buffer mechanism since 4.0.1) Using the query buffer, MySQL stores the SELECT statements and query results in the buffer.
# In the future, for the same SELECT statement (case sensitive), the results will be read directly from the buffer. According to the MySQL user manual, using the query buffer can achieve up to 238% efficiency.
# By checking the status value 'Qcache_%', you can know whether the query_cache_size setting is reasonable: if the value of Qcache_lowmem_prunes is very large, it indicates that insufficient buffering often occurs.
# If the value of Qcache_hits is also very large, it means that the query buffer is used very frequently, and you need to increase the buffer size; if the value of Qcache_hits is not large, it means that your query repetition rate is very low.
# In this case, using query buffer will affect efficiency, so you can consider not using query buffer. In addition, adding SQL_NO_CACHE to the SELECT statement can explicitly indicate that query buffer query_cache_limit = 2M is not used.
#Specify the buffer size that can be used by a single query, the default is 1M

key_buffer_size = 4M
#Specify the buffer size used for indexing, increase it to get better handling of indexing (for all reads and multiple writes), as much as you can afford. If you make it too big,
# The system will start paging and really slow down. For servers with around 4GB of memory, this parameter can be set to 384M or 512M. By checking the status values ​​Key_read_requests and Key_reads,
# You can know whether the key_buffer_size setting is reasonable. The ratio key_reads/key_read_requests should be as low as possible.
# At least 1:100, 1:1000 is better (the above status value can be obtained using SHOW STATUS LIKE 'key_read%'). Note: If this parameter value is set too high, the overall efficiency of the server will be reduced. ft_min_word_len = 4
# Minimum length of word segmentation vocabulary, default is 4

transaction_isolation = REPEATABLE-READ
#MySQL supports 4 transaction isolation levels, they are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# If not specified, MySQL defaults to REPEATABLE-READ and ORACLE defaults to READ-COMMITTED

log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30 #Delete binlogs older than 30 days log_error = /data/mysql/mysql-error.log #Error log path slow_query_log = 1
long_query_time = 1 #If the slow query time exceeds 1 second, it is a slow query slow_query_log_file = /data/mysql/mysql-slow.log

performance_schema = 0
explicit_defaults_for_timestamp

#lower_case_table_names = 1 #Case insensitive skip-external-locking #MySQL option to avoid external locking. This option is enabled by default default-storage-engine = InnoDB #Default storage engine innodb_file_per_table = 1
# InnoDB is in independent tablespace mode. Each table in each database will generate a data space. # Advantages of independent tablespace:
# 1. Each table has its own separate tablespace.
# 2. The data and indexes of each table are stored in their own tablespace.
# 3. A single table can be moved between different databases.
# 4. Space can be reclaimed (except for the drop table operation, where the table space cannot be reclaimed by itself)
# shortcoming:
# The size of a single table is too large, such as more than 100G
# in conclusion:
# Shared tablespaces have few advantages in Insert operations. None of the others perform as well as independent tablespaces. When enabling independent tablespaces, please adjust innodb_open_files appropriately:

innodb_open_files = 500
# Limit the data of the table that Innodb can open. If there are a lot of tables in the database, please increase this. The default value is 300

innodb_buffer_pool_size = 64M
# InnoDB uses a buffer pool to store indexes and raw data, unlike MyISAM.
# The larger you set it, the less disk I/O you need to access the data in the table.
# On a standalone database server, you can set this variable to 80% of the server's physical memory size.
# Do not set it too large, otherwise, the competition for physical memory may cause paging jitter in the operating system.
# Note that on 32-bit systems you may be limited to 2-3.5G user-level memory per process,
# So don't set it too high.

innodb_write_io_threads = 4
innodb_read_io_threads = 4
# innodb uses background threads to handle read and write I/O (input and output) requests on data pages. Change it according to the number of your CPU cores. The default is 4
# Note: These two parameters do not support dynamic changes. You need to add the parameters to my.cnf and restart the MySQL service after modification. The allowed value range is from 1 to 64.

innodb_thread_concurrency = 0
# The default setting is 0, which means there is no limit on the number of concurrent connections. It is recommended to set it to 0 to better utilize the CPU multi-core processing capabilities and increase the number of concurrent connections innodb_purge_threads = 1
# The cleanup operation in InnoDB is a type of operation that periodically recycles useless data. In previous versions, the purge operation was part of the main thread, which meant that it could block other database operations while running.
# Starting from MySQL 5.5.X, this operation runs in an independent thread and supports more concurrency. Users can choose whether to use a separate thread for the purge operation by setting the innodb_purge_threads configuration parameter. By default, the parameter is set to 0 (do not use a separate thread). When set to 1, it means using a separate purge thread. Recommended value: 1

innodb_flush_log_at_trx_commit = 2
# 0: If the value of innodb_flush_log_at_trx_commit is 0, the log buffer will be flushed to the disk every second, and no operation will be performed when the transaction is committed (the execution is performed by the MySQL master thread.
# The main thread writes the redo log buffer to the redo log file (REDO LOG) on disk every second. Regardless of whether the transaction has been committed) the default log file is ib_logfile0, ib_logfile1
# 1: When set to the default value of 1, the log buffer is flushed to the log each time a transaction is committed.
# 2: If set to 2, a log will be written each time a transaction is committed, but the flush operation will not be performed. The log file is refreshed every second. It should be noted that there is no guarantee that the data will be flushed to disk 100% every second, as this depends on the scheduling of the process.
# Every time a transaction is committed, the data is written to the transaction log. The write operation here only calls the file system's write operation, and the file system has a cache, so this write cannot guarantee that the data has been written to the physical disk. # The default value of 1 is to ensure complete ACID. Of course, you can set this configuration item to a value other than 1 to exchange for higher performance, but when the system crashes, you will lose 1 second of data.
# If set to 0, the last second of transactions will be lost when the mysqld process crashes. If set to 2, the last second of data will be lost only if the operating system crashes or there is a power outage. InnoDB ignores this value when doing recovery.
# Summary# Setting it to 1 is of course the safest, but the performance is the worst (relative to the other two parameters, but not unacceptable). If data consistency and integrity are not required, you can set it to 2. If you only need performance, such as a high-concurrency log server, set it to 0 to get higher performance. innodb_log_buffer_size = 2M
# This parameter determines the memory size used by these log files, in M. Larger buffers improve performance, but unexpected failures will result in data loss. MySQL developers recommend setting it between 1 and 8 MB innodb_log_file_size = 32 MB
# This parameter determines the size of the data log file. A larger setting can improve performance, but it will also increase the time required to recover a failed database. innodb_log_files_in_group = 3
# To improve performance, MySQL can write log files to multiple files in a circular manner. Recommended setting: 3

innodb_max_dirty_pages_pct = 90
# The innodb main thread refreshes the data in the cache pool to make the dirty data ratio less than 90%

innodb_lock_wait_timeout = 120 
#Timeout in seconds an InnoDB transaction can wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks in its own lock tables and rolls back the transaction. InnoDB notices the lock settings with the LOCK TABLES statement. The default value is 50 seconds bulk_insert_buffer_size = 8M
# Batch insert cache size. This parameter is for the MyISAM storage engine. Suitable for improving efficiency when inserting 100-1000+ records at a time. The default value is 8M. It can be doubled according to the amount of data.

myisam_sort_buffer_size = 8M
# MyISAM sets the size of the buffer used when restoring a table. The buffer allocated when sorting a MyISAM index during REPAIR TABLE or creating an index with CREATE INDEX or ALTER TABLE is myisam_max_sort_file_size = 10G
# Do not use the quick sort index method to create an index if the temporary file will grow larger than the index. Note: This parameter is given in bytes. myisam_repair_threads = 1
# If the value is greater than 1, create MyISAM table indexes in parallel during Repair by sorting (each index in its own thread) 

interactive_timeout = 28800
# The number of seconds the server waits for activity before closing an interactive connection. An interactive client is defined as one that uses the CLIENT_INTERACTIVE option in mysql_real_connect(). Default value: 28800 seconds (8 hours)

wait_timeout = 28800
# The number of seconds the server waits for activity before closing a non-interactive connection. When the thread starts, the session wait_timeout value is initialized according to the global wait_timeout value or the global interactive_timeout value.
# Depends on the client type (defined by the CLIENT_INTERACTIVE connection option of mysql_real_connect()). Default value: 28800 seconds (8 hours)
# The maximum number of connections supported by the MySQL server is capped. Since each connection consumes memory, we hope that the client will connect to the MySQL Server and complete the corresponding operations.
# Should disconnect and free the occupied memory. If your MySQL Server has a large number of idle connections, they will not only consume memory in vain, but also if the connections continue to accumulate without disconnecting,
# Eventually the MySQL Server connection limit will be reached, and a 'too many connections' error will be reported. The value of wait_timeout should be set based on the system's operating conditions.
# After the system has been running for a while, you can use the show processlist command to view the current system connection status. If you find a large number of connection processes in the sleep state, it means that the parameter is set too large.
# It can be adjusted appropriately to be smaller. To make it effective, both interactive_timeout and wait_timeout must be set at the same time.

[mysqldump]
quick
max_allowed_packet = 16M #The maximum packet length sent and received by the server [myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M