mysql配置参数详解

By | 2012/12/19

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

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

[mysqld]
#**********************Server**************************
#******server start related
#user=								#Run the mysqld server as the user having the name user_name or the numeric user ID user_id. 
#bind-address=							#Default 0.0.0.0;server listens on a single network socket which bound to a single address.
#port=								#The port number to listen for TCP/IP connections,must be 1024 or higher unless run as root. 
#port-open-timeout=						#Default 0;indicates how many seconds the server should wait for the TCP/IP port to become free.
#server-id=							#used in replication to enable master and slave servers to identify themselves uniquely.
#chroot								#Put the mysqld server in a closed environment during startup by using the chroot() system call.
#init-file=							#a file containing SQL statements that you want the server to execute when it starts.
#core-file=							#Default OFF;Write a core file if mysqld dies.
#skip-grant-tables						#This option causes the server to start without using the privilege system at all

#******location
#basedir=							#Default /;mysql installation directory
#pid-file=							#Default creates in data directory;The path name of the process ID file.
#socket=							#Default /tmp/mysql.sock;specifies the Unix socket file to use when listening for local connections.
#datadir=							#Default /var/lib/mysql;The path to the data directory
#tmpdir=							#Default /tmp;The path of the directory to use for creating temporary files.

#******security
#secure-auth							#prevent old format password client connect
#safe-user-create						#This ensures that the user cannot change any privilege columns directly, but has to use the GRANT statement to give privileges to other users.
#skip-show-database						#With this,SHOW DATABASES is permitted only to users have SHOW DATABASES privilege
#max_user_connections						#Default 0;Range 0 .. 4294967295;The maximum number of simultaneous connections permitted to any given MySQL user account.
#max_connect_errors=						#If more than this many connection requests interrupted,the server blocks that host.
#secure-file-priv=						#limits LOAD_FILE() and LOAD DATA and SELECT ... INTO OUTFILE statements to specified directory
#max_prepared_stmt_count=					#Default 16382;Range 0 .. 1048576;limits the total number of prepared statements in the server.
#skip-ssl							#indicate that SSL should not be used
#ssl-ca=							#The path to a file in PEM format
#ssl-capath=							#The path to a directory that contains trusted SSL certificate authority certificates in PEM format
#ssl-cert=							#The name of the SSL certificate file 
#ssl-cipher=							#A list of permissible ciphers to use for SSL encryption
#ssl-key=							#The name of the SSL key file

#******features
#default-storage-engine=					#Default InnoDB since 5.5.5.The default storage engine.
#ansi								#Use standard (ANSI) SQL syntax instead of MySQL syntax;is the same as --transaction-isolation=SERIALIZABLE --sql-mode=ANSI
#sql-mode=							#Default '';Valid Values ALLOW_INVALID_DATES,ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,HIGH_NOT_PRECEDENCE,IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_BACKSLASH_ESCAPES,NO_DIR_IN_CREATE,NO_ENGINE_SUBSTITUTION,NO_FIELD_OPTIONS,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_UNSIGNED_SUBTRACTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY,PAD_CHAR_TO_FULL_LENGTH,PIPES_AS_CONCAT,REAL_AS_FLOAT,STRICT_ALL_TABLES,STRICT_TRANS_TABLES
#auto_increment_increment=					#Default 1;Range 1 .. 65535;control the operation of AUTO_INCREMENT columns.
#auto_increment_offset=						#Default 1;Range 1 .. 65535;control the operation of AUTO_INCREMENT columns.
#div_precision_increment=					#Default 4;Range 0-30;This variable indicates the number of digits by which to increase the scale of the result of division operations performed with the / operator.
#event-scheduler=						#Default OFF;Valid Values ON OFF DISABLED;This variable indicates the status of the Event Scheduler; 
#skip-event-scheduler						#Turns the Event Scheduler OFF. 
#flush								#Default off;after each SQL statement,server flushes all changes.when off,to filesystem,when on to disk. 
#flush_time=							#Default 1800;Min Value 0.If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and synchronize unflushed data to disk.
#old								#when old is enabled, it changes the default scope of index hints to that used prior to MySQL 5.1.17.
#old-alter-table						#Default OFF;If given,server does not use optimized method of processing an ALTER TABLE.
#old-style-user-limits						#Default FALSE ;Enable old-style user limits.Before MySQL 5.0.3, account resource limits were counted separately for each host.
#partition							#Default ON ;Enables or disables user-defined partitioning support in the MySQL Server.
#skip-partition							#Disables user-defined partitioning.www.mysqlops.com
#plugin_dir=							#Default BASEDIR/lib/plugin ;The path name of the plugin directory. 
#plugin-load=							#plugin-load=myplug1=myplug1.so;myplug2=myplug2.so;This option tells the server to load the named plugins at startup. 
#symbolic-links							#you can link a MyISAM index file or data file to another directory with the INDEX DIRECTORY or DATA DIRECTORY options 
#skip-symbolic-links 						#you can't link a MyISAM index file or data file to another directory with the INDEX DIRECTORY or DATA DIRECTORY options 
#lock_wait_timeout=						#Default 31536000;Range 1 .. 31536000;This variable specifies the timeout in seconds for attempts to acquire metadata locks.
#sync_frm							#Default TRUE;when any nontemporary table is created its .frm file is synchronized to disk
#temp-pool							#Default TRUE;causes temporary files use a small set of names
#updatable_views_with_limit					#Default 1;controls whether updates to a view can be made when view does not contain all columns of the primary key and update statement contains a LIMIT clause.

