MYSQL 5.6.34 configuration file detailed

[Client]

Port = 3306 #客户端端口号

socket =/data/3306/mysql.sock #socket file is unique to the Linux/unix system, the user in this environment client connection can not be through the TCP/IP network, and directly using the socket file connection.

[MySQL]

No-auto-rehash #仅仅允许使用键值的update和deletes

[Mysqld] #mysqld组包括了mysqld服务启动的参数, it involves a lot of aspects, including MySQL directory and files, communication, network, information security, memory management, optimization, query buffers, MySQL log settings and so on.

user = MySQL #MySQL运行用户 (mysql user recommended)

Port = 3306 #MySQL服务运行时的端口号 (we recommend changing the default port, which is susceptible to attack by default)

Socket =/data/3306/mysql.sock #socket文件是在Linux/unix Environment is unique, the user in Linux/unix environment, client connection can not be directly used by TCP/IP network

Basedir =/application/mysql #mysql程序所存放的路径, typically used to store MySQL boot files, configuration files, logs, etc.

DataDir =/data/3306/data #mysql数据存放文件 (extremely important)

open_files_limit= #mysql能打开文件的最大个数, (if too mant open files are present, it means that the value needs to be adjusted)

Back_log = #back_log参数是指在mysql暂时停止响应新的请求之前, how many requests in a short period of time can be stored in the stack, if the system has a lot of connections in a short period of time, you need to adjust the value of this parameter, The value of this parameter specifies the size of the listening queue for incoming TCP/IP connections, and the size of the different operating systems on this queue has its own limits, and if attempting to set back_log higher than the operating system limit will be invalid, its default value is 50, for Linux systems, It is recommended to set to an integer less than 512.

Max_connections = #指定mysql允许的最大连接进程数如果在访问博客时经常出现 Too many connections error, you need to increase the value of this parameter.

Max_connect_errors = #设置每个主机的连接请求异常中断的最大次数, when this number is exceeded, the MySQL server disables the host connection request until the MySQL server restarts or the flush The hosts command to clear information about the host.

Table_open_cache = 614 #用来指定表高速缓存的大小, when MySQL accesses a table, if there is space in the MySQL cache, then the table is opened and put into the table buffer, the advantage is that it can be more quickly access to the contents of the table, In general, you can view the status values Open_tables and Open_tables of the peak time of a database, to determine if you need to increase the value of Table_open_cache, that is, if Open_tables is close to Table_open_cache , and opened_tables this value is gradually increasing, it is necessary to consider increasing the size of this value.

external-locking = FALSE #mysql选项可以避免外部锁定. True to open.

Max_allowed_packet =8m #服务器一次能处理最大的查询包的值 is also the largest query that a server program can handle.

Sort_buffer_size = 1M #设置查询排序时所能使用的缓存区的大小, the system defaults to 2M.

Note: This parameter corresponds to the allocation of memory is exclusive to each connection, if there are 100 connections, then the actual allocation of the total sort buffer size is 100x6=600m, so for the presence of around 4GB servers, it is recommended to set it to 6MB~8MB.

Join_buffer_size = 1M # The buffer size that can be used by the Federated query operation, like the sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.

Thread_cache_size = #设置Thread The maximum number of connection threads that can be cached in the cache pool, which can be set to 0~16384 and default to 0, which means that the number of threads saved in the cache can be re-used. When disconnected, if there is room in the cache, 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 this thread will be recreated, if there are many threads, increase this value can improve system performance, By comparing the number of connection and thread_created states, you can see the effect of this variable, we can set the rules according to the physical memory as follows: 1GB memory We configured 8,2GB memory we configured for 16,3GB memory we configured to 32, 4GB or 4GB above we give this value 64 or greater.

Thread_concurrency = 2 #该参数取值为服务器逻辑CPU数量x2.

Query_cache_size = 2M #指定mysql查询缓冲区的大小, can be observed in the MySQL console, if the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering, If the value of qcache_hits is very large, it indicates that the query buffer is used very frequently, and if the value is smaller instead of affecting efficiency, then you can consider not querying the buffer, for acache_free_blocks, if the value is very large, it indicates a lot of buffer fragmentation.

Query_cache_limit = 1M #只有小于此设置值的结果才会被缓存.

Query_cache_min_res_unit = 2k #设置查询缓存分配内存的最小单位, to set this parameter appropriately, you can reduce the number of requests and allocations of memory quickly, but setting too large may cause memory fragmentation value to rise, the default value is 4K, It is recommended to set W to 1k~16k.

Default_table_type = InnoDB #默认表的类型为InnoDB.

Thread_stack = 192K #设置mysql每个线程的堆栈大小, the default value is large enough to meet normal operation, can be set to a range of 128K~4GB, the default is 192K.

Transaction_isolation = read-committed #数据库隔离级别 (READ UNCOMMITTED (reading uncommitted content) Read COMMITTED (read submissions) repeatable READ (can be reread) SERIALIZABLE ( serializable))

Tmp_table_size = 2M #设置内存临时表最大值, if this value is exceeded, the temporary table is written to disk with a range of 1KB~4GB.

Max_heap_table_size = 2M #独立的内存表所允许的最大容量

Long_query_time = 1 #慢查询的执行用时上限, default is 10s, recommended 1s~2s.

Log_long_format #没有使用索引的查询也会被记录 (recommended, adjusted according to business)

Log-error =/data/3306/error.log #默认错误日志存放路径及名称 (Error log if error is seen)

#log-slow-queries =/data/3306/slow.log #慢查询日志文件路径 (it is recommended to open this log if slow query is turned on)

Pid-file =/data/3306/mysql.pid #mysql_pid文件记录的是当期mysqld进程的pid, PID is ProcessID

