一、 密码复杂度审计
1.1 查看当前密码策略
MySQL> SHOW VARIABLES LIKE 'validate_password%';
1.2 插件安装
MySQL> install plugin validate_password soname 'validate_password.so';
1.3 my.cnf 参数配置
vim /etc/my.cnf
[mysqld]
plugin-load-add=validate_password.so
validate-password=ON
validate_password_policy = 1
validate_password_length = 8
validate_password_mixed_case_count = 1
validate_password_number_count = 2
validate_password_special_char_count = 1
|
二、 登录失败处理
2.1 数据库并查看是否开启登录失败和超时功能参数
MySQL> show variables like '%connection_control%';
2.2 安装插件
MySQL> install plugin CONNECTION_CONTROL soname 'connection_control.so';
MySQL> install plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS soname 'connection_control.so';
2.3 修改配置文件my.cnf
vim /etc/my.cnf
[mysqld]
plugin-load-add=connection_control.so
connection-control-failed-connections-threshold=5
connection-control-min-connection-delay=300000
|
三、 配置模板
3.1 某生产环境使用的配置文件
[mysqld]
skip_ssl
skip-name-resolve
default_password_lifetime=0
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
port = 3306
server_id = 1
socket = /usr/local/mysql/mysql.sock
pid-file = /usr/local/mysql/mysql.pid
log-error = /data/mysql/data/mysql.err
log-bin = /data/mysql/data/binlog/mysql-bin
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
character-set-server = utf8mb4
log_bin_trust_function_creators=1
explicit_defaults_for_timestamp=true
default_time_zone = "+8:00"
log_timestamps = SYSTEM
explicit_defaults_for_timestamp = 1
table_open_cache = 500
table_definition_cache = 1000
table_open_cache_instances = 64
thread_stack = 512K
thread_cache_size = 700
max_allowed_packet = 256M
join_buffer_size = 4M
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
sort_buffer_size = 2M
max_connections = 3000
max_connect_errors = 2000
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_buffer_pool_size = 48G #重要参数，建议为内存的80%
innodb_buffer_pool_instances = 8
innodb_io_capacity=1200
innodb_page_cleaners=8
log_bin_trust_function_creators=1
binlog_cache_size = 1M
expire_logs_days = 7
binlog_format = row
#
gtid-mode = ON
enforce-gtid-consistency = ON
slow_query_log = 1
slow_query_log_file = /data/mysql/data/slow_query.log
long_query_time= 60
log_queries_not_using_indexes = 1
[mysqldump]
quick
max_allowed_packet = 256M
[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
#default-character-set = utf8mb4
|