#******function
#allow-suspicious-udfs						#Default FALSE; whether udf that have only an xxx symbol for the main function can be loaded
#des-key-file=							#These keys are used by the DES_ENCRYPT() and DES_DECRYPT() functions. 
#group_concat_max_len=						#Default 1024;Range 4 .. 18446744073709547520;The maximum permitted result length in bytes for the GROUP_CONCAT() function.
#max_long_data_size=						#Default 1048576;Range 1024 .. 4294967295;The maximum size of parameter values that can be sent with the mysql_stmt_send_long_data() C API function. 
#sysdate-is-now							#by default SYSDATE() returns the time it executes,not the time the statement begins executing.This differs from the behavior of NOW()
#default_week_format=						#Default 0;Range 0 .. 7;The default mode value to use for the WEEK() function.

#******character set&time zone etc..
#character-set-server=						#The server's default character set. default-character-set is deprecated,use this.
#collation-server=						#The server's default collation.collation is deprecated,use this.
#character-set-client-handshake					#Default TRUE;Do not ignore character set information sent by the client.
#skip-character-set-client-handshake				#this makes MySQL behave like MySQL 4.0. 
#character-set-filesystem=					#Default binary;The file system character set.set the same as system's character set.
#character-sets-dir=						#The directory where character sets are installed.
#lower_case_file_system						#On Linx OFF;On Windows ON;describes the case sensitivity of file names
#lower_case_table_names=					#Default 0;Range 0 .. 2;0 table name case sensitive,1 table name lowercase on disk and comparisons are not case sensitive,2 table name as given but compared in lowercase
#lc-messages=							#The locale to use for error messages. 
#lc-messages-dir=						#The directory where error messages are located.
#default-time-zone=						#default system time zone;Set the default server time zone.

#******buffer&cache
#memlock							#Default FALSE;Lock the mysqld process in memory.require root,or changing the limits.conf file
#large-pages							#Default FALSE;On Linux,due to reduced TLB misses,applications may obtain performance improvements by using large pages
#join_buffer_size=						#Default The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
#sort_buffer_size=						#Default 2097144;Max Value 18446744073709547520;Each session that needs to do a sort allocates a buffer of this size.
#table_open_cache=						#Default 400;Range 400-524288;The number of open tables for all threads,it requires file descriptors. 
#table_definition_cache=					#Default 400,Range 400-524288;The number of table definitions can be cache,it does not use file descriptors.
#range_alloc_block_size=					#Default 4096;Range 4096-4294967295;The size of blocks that are allocated when doing range optimization. 
#query_prealloc_size=						#Default 8192;Range 8192 .. 18446744073709547520;Block Size 1024;The size of the persistent buffer used for statement parsing and execution.
#query_alloc_block_size=					#Default 8192;Range 1024 .. 18446744073709547520;Block Size 1024;The allocation size of memory blocks that are allocated for objects created during statement parsing and execution.
#stored_program_cache=						#Default 256;Range 256 .. 524288;Sets a soft upper limit for the number of cached stored routines per connection.

#*****query cache
#query_cache_type=						#Default 1;Valid Values 0 1 2 OFF ON DEMAND;Set the query cache type.
#query_cache_size=						#Default 0;Range 0 .. 18446744073709547520;The amount of memory allocated for caching query results.
#query_cache_min_res_unit=					#Default 4096;Range 512 .. 18446744073709547520;The minimum size (in bytes) for blocks allocated by the query cache.
#query_cache_limit=						#Default 1048576;Range 0 .. 18446744073709547520;Do not cache results that are larger than this number of bytes. 
#query_cache_wlock_invalidate					#Default FALSE;Setting this variable to 1 causes acquisition of a WRITE lock for a table to invalidate any queries in the query cache that refer to the table.

