MySQL 8 Configuration for Production Use

my.cnf

[mysql]
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

[mysqld]
# Required Settings
basedir = /usr
bind_address = 127.0.0.1

# localhost which is more compatible and is not less secure.
bind-address = *
mysqlx-bind-address	= 127.0.0.1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

datadir = /var/lib/mysql
max_allowed_packet = 256M
max_connect_errors = 1000000
pid_file  = /var/run/mysqld/mysqld.pid
port = 3306
skip_external_locking
skip_name_resolve
socket = /var/run/mysqld/mysqld.sock

tmpdir = /tmp
user = mysql

default_authentication_plugin= mysql_native_password
 
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB Settings
default_storage_engine = InnoDB
innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 2G
innodb_file_per_table  = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_stats_on_metadata  = 0

innodb_read_io_threads = 128
innodb_write_io_threads = 128

key_buffer_size = 64M

low_priority_updates = 1
concurrent_insert  = 2

# Connection Settings
max_connections = 200

back_log  = 1024
thread_cache_size  = 200
thread_stack = 384K

interactive_timeout = 180
wait_timeout = 180

# Buffer Settings
join_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
sort_buffer_size = 16M

# Table Settings
table_definition_cache = 80000
table_open_cache = 80000
open_files_limit = 90000

max_heap_table_size = 256M
tmp_table_size  = 256M

# Search Settings
ft_min_word_len = 2

# Logging
log_error = /var/log/mysql/error.log
log_queries_not_using_indexes = 1
long_query_time = 5
slow_query_log  = 0
slow_query_log_file = /var/lib/mysql/mysql_slow.log

myisam-recover-options = BACKUP

max_binlog_size = 256M
default_password_lifetime = 0

[mysqldump]
quick
quote_names
max_allowed_packet = 384M