Document toolboxDocument toolbox

(v1) my.cnf settings MySQL 8

These settings are for a standard hosted server with 2 CPUs and 8GB of RAM

Note: Changes from MySQL 5.7 or earlier

  • ERROR_FOR_DIVISION_BY_ZERO will eventually be fully deprecated and included in MySQL. This is covered by setting "STRICT_TRANS_TABLES" in the SQL mode. For now you will need to use all three of these values in the configuration. 

    NO_ZERO_DATE,NO_ZERO_IN_DATE, and ERROR_FOR_DIVISION_BY_ZERO

  • NO_AUTO_CREATE_USER should be removed, it is now the default behavior. 

  • query_cache settings have been deprecated with MySQL 8

  • symbolic-links are disabled by default in MySQL 8 and it is not required to set this in the configuration file.
cat > /etc/mysql/my.cnf << SQL
#
# The Percona Server 8 configuration file.
#
[mysqld]
#
# Recommended Settings
#
default-authentication-plugin=mysql_native_password
innodb_buffer_pool_size = 5600M    # set value to max out at 70% of the total ram
innodb_log_file_size = 256M        # set a hard limit on the log file size
innodb_log_buffer_size = 1M
innodb_flush_log_at_trx_commit = 1 # this is actually the default, but I put it in here to make it easier to adjust, if needed
innodb_flush_method = O_DIRECT     # avoid double buffering
#
# Make MySQL more strict (for future-proofing MySQL version upgrades)
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO'
#
# Optional, but often useful, tweaks
#
join_buffer_size = 16M
key_buffer_size = 4M
log_queries_not_using_indexes = 0
long_query_time = 7
max_allowed_packet = 32M
max_connections = 30
slow_query_log = 1
slow_query_log_file=/var/log/mysqld-slow.log
table_definition_cache = 800
table_open_cache = 810
thread_cache_size = 8
SQL

systemctl restart mysql
 

Help Desk Portal - Email: help@pcr.com - Phone: 616.259.9242