mysql配置参数详解

By | 2012/12/19

今天看到一篇mysql5.5配置文件的文章,博主自己写了超详细的mysql5.5版本的配置参数,包括默认值、作用,超级详细啊,赶紧收藏!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
#*******以下开始为配置文件模板
 
[mysqld]
#**********************Server**************************
#******server start related
#user=								#Run the mysqld server as the user having the name user_name or the numeric user ID user_id. 
#bind-address=							#Default 0.0.0.0;server listens on a single network socket which bound to a single address.
#port=								#The port number to listen for TCP/IP connections,must be 1024 or higher unless run as root. 
#port-open-timeout=						#Default 0;indicates how many seconds the server should wait for the TCP/IP port to become free.
#server-id=							#used in replication to enable master and slave servers to identify themselves uniquely.
#chroot								#Put the mysqld server in a closed environment during startup by using the chroot() system call.
#init-file=							#a file containing SQL statements that you want the server to execute when it starts.
#core-file=							#Default OFF;Write a core file if mysqld dies.
#skip-grant-tables						#This option causes the server to start without using the privilege system at all
 
#******location
#basedir=							#Default /;mysql installation directory
#pid-file=							#Default creates in data directory;The path name of the process ID file.
#socket=							#Default /tmp/mysql.sock;specifies the Unix socket file to use when listening for local connections.
#datadir=							#Default /var/lib/mysql;The path to the data directory
#tmpdir=							#Default /tmp;The path of the directory to use for creating temporary files.
 
#******security
#secure-auth							#prevent old format password client connect
#safe-user-create						#This ensures that the user cannot change any privilege columns directly, but has to use the GRANT statement to give privileges to other users.
#skip-show-database						#With this,SHOW DATABASES is permitted only to users have SHOW DATABASES privilege
#max_user_connections						#Default 0;Range 0 .. 4294967295;The maximum number of simultaneous connections permitted to any given MySQL user account.
#max_connect_errors=						#If more than this many connection requests interrupted,the server blocks that host.
#secure-file-priv=						#limits LOAD_FILE() and LOAD DATA and SELECT ... INTO OUTFILE statements to specified directory
#max_prepared_stmt_count=					#Default 16382;Range 0 .. 1048576;limits the total number of prepared statements in the server.
#skip-ssl							#indicate that SSL should not be used
#ssl-ca=							#The path to a file in PEM format
#ssl-capath=							#The path to a directory that contains trusted SSL certificate authority certificates in PEM format
#ssl-cert=							#The name of the SSL certificate file 
#ssl-cipher=							#A list of permissible ciphers to use for SSL encryption
#ssl-key=							#The name of the SSL key file
 
#******features
#default-storage-engine=					#Default InnoDB since 5.5.5.The default storage engine.
#ansi								#Use standard (ANSI) SQL syntax instead of MySQL syntax;is the same as --transaction-isolation=SERIALIZABLE --sql-mode=ANSI
#sql-mode=							#Default '';Valid Values ALLOW_INVALID_DATES,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE,IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_UNSIGNED_SUBTRACTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,PAD_CHAR_TO_FULL_LENGTH,PIPES_AS_CONCAT,REAL_AS_FLOAT,STRICT_ALL_TABLES,STRICT_TRANS_TABLES
#auto_increment_increment=					#Default 1;Range 1 .. 65535;control the operation of AUTO_INCREMENT columns.
#auto_increment_offset=						#Default 1;Range 1 .. 65535;control the operation of AUTO_INCREMENT columns.
#div_precision_increment=					#Default 4;Range 0-30;This variable indicates the number of digits by which to increase the scale of the result of division operations performed with the / operator.
#event-scheduler=						#Default OFF;Valid Values ON OFF DISABLED;This variable indicates the status of the Event Scheduler; 
#skip-event-scheduler						#Turns the Event Scheduler OFF. 
#flush								#Default off;after each SQL statement,server flushes all changes.when off,to filesystem,when on to disk. 
#flush_time=							#Default 1800;Min Value 0.If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk.
#old								#when old is enabled, it changes the default scope of index hints to that used prior to MySQL 5.1.17.
#old-alter-table						#Default OFF;If given,server does not use optimized method of processing an ALTER TABLE.
#old-style-user-limits						#Default FALSE ;Enable old-style user limits.Before MySQL 5.0.3, account resource limits were counted separately for each host.
#partition							#Default ON ;Enables or disables user-defined partitioning support in the MySQL Server.
#skip-partition							#Disables user-defined partitioning.www.mysqlops.com
#plugin_dir=							#Default BASEDIR/lib/plugin ;The path name of the plugin directory. 
#plugin-load=							#plugin-load=myplug1=myplug1.so;myplug2=myplug2.so;This option tells the server to load the named plugins at startup. 
#symbolic-links							#you can link a MyISAM index file or data file to another directory with the INDEX DIRECTORY or DATA DIRECTORY options 
#skip-symbolic-links 						#you can't link a MyISAM index file or data file to another directory with the INDEX DIRECTORY or DATA DIRECTORY options 
#lock_wait_timeout=						#Default 31536000;Range 1 .. 31536000;This variable specifies the timeout in seconds for attempts to acquire metadata locks.
#sync_frm							#Default TRUE;when any nontemporary table is created its .frm file is synchronized to disk
#temp-pool							#Default TRUE;causes temporary files use a small set of names
#updatable_views_with_limit					#Default 1;controls whether updates to a view can be made when view does not contain all columns of the primary key and update statement contains a LIMIT clause.
 
