MySQL configuration file-my.ini

Original address: http://www.cnblogs.com/Ray-xujianguo/p/3322455.html

MySQL configuration file-my.ini

Let me introduce the MySQL my.ini configuration file:

What is My.ini?

My.ini is the configuration file used in the MySQL database, and modifying this file can achieve the purpose of updating the configuration.

Where is the My.ini stored?

My.ini stored in the root directory of the MySQL installation:

　　

The specific contents of My.ini are as follows:

1 # CLIENT Section 2 #----------------------------------------------------------------------3 # 4 # The following option s would be is read by MySQL client applications. 5 # Note that only the client applications shipped by MySQL is guaranteed 6 # to the Read this section. If you want your own MySQL client program to 7 # Honor these values, you need to specify it as an option during the 8 # My SQL Client library initialization. 9 #10 [client]11 port=330613 [mysql]15 default-character-set=gb2312

The client's parameters are shown above, [client] and [MySQL] are the clients, and here is the description of the parameters:

The 1.port parameter represents the port of the MySQL database, the default port is 3306, and if you need to change the port number, you can modify it here.

The 2.default-character-set parameter is the default character set for the client and can be set to GBK or UTF8 if you want it to support Chinese.

3. There is also a password parameter, where the value of the password parameter is set so that you can enter it without entering the password when you log in

 1 # SERVER Section 2 #----------------------------------------------------------------------3 # 4 # The following optio NS would be read by the MySQL Server. Make sure which 5 # you have installed the server correctly (see above) so it reads this 6 # file.  7 # 8 [mysqld] 9 # The TCP/IP Port the MySQL Server would listen on11 port=330612 #Path to installation directory. All paths is usually resolved relative to this.15 basedir= "e:/java/mysql/" from #Path to the database root18 datadir= "C: /programdata/mysql/mysql Server 5.5/data/"# The default character set that would be used when a new schema or table I  S21 # created and no character set is Defined22 character-set-server=gb231223 # The default storage engine, that'll be Used when create new tables When25 Default-storage-engine=innodb26 # Set The SQL mode to Strict28 sql-mode= "Strict_tra Ns_tables,no_auto_create_user,no_engine_substitution "# The maximum amount of concurrent sessions the MySQL server wi LL31 # Allow.  One of these connections'll be reserved-a user With32 # SUPER privileges to allow the administrator to login even if THE33 # Connection limit has been reached.34 max_connections=10035 * Query cache is used to cache SELECT results and L Ater return THEM37 # without actual executing the same query once again. Has the query38 # cache enabled may result in significant speed improvements, if YOUR39 # has a lot of identical queri Es and rarely changing tables. See The40 # "Qcache_lowmem_prunes" status variable to check if the current value41 # are high enough for your load.42 # not E:in case your tables change very often or if your queries are43 # textually different every time, the query caches may re  Sult in A44 # slowdown instead of a performance improvement.45 query_cache_size=046 ~ The number of open tables for all Threads. Increasing this value48 # increases the number of the file descriptors that mysqld requires.49 # Therefore you had to make Su Re to set the amount of open FILES50 # allowed to @ least 4096 in the variable ' open-files-limit ' in51 # section [mysqld_safe]52 table_cache=25653 54 # Maximum size for internal (in-memory) temporary tables. If a Table55 # grows larger than this value, it's automatically converted to Disk56 # based table This limitation are for A single table. There can many57 # of them.58 tmp_table_size=35m59 # How many threads we should keep in a cache for reuse. When a client62 # disconnects, the client ' s threads is put in the cache if there aren ' t63 # more than Thread_cache_size T  Hreads from before. This greatly reduces64 # The amount of thread creations needed if you had a lot of new65 # connections. (Normally this doesn ' t give a notable performance66 # improvement if you have a good thread implementation.)  thread_cache_size=868 #*** MyISAM Specific OPTIONS70 # The maximum size of the temporary file MySQL is allowed to Use while72 # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA infile.73# If The file-size would be bigger than this, the index would be created74 # through the key cache (which is slower). myi Sam_max_sort_file_size=100g76 # If The temporary file used for fast index creation would be bigger78 # than using the K  EY cache by the amount specified this, then prefer The79 # Key cache method. This is mainly used to force long character keys In80 # Large tables to use the slower key, cache method to create the Inde x.81 Myisam_sort_buffer_size=69m82 # Size of the Key buffer, used to cache index blocks for MyISAM tables.84 # does not s Et it larger than 30% of your available memory, as some memory85 # is also required by the OS to cache rows. Even if you ' re not using86 # MyISAM tables, you should still set it to 8-64m as it would also be87 # used for internal temp Orary disk tables.88 key_buffer_size=55m89 # Size of the buffer used for doing full table scans of MyISAM tables.91 # A llocated per thread, if a full scan is needed.92 read_buffer_size=64k93 read_rnd_Buffer_size=256k94 # This buffer was allocated when MySQL needs to rebuild the index In96 # REPAIR, Optimze, ALTER table Statements as well as in LOAD DATA INFILE97 # to an empty table. It is allocated per thread so being careful with98 # large settings.99 sort_buffer_size=256k