#******thread&connection
#thread_handling=						#default one-thread-per-connection;The thread-handling model used by the server for connection threads.no-threads is useful for debugging.
#slow_launch_time=						#Default 2;If creating a thread takes longer than this many seconds, the server increments the Slow_launch_threads status variable. 
#init_connect=							#A string to be executed by the server for each client that connects.
#back_log=							#Default 50;Range 1-65535;The number of outstanding connection requests MySQL can have
#thread_cache_size=						#Default 0;Range 0-16384;How many threads the server should cache for reuse.
#max_connections=						#Default 151;Range 1-100000;The maximum permitted number of simultaneous client connections.

#******temptable
#big-tables							#Enable large result sets by saving all temporary sets in files
#tmp_table_size=						#Default system dependent;Range 1024-4294967295;The maximum size of internal in-memory temporary tables.max_tmp_tables is unused.
#max_heap_table_size=						#Default 16777216;Range 16384-1844674407370954752; the maximum size user-created MEMORY tables are permitted to grow.

#******network
#skip-networking						#Do not listen for TCP/IP connections at all. 
#skip-name-resolve						#Do not resolve host names when checking client connections.
#skip-host-cache						#Disable internal host cache,server performs a DNS lookup every time a client connects. 
#net_buffer_length=						#Default 16384;Range 1024-1048576;connection buffer and result buffer begin with a size given by net_buffer_length,dynamically enlarged up to max_allowed_packet bytes as needed.The result buffer shrinks to net_buffer_length after each SQL statement. 
#max_allowed_packet=						#Default 1048576;Range 1024-1073741824;The maximum size of one packet or any generated/intermediate string.
#connect_timeout=						#Default 10;The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.
#wait_timeout=							#Default 28800;Range 1 .. 2147483; waits for activity on a noninteractive connection before closing it
#interactive_timeout=						#Default 28800;fits client that uses the CLIENT_INTERACTIVE;
#net_read_timeout=						#Default 30;Min Value 1;The number of seconds to wait for more data from a connection before aborting the read.
#net_write_timeout=						#Default 60;Min Value 1;The number of seconds to wait for a block to be written to a connection before aborting the write. 
#net_retry_count=						#Default 10;Range 1 .. 18446744073709547520;If a read or write on a communication port is interrupted, retry this many times before giving up.

#*****profile&optimizer
#profiling							#Default OFF;statement profiling,you can use SHOW PROFILES and SHOW PROFILE if this enabled.
#profiling_history_size=					#Default 15;Maximum 100;The number of statements for which to maintain profiling information.
#optimizer_prune_level=						#Default 1;Controls the heuristics applied during query optimization.
#optimizer_search_depth=					#Default 62;Range 0 .. 63;The maximum depth of search performed by the query optimizer.If set to 63, the optimizer switches to the algorithm used in MySQL 5.0.0.
#optimizer_switch=						#Valid Values engine_condition_pushdown={on|off} index_merge={on|off} index_merge_intersection={on|off} index_merge_sort_union={on|off} index_merge_union={on|off}
#max_seeks_for_key=						#Default 18446744073709547520;Range 1 .. 18446744073709547520;Limit the assumed maximum number of seeks when looking up rows based on a key.
#max_length_for_sort_data=					#Default 1024;Range 4-8388608;determines which filesort algorithm to use.

#******limitation
#max_error_count=						#Default 64;Range 0 .. 65535;The maximum number of  messages to be stored for display by the SHOW ERRORS and SHOW WARNINGS statements. 
#max_join_size=							#Default 18446744073709551615;Range 1 .. 18446744073709551615;Do not permit SELECT statements that probably need to examine many rows.
#max_sort_length=						#Default 1024;Range 4 .. 8388608; use when sorting data values. Only the first max_sort_length bytes are used
#max_sp_recursion_depth=					#Default 0;Max Value 255;The number of times that any given stored procedure may be called recursively.
#open-files-limit=						#Default 0;Range 0 .. 65535;the number of file descriptors available to mysqld
#thread_stack=							#Default 262144;Range 131072-18446744073709547520;The stack size for each thread.

#**********************Logs****************************
#log-output=							#Default FILE;Valid Values TABLE FILE NONE;This option determines the destination for general query log and slow query log output.