#******function
#allow-suspicious-udfs						#Default FALSE; whether udf that have only an xxx symbol for the main function can be loaded
#des-key-file=							#These keys are used by the DES_ENCRYPT() and DES_DECRYPT() functions. 
#group_concat_max_len=						#Default 1024;Range 4 .. 18446744073709547520;The maximum permitted result length in bytes for the GROUP_CONCAT() function.
#max_long_data_size=						#Default 1048576;Range 1024 .. 4294967295;The maximum size of parameter values that can be sent with the mysql_stmt_send_long_data() C API function. 
#sysdate-is-now							#by default SYSDATE() returns the time it executes,not the time the statement begins executing.This differs from the behavior of NOW()
#default_week_format=						#Default 0;Range 0 .. 7;The default mode value to use for the WEEK() function.
 
#******character set&time zone etc..
#character-set-server=						#The server's default character set. default-character-set is deprecated,use this.
#collation-server=						#The server's default collation.collation is deprecated,use this.
#character-set-client-handshake					#Default TRUE;Do not ignore character set information sent by the client.
#skip-character-set-client-handshake				#this makes MySQL behave like MySQL 4.0. 
#character-set-filesystem=					#Default binary;The file system character set.set the same as system's character set.
#character-sets-dir=						#The directory where character sets are installed.
#lower_case_file_system						#On Linx OFF;On Windows ON;describes the case sensitivity of file names
#lower_case_table_names=					#Default 0;Range 0 .. 2;0 table name case sensitive,1 table name lowercase on disk and comparisons are not case sensitive,2 table name as given but compared in lowercase
#lc-messages=							#The locale to use for error messages. 
#lc-messages-dir=						#The directory where error messages are located.
#default-time-zone=						#default system time zone;Set the default server time zone.
 
#******buffer&cache
#memlock							#Default FALSE;Lock the mysqld process in memory.require root,or changing the limits.conf file
#large-pages							#Default FALSE;On Linux,due to reduced TLB misses,applications may obtain performance improvements by using large pages
#join_buffer_size=						#Default The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
#sort_buffer_size=						#Default 2097144;Max Value 18446744073709547520;Each session that needs to do a sort allocates a buffer of this size.
#table_open_cache=						#Default 400;Range 400-524288;The number of open tables for all threads,it requires file descriptors. 
#table_definition_cache=					#Default 400,Range 400-524288;The number of table definitions can be cache,it does not use file descriptors.
#range_alloc_block_size=					#Default 4096;Range 4096-4294967295;The size of blocks that are allocated when doing range optimization. 
#query_prealloc_size=						#Default 8192;Range 8192 .. 18446744073709547520;Block Size 1024;The size of the persistent buffer used for statement parsing and execution.
#query_alloc_block_size=					#Default 8192;Range 1024 .. 18446744073709547520;Block Size 1024;The allocation size of memory blocks that are allocated for objects created during statement parsing and execution.
#stored_program_cache=						#Default 256;Range 256 .. 524288;Sets a soft upper limit for the number of cached stored routines per connection.
 
#*****query cache
#query_cache_type=						#Default 1;Valid Values 0 1 2 OFF ON DEMAND;Set the query cache type.
#query_cache_size=						#Default 0;Range 0 .. 18446744073709547520;The amount of memory allocated for caching query results.
#query_cache_min_res_unit=					#Default 4096;Range 512 .. 18446744073709547520;The minimum size (in bytes) for blocks allocated by the query cache.
#query_cache_limit=						#Default 1048576;Range 0 .. 18446744073709547520;Do not cache results that are larger than this number of bytes. 
#query_cache_wlock_invalidate					#Default FALSE;Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to the table.
 
