Setting
Old
New
Notes
back_log
50
50 + ( max_connections / 5 ) capped at 900
binlog_checksum
NONE
CRC32
New variable in 5.6. 5.5 doesn't accept the checksums. Use NONE if you want to read binlog with 5.5 or on master if have replication slaves using 5.5.binlog-row-event-max-size
1k
8k
no binlog_row_event_max_size variable, just the option form.flush_time
1800
Windows changes from 1800 to 0
Was already 0 on other platformshost_cache_size
128
128 + 1 for each of the first 500 max_connections + 1 for every 20 max_connections over 500, capped at 2000
New variable in 5.6innodb_autoextend_increment
8
64
64 is 64 megabytesinnodb_buffer_pool_instances
0
8. On 32 bit Windows only, if innodb_buffer_pool_size is greater than
1300M, default is innodb_buffer_pool_size
/ 128M
innodb_concurrency_tickets
500
5000

Note 1: In 5.5 there were already rules to ask the OS for the highest of (10 + max_connections + table_cache_size * 2) or (max_connections * 5) or the specified/default open_files_limit. The default is now calculated but the other rules are still used. If the OS refuses to allow as many as requested, max_connections and table_cache_size are reduced and you will see a “Changed limits” warning in the error log, as before.

Additional recommendations for new applications

Part of continuing to be easy to use means that as well as having a good set of defaults, users also have an expectation of backwards compatibility. So there is a tradeoff that sometimes needs to be taken to make sure that novice users can upgrade their legacy applications with minimal issues.

This tradeoff doesn’t apply to new applications, and in which case I recommend the following additional configuration changes which should be enabled:

UTF-8 is a much smarter default than latin1 today. A simple Google search shows that converting is an issue for a number of users. I would even go so far as to encourage setting skip-character-set-client-handshake to ignore applications trying to set the character-set otherwise.

MySQL (arguably) became popular for not being strict about SQL and allowing you to insert any values. This may have made sense if a poor application did not perform error checking, and the alternative was not to store any data. I think that times have changed now - many users will use frameworks and ORMs, and are happy to catch errors up front, rather than later find their data is corrupt.

Moving forward

We are open to feedback on how we introduce configuration changes. You can leave a comment here, or feel free to contact me.

To get the discussion rolling, what do you think about bundling a small program (mysql-upgrade-defaults) with packages that can optionally be run after installation?

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.