Morgan Tocker's defaults, for everything actually in MariaDB, seem quite sane to me. Ref 1 - bit dated.

Ref 2 interesting rant:

bad points:

• seems to oversimplify transactions by assuming the API are the same as the data
• character collations - rtfm and stop making assumptions.
• transaction isolation level - wrong reason to change and MySQL backed out of this change
• doesn't get that null isn't a value

on the valid good points:

• sql_mode - http://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sql-mode - invalid dates part of STRICT_TRANS_TABLES - part of Tocker's changes
• utf8 - i think where there, +innodb_large_prefix will help with indexes as Tocker point out
• we have computed columns
• conditional index - i'm sure there is a feature request somewhere though probably low priority
• non-transaction DDL/slow alter table - fair point - hard problem - if their south/migration implementer didn't hate mysql could implement pt-online-schema-change in migrations but they won't (with probably good reason)

As pointed out on irc, sql_mode=STRICT_TRANS_TABLES would of helped prevent https://www.bugzilla.org/security/4.2.14/ .

A major release is the only time to change these defaults. 10.2 is a long way off.

So many of these values are already out of date addressing some would be a good start.

So to give this issue some concrete values (from mysql-5.7 changes):

• sql_mode = ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
• innodb_large_prefix = yes
• log_slow_admin_statements ON
• log_slow_slave_statements ON
• long-query-time 2
• log-queries-not-using-indexes ON
• min-examined-row-limit 1000
• group_concat_max_len 1M
• max_allowed_packet 16M
• binlog_format ROW (require for galera)
• innodb_autoinc_lock_mode 2 (required for galera anyway)
• innodb_checksum_algorithm crc32
• innodb_purge_threads 4
• innodb_strict_mode ON
• innodb_log_file_size 128M
• innodb_buffer_pool_dump_at_shutdown ON
• innodb_buffer_pool_load_at_startup ON
• innodb_buffer_pool_dump_pct 25 (Not currently implemented in mariadb)
• innodb_file_format Barracuda
• sync_binlog =1 ( or at most 10)
• slave_net_timeout 60
• performance schema - Enable events_statements_history and events_transactions_history consumers by default
• mysql client prompt="\u@\h [\d] > "

My additions:

• skip-name-resolve (packages can enable on upgrade if host based usernames exist)
• binlog_annotate_row_events ON
• replicate_annotate_row_events ON
• innodb_stats_traditional FALSE
• use_stat_tables complementary
• histogram_size 255
• aria_recover QUICK,BACKUP
• myisam-recover QUICK, BACKUP
• table_definition_cache autosize to max(2000, 400+table_open_cache / 2) like 5.7
• query_cache_strip_comments = ON

If label=order-by-optimisations are resolved:

• optimizer_switch = mrr=on,mrr_sort_keys=on,optimize_join_buffer_size=on
• optimizer_use_condition_selectivity =4
• join_cache_level = 8