#*****error log
#log-error=							#Default host_name.err;Log errors and startup messages to this file.
#log-warnings							#Default 1;Range 0,1,greater than 1;Whether to produce additional warning messages to the error log. 
#skip-log-warnings						#Disable log-warnings

#*****slow log
#slow-query-log							#Default OFF;Whether the slow query log is enabled
#slow_query_log_file=						#default value host_name-slow.log;The name of the slow query log file.
#long_query_time=						#Default 10;query longer than long_query_time seconds will be log to slow log and slow_queries status variable.
#log-queries-not-using-indexes					#Default OFF;queries that are expected to retrieve all rows are logged.
#log-slow-admin-statements					#Default FALSE;Log slow administrative statements such as OPTIMIZE TABLE, ANALYZE TABLE, and ALTER TABLE to the slow query log. 
#log-slow-slave-statements					#Default off;enables logging for queries that have taken more than long_query_time seconds to execute on the slave.
#min-examined-row-limit=					#Default 0;Range 0-18446744073709547520;Queries that examine fewer than this number of rows are not logged to the slow query log.

#*****general log
#general-log							#Default OFF;Specify the initial general query log state.
#general_log_file=						#Default host_name.log;The name of the general query log file.

#**********************Replication*********************
#skip-slave-start						#Default FALSE;Tells the slave server not to start the slave threads when the server starts
#read_only							#Default false;When it is enabled, the server permits no updates except from users have SUPER privilege or slave threads.
#init_slave=							#a string to be executed by a slave server each time the SQL thread starts. 
#master-info-file=						#Default master.info;The name to use for the file in which the slave records information about the master.
#sync_master_info=						#default 0;If greater than 0,slave synchronizes master.info file to disk after every sync_master_info events
#slave_type_conversions=					#Valid Values ALL_LOSSY ALL_NON_LOSSY ALL_LOSSY,ALL_NON_LOSSY; when using row-based replication
#slave_transaction_retries=					#Default 10;Range 0 .. 18446744073709547520;If a replication slave SQL thread fails to execute a transaction because of lock block, it automatically retries slave_transaction_retries times before stopping with an error. 
#slave_exec_mode=						#Default STRICT (ALL);Default IDEMPOTENT (NDB);Controls IDEMPOTENT or STRICT mode in replication conflict resolution and error checking.
#slave-skip-errors=						#Default OFF;Valid Values [list of error codes] all ddl_exist_errors;tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the variable value
#slave-load-tmpdir=						#Default /tmp;by default equal to tmpdir system variable;directory where slave creates temporary files for replicating LOAD DATA INFILE statements. 

#******network
#slave_compressed_protocol					#Default OFF;Whether to use compression of the slave/master protocol 
#slave-max-allowed-packet=					#Default 1073741824;Range 1024 .. 1073741824;In MySQL 5.5.26 and later,this option sets the maximum packet size in bytes for the slave SQL and I/O threads
#slave-net-timeout=						#Default 3600;The number of seconds to wait for more data from a master/slave connection before aborting the read. 
#master-retry-count=						#Default 86400;Range 0 .. 18446744073709551615;The number of times that the slave tries to connect to the master before giving up.

#******report
#show-slave-auth-info						#Default FALSE;Display slave user names and passwords in the output of SHOW SLAVE HOSTS on the master server 
#report-host=							#host name or IP address to be reported to the master
#report-password=						#account password to be reported to the master 
#report-port=							#TCP/IP port number to be reported to the master 
#report-user=							#account user name to be reported to the master 

