mysql 5.6 conf

 

http://dev.mysql.com/doc/refman/5.6/en/option-files.html

http://dev.mysql.com/doc/refman/5.6/en/server-default-configuration-file.html

 

 

5.1.2.1. Changes to Server Defaults

Beginning with MySQL 5.6.6, several MySQL Server parameter defaults differ from the defaults in previous releases. The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually.

In some cases, a parameter has a different fixed default value. In other cases, the server autosizes a parameter at startup using a formula based on other related parameters or server host configuration, rather than using a fixed value. For example, the setting for back_log is its previous default of 50, adjusted up by an amount proportional to the value of max_connections. The idea behind autosizing is that when the server has information available to make a decision about a parameter setting likely to be better than a fixed default, it will.

The following table summarizes changes to defaults. The Version column indicates when each default changed. For variables that are autosized, the main variable description provides additional detail about the sizing algorithm. See Section 5.1.4, “Server System Variables”, and Section 14.2.6, “InnoDB Startup Options and System Variables”. Any of these default settings can be overridden by specifying an explicit value at server startup.

Table 5.2. Changes to Server Defaults in MySQL 5.6

Parameter	Old Default	New Default	Version
back_log	50	Autosized usingmax_connections	5.6.6
binlog_checksum	NONE	CRC32	5.6.6
--binlog-row-event-max-size	1024	8192	5.6.6
flush_time	1800 (on Windows)	0	5.6.6
host_cache_size	128	Autosized usingmax_connections	5.6.8
innodb_autoextend_increment	8	64	5.6.6
innodb_buffer_pool_instances	1	8 (platform dependent)	5.6.6
innodb_concurrency_tickets	500	5000	5.6.6
innodb_data_file_path	ibdata1:10M:autoextend	ibdata1:12M:autoextend	5.6.7
innodb_file_per_table	0	1	5.6.6
innodb_log_file_size	5MB	48MB	5.6.8
innodb_old_blocks_time	0	1000	5.6.6
innodb_open_files	300	Autosized usinginnodb_file_per_table,table_open_cache	5.6.6
innodb_stats_on_metadata	ON	OFF	5.6.6
join_buffer_size	128KB	256KB	5.6.6
max_allowed_packet	1MB	4MB	5.6.6
max_connect_errors	10	100	5.6.6
open_files_limit	0	Autosized usingmax_connections	5.6.8
performance_schema	OFF	ON	5.6.6
performance_schema_events_waits_history_long_size	10000	Autosized	5.6.6
performance_schema_events_waits_history_size	10	Autosized	5.6.6
performance_schema_max_cond_instances	1000	Autosized	5.6.6
performance_schema_max_file_instances	10000	Autosized	5.6.6
performance_schema_max_mutex_instances	1000000	Autosized	5.6.6
performance_schema_max_rwlock_instances	1000000	Autosized	5.6.6
performance_schema_max_table_handles	100000	Autosized	5.6.6
performance_schema_max_table_instances	50000	Autosized	5.6.6
performance_schema_max_thread_instances	1000	Autosized	5.6.6
query_cache_size	0	1M	5.6.8
query_cache_type	ON	OFF	5.6.8
secure_auth	OFF	ON	5.6.7
sql_mode	'' (empty string)	NO_ENGINE_SUBSTITUTION	5.6.6
sync_master_info	0	10000	5.6.6
sync_relay_log	0	10000	5.6.6
sync_relay_log_info	0	10000	5.6.6
table_definition_cache	400	Autosized usingtable_open_cache	5.6.8
table_open_cache	400	2000	5.6.8
thread_cache_size	0	Autosized usingmax_connections	5.6.8

 

In MySQL 5.6.6, the default for innodb_checksum_algorithm was changed from INNODB to CRC32. For compatibility reasons, the default was returned in 5.6.7 to INNODB.

 

5.1.2.2. Using a Sample Default Server Configuration File

As of MySQL 5.6.8, on Unix platforms, mysql_install_db creates a default option file named my.cnf in the base installation directory. This file is created from a template included in the distribution package named my-default.cnf. You can find the template in or under the base installation directory. When started usingmysqld_safe, the server uses my.cnf file by default. If my.cnf already exists, mysql_install_db assumes it to be in use and writes a new file named my-new.cnf instead.

With one exception, the settings in the default option file are commented and have no effect. The exception is that the file changes the sql_mode system variable from its default of NO_ENGINE_SUBSTITUTION to also includeSTRICT_TRANS_TABLES:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

This setting produces a server configuration that results in errors rather than warnings for bad data in operations that modify transactional tables. See Section 5.1.7, “Server SQL Modes”.

The my-default.cnf template replaces the older sample option files formerly supplied with MySQL distributions (my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf). As of MySQL 5.6.8, these older files are no longer distributed.

On Windows, MySQL Installer interacts with the user and creates a file named my.ini in the base installation directory as the default option file. If you install on Windows from a Zip archive, you can copy the my-default.ini template file in the base installation directory to my.ini and use the latter as the default option file.

Note

On Windows, the .ini or .cnf option file extension might not be displayed.

On any platform, after completing the installation process, you can edit the default option file at any time to modify the parameters used by the server. For example, to use a parameter setting in the file that is commented with a #character at the beginning of the line, remove the #, and modify the parameter value if necessary. To disable a To disable a setting, either add a # to the beginning of the line or remove it.

For additional information about option file format and syntax, see Section 4.2.3.3, “Using Option Files”.

Before MySQL 5.6.8, MySQL distributions include several sample option files that can be used as a basis for tuning the MySQL server. Look for files named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf, which are sample files for small, medium, large, and very large systems. On Windows, the extension is.ini rather than .cnf.

For a binary distribution, look for the sample files in or under your installation directory. If you have a source distribution, look in the support-files directory. To use a sample file as a base configuration file, rename a copy of it and place the copy in the appropriate location. Regarding names and appropriate location, see the general information provided in Section 4.2.3.3, “Using Option Files”.