mysql优化

[mysqld]

# 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%.
innodb_buffer_pool_size = 3072M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = minimal
binlog_cache_size = 524288
expire_logs_days = 7
binlog_ignore_db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys

# These are commonly set, remove the # and set as required.
basedir = /mnt/mysql
datadir = /mnt/mysql/data
port = 3306
server_id = 100
# socket = .....
 log_error = error.log
pid-file = /mnt/mysql/data/mysql.pid
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 0
max_connections = 400
max_connect_errors = 100000
explicit_defaults_for_timestamp = 1
max_allowed_packet = 1024M
#query_cache_size = 0
#query_cache_type = 0
# back_log = 50 + (max_connections / 5), max:900
back_log = 130

# 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 = 10M
read_buffer_size = 8M
read_rnd_buffer_size = 8M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

##########################
# innodb
##########################
innodb_file_per_table = 1
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_io_capacity = 2000
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_change_buffering=all
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1