MySQL Server 5.6 default my.cnf and my.ini

We've introduced a default my.cnf / my.ini file for MySQL Server that you can now see in the 5.6.8 release candidate:

# For advice on how to change settings please see

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M


# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin


# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# socket = ..... 
# server_id = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 




There is also a template file called my-default.cnf or my-default.ini that has these lines near the start:

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.


On Linux systems, the mysql_install_db command will copy the template file to the final location, where the server will read and use the file, removing the extra three lines. On Windows, the installer will create extra settings based on the answers you gave during installation. Neither will overwrite an existing my.cnf or my.ini file.

The only initially active setting here is to change the value of  sql_mode from the server default of NO_ENGINE_SUBSTITUTION to NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES. This strict mode changes warnings for some non-standard behaviour into errors. This can cause applications which rely on the non-standard things, like dates that aren't valid, to lose data. If we had just changed the server default, the new setting would affect all servers that lack an explicit sql_mode setting, including those where strict mode is harmful. So we did it in the default file instead because that will only affect new server installations.

You should expect that in our next version after 5.6, the server default will include STRICT_TRANS_TABLES. Our Windows installer and some of our connectors already use STRICT_TRANS_TABLES by default. Strict has been our preferred setting for many years and it is good to see some development platforms are using it.

If you need the old behaviour, just remove the STRICT_TRANS_TABLES setting. If you do this, please also ask your application provider to make it unnecessary. They can do that by setting the session sql_mode setting in their own connections, so the rest of the applications using the server don't have to have an undesirable default.

We've kept this file as small as possible because we found that our old files were too big and confused people. We've also now removed the old my-huge and related example files.

One key part of this is the link to the documentation, where we will provide an introduction to some key settings. We'd like to hear your feedback on settings that will benefit most users or are most important to call out for existing users. Please do that by commenting here or if you prefer by adding comments to this bug report.


here's my vote for innodb_file_per_table by default....

Posted by Sheeri Cabral on November 16, 2012 at 06:34 AM PST #

So is fixed?

Posted by Daniël van Eeden on November 23, 2012 at 01:41 AM PST #

Sheeri, you get your wish. innodb_file_per_table defaults to on in 5.6. :)

You should also watch what we're doing to exploit that to make it far easier to recover the data in undamaged tables if there's a corruption problem. You should end up being able to get back almost all of the data very quickly with minimal time spent, then concentrate on just the smaller portion with actual damage.

Daniël, the old files are no longer being included, so yes, any problem relating to what they contain is fixed. There's a chance that they might show up in the docs subdirectory but I think they will stay just gone.

There's often a lag before some bug reports are updated with changes, particularly during the very busy time for the documentation team when we're doing release candidates and trying to be sure that all new features are well documented. If you agree that the changes in 5.6 eliminate the problem and no longer want it changed in 5.5, please say so in your bug report, so that whoever looks at it knows that you as reporter agree that it should be closed.

Thanks for the feedback, please do keep it coming on these things or via bugs, blogs or whatever about any other part of 5.6.

Posted by guest on November 25, 2012 at 06:14 AM PST #

Post a Comment:
Comments are closed for this entry.

I'm James Day, one of the more senior support engineers in Oracle's MySQL team. A former developer, I've had the pleasure of being featured at a MySQL User Conference twice to receive awards, once in 2005 on behalf of Wikipedia as Application of the Year and in 2012 as one of the Community Contributors of the Year for my comments on the blog entries of other people.


Top Tags
« July 2016