#******thread&connection
#thread_handling=						#default one-thread-per-connection;The thread-handling model used by the server for connection threads.no-threads is useful for debugging.
#slow_launch_time=						#Default 2;If creating a thread takes longer than this many seconds, the server increments the Slow_launch_threads status variable. 
#init_connect=							#A string to be executed by the server for each client that connects.
#back_log=							#Default 50;Range 1-65535;The number of outstanding connection requests MySQL can have
#thread_cache_size=						#Default 0;Range 0-16384;How many threads the server should cache for reuse.
#max_connections=						#Default 151;Range 1-100000;The maximum permitted number of simultaneous client connections.
 
#******temptable
#big-tables							#Enable large result sets by saving all temporary sets in files
#tmp_table_size=						#Default system dependent;Range 1024-4294967295;The maximum size of internal in-memory temporary tables.max_tmp_tables is unused.
#max_heap_table_size=						#Default 16777216;Range 16384-1844674407370954752; the maximum size user-created MEMORY tables are permitted to grow.
 
#******network
#skip-networking						#Do not listen for TCP/IP connections at all. 
#skip-name-resolve						#Do not resolve host names when checking client connections.
#skip-host-cache						#Disable internal host cache,server performs a DNS lookup every time a client connects. 
#net_buffer_length=						#Default 16384;Range 1024-1048576;connection buffer and result buffer begin with a size given by net_buffer_length,dynamically enlarged up to max_allowed_packet bytes as needed.The result buffer shrinks to net_buffer_length after each SQL statement. 
#max_allowed_packet=						#Default 1048576;Range 1024-1073741824;The maximum size of one packet or any generated/intermediate string.
#connect_timeout=						#Default 10;The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.
#wait_timeout=							#Default 28800;Range 1 .. 2147483; waits for activity on a noninteractive connection before closing it
#interactive_timeout=						#Default 28800;fits client that uses the CLIENT_INTERACTIVE;
#net_read_timeout=						#Default 30;Min Value 1;The number of seconds to wait for more data from a connection before aborting the read.
#net_write_timeout=						#Default 60;Min Value 1;The number of seconds to wait for a block to be written to a connection before aborting the write. 
#net_retry_count=						#Default 10;Range 1 .. 18446744073709547520;If a read or write on a communication port is interrupted, retry this many times before giving up.
 
#*****profile&optimizer
#profiling							#Default OFF;statement profiling,you can use SHOW PROFILES and SHOW PROFILE if this enabled.
#profiling_history_size=					#Default 15;Maximum 100;The number of statements for which to maintain profiling information.
#optimizer_prune_level=						#Default 1;Controls the heuristics applied during query optimization.
#optimizer_search_depth=					#Default 62;Range 0 .. 63;The maximum depth of search performed by the query optimizer.If set to 63, the optimizer switches to the algorithm used in MySQL 5.0.0.
#optimizer_switch=						#Valid Values engine_condition_pushdown={on|off} index_merge={on|off} index_merge_intersection={on|off} index_merge_sort_union={on|off} index_merge_union={on|off}
#max_seeks_for_key=						#Default 18446744073709547520;Range 1 .. 18446744073709547520;Limit the assumed maximum number of seeks when looking up rows based on a key.
#max_length_for_sort_data=					#Default 1024;Range 4-8388608;determines which filesort algorithm to use.
 
#******limitation
#max_error_count=						#Default 64;Range 0 .. 65535;The maximum number of  messages to be stored for display by the SHOW ERRORS and SHOW WARNINGS statements. 
#max_join_size=							#Default 18446744073709551615;Range 1 .. 18446744073709551615;Do not permit SELECT statements that probably need to examine many rows.
#max_sort_length=						#Default 1024;Range 4 .. 8388608; use when sorting data values. Only the first max_sort_length bytes are used
#max_sp_recursion_depth=					#Default 0;Max Value 255;The number of times that any given stored procedure may be called recursively.
#open-files-limit=						#Default 0;Range 0 .. 65535;the number of file descriptors available to mysqld
#thread_stack=							#Default 262144;Range 131072-18446744073709547520;The stack size for each thread.
 
#**********************Logs****************************
#log-output=							#Default FILE;Valid Values TABLE FILE NONE;This option determines the destination for general query log and slow query log output.
 