#*****binlog
#log-bin=							#Default OFF;The option value, if given, is the basename for the log sequence.Otherwise, MySQL uses host_name-bin as the basename. 
#log-bin-index=							#Default OFF;The index file for binary log file names.If you omit the file name, and if you did not specify one with --log-bin, MySQL uses host_name-bin.index as the file name. 
#sync_binlog=							#Default 0;Range 0-18446744073709547520;If greater than 0,server synchronizes binary log to disk after every sync_binlog writes to the binary log.
#binlog-format=							#Default STATEMENT;Valid Values ROW,STATEMENT,MIXED;sets the binary logging format,this option affect many other behavior,be cautious.
#max_binlog_size=						#Default 1073741824;If a write causes current log file size exceed this value, the server rotates the binary logs 
#expire_logs_days=						#Default 0;Range 0-99;The number of days for automatic binary log file removal. 
#binlog_cache_size=						#Default 32768;Range 4096-18446744073709547520;The size of the cache to hold changes to the binary log during a transaction.
#max_binlog_cache_size=						#Default 18446744073709547520;Range 4096 .. 18446744073709547520;If a transaction requires more than this many bytes of memory, the server generates a error
#binlog_stmt_cache_size=					#Default 32768;Range 4096-18446744073709547520.It is a binary log statement cache for non-transaction table since 5.5.9.
#max_binlog_stmt_cache_size=					#Default 18446744073709547520;Range 4096 .. 18446744073709547520;If nontransactional statements within a transaction require more than this many bytes of memory, the server generates an error.
#binlog-row-event-max-size=					#default is 1024;maximum size of a row-based binary log event, in bytes.The value should be a multiple of 256.
#log-short-format						#Default FALSE;Log less information to the binary log and slow query log;www.mysqlops.com
#log_slave_updates						#Default FALSE:tells the slave to log the updates performed by its SQL thread to its own binary log
#log-bin-trust-function-creators				#Default FALSE;affects how MySQL enforces restrictions on stored function and trigger creation.
#log-bin-trust-routine-creators					#no official description found,may be similar to log-bin-trust-function-creators;Default FALSE;affects how MySQL enforces restrictions on stored procedure creation.
#binlog_direct_non_transactional_updates			#Default OFF;About transaction on non transaction table problem.

#*****relaylog
#relay-log=							#default basename is host_name-relay-bin.;The basename for the relay log.
#relay-log-index=						#The default name is host_name-relay-bin.index;The name to use for the relay log index file.
#relay-log-info-file=						#Default relay-log.info;The name to use for the file in which the slave records information about the relay logs.
#sync_relay_log=						#Default 0;If greater than 0,server synchronizes relay log to disk after every sync_relay_log writes to the relay log
#sync_relay_log_info=						#Default 0;If greater than 0,slave synchronizes relay-log.info file to disk after every sync_relay_log_info transactions
#max_relay_log_size=						#Default 0;Range 0 .. 1073741824;If a write causes current log file size exceed this value,the slave rotates the relay logs.
#relay_log_space_limit=						#Default 0;Range 0 .. 18446744073709547520;The maximum amount of space to use for all relay logs.
#relay_log_purge=						#Default 1;Range 0,1;automatic purging of relay log files
#relay_log_recovery						#Default FALSE;slave discards all unprocessed relay logs and retrieves them from the master.

#******filter
#binlog-do-db=							#different action when difference setting in binlog-format.
#binlog-ignore-db=						#refer binlog-do-db
#replicate-do-db=						#different behavior under different binlog format
#replicate-ignore-db=						#different behavior under different binlog format
#replicate-do-table=						#restrict replication to the specified table
#replicate-ignore-table=					#not to replicate any statement that updates the specified table
#replicate-wild-do-table=					#Patterns can contain the “%” and “_” wildcard 
#replicate-wild-ignore-table=					#Patterns can contain the “%” and “_” wildcard 
#replicate-same-server-id					#Default FALSE;Cannot be set to 1 if --log-slave-updates is used
#replicate-rewrite-db=						#translate the default database to to_name

#**********************InnoDB**************************
#innodb_data_home_dir=						#The default value is the MySQL data directory;The common part of the directory path for all InnoDB data files in the shared tablespace.
#innodb_data_file_path=						#default behavior is to create a single 10MB auto-extending data file named ibdata1;The paths to individual data files and their sizes.;innodb_data_file_path=datafile_spec1[;datafile_spec2]...;datafile_spec=file_name:file_size[:autoextend[:max:max_file_size]]
#innodb_log_group_home_dir=					#default is to create two 5MB files named ib_logfile0 and ib_logfile1 in the MySQL data directory. ;The directory path to the InnoDB redo log files
#innodb_log_files_in_group=					#Default 2;Range 2-100;The size in bytes of each log file in a log group.
#innodb_log_file_size=						#Default 5242880;Range 108576-4294967295;The size in bytes of each log file in a log group.

#*****feature
#innodb_open_files=						#Default 300;Range 10-4294967295;specifies the maximum number of .ibd files that MySQL can keep open at one time.independent from --open-files-limit and table cache.
#innodb_change_buffering=					#Default all;Valid Values inserts,deletes,purges,changes,all,none.Whether InnoDB performs change buffering.
#innodb_adaptive_hash_index=					#Default On;Valid Values ON,OFF.the same old way to control build-in hash index,,but now you can trun off it if you want.
#innodb_autoinc_lock_mode=					#Default 1;Valid Values 0,1,2;The locking mode to use for generating auto-increment values.
#innodb_large_prefix						#Default OFF;Enable to allow index key prefixes longer than 767 bytes for DYNAMIC and COMPRESSED tables.requires innodb_file_format=barracuda and innodb_file_per_table=true
#innodb_strict_mode						#Default OFF;Whether InnoDB returns errors rather than warnings for certain conditions.
#innodb_use_sys_malloc=						#Default ON;Whether InnoDB uses the operating system memory allocator (ON) or its own (OFF).

