mysql5 my.cnf_MySQL5.5数据库my.cnf配置文件模板

[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

+->

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