#*****error log
#log-error=							#Default host_name.err;Log errors and startup messages to this file.
#log-warnings							#Default 1;Range 0,1,greater than 1;Whether to produce additional warning messages to the error log. 
#skip-log-warnings						#Disable log-warnings
 
#*****slow log
#slow-query-log							#Default OFF;Whether the slow query log is enabled
#slow_query_log_file=						#default value host_name-slow.log;The name of the slow query log file.
#long_query_time=						#Default 10;query longer than long_query_time seconds will be log to slow log and slow_queries status variable.
#log-queries-not-using-indexes					#Default OFF;queries that are expected to retrieve all rows are logged.
#log-slow-admin-statements					#Default FALSE;Log slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log. 
#log-slow-slave-statements					#Default off;enables logging for queries that have taken more than long_query_time seconds to execute on the slave.
#min-examined-row-limit=					#Default 0;Range 0-18446744073709547520;Queries that examine fewer than this number of rows are not logged to the slow query log.
 
#*****general log
#general-log							#Default OFF;Specify the initial general query log state.
#general_log_file=						#Default host_name.log;The name of the general query log file.
 
#**********************Replication*********************
#skip-slave-start						#Default FALSE;Tells the slave server not to start the slave threads when the server starts
#read_only							#Default false;When it is enabled, the server permits no updates except from users have SUPER privilege or slave threads.
#init_slave=							#a string to be executed by a slave server each time the SQL thread starts. 
#master-info-file=						#Default master.info;The name to use for the file in which the slave records information about the master.
#sync_master_info=						#default 0;If greater than 0,slave synchronizes master.info file to disk after every sync_master_info events
#slave_type_conversions=					#Valid Values ALL_LOSSY ALL_NON_LOSSY ALL_LOSSY,ALL_NON_LOSSY; when using row-based replication
#slave_transaction_retries=					#Default 10;Range 0 .. 18446744073709547520;If a replication slave SQL thread fails to execute a transaction because of lock block, it automatically retries slave_transaction_retries times before stopping with an error. 
#slave_exec_mode=						#Default STRICT (ALL);Default IDEMPOTENT (NDB);Controls IDEMPOTENT or STRICT mode in replication conflict resolution and error checking.
#slave-skip-errors=						#Default OFF;Valid Values [list of error codes] all ddl_exist_errors;tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the variable value
#slave-load-tmpdir=						#Default /tmp;by default equal to tmpdir system variable;directory where slave creates temporary files for replicating LOAD DATA INFILE statements. 
 
#******network
#slave_compressed_protocol					#Default OFF;Whether to use compression of the slave/master protocol 
#slave-max-allowed-packet=					#Default 1073741824;Range 1024 .. 1073741824;In MySQL 5.5.26 and later,this option sets the maximum packet size in bytes for the slave SQL and I/O threads
#slave-net-timeout=						#Default 3600;The number of seconds to wait for more data from a master/slave connection before aborting the read. 
#master-retry-count=						#Default 86400;Range 0 .. 18446744073709551615;The number of times that the slave tries to connect to the master before giving up.
 
#******report
#show-slave-auth-info						#Default FALSE;Display slave user names and passwords in the output of SHOW SLAVE HOSTS on the master server 
#report-host=							#host name or IP address to be reported to the master
#report-password=						#account password to be reported to the master 
#report-port=							#TCP/IP port number to be reported to the master 
#report-user=							#account user name to be reported to the master 
 