#******buffer&cache
#innodb_buffer_pool_size=					#Default 128m;Range 1048576-2**64-1;The size of the memory buffer InnoDB uses to cache data and indexes of its tables. 
#innodb_buffer_pool_instances=					#Default 1;Range 1-64;The number of regions that the InnoDB buffer pool is divided into.the manual recommend each buffer pool instance is at least 1 gigabyte.
#innodb_max_dirty_pages_pct=					#Default 75;Range 0-99;InnoDB tries to write pages so that the percentage of dirty pages will not exceed this value. 
#innodb_old_blocks_pct=						#Default 37;Range 5-95;Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist.
#innodb_old_blocks_time=					#Default 0;Range 0-2**32-1;Non-zero values protect against the buffer pool being filled up by data that is referenced only for a brief period, such as during a full table scan.
#innodb_additional_mem_pool_size=				#Default 8388608;Range 2097152-4294967295;The size of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
#innodb_log_buffer_size=					#Default 8388608;Range 262144-4294967295;The size of the buffer that InnoDB uses to write to the log files on disk. 

#******IO
#innodb_flush_method=						#Default fdatasync;Valid Values O_DSYNC,O_DIRECT;specify the way to open and flush files.
#innodb_use_native_aio						#Default ON;Specifies whether to use the Linux asynchronous I/O subsystem.
#innodb_adaptive_flushing					#Default On;Valid Values ON,OFF.the same old way to flush dirty page,but now you can trun off it if you want.
#innodb_flush_log_at_trx_commit=				#Default 1;Valid Values 0,1,2;you should read manual for details
#innodb_io_capacity=						#Default 200;Range 100-2**64-1.An upper limit on the I/O activity performed by the InnoDB background tasks,set this value to system's IOPS.
#innodb_read_io_threads=					#Default 4;Range 1-64;The number of I/O threads for read operations in InnoDB.
#innodb_write_io_threads=					#Default 4;Range 1-64;The number of I/O threads for write operations in InnoDB.
#innodb_read_ahead_threshold=					#Default 56;Range 0-64;Controls the sensitivity of linear read-ahead
#innodb_doublewrite						#Default ON;If enabled (the default),stores all data twice, first to doublewrite buffer,then to data files. 
#innodb_purge_threads=						#Default 0;set 1 can reduce internal contention within InnoDB, improving scalability.but now the performance gain might be minimal.
#innodb_purge_batch_size=					#Default 20;Range 1 .. 5000;tuning performance in combination with the setting innodb_purge_threads=1.
#innodb_max_purge_lag=						#Default 0;Range 0 .. 4294967295; controls how to delay DML operations when purge operations are lagging

#*****fileformat
#innodb_file_per_table						#If disabled (the default),tables created in the system tablespace.If enabled,each new table create its own .ibd file.
#innodb_autoextend_increment=					#Default 8;Range 1-1000;increment size (in MB) for extending an auto-extending shared tablespace.per-table tablespace use another way.
#innodb_file_format=						#Default Antelope;Valid Values Antelope Barracuda;The file format to use for new per-table tablespace InnoDB tables.
#innodb_file_format_check=					#Default ON;enable or disable whether InnoDB checks the file format tag in the shared tablespace
#innodb_file_format_max=					#Default Antelope;Valid Values Antelope,Barracuda; sets the value of innodb_file_format_max to the file format tag in the shared tablespace

#******static&status
#innodb_stats_on_metadata					#Default ON.if on InnoDB updates statistics when SHOW TABLE STATUS,SHOW INDEX,access INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.STATISTICS. 
#innodb_stats_sample_pages=					#Default 8;Range 1-2**64-1.The number of index pages to sample for index distribution statistics.
#innodb_stats_method=						#Default nulls_equal;Valid Values nulls_equal nulls_unequal nulls_ignored;How the server treats NULL values when collecting statistics.
#timed_mutexes							#Default OFF;When enabled, the os_wait_times indicates time spent in operating system waits.
#innodb-status-file						#Default OFF;innodb_status. in data directory.

