推荐初始化配置-MYSQL

推荐配置

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

[mysqld]
# GENERAL #
character-set-server=utf8mb4
server-id=1
port = 3306
user = mysql
socket = /opt/mysql/run/mysql.sock
basedir = /opt/mysql/
open_files_limit = 10240
lower_case_table_names=1
explicit_defaults_for_timestamp
sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#最大并发线程数，一般设置为cpu核心的2倍，或线程的1倍
innodb_thread_concurrency=32
table_open_cache=2048
default_time_zone = '+8:00'
log_timestamps = system

# DATA STORAGE #
datadir = /opt/mysql/data
tmpdir = /tmp

#connect
max_connections=4000
max_user_connections=2000
wait_timeout=300
interactive_timeout=300
#瞬时能够接收的连接数，高并发时需要配置最大连接的20%~30%
back_log=1200

#Buffer
max_allowed_packet = 128M
max_heap_table_size = 512M
net_buffer_length = 16k
sort_buffer_size = 16M
join_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
#下面的参数如果数据库存在重复提交的行为，则建议开启，默认为0，不开启
query_cache_type=0
#缓存大小
query_cache_size=0 

#Log#
#下面参数如果写入非常高，建议为2，视具体系统调整
innodb_flush_log_at_trx_commit=1
sync_binlog=1
log-bin = /opt/mysql/binlog/mysql-bin
expire-logs-days = 14
binlog_cache_size = 64M
max_binlog_cache_size = 6000M
max_binlog_size = 128M
binlog_format = row
log_output = FILE
log-error = /opt/mysql/log/mysql-error.log
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /opt/mysql/log/slow_query.log
#审计日志开启，开启后日志量非常大，需要定期清理备份
general_log = 1
general_log_file = /opt/mysql/log/general_query.log

#InnoDB
#innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
#配置内存的50%~80%
innodb_buffer_pool_size = 32G

#slave 需要配置主从的时候，放开下面参数，并修改server_id与主库不同即可
#relay_log =/opt/unicloud/mysql/relaylog/mysql-relay-bin
log_slave_updates = 1
#read_only=1

#password and connect 以下配置需安装完安全插件及密码插件才可以放开
#validate_password_check_user_name=ON
#validate_password_length=8
#validate_password_mixed_case_count=1
#validate_password_number_count=1
#validate_password_policy=MEDIUM
#validate_password_special_char_count=1
#connection_control_failed_connections_threshold=5
#connection_control_min_connection_delay=30000

#GTID 默认不开启，需要开启时取消注释，重启服务即可#
#gtid_mode=ON
#enforce_gtid_consistency=ON

[mysql]
no-auto-rehash
prompt = (\u@\h)[\d]>\_
default-character-set = utf8mb4

配置说明

添加了gtid的配置，没有开启，只有需要click hous链接时才需要开启

#gtid_mode=ON

#enforce_gtid_consistency=ON

调整了审计日志为默认开启

general_log = 1

添加了时区默认为东八区，日志默认时间为系统时间

default_time_zone = '+8:00'

log_timestamps = system

添加了初始安全参数的配置，需安装安全模块和密码模块

#password and connect 以下配置需安装完安全插件及密码插件才可以放开

#validate_password_check_user_name=ON

#validate_password_length=8

#validate_password_mixed_case_count=1

#validate_password_number_count=1

#validate_password_policy=MEDIUM

#validate_password_special_char_count=1

#connection_control_failed_connections_threshold=30000

安装方法：

install pluginvalidate_password SONAME 'validate_password.so';

install plugin CONNECTION_CONTROL soname 'connection_control.so';

install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.so';