MySQL configuration file mysql. ini Parameters

MySQL configuration file mysql. ini parameter description my. ini (my. cnf in Linux), when the mysql server starts, it will read this file and set relevant runtime environment parameters. Www.2cto.com my. ini is divided into two parts: Client Section and Server Section. The Client Section is used to configure MySQL Client parameters. To view the configuration parameters, run the following command: show variables like '% innodb %'; # view the innodb-related configuration parameters: show status like '% innodb % '; # view innodb related runtime parameters (such as the number of currently opened tables and the number of opened tables) show global status like 'open % tables '; # view global runtime parameters. In addition, global is used to count all database instances running on the current mysql server. If global is not added, only the current database instance is counted. 1. Client Section [client] port = 3306 # set the default port [mysql] default-character-set = utf8 # set the default character set of mysql Client 2. Server section [mysqld] port = 3306 # mysql Server default listener (listen on) basedir = "C:/Program Files/MySQL Server 5.5/" # baseline path: /Program Files/MySQL Server 5.5/Data "# character-set-server = latin1 # the character set used by the Server is an 8-bit latin1 character set default- stor Age-engine = INNODB # default storage engine SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION "# the SQL mode is strict mode max_connections = 100 # the maximum number of concurrent connections supported by the mysql server (number of users ). However, a connection is always reserved for the Administrator to log on with super permissions, even if the maximum number of connections is reached. If the configuration is Too small and there are many users, the "Too connector connections" error will often occur. Query_cache_size = 0 # query cache size, used to cache SELECT query results. If many SELECT queries that return the same query results and rarely change the table, you can set query_cache_size to be greater than 0, which greatly improves the query efficiency. If the table data changes frequently, do not use this parameter. table_cache = 256 # this parameter is called table_open_cache in Versions later than 5.1.3 and is used to set the table cache quantity. Since 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 cached tables. If the table has been opened in the cache, it will directly access the table in the cache to speed up the query. If the table is not cached, it will add the current table to the cache for query. Before caching, table_cache is used to limit the maximum number of cached tables. If the number of cached tables does not reach table_cache, a new table is added. If this value is reached, mySQL releases the cache based on the last query time and query rate of the cache table. Tmp_table_size = 34 M # maximum size allowed for each temporary table in the memory. If the temporary Table size exceeds this value, the temporary Table is automatically converted to a Disk-Based Table ). Thread_cache_size = 8 # maximum number of cached threads. When the client connection is disconnected, if the total number of client connections is less than this value, the thread that processes the client task is put back into the cache. In the case of high concurrency, if this value is set too small, many threads will be created frequently, the thread creation overhead will increase, and the query efficiency will also decrease. In general, if the application side has a good multi-threaded processing, this parameter will not greatly improve the performance. # MyISAM-related parameters myisam_max_sort_file_size = 100G # maximum temporary file size myisam_sort_buffer_size = 68 M key_buffer_size = 54 M # Key Buffer size for mysql re-indexing, used to cache index blocks of the MyISAM table. Determines the database index processing speed (especially index reading) read_buffer_size = 64 K # the buffer size used for full table scan of the MyISAM table. Allocate each thread (on the premise that a full table scan is performed ). During sorting query, MySql first scans the buffer to avoid disk search and increase the query speed. If you need to sort a large amount of data, you can increase the value accordingly. However, MySql will issue this buffer space for each client connection. Therefore, set this value as much as possible to avoid excessive memory overhead. Read_rnd_buffer_size = 256 K sort_buffer_size = 256 K # connection-level parameters (configured for each thread), 500 threads consume 500 * K of sort_buffer_size. # InnoDB related parameters innodb_additional_mem_pool_size = 3 M # InnoDB memory pool size for storing metadata information. Generally, innodb_flush_log_at_trx_commit = 1 # transaction related parameters are not required. If the value is 1, innoDB writes transaction logs to the disk at each commit (High Disk IO consumption), ensuring the complete ACID feature. If the value is set to 0, the transaction log is written to the memory log and the memory log is written to the disk once per second. If it is set to 2, transaction logs are written to the memory log at each commit, but the memory log is written to the disk once per second. Innodb_log_buffer_size = 2 M # InnoDB log data buffer size. If the buffer is full, the log data in the buffer is written to the disk (flush ). Generally, a disk is written at least once per second, so it is not necessary to set a large value, even for long transactions. Innodb_buffer_pool_size = 105 M # InnoDB uses the buffer pool to cache indexes and row data. The larger the value, the less disk I/O. Generally, this value is set to 80% of the physical memory. Innodb_log_file_size = 53 M # size of each InnoDB Transaction log. Generally, it is set to 25% to 100% of innodb_thread_concurrency = 9 # maximum number of concurrent threads in innodb_buffer_pool_size.