#******recovery&related
#innodb_fast_shutdown=						#Default 1;Valid Values 0,1,2.means slow shutdown,fast shutdown,flush logs and then abort respectively
#innodb_force_load_corrupted					#Use only during troubleshooting
#innodb_force_recovery=						#Only set this variable greater than 0 in an emergency situation
#innodb_checksums						#Default ON;validation all pages read from disk

#******transaction,lock,concurrency,rollback
#autocommit=							#default 1;Range 1,0;If 1,all changes take effect immediately.If 0,you must use COMMIT or ROLLBACK.this option affect many other behavior,be cautious.
#transaction-isolation=						#Default REPEATABLE-READ;Valid Values READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE
#transaction_prealloc_size=					#Default 4096;Range 1024-18446744073709547520. a per-transaction memory pool for various transaction-related allocations.use it can avoid many malloc() calls.
#transaction_alloc_block_size=					#Default 8192;Range 1024-18446744073709547520.when per-transaction memory pool is not enough ,the pool is increased by transaction_alloc_block_size bytes. 
#completion_type=						#Default NO_CHAIN;Valid Values NO_CHAIN CHAIN RELEASE 0 1 2;Only affects transactions that begin with START TRANSACTION or BEGIN and end with COMMIT or ROLLBACK.
#innodb_support_xa						#Default TRUE;Enables InnoDB support for two-phase commit in XA transactions

#innodb_table_locks						#Default TRUE;The default value means LOCK TABLES causes InnoDB to lock a table internally if autocommit = 0. 
#innodb_lock_wait_timeout=					#Default 50;Range 1-1073741824;The timeout in seconds an InnoDB transaction waits for a row lock before giving up.
#innodb_locks_unsafe_for_binlog					#Default OFF;affects how InnoDB uses gap locking for searches and index scans. 
#innodb_spin_wait_delay=					#Default 6;Range 0-4294967295;The maximum delay between polls for a spin lock.
#innodb_sync_spin_loops=					#Default 30;Range 0-4294967295;The number of times a thread waits for an InnoDB mutex.

#innodb_commit_concurrency=					#Default 500;Range 1-4294967295;The number of threads that can commit at the same time.
#innodb_thread_concurrency=					#Default 0;Range 0-1000;InnoDB tries to keep threads concurrently inside InnoDB less than this limit. 
#innodb_concurrency_tickets=					#Default 500;Range 1-4294967295.when innodb_thread_concurrency=0,there is no need to set.
#innodb_replication_delay=					#Default 0;Range 0 .. 4294967295;The replication thread delay (in ms) on a slave server if innodb_thread_concurrency is reached
#innodb_thread_sleep_delay=					#Default 10000;How long InnoDB threads sleep before joining the InnoDB queue, in microseconds.

#innodb_rollback_on_timeout					#Default OFF;only rolls back the last statement by default. If innodb_rollback_on_timeout is specified, entire transaction 
#innodb_rollback_segments=					#Default 128;Range 1-128.reduce this value to a smaller number might performs better for your workload. 

#**********************MyISAM**************************
#******feature
#myisam_data_pointer_size=					#Default 6;Range 2 .. 7;The default pointer size,used when no MAX_ROWS specified. 
#myisam_use_mmap						#Default OFF;Use memory mapping for reading and writing MyISAM tables. 
#keep_files_on_create						#Default OFF;By default,MyISAM overwrites an existing .MYD file if no DATA DIRECTORY option given.
#myisam-block-size=						#Default 1024;Range 1024 .. 16384 ;The block size to be used for MyISAM index pages.
#delay-key-write=						#Default ON;Valid Values ON,OFF,ALL;causes key buffers not flush between writes for MyISAM tables.
#preload_buffer_size						#Default 32768;Range 1024 .. 1073741824;The size of the buffer that is allocated when preloading indexes. 
#myisam_stats_method=						#Valid Values nulls_equal,nulls_unequal,nulls_ignored;How the server treats NULL values when collecting statistics 
#myisam-recover-options=					#Default OFF; Valid Values OFF DEFAULT BACKUP FORCE QUICK;Set the MyISAM storage engine recovery mode. 
#myisam_repair_threads=						#Note:it is still beta-quality code.Default 1;Range 1-18446744073709547520;If greater than 1,indexes are created in parallel.

