MySQL configuration file

Description

1, red indicates key parameter
2, "Global Cache", "Thread cache", the global cache is all thread sharing, the thread cache is each line thread attached the data when the creation of a thread ( if not set up a thread pool), if there is a 200 connection, that is 200 threads, if the parameter setting value is 10M, then the parameter value is 10 *200=2000M=2G, it is possible to eat down memory, so the thread cache is not like the global cache as much as the number of occupied, not set too large, must pay attention to

Character-set-server=utf8
#服务器端字符集

Collation-server=utf8_bin
#字符集的校队规则, this is a pit, and the novice has to be careful.
#默认 ***_ci,ci is case insensitive, that is, "casing is not sensitive", a and a will be the same in character judgment;
_bin is a binary data compilation store, case sensitive,

Lower_case_table_names = 0
#默认为0, database, table name is case sensitive

Back_log = 600
#在MYSQL暂时停止响应新请求之前, the number of requests within a short period of time can be present on the stack. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. The default value is 50.

Max_connections = 3000
#MySQL允许最大的进程连接数, if the too many connections error is frequently present, you need to increase this value, but the larger the value, the larger the memory.

Max_connect_errors = 50
#设置每个主机的连接请求异常中断的最大次数, when this number of times is exceeded, the MySQL server will disallow host connection requests until the MySQL server restarts or clears the host information through the flush hosts command.

wait_timeout=864000
#wait_timeout的初始值是28800, when the application is not connected for 8 hours, it will start to sever the association with the application Connection pool and, when connected, will prompt for wait_timeout errors.

external-locking = FALSE
#使用 –skip-external-locking mysql option to avoid external locking. This option is turned on by default

Max_allowed_packet = 32M
#设置在网络传输中一次消息传输量的最大值. The system defaults to 1MB and the maximum value is 1GB, which must be set to a multiple of 1024.

Skip-name-resolve
#禁用DNS反向解析, the only limitation is that only IP addresses can be used later in the grant statement, so it is important to note that in adding this setting to an existing system

Slow_query_log=on
#打开慢查询记录
Slow_query_log_file=mysql-slow
#慢查询记录日志
Long_query_time = 1
Query for #记录执行时间超过N (seconds)

Server-id = 1
#主从复制时必须设置的参数, and cannot be duplicated with other machines
Log-bin=mysql-bin
#二进制日志
Binlog_cache_size = 4M
# " Thread Cache " is the memory allocated for each session, which is used to store the cache of binary logs during the transaction.
#show Global status like ' bin% ';
#上述语句我们可以得到当前 Usage of database binlog_cache_size
#+-----------------------+-------+
#| variable_name | Value |
#+-----------------------+-------+
#| Binlog_cache_disk_use |???
#| Binlog_cache_use |?????
#Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数
#Binlog_cache_use indicates the number of times the binlog_cache_size cache was used
When the #当对应的Binlog_cache_disk_use value is larger, we can consider the appropriate height binlog_cache_size corresponding value
Max_binlog_cache_size =2m
#表示的是binlog the maximum cache memory size that can be used, the default is
Max_binlog_size = 512M
#单binlog文件最大容量, a new Binlog file is created
Expire_logs_days = 7
#二进制文件自动删除天数, the default is 0, which means no deletion
#mysql使用flushlogs的操作来清除日志, the following conditions will trigger Flushlogs
#1. Restart
#2. binlog file Size reached parameter max_binlog_size limit
#3. Execute the command manually.

Default-storage-engine = InnoDB
#默认引擎

Transaction_isolation = read-committed
# Set the default transaction isolation level. The available levels are as follows:
# read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE
# 1.READ uncommitted-READ UNCOMMITTED 2.READ committe-Read Committed 3.REPEATABLE read-repeatable read 4. SERIALIZABLE-Serial

Table_cache = 2000
#设置最大缓存表的个数. Default 2000, global parameters, cache table information, including fields, indexes, and so on. The advantage of using table caching is that you can access the contents of the table more quickly. Executing flush tables empties the cached content. In general, you can see the status value Open_tables and opened_tables for the peak time of the database by showing the ' open%tables% ' to determine if you need to increase the value of Table_cache (where Open_ Tables is the number of tables that are currently open, and Opened_tables is the number of tables that are already open. That is, if open_tables approach Table_cache, and opened_tables this value is gradually increasing, then consider increasing the size of this value. There is table_locks_waited higher than the time, also need to increase the table_cache.
Tmp_table_size = 20M
# " Thread caching " It specifies the maximum value of the internal memory temporary table, which is allocated for each thread. (The actual limiting effect is the minimum value of Tmp_table_size and Max_heap_table_size.) If the temporary table exceeds the limit, MySQL automatically translates it into a disk-based MyISAM table, and the memory table does not.
#如果使用临时表情况少, you can default
Max_heap_table_size = 256M
#如果不使用内存表, you can default

