MySQL configuration file Mysql.ini parameter details

My.ini (Linux system is my.cnf), when the MySQL server starts it will read this file, set the relevant operating environment parameters.

The My.ini is divided into two blocks: the Client section and the server section.    The client section is used to configure MySQL clients parameters. To view configuration parameters, you can use the following command:

' %innodb% '  '%innodb%'global'open%tables' ; # View the Global runtime parameters, plus global is the statistics for all DB instances running in the current MySQL server. Without global, only the current DB instance is counted.

1. Client section
[Client]
Port = 3306 # Sets the default ports used by the MySQL client when connecting to the server
[MySQL]
Default-character-set=utf8 # setting MySQL client default character set

2. Server section

[Mysqld]port=3306# MySQL service-side default listener (listen on) tcp/IP Port basedir="c:/program files/mysql/mysql Server 5.5/"# datum path, other paths are relative to this path DataDir="c:/program files/mysql/mysql Server 5.5/data"# The MySQL database file is located in the directory character-Set-server=Latin1 # The character set used by the service side defaults to a 8-bit encoded latin1 character Setdefault-storage-engine=INNODB # Default storage engine SQL to be used when creating a new table-mode="strict_trans_tables,no_auto_create_user,no_engine_substitution"# SQL Mode is strict mode max_connections= -# The maximum number of concurrent connections supported by the MySQL server (number of users). However, one of the connections is always reserved for administrators to log on with super privileges, even if the number of connections reaches the maximum limit. If the settings are too small and the user is more, the "Too many connections" error will often occur. Query_cache_size=0# Query cache size, used to cache select query results. If you have many select queries that return the same query results, and you rarely change the table, you can set query_cache_size greater than 0, which can greatly improve query efficiency. And if the table data changes frequently, do not use this, will backfire Table_cache= the# This parameter is in 5.1The version after. 3 is called Table_open_cache, which sets the number of table caches. Because each client connection accesses at least one table, the value of this parameter is related to Max_connections. When a connection accesses a table, MySQL checks the number of tables that are currently cached. If the table is already open in the cache, direct access to the tables in the cache speeds up the query, and if the table is not cached, the current table is added to the cache and queried. Before performing a cache operation, Table_cache is used to limit the maximum number of cache tables: If the currently cached table does not reach Table_cache, the new table will be added, and if this value is reached, MySQL will release the previous cache based on rules such as the last query time of the cache table, query rate, and so on. Tmp_table_size=34M # The maximum size allowed for each temporary table in memory. If the temporary table size exceeds this value, the temporary table is automatically converted to a disk Based table. Thread_cache_size=8# The maximum number of threads to cache. When the client connection is broken, the thread that handles the client task is put back into the cache if the total number of client connections is less than that value. In high concurrency situations, if the value is set too small, many threads are created frequently, the overhead of thread creation becomes larger, and the query efficiency decreases. In general, if there is good multithreading on the application side, this parameter will not improve the performance much.

  =68mkey_buffer_size  =54m # Key buffer size, which is used to cache the index block of the MyISAM table. Determines the speed of database index processing (especially index reads) read_buffer_size  =64k # The buffer size used for full table scans of MyISAM tables. Allocated for each thread (provided a full table scan is performed). 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. Read_rnd_buffer_size  =256ksort_buffer_size  =256k # Connection-level parameters (configured for each thread), 500 threads consume 500*256k sort_buffer_size. 

# InnoDB Correlation parameter Innodb_additional_mem_pool_size==1 # Transaction-related parameters, if the value is 1, The InnoDB writes the transaction log to disk (disk IO consumption is large) at each commit, which guarantees full acid performance. If set to 0, the transaction log writes to the memory log and the memory log writes to the disk at a frequency of 1 times per second. If set to 2, the transaction log is written to memory log at each commit, but the memory log is written to disk 1 times per second. Innodb_log_buffer_size=2M # INNODB log data buffer size, if the buffer is full, the log data in the buffer will be written to disk (flush). Since the disk is usually written at least 1 seconds, there is no need to set it too large, even for a long transaction. Innodb_buffer_pool_size=105m # InnoDB uses a buffer pool to cache index and row data. The larger the value is set, the less disk IO. This value is generally set to the 80% of physical memory. Innodb_log_file_size=53m # The size of each INNODB transaction log. Typically set to Innodb_buffer_pool_size of 25% to 100%innodb_thread_concurrency=9 # InnoDB Maximum number of concurrent threads in the kernel

MySQL configuration file Mysql.ini parameter details