mysql 5 数据库 源文件_MySQL5.5数据库my.cnf配置文件模板

原帖地址：http://www.mysqlops.com/2012/12/18/mycnf.html

5.5的配置文件，在很早以前就有了一个初稿，当初可是相当的简陋。后来决定把这个东西分享给大家，于是下了大决心仔细修改，期望达成：即使是刚接触MySQL不久，也能用这个模板配置出可以一用的配置文件。期间来来回回各种调整，花了相当长的时间才完成。直接导致5.5参数的翻译，和bug测试都停滞不前，不过这个配置文件模板完成度很高，一切的付出都是值得的。

需要说明的一些东西是：

1.这个配置文件模板主要用于非资深的MySQL DBA调整配置，因此有大量的注释，这个模板的精华也就是注释了，实在不喜欢可以用UE等编辑器的列模式轻松删除。

2.它是为Linux环境下的MySQL5.5使用而设计的，因此不包括windows、solaris等操作系统下的MySQL特有的选项。

3.它不包括NDB相关的选项。

4.由于MySQL应用环境不同，为了不误导他人，所有选项都注释了，并且删掉了选项值。

5.大家复制回去后，请小心检查开头、结尾处是否正确复制。也请注意，另存为Unix格式、utf8无BOM或合适的字符集后再放到服务器上。若有发现错误或者遗漏的选项，请尽快联系我sserreflection@gmail.com，感激不尽。

6.假设你的同事、同学也想要，如果可以的话，希望告诉他来我们网站(http://www.mysqlops.com/)上复制，为网站增加一点人气，谢谢。

#*******以下开始为配置文件模板

[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

#*******以上为配置文件模板，在此处结束