thread_cache_size=
# Server thread Cache This value indicates that the number of threads stored in the cache can be re-used, and if there is room in the cache when the connection is disconnected, then the client's thread will be placed in the cache, if the thread is re-requested, Then the request will be read from the cache, if the cache is empty or a new request, then the thread will be recreated, and 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 effect of this variable. Set the rules as follows: The 1GB memory configuration is 8,2gb configured to 16,3GB configured for 32,4GB or higher memory, which can be configured larger.
thread_concurrency= 8
#hread_concurrency变量是针对于Solaris 8 and a lower version of the system, setting this variable mysqld calls the Thr_setconcurrency () function. This function allows the application to prompt the required number of threads for a thread that is running at the same time. This parameter has no effect in the current Solaris version. This parameter has been marked as obsolete in MySQL 5.6.1 and is removed in MySQL version 5.7.2.

Query_cache_size = 64M
# The previous article in the "global cache" specifically says this parameter, which is not suitable for allocation too large. And in high concurrency, write a large-scale system, it is recommended to disable this function.
Query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小, default is 1M
Query_cache_min_res_unit = 2k
#默认是4KB, setting a big value is good for big data queries, but if your queries are small data queries, it's easy to create memory fragmentation and waste.
#查询缓存碎片率 = Qcache_free_blocks/qcache_total_blocks * 100%
#如果查询缓存碎片率超过20%, you can use flush query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small data volumes.
#查询缓存利用率 = (query_cache_size–qcache_free_memory)/query_cache_size * 100%
#查询缓存利用率在25% below indicates that the Query_cache_size setting is too large, can be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 description query_cache_ Size may be a little bit small, or too much fragmentation.
#查询缓存命中率 = (qcache_hits–qcache_inserts)/qcache_hits * 100%

Read_buffer_size = 1M
#【Thread Caching"MySQL reads into buffer size. A request to sequentially scan a table allocates a read-in buffer, and MySQL allocates a memory buffer for it. The read_buffer_size variable controls the size of this buffer. If the sequential scan requests for a table are frequent, and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer. As with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.
Read_rnd_buffer_size = 16M
#【Thread Caching"MySQL Random read (query operation) buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.
Sort_buffer_size = 2M
#【Thread Caching"Sort_buffer_size is a connection-level parameter that allocates the set of memory once for the first time each connection (session) needs to use this Buffer.
# sort_buffer_size is not the bigger the better, because it is a connection-level parameter, too large settings + high concurrency may drain the system memory resources. For example: 500 connections will consume 500*sort_buffer_size (8M) =4g memory
# when Sort_buffer_size exceeds 2KB, memory allocations are made using mmap () rather than malloc (), resulting in reduced efficiency.
# explain Select*from table where order limit; appears Filesort
Max_length_for_sort_data= 1024
# MySQL sort using sort_buffer_size and max_length_for_sort_data two ways, if the length and value of the query column and the order by column are greater than 1024, the max_length_for_sort_data will be used ，
# can default
Join_buffer_size = 2M
#【Thread Caching"For the size of the association cache between tables, as with sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.
Bulk_insert_buffer_size = 64M
# Multi-value insert or LOAD data is added to a non-empty data table, by adjusting this parameter can effectively improve the efficiency of insertion, the default is 8M

Key_buffer_size = 2048M
# "Global Cache" for the MyISAM engine, the size of the buffer allocated for the index, increase it to get better index processing performance, for the presence of around 4GB server, this parameter can be set to 256MB or 384MB.
Myisam_sort_buffer_size = 128M
# MyISAM the buffer required to reorder when the table is changed
Myisam_max_sort_file_size = 10G
# The size of the maximum temporary file allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE or LOAD DATA INFILE).
# If the file size is larger than this value, the index will be created by the key buffer (slower)
Myisam_max_extra_sort_file_size = 10G
Myisam_repair_threads = 1
# If a table has more than one index, MyISAM can use more than one thread to fix them by using parallel sorting.
# This is a good choice for users with multiple CPUs and a lot of memory.
Myisam_recover
#自动检查和修复没有适当关闭的 MyISAM Table

