MySQL configuration file Parameters

MySQL configuration file Parameters

According to the mysql [d] -- help -- verbose output, we can see the order and path of the configuration files loaded by the mysql client and server programs.

/Etc/mysql/my. cnf -->/etc/my. cnf --> -- default-extra-file option specifies the file --> ~ /. My. cnf

Mysql uses a centralized configuration file. The configuration file is segmented and starts with the [NAME] independent line. The end of the next [NAME] independent line is the configuration that acts on the program specified by NAME.

For example, [client] is the configuration block read by all mysql client programs. [Server] is the configuration block read by all servers, such as mysqld. [Mysqldump] is the configuration file that only mysqldump can read. [mysql] is

And [mysqld] is the configuration file of mysqld, mysqld_safe, and mysqld_multi on the mysql server.
Description of parameters in the initial configuration file and other parameters:

[Client] port = 3306 socket =/tmp/mysql. sock # When the mysql client and server are on the same host, using socket communication is more efficient than TCP/IP communication, the specified soket file path must be consistent with the setting in [mysqld] [mysqld] port = 3306 socket =/tmp/mysql. sockskip-external-locking # disable external-locking. See the description of external-locking: Use system (external) locking (disabled by default ). with this option enabled you can run myisamchk to test not repair) tableswhile the MySQL server is running. disable with -- skip-external-locking.key_buffer_size = 256 M # The size of the buffer used for index blocks for MyISAM tables. increase this to get better index handling (for all reads and multiple writes) to as much asy Ou can affordmax_allowed_packet = 1 M # Max packet length to send to or receive from the servertable_open_cache = 256 # The number of cached open tablessort_buffer_size = 1 M # Each thread that needs to do a sort allocates a buffer of this size Each thread to be sorted will be allocated a bufferread_buffer_size = 1 M # each thread that does a sequential scan allocates a buffer of this size for Each table it scans. if you do not Sequential scans, you may want to increasethis valuethread_cache_size = 8 # How many threads we shoshould keep in a cache for reusequery-cache-type = name # OFF = Don't cache or retrieve results. ON = Cache all results into t select SQL _NO_CACHE... queries. DEMAND = Cache only SELECT SQL _CACHE... queriesquery_cache_size = 16 M # The memory allocated to store results from old queries # query The total memory size of The cache, which must be an integer of 1024 Times, in bytes. When MySQL is started, it is allocated at one time and the memory size specified here is initialized. When the value is changed, MySQL immediately deletes all cached objects and reconfigured their size and initialization. On general-purpose servers with high performance, query cache may be a factor that affects server expansion, because it may become a single point of competition for server resources, on a multi-core server, service processes may even crash. Content that will not be cached: user-Defined Functions, User-Defined variables, temporary tables, mysql database system tables, column-level permissions, storage functions, and uncertain data query_cache_min_res_unit =## The minimum size for blocks allocated the query cache stores the minimum cache memory block; if the value is too small, it will reduce the waste of space, but it will lead to more frequent memory block application operations. If the value is too large, it will have a higher fragmentation rate. You can use (query_cache_size-Qcache_free_memory)/Qcache_queryes_in_cache to obtain a value close to the ideal. At the same time, if Qcache_free_blocks has idle blocks but Qcache_lowmem_prunes is still increasing, it indicates that excessive fragments will cause the cache results to be deleted too early. Query_cache_limit = ## Don't cache results that are bigger than this # maximum value of a single cache object that MySQL allows to cache. However, MySQL only knows whether the query results exceed this size after all the results are returned. However, it tries to store the query results in the cache at the beginning of the query, once timeout is found, the maximum value that can be cached is deleted from the cache and the value of Qcache_not_cached is increased. Therefore, if you know that the results of a query exceed the maximum objects that can be cached, you should use SQL _NO_CACHE in the query statement. Query_cache_wlock_invalidate = # Invalidate queries in query cache on LOCK for write: If a data table is locked by another thread, whether or not results are still returned from the query cache. OFF indicates return. Thread_concurrency = 4 # Permits the application to give the threads system a hint for the desired number of threads that shocould be run at the same time. try number of CPU's * 2 forthread_concurrencydatadir =/mydata/datainnodb_file_per_table = 1 # enable InnoDB table file per table. By default, all databases use one tablespace, this is required for single-Table backup and recovery and is easy to manage, we recommend that you enable log-bin =/mybinlog/mysql-bin # binary log directory and file name prefix log_slow_queries = {YES | NO} # to check whether slow query logs are recorded. A slow query is an event that exceeds the scheduled duration set by the long_query_time parameter. MySQL 5.6 changed this parameter to slow_query_log. The scope is global. It can be used in configuration files and is a dynamic variable. Long_query_time = # Set the difference between the statement execution time of a slow query and a general query. The statement execution time here is the actual execution time, rather than the execution time on the CPU. Therefore, the server with heavy load is more prone to slow queries. The minimum value is 0, and the default value is 10, in seconds. It also supports millisecond-level resolution. It can be used in configuration files at the global or session level and is a dynamic variable. -- Slow-query-log # Log slow queries to a table or log file. defaults logging to a file hostname-slow.log or a table mysql. slow_log if -- log-output = TABLE is used. must be enabledto activate other slow log options -- slow-query-log-file = name # Log slow queries to given log file. defaults logging to hostname-slow.log. must be enabled to activate other slow log options -- long-query-time =## Log all queries that Have taken more than long_query_time seconds to execute to file. the argument will be treated as a decimal value with microsecondprecisioninnodb_flush_log_at_trx_commit: 0: the log buffer is written out to the log file once per second and the flush to disk operation is already med on the log file, but nothing is done at atransaction commit; 1 :( the default) the log buffer is written out to the log file At each transaction commit and the flush to disk operation is performed med on the log file; 2: the log buffer is written out to the file at each commit, but the flush to disk operation is not supported med on it .; for the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit = 1 andsync_binlog = 1 in your master server my. cnf fil E. cautionoperating systems and some disk hardware fool the flush-to-disk operation. they may tell mysqld that the flush has taken place, even though it has not. then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even upt the InnoDB database. using abattery-backed disk cache in the SCSI disk controller or in the disk itsel F speeds up file flushes, and makes the operation safer. you can also try using the Unixcommand hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor. innodb_additional_mem_pool_sizeThe size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in yourapplication, the more memory you need to allocate here. if InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writeswarning messages to the MySQL error log. the default value is 8 Mb. innodb_buffer_pool_sizeThe size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. on mysql5.5, the default value is M. for dedicated D Server B, which is dominated by the InnoDB engine, can usually set 50% of the physical memory. If it is not a dedicated DB server, you can first set it to 1/4 of the memory, if there is a problem, adjust the important configuration parameters and variables: SQL _log_bin in restoring the backup data logical backup), you need to disable the binary log record, so as not to record the recovery process into the log. Innodb_file_per_table = 1 enable InnoDB tables. Each table has one file. By default, all databases use one tablespace. This is required for single-Table backup and recovery and is easy to manage. We recommend that you enable this feature.

Parameters in most configuration files can be provided as an option at server startup. Therefore, you can use mysqld -- help -- verbose to view the solution of configuration parameters. After the server is started, you can view the current server

Variable Parameter Value.

For more information about the parameters in the configuration file, see
Http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
Change Version 5.5 to your version.