#******buffer&cache
#key_buffer_size=						#Default 8M;Range 8-4294967295;the size of the buffer used for MyISAM index blocks,shared by all threads.
#key_cache_block_size=						#Default 1024;Range 512-16384;The size of blocks in the key cache.
#key_cache_age_threshold=					#Default 300;Range 100-18446744073709547520;controls the demotion of buffers from hot sublist to warm sublist.
#key_cache_division_limit=					#Default 100;Range 1 .. 100;The division between hot and warm sublists. 
#read_buffer_size=						#Default 131072;Range 8200-2147479552;Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans.
#read_rnd_buffer_size=						#Default Default 262144;Range 8200-4294967295;When reading rows from a MyISAM table in sorted order following a key-sorting operation, the rows are read through this buffer to avoid disk seeks.

#*******delayed insert
#delayed_queue_size=						#Default 1000;Range 1 .. 18446744073709547520;a per-table limit on the number of rows 
#max_delayed_threads=						#Default 20;Range 0 .. 16384;Do not start more than this number of threads to handle INSERT DELAYED statements. 
#delayed_insert_limit=						#Default 100;Range 1 .. 18446744073709547520;After inserting delayed_insert_limit delayed rows,permits pending SELECT to execute. 
#delayed_insert_timeout=					#Default 300;seconds wait for INSERT before terminating. 

#******fulltext
#ft_boolean_syntax=						#Type string;Default +-><()~*:""&;The list of operators supported by boolean full-text searches performed using IN BOOLEAN MODE.
#ft_max_word_len=						#Min Value 10;The maximum length of the word in FULLTEXT index.indexes must be rebuilt after changing this variable.
#ft_min_word_len=						#Default 4;Min Value 1;The minimum length of the word in FULLTEXT index.indexes must be rebuilt after changing this variable.
#ft_query_expansion_limit=					#Default 20;Range 0 .. 1000;The number of top matches to use for full-text searches performed using WITH QUERY EXPANSION. 
#ft_stopword_file=						#The file from which to read the list of stopwords for full-text searches.

#******limitation
#bulk_insert_buffer_size=					#Default 8388608;Range 0-18446744073709547520; limits the size of the cache tree in bytes per thread.
#myisam_sort_buffer_size=					#Default 8m;Range 4-18446744073709547520;when sorting MyISAM indexes 
#myisam_max_sort_file_size=					#Default 2g;while re-creating a MyISAM index 
#myisam_mmap_size=						#Default 18446744073709547520;Range 7 .. 18446744073709547520; If many compressed MyISAM tables are used,decrease the value to reduce memory-swapping problems.

#******lock&concurrency
#external-locking						#Default FALSE;use this option on a system on which lockd does not fully work (such as Linux), it is easy for mysqld to deadlock. 
#skip-external-locking						#Do not use external locking .This affects only MyISAM table access.

#concurrent_insert=						#Default AUTO;Valid Values NEVER,AUTO,ALWAYS,0,1,2;control INSERT and SELECT concurrency;
#skip-concurrent-insert						#Turn off the ability to select and insert at the same time on MyISAM tables
#max_write_lock_count=						#Default 18446744073709547520;Range 1 .. 18446744073709547520;After this many write locks, permit some pending read lock requests to be processed in between.
#low-priority-updates						#Default FALSE;Give DML lower priority than selects.

#********************performance_schema****************
#performance_schema=						#Default OFF;whether the Performance Schema is enabled
#performance_schema_events_waits_history_long_size=		#The number of rows in the events_waits_history_long table
#performance_schema_events_waits_history_size=			#The number of rows per thread in the events_waits_history table
#performance_schema_max_cond_classes=				#Default 80;The maximum number of condition instruments
#performance_schema_max_cond_instances=				#The maximum number of instrumented condition objects
#performance_schema_max_file_classes=				#Default 50;The maximum number of file instruments
#performance_schema_max_file_handles=				#Default 32768;The maximum number of opened file objects
#performance_schema_max_file_instances=				#The maximum number of instrumented file objects
#performance_schema_max_mutex_classes=				#Default 200;The maximum number of mutex instruments
#performance_schema_max_mutex_instances=			#The maximum number of instrumented mutex objects
#performance_schema_max_rwlock_classes=				#Default 20;The maximum number of rwlock instruments
#performance_schema_max_rwlock_instances=			#The maximum number of instrumented rwlock objects
#performance_schema_max_table_handles=				#The maximum number of opened table objects
#performance_schema_max_table_instances=			#The maximum number of instrumented table objects
#performance_schema_max_thread_classes=				#Default 50;The maximum number of thread instruments
#performance_schema_max_thread_instances=			#The maximum number of instrumented thread objects

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

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

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