Innodb_buffer_pool_size= 2048M
# "Global Cache" This is very important for the InnoDB table. InnoDB is more sensitive to buffering than the MyISAM table. MyISAM can be run under the default Key_buffer_size settings, but InnoDB is snail-like under the default Innodb_buffer_pool_size settings. Since InnoDB caches both data and indexes, there is no need to leave too much memory on the operating system, so if you only need to use InnoDB, you can set it up to 70-80% of available memory (note that this is available , not the total amount of memory).

Innodb_additional_mem_pool_size = 16M
# the "Global cache" parameter is used to set the memory pool size of InnoDB stored data directory information and other internal data structures, similar to Oracle's library cache.
#可以默认

Innodb_data_file_path = Ibdata1:1024m:autoextend
#表空间文件 Important data
#可以默认

Innodb_read_io_threads
#读线程数, default is 4

Innodb_write_io_threads
#写线程数, default is 4

Innodb_thread_concurrency =0
#并发线程数量, the default is 0 is unlimited, the value range 0-1000, cannot be modified dynamically
#可以默认

Innodb_flush_log_at_trx_commit= 1
# If this parameter is set to 1, the log will be written to disk after each commit transaction. To provide performance, you can set to 0 or 2, but assume the risk of losing data in the event of a failure.
# The default value of 1 means that every single transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time consuming. Set to 2 for many uses, it means not writing to the hard disk but writing to the system cache. The log will still flush to the hard drive every second, so you will generally not lose more than 1-2 seconds of updates.
# set to 0 will be faster, but the security side is poor, even if the MySQL hangs may also lose the transaction data, set to 2 only when the entire operating system hangs the data can be lost, set to 1 is the most secure settings, performance is the weakest.

Innodb_log_buffer_size = 1M
# Global Cache This configuration determines the cache that is allocated for transactions that have not yet been executed. Its default value (1MB) is generally sufficient, but if your transaction contains binary large objects or large text fields, this cache will quickly fill up and trigger additional I/O operations. Look at the innodb_log_waits state variable, if it is not 0, increase the innodb_log_buffer_size. MySQL developer recommends setting the 1-8m between

Innodb_log_file_size= 500M
#redo日志的大小, the redo log is used to ensure that the write operation is fast and reliable and recovers when it crashes. Up to MySQL the total size of the 5.5,redo log is limited to 4GB (the default can be 2 log files). This is improved in MySQL 5.6. If you set the Innodb_log_file_size to 512M (so there is a 1GB redo log), you will have ample space to write. Generally set to 256~512m

Innodb_log_files_in_group = 3
#为提高性能, MySQL can write log files to multiple files in a circular fashion. Default is 2, recommended set to 3M

innodb_max_dirty_pages_pct = 75
# The number of Dirty_page (dirty pages) in Buffer_pool directly affects the closing time of the InnoDB. The parameter innodb_max_dirty_pages_pct can directly control the ratio of dirty_page in Buffer_pool, and fortunately innodb_max_dirty_pages_pct can be changed dynamically. Therefore, the innodb_max_dirty_pages_pct is reduced before closing the InnoDB, forcing the data block to flush for a period of time, which can greatly shorten the time of MySQL shutdown.
#可以默认

Innodb_lock_wait_timeout = 50
# InnoDB has its built-in deadlock detection mechanism that can cause incomplete transactions to be rolled back. However, if you combine InnoDB with a MYISAM lock tables statement or a third-party transaction engine, the INNODB does not recognize the deadlock. To eliminate this possibility, you can set Innodb_lock_wait_timeout to an integer value that indicates how long (in seconds) the MySQL waits before allowing other transactions to modify data that is eventually rolled back by the transaction.
# When a transaction waits for a lock time, if it exceeds the time it is rolled back
# Set the wait time as needed

innodb_file_per_table= 0
#独立表空间的设置, the default is 0, the shared tablespace, or a stand-alone table space if 1
#建议打开, especially in the larger data volume, the Ibdata1 file will only increase, will not decrease, large to a certain extent will affect the insertupdate speed, in addition, if the deletion of the table frequently, the shared table space will produce more fragments, and can not reclaim space to the OS.

[Mysqldump]
Quick
Max_allowed_packet = 32M

[Mysqld_safe]
Log-error=/data/3306/mysql_oldboy.err
Pid-file=/data/3306/mysqld.pid

Source: http://www.kuqin.com/shuoit/20160128/350365.html

MySQL configuration file