MySQL configuration file parameters in a detailed

[Client]

Port = 3307

Socket =/usr/local/mysql5_6/mysql.sock

Default-character-set=utf8

[MySQL]

prompt = MySQL (\\[email protected]\h-\r:\\m:\\s [\\d]) >

#提示当前数据库操作用户名, library name, time, etc.

Default-character-set=utf8

[Mysqld]

Basedir =/usr/local/mysql5_6

DataDir =/usr/local/mysql5_6/data

Port = 3307

SERVER_ID = 111

Socket =/usr/local/mysql5_6/mysql.sock

Log-error =/usr/local/mysql5_6/error.log

Pid-file =/usr/local/mysql5_6/mysql.pid

Character-set-server=utf8

Max_connections = 3000

#MySQL允许最大的进程连接数, this value needs to be increased if the too many connections error is frequently present.

Skip-external-locking

Skip-name-resolve

Max_allowed_packet = 32M

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

Wait_timeout = 28800

#结束回话等待时间

Thread_cache_size = 64

#服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量, if there is room in the cache when the connection is disconnected, the client's thread will be placed in the cache, and if the thread is requested again, the request will be read from the cache if it is empty or a new request in the cache. Then this thread will be recreated, and if there are many new threads, adding this value can improve system performance. You can see the effect of this variable by comparing the variables of the connections and threads_created states. 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.

Explicit_defaults_for_timestamp=true

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%

Tmp_table_size = 128M

# The default size of Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, and if you do many advanced GROUP by queries, increase the tmp_table_size value. If this value is exceeded, the temporary table is written to disk.

Thread_concurrency = 32

#设置thread_concurrency的值的正确与否, the performance of MySQL is very significant, in the case of multiple CPUs (or multicore), the error setting the value of Thread_concurrency, will lead to MySQL can not take full advantage of multi-CPU (or multicore), Occurs at the same moment only one CPU (or core) is in the working condition. Thread_concurrency should be set to twice times the number of CPU cores such as a dual-core CPU, then thread_concurrency should be 4, 2 dual-core CPU, thread_concurrency value should be 8

Open_files_limit = 10240

#每个客户端的连接也是一个文件句柄.

Back_log = 2048

#在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.

Innodb_flush_log_at_trx_commit = 1

#每次commit the data in the log cache is brushed to disk

Innodb_lock_wait_timeout = 50

#InnoDB事务在被回滚之前可以等待一个锁定的超时秒数. InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB uses the Locktables statement to notice the lock setting. The default value is 50 seconds

#innodb_auto_extend_increment = 64M

#可帮助降低碎片

Innodb_buffer_pool_size = 8192M

#这对Innodb表来说非常重要. 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 usable memory. Some rules apply to Key_buffer-if your data volume is small and does not increase, then you do not need to set the innodb_buffer_pool_size too large

Innodb_log_buffer_size = 16M

#此参数确定些日志文件所用的内存大小, in M. Larger buffers can improve performance, but unexpected failures will lose data

Innodb_buffer_pool_instances = 8

#用于优化更高并发的负载

Innodb_log_file_size = 60M

#此参数确定数据日志文件的大小, in M, larger settings can improve performance, but also increase the time it takes to recover a failed database

Innodb_log_files_in_group = 3M

#为提高性能, MySQL can write log files to multiple files in a circular fashion. Recommended set to 3M

Innodb_io_capacity = 400

#InnoDB曾经在代码里写死了假设服务器运行在每秒100个I/O operation on a single hard drive. The default value is bad. You can now tell the INNODB server how much I/O is capable. It is sometimes necessary to set this value quite high (it needs to be set to tens of thousands on an extremely fast storage device such as an SSD) in order to refresh the dirty pages stably.

Innodb_write_io_threads = 4

#文件IO的线程数, typically 4, but under Windows, you can set it larger.

Innodb_read_io_threads = 4

#文件IO的线程数, typically 4, but under Windows, you can set it larger.

Innodb_thread_concurrency = 8

#服务器有几个CPU就设置为几, the default setting is recommended, typically 8

Innodb_file_per_table = On

#其默认值在mysql5.6.6+ is on after. When this option is turned on, data and indexes about the InnoDB table are stored separately in their own tablespace (the. IBD end of the file). Otherwise, it is stored in the system's Table space (Ibdata).

Innodb_print_all_deadlocks = 1

#死锁信息打印到错误日志里

#------Quickly Preheat the buffer_pool buffer pool-------#

#在之前的版本里, if a high-load machine restarts, a large amount of hot data in memory is emptied and then re-loaded from disk into the Buffer_pool buffer pool, so that when the peak period, the performance will become very poor, the number of connections will be high. When MySQL is turned off, the in-memory thermal data is stored in the Ib_buffer_pool file on the disk, and in the data directory, the hot data is automatically loaded into the Buffer_pool buffer pool after startup.

Innodb_buffer_pool_dump_at_shutdown = 1

#在关闭时把热数据dump到本地磁盘.

Innodb_buffer_pool_dump_now = 1

#采用手工方式把热数据dump到本地磁盘.

Innodb_buffer_pool_load_at_startup = 1