#*****binlog
#log-bin=							#Default OFF;The option value, if given, is the basename for the log sequence.Otherwise, MySQL uses host_name-bin as the basename. 
#log-bin-index=							#Default OFF;The index file for binary log file names.If you omit the file name, and if you did not specify one with --log-bin, MySQL uses host_name-bin.index as the file name. 
#sync_binlog=							#Default 0;Range 0-18446744073709547520;If greater than 0,server synchronizes binary log to disk after every sync_binlog writes to the binary log.
#binlog-format=							#Default STATEMENT;Valid Values ROW,STATEMENT,MIXED;sets the binary logging format,this option affect many other behavior,be cautious.
#max_binlog_size=						#Default 1073741824;If a write causes current log file size exceed this value, the server rotates the binary logs 
#expire_logs_days=						#Default 0;Range 0-99;The number of days for automatic binary log file removal. 
#binlog_cache_size=						#Default 32768;Range 4096-18446744073709547520;The size of the cache to hold changes to the binary log during a transaction.
#max_binlog_cache_size=						#Default 18446744073709547520;Range 4096 .. 18446744073709547520;If a transaction requires more than this many bytes of memory, the server generates a error
#binlog_stmt_cache_size=					#Default 32768;Range 4096-18446744073709547520.It is a binary log statement cache for non-transaction table since 5.5.9.
#max_binlog_stmt_cache_size=					#Default 18446744073709547520;Range 4096 .. 18446744073709547520;If nontransactional statements within a transaction require more than this many bytes of memory, the server generates an error.
#binlog-row-event-max-size=					#default is 1024;maximum size of a row-based binary log event, in bytes.The value should be a multiple of 256.
#log-short-format						#Default FALSE;Log less information to the binary log and slow query log;www.mysqlops.com
#log_slave_updates						#Default FALSE:tells the slave to log the updates performed by its SQL thread to its own binary log
#log-bin-trust-function-creators				#Default FALSE;affects how MySQL enforces restrictions on stored function and trigger creation.
#log-bin-trust-routine-creators					#no official description found,may be similar to log-bin-trust-function-creators;Default FALSE;affects how MySQL enforces restrictions on stored procedure creation.
#binlog_direct_non_transactional_updates			#Default OFF;About transaction on non transaction table problem.
 
#*****relaylog
#relay-log=							#default basename is host_name-relay-bin.;The basename for the relay log.
#relay-log-index=						#The default name is host_name-relay-bin.index;The name to use for the relay log index file.
#relay-log-info-file=						#Default relay-log.info;The name to use for the file in which the slave records information about the relay logs.
#sync_relay_log=						#Default 0;If greater than 0,server synchronizes relay log to disk after every sync_relay_log writes to the relay log
#sync_relay_log_info=						#Default 0;If greater than 0,slave synchronizes relay-log.info file to disk after every sync_relay_log_info transactions
#max_relay_log_size=						#Default 0;Range 0 .. 1073741824;If a write causes current log file size exceed this value,the slave rotates the relay logs.
#relay_log_space_limit=						#Default 0;Range 0 .. 18446744073709547520;The maximum amount of space to use for all relay logs.
#relay_log_purge=						#Default 1;Range 0,1;automatic purging of relay log files
#relay_log_recovery						#Default FALSE;slave discards all unprocessed relay logs and retrieves them from the master.
 
#******filter
#binlog-do-db=							#different action when difference setting in binlog-format.
#binlog-ignore-db=						#refer binlog-do-db
#replicate-do-db=						#different behavior under different binlog format
#replicate-ignore-db=						#different behavior under different binlog format
#replicate-do-table=						#restrict replication to the specified table
#replicate-ignore-table=					#not to replicate any statement that updates the specified table
#replicate-wild-do-table=					#Patterns can contain the “%” and “_” wildcard 
#replicate-wild-ignore-table=					#Patterns can contain the “%” and “_” wildcard 
#replicate-same-server-id					#Default FALSE;Cannot be set to 1 if --log-slave-updates is used
#replicate-rewrite-db=						#translate the default database to to_name
 
#**********************InnoDB**************************
#innodb_data_home_dir=						#The default value is the MySQL data directory;The common part of the directory path for all InnoDB data files in the shared tablespace.
#innodb_data_file_path=						#default behavior is to create a single 10MB auto-extending data file named ibdata1;The paths to individual data files and their sizes.;innodb_data_file_path=datafile_spec1[;datafile_spec2]...;datafile_spec=file_name:file_size[:autoextend[:max:max_file_size]]
#innodb_log_group_home_dir=					#default is to create two 5MB files named ib_logfile0 and ib_logfile1 in the MySQL data directory. ;The directory path to the InnoDB redo log files
#innodb_log_files_in_group=					#Default 2;Range 2-100;The size in bytes of each log file in a log group.
#innodb_log_file_size=						#Default 5242880;Range 108576-4294967295;The size in bytes of each log file in a log group.
 
#*****feature
#innodb_open_files=						#Default 300;Range 10-4294967295;specifies the maximum number of .ibd files that MySQL can keep open at one time.independent from --open-files-limit and table cache.
#innodb_change_buffering=					#Default all;Valid Values inserts,deletes,purges,changes,all,none.Whether InnoDB performs change buffering.
#innodb_adaptive_hash_index=					#Default On;Valid Values ON,OFF.the same old way to control build-in hash index,,but now you can trun off it if you want.
#innodb_autoinc_lock_mode=					#Default 1;Valid Values 0,1,2;The locking mode to use for generating auto-increment values.
#innodb_large_prefix						#Default OFF;Enable to allow index key prefixes longer than 767 bytes for DYNAMIC and COMPRESSED tables.requires innodb_file_format=barracuda and innodb_file_per_table=true
#innodb_strict_mode						#Default OFF;Whether InnoDB returns errors rather than warnings for certain conditions.
#innodb_use_sys_malloc=						#Default ON;Whether InnoDB uses the operating system memory allocator (ON) or its own (OFF).
 
