***以下开始为配置文件模板
[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 MyS QL 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
<\pre><\code>
<a href="http://www.ha97.com/4110.html"MySQL my.cnf参数配置优化详解></a>