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

MySQL · ruyi · 于 2年前发布 · 1851 次阅读

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#innodb_commit_concurrency=			#Default 500;Range 1-4294967295;The number of threads that can commit at the same time.
#innodb_thread_concurrency=		#Default 0;Range 0-1000;InnoDB tries to keep threads concurrently inside InnoDB less than this limit. 
#innodb_concurrency_tickets=			#Default 500;Range 1-4294967295.when innodb_thread_concurrency=0,there is no need to set.
#innodb_replication_delay=			#Default 0;Range 0 .. 4294967295;The replication thread delay (in ms) on a slave server if innodb_thread_concurrency is reached
#innodb_thread_sleep_delay=		#Default 10000;How long InnoDB threads sleep before joining the InnoDB queue, in microseconds.
#innodb_rollback_on_timeout		#Default OFF;only rolls back the last statement by default. If innodb_rollback_on_timeout is specified, entire transaction 
#innodb_rollback_segments=		#Default 128;Range 1-128.reduce this value to a smaller number might performs better for your workload. 

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

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

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

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

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

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

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

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

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


如果这篇文章对您有帮助,不妨微信小额赞助我一下,让我有动力继续写出高质量的帖子。

本帖已经被管理员设置为: 精华帖 !
共收到 3 条回复 MySQL 配置文件 数据库
memory#12年前 0 个赞

好变态!居然全搞英文!

memory#22年前 1 个赞

好变态!居然全搞英文!

light#39个月前 0 个赞

有没有配置的最佳实践,比如哪些参数配置什么值,达到最佳状态

回复本帖 (需要登录)