#******buffer&cache
#innodb_buffer_pool_size=					#Default 128m;Range 1048576-2**64-1;The size of the memory buffer InnoDB uses to cache data and indexes of its tables. 
#innodb_buffer_pool_instances=					#Default 1;Range 1-64;The number of regions that the InnoDB buffer pool is divided into.the manual recommend each buffer pool instance is at least 1 gigabyte.
#innodb_max_dirty_pages_pct=					#Default 75;Range 0-99;InnoDB tries to write pages so that the percentage of dirty pages will not exceed this value. 
#innodb_old_blocks_pct=						#Default 37;Range 5-95;Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist.
#innodb_old_blocks_time=					#Default 0;Range 0-2**32-1;Non-zero values protect against the buffer pool being filled up by data that is referenced only for a brief period, such as during a full table scan.
#innodb_additional_mem_pool_size=				#Default 8388608;Range 2097152-4294967295;The size of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
#innodb_log_buffer_size=					#Default 8388608;Range 262144-4294967295;The size of the buffer that InnoDB uses to write to the log files on disk. 
 
#******IO
#innodb_flush_method=						#Default fdatasync;Valid Values O_DSYNC,O_DIRECT;specify the way to open and flush files.
#innodb_use_native_aio						#Default ON;Specifies whether to use the Linux asynchronous I/O subsystem.
#innodb_adaptive_flushing					#Default On;Valid Values ON,OFF.the same old way to flush dirty page,but now you can trun off it if you want.
#innodb_flush_log_at_trx_commit=				#Default 1;Valid Values 0,1,2;you should read manual for details
#innodb_io_capacity=						#Default 200;Range 100-2**64-1.An upper limit on the I/O activity performed by the InnoDB background tasks,set this value to system's IOPS.
#innodb_read_io_threads=					#Default 4;Range 1-64;The number of I/O threads for read operations in InnoDB.
#innodb_write_io_threads=					#Default 4;Range 1-64;The number of I/O threads for write operations in InnoDB.
#innodb_read_ahead_threshold=					#Default 56;Range 0-64;Controls the sensitivity of linear read-ahead
#innodb_doublewrite						#Default ON;If enabled (the default),stores all data twice, first to doublewrite buffer,then to data files. 
#innodb_purge_threads=						#Default 0;set 1 can reduce internal contention within InnoDB, improving scalability.but now the performance gain might be minimal.
#innodb_purge_batch_size=					#Default 20;Range 1 .. 5000;tuning performance in combination with the setting innodb_purge_threads=1.
#innodb_max_purge_lag=						#Default 0;Range 0 .. 4294967295; controls how to delay DML operations when purge operations are lagging
 
#*****fileformat
#innodb_file_per_table						#If disabled (the default),tables created in the system tablespace.If enabled,each new table create its own .ibd file.
#innodb_autoextend_increment=					#Default 8;Range 1-1000;increment size (in MB) for extending an auto-extending shared tablespace.per-table tablespace use another way.
#innodb_file_format=						#Default Antelope;Valid Values Antelope Barracuda;The file format to use for new per-table tablespace InnoDB tables.
#innodb_file_format_check=					#Default ON;enable or disable whether InnoDB checks the file format tag in the shared tablespace
#innodb_file_format_max=					#Default Antelope;Valid Values Antelope,Barracuda; sets the value of innodb_file_format_max to the file format tag in the shared tablespace
 
#******static&status
#innodb_stats_on_metadata					#Default ON.if on InnoDB updates statistics when SHOW TABLE STATUS,SHOW INDEX,access INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.STATISTICS. 
#innodb_stats_sample_pages=					#Default 8;Range 1-2**64-1.The number of index pages to sample for index distribution statistics.
#innodb_stats_method=						#Default nulls_equal;Valid Values nulls_equal nulls_unequal nulls_ignored;How the server treats NULL values when collecting statistics.
#timed_mutexes							#Default OFF;When enabled, the os_wait_times indicates time spent in operating system waits.
#innodb-status-file						#Default OFF;innodb_status. in data directory.
 
