mysql 5.7 configuration for Linux distro

my.cnf

# For advice on how to change settings please see
# See MySQL 5.7 - http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# MySQL 8.0 - https://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysql]
local_infile=true

[mysqld]
local_infile=true

# if using MySQL 8 or above, default collation is utf8mb4_0900_ai_ci.

# collation_server = utf8mb4_unicode_ci
# collation_server = utf8mb4_0900_ai_ci

character_set_server = utf8mb4
skip_character_set_client_handshake

default_time_zone='+9:00'


#
# 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%.

#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# 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
# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5_20120208
# # Configuration name kccf_dev generated for lesstif@gmail.com at 2018_04_20 15:47:53
#
#
# # GENERAL #
# user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid_file                       = /var/lib/mysql/mysql.pid
#

# # MyISAM #
# key_buffer_size                = 32M
# myisam_recover_options         = FORCE,BACKUP
#
# # SAFETY #
max_allowed_packet             =  512M
# max_connect_errors             = 1000000
#
# # DATA STORAGE #
datadir                        = /var/lib/mysql/
#
# # BINARY LOGGING #
log_bin                        = /var/lib/mysql/mysql_bin
expire_logs_days               = 14
sync_binlog                    = 1
#
# # CACHES AND LIMITS #

# The maximum size of internal in-memory temporary tables. This variable does not apply to user-created MEMORY tables.
tmp_table_size                 = 256M

# This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. 
# The value of the variable is used to calculate MEMORY table MAX_ROWS values. 
# Setting this variable has no effect on any existing MEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. 

# see https://dba.stackexchange.com/questions/209411/rule-of-thumb-for-tmp-table-size-and-max-heap-table-size-mysql-properties
max_heap_table_size            = 256M

## remove from mysql 8
query_cache_type               = 0
query_cache_size               = 0

## 
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048

##

# # INNODB #

# When this option is enabled, index key prefixes longer than 767 bytes (up to 3072 bytes) 
# are allowed for InnoDB tables that use DYNAMIC or COMPRESSED row format.
# innodb_large_prefix is enabled by default in MySQL 5.7. 
innodb_large_prefix=on

# Enables an InnoDB file format for file-per-table tablespaces. Supported file formats are Antelope and Barracuda. 
# Antelope is the original InnoDB file format, which supports REDUNDANT and COMPACT row formats. 
innodb_file_format=barracuda

# When innodb_file_per_table is enabled, tables are created in file-per-table tablespaces by default.
innodb_file_per_table=true

# Defines the method used to flush data to InnoDB data files and log files, which can affect I/O throughput.
# O_DIRECT: InnoDB uses O_DIRECT (or directio() on Solaris) to open the data files, and uses fsync() to flush both the data and log files. 
innodb_flush_method            = O_DIRECT

# The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2. 
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M

# Controls the balance between strict ACID compliance for commit operations and higher performance that is possible 
# when commit-related I/O operations are rearranged and done in batches. 
# You can achieve better performance by changing the default value but then you can lose transactions in a crash.
innodb_flush_log_at_trx_commit = 1

# The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. 
# The default value is 128MB.
# See https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size
innodb_buffer_pool_size        = 2G

#
# # LOGGING #
log_error                      = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql_slow.log

# Disabling symbolic_links is recommended to prevent assorted security risks
symbolic_links=0

# https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html
transaction-isolation=READ-COMMITTED

validate_password_policy=LOW
default_password_lifetime=0           

see also MySQL ini config file for laragon