Log-bin =/data/3306/mysql-bin #logbin是数据库的操作日志, for example: Update, delete, create, etc. are stored in the Binlog log and can be incrementally restored through Logbin.

Relay-log =/data/3306/relay-bin #relay-log Log is logged from the server I/O thread that reads the primary server's binary log to the local file from the server. The SQL thread then reads the contents of the Relay-log log and applies it to the slave server.

Relay-log-info-file =/data/3306/relay-log.info #从服务器用于记录中继日志相关信息的文件, the default name is relay-log.info in the data directory.

Binlog_cache_size = 1M #在一个事务中binlog为了记录SQL状态所持有的Cache大小, if large, multi-declarative transactions are frequently used, you can increase this value to obtain greater performance, All the state of affairs is buffered in the Binlog buffer, and then commits a one-time write to Binlog, and if this transaction is larger than this value, the temporary file on the disk is used instead, which is created when each linked transaction first updates the state.

Max_binlog_cache_size = 1M #二进制日志文件的最大长度 (default 1GB), the MySQL server automatically provides a new binary file connection before a binary file information exceeds this maximum length.

Expire_logs_days = 7 #超过7天的binlog. mysql program is automatically deleted (if the data is important, it is recommended not to turn on this option)

Key_buffer_size = 16M #指定用于索引的缓冲区大小, increase it to get better index processing performance, for a server with around 4GB, this parameter can be set to 256MB or 384MB.

Note: If the parameter value is too large, the overall efficiency of the server is reduced.

Read_buffer_size = 1M #读查询操作所能使用的缓冲区大小, as with Sort_buffer_size, the allocated memory for this parameter is also exclusive to each connection.

Read_rnd_buffer_size = 1M #设置进行随机读的时候所使用的缓冲区, this parameter is opposite to the buffer set by Read_buffer_size, one is used when sequential reads, one is random read, However, both are set for the thread and each thread can produce either of the two buffer types, the default value is 256KB and the maximum value is 4GB.

Bulk_insert_buffer_size = 1M #如果经常性的需要使用批量插入放入特殊语句来插入数据, the parameters can be adjusted to 16MB~32MB, recommended 8MB.

Lower_case_table_names = 1 #实现mysql不区分大小 (development requirements, recommended)

Skip-name-resolve #禁止mysql对外部连接进行DNS解析, this option can be used to eliminate the DNS resolution of MySQL. However, it is important to note that if this option is turned on, all remote host connection authorizations will have to use the IP address method, otherwise MySQL would not be able to handle the connection request properly.

Slave-skip-errors = 1032,1062 #从库可以跳过的错误数字值

Replicate-ignore-db=mysql #在做主从的情况下, set up libraries that do not need to be synchronized.

Server-id = 6 #表示本机的序列号为1, Server-id must not be the same if the master is from or multiple instances

Innodb_additional_mem_pool_size = 4M #用来设置InnoDB存储的数据目录信息和其他内部数据结构的内存池大小, the more tables in the application, the more memory that needs to be allocated here, For a relatively stable application, the size of this parameter is relatively stable, there is no need to reserve a very large value, if the InnoDB out of memory in the pool, InnoDB began to allocate memory from the operating system, and to the MySQL error log to write a warning message, the default is 1MB, You should increase the size of this parameter appropriately when you find that there are already warning messages in the error log.

Innodb_buffer_pool_size = 32M #InnoDB使用一个缓冲池来保存索引和原始数据, the larger the setting, the less disk I/O required to access the data in the table, is generally set to about 25 of the physical memory, Note: Do not configure the InnoDB buffer pool value as the 50%~80% of physical memory, depending on your production environment.

Innodb_data_file_path = Ibdata1:128m:autoextend #设置配置一个可扩展大小的尺寸为128MB的单独文件, named Ibdata1, does not give the location of the file, All defaults are within the MySQL data directory.

Innodb_file_io_threads = 4 #InnoDB中的文件I/O thread, typically set to 4, if Windows can set a larger value to increase disk I/O.

Innodb_thread_concurrency = 8 #服务器有几个CPU就设置为几, it is recommended to use the default setting, which is generally set to 8.

Innodb_flush_log_at_trx_commit = 2 #设置为0就等于Innodb_log_buffer_size队列满后再统一存储, which defaults to 1 and is the safest setting.

Innodb_log_buffer_size = 2M #默认为1MB, usually set to 8~16MB is sufficient.

Innodb_log_file_size = 4M #确定日志文件的大小, larger settings can improve performance, but also increase the time to restore the database.

Innodb_log_files_in_group = 3 #为提高性能, MySQL can write log files to multiple files in a circular manner. The recommended setting is 3.

innodb_max_dirty_pages_pct = #InnoDB主线程刷新缓存池中的数据.

Innodb_lock_wait_timeout = #InnoDB事务被回滚之前可以等待一个锁定的超时秒数, InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB uses the Lock tables statement to notice the locking setting, which defaults to 50 seconds.

innodb_file_per_table = 0 #InnoDB为独立表空间模式, each table in each database generates a data space. 0 is off, 1 is turned on.

Standalone Table Benefits:

1, each table has its own independent table space.

2. The data and indexes of each table will exist in its own tablespace.

3, you can implement a single table in a different database to move.

4, the space can be recycled (except the drop table operation, the table empty can not be recycled)

[Mysqldump]

Quick

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

[Mysqld_safe]

Log-error=/data/3306/oldboy_3306.err #mysql错误日志存放路径及名称

Pid-file=/data/3306/mysqld.pid #mysql_pid文件记录的是当期的mysqld进程的Pid, PID is ProcessID.

This article is from the "12206254" blog, please be sure to keep this source http://12216254.blog.51cto.com/12206254/1908401

MYSQL 5.6.34 configuration file detailed