#******recovery&related
#innodb_fast_shutdown=						#Default 1;Valid Values 0,1,2.means slow shutdown,fast shutdown,flush logs and then abort respectively
#innodb_force_load_corrupted					#Use only during troubleshooting
#innodb_force_recovery=						#Only set this variable greater than 0 in an emergency situation
#innodb_checksums						#Default ON;validation all pages read from disk
 
#******transaction,lock,concurrency,rollback
#autocommit=							#default 1;Range 1,0;If 1,all changes take effect immediately.If 0,you must use COMMIT or ROLLBACK.this option affect many other behavior,be cautious.
#transaction-isolation=						#Default REPEATABLE-READ;Valid Values READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE
#transaction_prealloc_size=					#Default 4096;Range 1024-18446744073709547520. a per-transaction memory pool for various transaction-related allocations.use it can avoid many malloc() calls.
#transaction_alloc_block_size=					#Default 8192;Range 1024-18446744073709547520.when per-transaction memory pool is not enough ,the pool is increased by transaction_alloc_block_size bytes. 
#completion_type=						#Default NO_CHAIN;Valid Values NO_CHAIN CHAIN RELEASE 0 1 2;Only affects transactions that begin with START TRANSACTION or BEGIN and end with COMMIT or ROLLBACK.
#innodb_support_xa						#Default TRUE;Enables InnoDB support for two-phase commit in XA transactions
 
#innodb_table_locks						#Default TRUE;The default value means LOCK TABLES causes InnoDB to lock a table internally if autocommit = 0. 
#innodb_lock_wait_timeout=					#Default 50;Range 1-1073741824;The timeout in seconds an InnoDB transaction waits for a row lock before giving up.
#innodb_locks_unsafe_for_binlog					#Default OFF;affects how InnoDB uses gap locking for searches and index scans. 
#innodb_spin_wait_delay=					#Default 6;Range 0-4294967295;The maximum delay between polls for a spin lock.
#innodb_sync_spin_loops=					#Default 30;Range 0-4294967295;The number of times a thread waits for an InnoDB mutex.
 
#innodb_commit_concurrency=					#Default 500;Range 1-4294967295;The number of threads that can commit at the same time.
#innodb_thread_concurrency=					#Default 0;Range 0-1000;InnoDB tries to keep threads concurrently inside InnoDB less than this limit. 
#innodb_concurrency_tickets=					#Default 500;Range 1-4294967295.when innodb_thread_concurrency=0,there is no need to set.
#innodb_replication_delay=					#Default 0;Range 0 .. 4294967295;The replication thread delay (in ms) on a slave server if innodb_thread_concurrency is reached
#innodb_thread_sleep_delay=					#Default 10000;How long InnoDB threads sleep before joining the InnoDB queue, in microseconds.
 
#innodb_rollback_on_timeout					#Default OFF;only rolls back the last statement by default. If innodb_rollback_on_timeout is specified, entire transaction 
#innodb_rollback_segments=					#Default 128;Range 1-128.reduce this value to a smaller number might performs better for your workload. 
 
#**********************MyISAM**************************
#******feature
#myisam_data_pointer_size=					#Default 6;Range 2 .. 7;The default pointer size,used when no MAX_ROWS specified. 
#myisam_use_mmap						#Default OFF;Use memory mapping for reading and writing MyISAM tables. 
#keep_files_on_create						#Default OFF;By default,MyISAM overwrites an existing .MYD file if no DATA DIRECTORY option given.
#myisam-block-size=						#Default 1024;Range 1024 .. 16384 ;The block size to be used for MyISAM index pages.
#delay-key-write=						#Default ON;Valid Values ON,OFF,ALL;causes key buffers not flush between writes for MyISAM tables.
#preload_buffer_size						#Default 32768;Range 1024 .. 1073741824;The size of the buffer that is allocated when preloading indexes. 
#myisam_stats_method=						#Valid Values nulls_equal,nulls_unequal,nulls_ignored;How the server treats NULL values when collecting statistics 
#myisam-recover-options=					#Default OFF; Valid Values OFF DEFAULT BACKUP FORCE QUICK;Set the MyISAM storage engine recovery mode. 
#myisam_repair_threads=						#Note:it is still beta-quality code.Default 1;Range 1-18446744073709547520;If greater than 1,indexes are created in parallel.
 
