MySQL5.5配置文件模板

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

#*******以上为配置文件模板,在此处结束
觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜想失败,您看看下面的文章有用吗?

3 thoughts on “MySQL5.5配置文件模板

  1. Pingback: mysql配置参数详解 – 爱上生活

  2. Good quality sun shoes offer all the safety you’ll need, and in addition they get outstanding visual acuity. Wearing them is really a pleasure. They are worth searching for.

  3. Don’t be inclined to invest in jewelry stores for sale. Operational zones, there are several jewelry stores which tempt purchasers to action 12 inches inside store by simply inserting large “We’re about SALE” indications. A few such jewelry stores mark up the prices prior to the purchase and low cost these people throughout the purchase interval to make sure that that they find the neat profit. So, until you are already eyeing which engagement ring pertaining to several weeks and realize its cost draw off by heart, don’t purchase this for sale.

发表评论

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

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>