#在启动时把热数据加载到内存.

Innodb_buffer_pool_load_now = 1

#采用手工方式把热数据加载到内存.

Table_open_cache = 4096

#从官方文档看出在MySQL5.6.8+ start with a default value of 2000, you can simply determine the original default value is not enough. The variable can be examined by observing the Opened_tables its value and its period of time. If you see that the value of opened_tables is large and you do not perform flush TABLES frequently (executing its commands to force all tables to close again and open), you might want to increase the value of the variable.

max_connect_errors = 1000

#设置每个主机的连接请求异常中断的最大次数, 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 flushhosts command.

Expire_logs_days = 7

#如果启用了二进制日志, this option should be turned on to allow the server to clean up the old binary log after a specified number of days. If not enabled, the final server space is exhausted, causing the server to get stuck or crash.

Sort_buffer_size = 1M

#Sort_Buffer_Size是一个connection级参数, when each connection (session) needs to use this Buffer for the first time, allocate the set of memory at once. 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, sort_buffer_size more than 2KB, will use Mmap () instead of malloc () for memory allocation, resulting in reduced efficiency.

Join_buffer_size = 8M

#用于表间关联缓存的大小, as with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.

Read_buffer_size = 1M

#MySql读入缓冲区大小. 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

#MySql的随机读 (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.

Myisam_sort_buffer_size = 256M

# MyISAM the buffer required to reorder when the table is changed

Bulk_insert_buffer_size = 64M

#批量插入数据缓存大小, can effectively improve the efficiency of insertion, the default is 8M

Key_buffer_size = 1024M

#批定用于索引的缓冲区大小, increase it to get better index processing performance, the parameter can be set to 256MB or 384MB for a server that has around 4GB.

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 is created by the key buffer (slower)

#-------------------mysql5.6 's master and slave added a lot of parameters, improve the security and efficiency of master-slave synchronization, the following master and slave parameters--------------------#

Server-id=1

Binlog-format=row

Log-bin=master-bin.log

Log-bin-index=master-bin.index

Log-slave-updates=true

#gtid-mode=on

#enforce-gtid-consistency=true

#这两个参数是启用mysql5.6 in the UUID synchronization mode, two parameters must be opened together, or error, slave in the synchronous replication, no need to find Binlog logs and Pos point, Direct change master to Master_auto_ Position=1 can be, automatically find point synchronization.

#GTID的局限性: (In view of these limitations, use caution, so do close processing)

#1. CREATE TABLE ... The SELECT statement is not supported. Because the statement is split into createtable and insert two transactions, and the two transactions are assigned the same Gtid, this causes the insert to be ignored by the repository.

#2. Create temporary table, DROP temporary table operations are not supported.

Master-info-repository=table

Relay-log-info-repository=table

Sync-master-info=1

slave-parallel-workers=2

#默认是0, does not turn on, the maximum number of concurrent threads is 1024. Master-slave replication enables 4 SQL threads, increases throughput from the server, reduces latency, and uses concurrent SQL threads to apply events to different databases in parallel

Binlog-checksum=crc32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log-events=1

#这四个参数是启用binlog/relaylog Check to prevent log errors

Sync_binlog=1

#默认情况下, the Binlog is not synchronized with the hard disk each time it is written. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the Binlog is lost. To prevent this, you can use the Sync_binlog global variable (1 is the safest value, but also the slowest), so that Binlog synchronizes with the hard disk after every n binlog write. Even if Sync_binlog is set to 1, there is a possibility of inconsistency between the table content and the Binlog content when a crash occurs. If you use the InnoDB table, the MySQL server processes the commit statement, which writes the entire transaction to Binlog and commits the transaction to InnoDB. If a crash occurs between two operations, the transaction is InnoDB rolled back, but still exists in Binlog. You can use the--innodb-safe-binlog option to increase the consistency between InnoDB table content and Binlog. (Note:--innodb-safe-binlog is not required in MySQL5.1; This option is obsolete because of the introduction of XA transaction support), which provides greater security for Binlog (Sync_binlog=1) and (by default, True) for each transaction InnoDB logs are synchronized with the hard disk, the effect of this option is that after the crash restarts, after rolling back the transaction, the MySQL server cuts the rollback from the Binlog innodb transaction. This ensures that the Binlog feedback innodb the exact data of the table, etc., and keeps the slave server in sync with the primary server (without taking the rollback statement).

Relay_log_purge = 1

Relay_log_recovery = 1

#这两个是启用relaylog的自动修复功能, avoid log corruption due to external factors such as network, master and slave stop.

Slave-skip-errors = 1062

Binlog_cache_size = 4M

Max_binlog_cache_size = 2G

#指定binary the maximum capacity of the log cache, and if the settings are too small, MySQL will error when executing complex query statements.

Max_binlog_size = 1G

Maximum capacity of #指定binary log file, default is 1GB

[Mysqldump]

Quick

Max_allowed_packet = 2048M

Sql_mode=no_engine_substitution,strict_trans_tables

This article is from the "My Ops Time" blog, so be sure to keep this source http://aaronsa.blog.51cto.com/5157083/1740634

MySQL configuration file parameters in a detailed