The above is the server break parameters, a brief description of the parameters:

The 1.port parameter is also the port that represents the database.

The 2.basedir parameter represents the installation path for MySQL.

The 3.datadir parameter indicates where the MySQL data file is stored, and where the database table is stored.

The 4.default-character-set parameter represents the default character set, which is server-side.

The 5.default-storage-engine parameter is the default storage engine.

The 6.sql-mode parameter represents the parameters of the SQL schema, which allows you to set the severity of the test SQL statement.

The 7.max_connections parameter represents the maximum number of connections that allow simultaneous access to the MySQL server, one of which is reserved and left to the administrator.

The 8.query_cache_size parameter represents the size of the cache at query time, and the cache can store information previously queried through the SELECT statement, which can be accessed directly from the cache when queried again.

The 9.table_cache parameter represents the total number of open tables for all processes.

The 10.tmp_table_size parameter represents the total number of temporary tables in memory.

The 11.thread_cache_size parameter indicates that the cache of the client thread is reserved.

The 12.myisam_max_sort_file_size parameter represents the size of the maximum temporary file allowed when MySQL rebuilds the index.

The 13.myisam_sort_buffer_size parameter indicates the size of the cache when rebuilding the index.

The 14.key_buffer_size parameter represents the cache size of the keyword.

The 15.read_buffer_size parameter represents the cache size of the MyISAM table full table scan.

The 16.read_rnd_buffer_size parameter indicates that the sorted data is stored in the cache.

The 17.sort_buffer_size parameter represents the cache size used for sorting

 1 #*** INNODB Specific Options * * * 2 3 4 # Use the This option if you had a MySQL server with INNODB support enabled 5 # b UT you don't plan to use it. This would save memory and disk space 6 # and speed up some things.  7 #skip-innodb 8 9 # Additional memory pool that's used by InnoDB to store metadata10 # information.  If InnoDB requires more memory for this purpose it WILL11 # Start-to-allocate it from the OS. As this is fast enough on most12 # Recent operating systems, you normally does not need to change This13 # value. SHOW INNODB STATUS would display the current amount used.14 INNODB_ADDITIONAL_MEM_POOL_SIZE=3M15-If set to 1, INNODB W Ill flush (Fsync) The transaction logs to The17 # disk at each commit, which offers full ACID behavior. If you are18 # willing to compromise this safety, and you is running Small19 # transactions, you could set this to 0 or 2 t o Reduce disk I/O to The20 # logs. Value 0 means that the log was only written to the log file And21 # The log file flushed To disk approximately once per second. Value 222 # means the log is written to the log file at each commit, but the Log23 # file is only flushed to disk Approxim Ately once per second.24 innodb_flush_log_at_trx_commit=125 # The size of the buffer InnoDB uses for buffering log data . As soon As27 # It's full, InnoDB'll has to flush it to disk. As it is FLUSHED28 # once per second anyway, it does does sense to has it very large29 # (even with long transactions ). INNODB_LOG_BUFFER_SIZE=2M31-InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes And33 # row data. The bigger you set this and the less disk I/O is needed to34 # Access data in tables. On a dedicated database server, set THIS35 # parameter up to 80% of the machine physical memory size. Do not set It36 # too large, though, because competition of the physical memory may37 # cause paging in the operating Syst  Em. Note that on 32bit Systems You38 # might is limited to 2-3.5g of the user level memory per PROcess, so do not39 # set it too high.40 innodb_buffer_pool_size=107m41 the size of each log file in a log group. You should set the combined Size43 # of logs files to about 25%-100% of your buffer pool size to Avoid44 # unneeded buffer Pool flush activity on log file overwrite. HOWEVER,45 # Note that a larger logfile size would increase the time needed for The46 # recovery process.47 innodb_log_file _SIZE=54M48 # of threads allowed inside the InnoDB kernel. The optimal Value50 # depends highly on the application, hardware as well as the OS51 # Scheduler properties. A too high value may leads to thread thrashing.52 innodb_thread_concurrency=18

The above is the parameters used by the InnoDB storage engine, which is an introduction to the parameters:

The 1.innodb_additional_mem_pool_size parameter represents an additional pool of memory used to store the contents of the InnoDB table.

The 2.innodb_flush_log_at_trx_commit parameter is the time to set the commit log, and if set to 1,innodb writes the transaction log to disk after each commit.

The 3.innodb_log_buffer_size parameter represents the size of the buffer used to store the log data.

The 4.innodb_buffer_pool_size parameter represents the size of the cache, and InnoDB uses a buffer pool class to hold the index and raw data.

The 5.innodb_log_file_size parameter represents the size of the log file.

The 6.innodb_thread_concurrency parameter represents the maximum number of threads allowed in the InnoDB storage engine.

Note: The MySQL service must be restarted after each parameter modification to be valid.

MySQL configuration file-my.ini