#******buffer&cache
#key_buffer_size=						#Default 8M;Range 8-4294967295;the size of the buffer used for MyISAM index blocks,shared by all threads.
#key_cache_block_size=						#Default 1024;Range 512-16384;The size of blocks in the key cache.
#key_cache_age_threshold=					#Default 300;Range 100-18446744073709547520;controls the demotion of buffers from hot sublist to warm sublist.
#key_cache_division_limit=					#Default 100;Range 1 .. 100;The division between hot and warm sublists. 
#read_buffer_size=						#Default 131072;Range 8200-2147479552;Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans.
#read_rnd_buffer_size=						#Default Default 262144;Range 8200-4294967295;When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.
 
#*******delayed insert
#delayed_queue_size=						#Default 1000;Range 1 .. 18446744073709547520;a per-table limit on the number of rows 
#max_delayed_threads=						#Default 20;Range 0 .. 16384;Do not start more than this number of threads to handle INSERT DELAYED statements. 
#delayed_insert_limit=						#Default 100;Range 1 .. 18446744073709547520;After inserting delayed_insert_limit delayed rows,permits pending SELECT to execute. 
#delayed_insert_timeout=					#Default 300;seconds wait for INSERT before terminating. 
 
#******fulltext
#ft_boolean_syntax=						#Type string;Default +-><()~*:""&;The list of operators supported by boolean full-text searches performed using IN BOOLEAN MODE.
#ft_max_word_len=						#Min Value 10;The maximum length of the word in FULLTEXT index.indexes must be rebuilt after changing this variable.
#ft_min_word_len=						#Default 4;Min Value 1;The minimum length of the word in FULLTEXT index.indexes must be rebuilt after changing this variable.
#ft_query_expansion_limit=					#Default 20;Range 0 .. 1000;The number of top matches to use for full-text searches performed using WITH QUERY EXPANSION. 
#ft_stopword_file=						#The file from which to read the list of stopwords for full-text searches.
 
#******limitation
#bulk_insert_buffer_size=					#Default 8388608;Range 0-18446744073709547520; limits the size of the cache tree in bytes per thread.
#myisam_sort_buffer_size=					#Default 8m;Range 4-18446744073709547520;when sorting MyISAM indexes 
#myisam_max_sort_file_size=					#Default 2g;while re-creating a MyISAM index 
#myisam_mmap_size=						#Default 18446744073709547520;Range 7 .. 18446744073709547520; If many compressed MyISAM tables are used,decrease the value to reduce memory-swapping problems.
 
#******lock&concurrency
#external-locking						#Default FALSE;use this option on a system on which lockd does not fully work (such as Linux), it is easy for mysqld to deadlock. 
#skip-external-locking						#Do not use external locking .This affects only MyISAM table access.
 
#concurrent_insert=						#Default AUTO;Valid Values NEVER,AUTO,ALWAYS,0,1,2;control INSERT and SELECT concurrency;
#skip-concurrent-insert						#Turn off the ability to select and insert at the same time on MyISAM tables
#max_write_lock_count=						#Default 18446744073709547520;Range 1 .. 18446744073709547520;After this many write locks, permit some pending read lock requests to be processed in between.
#low-priority-updates						#Default FALSE;Give DML lower priority than selects.
 
#********************performance_schema****************
#performance_schema=						#Default OFF;whether the Performance Schema is enabled
#performance_schema_events_waits_history_long_size=		#The number of rows in the events_waits_history_long table
#performance_schema_events_waits_history_size=			#The number of rows per thread in the events_waits_history table
#performance_schema_max_cond_classes=				#Default 80;The maximum number of condition instruments
#performance_schema_max_cond_instances=				#The maximum number of instrumented condition objects
#performance_schema_max_file_classes=				#Default 50;The maximum number of file instruments
#performance_schema_max_file_handles=				#Default 32768;The maximum number of opened file objects
#performance_schema_max_file_instances=				#The maximum number of instrumented file objects
#performance_schema_max_mutex_classes=				#Default 200;The maximum number of mutex instruments
#performance_schema_max_mutex_instances=			#The maximum number of instrumented mutex objects
#performance_schema_max_rwlock_classes=				#Default 20;The maximum number of rwlock instruments
#performance_schema_max_rwlock_instances=			#The maximum number of instrumented rwlock objects
#performance_schema_max_table_handles=				#The maximum number of opened table objects
#performance_schema_max_table_instances=			#The maximum number of instrumented table objects
#performance_schema_max_thread_classes=				#Default 50;The maximum number of thread instruments
#performance_schema_max_thread_instances=			#The maximum number of instrumented thread objects
 
#*******以上为配置文件模板,在此处结束

发表评论

电子邮件地址不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据