一、设置数据库允许创建函数

有时候我们在导入数据库,会报错误代码: 1418,

这个原因是我们的mysql没有开启创建函数模块;

1.查看数据库是否允许创建函数

SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

mysql-A01

2.设置数据库允许创建函数

在my.ini的[mysqld]中加入以下命令,保存后重启/重载mysql服务。

set global log_bin_trust_function_creators=1;

二、设置mysql对数据忽略其大小写

编辑vim /etc/my.cnf文件中的[mysqld]下加入以下命令,保存后重启/重载mysql服务。

lower_case_table_names=1;

三、mysql开启日志

1.查看是否开启binlog日志bash

mysql> show variables like "%slow_query_log%";
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | OFF                                       |
| slow_query_log_file | /usr/local/mysql/data/1055-mysql-slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.00 sec)

2.开启mysql日志审计功能

编辑vim /etc/my.cnf文件中的[mysqld]下加入以下命令,保存后重启/重载mysql服务。

log_timestamps=SYSTEM  #日志记录时间的方式:UTC、SYSTEM、
log-bin=mysql-bin #开启binlog日志
binlog_format=mixed  #binlog日志格式
expire_logs_days = 10 #过期删除
slow_query_log=1 #开启慢日志
slow-query-log-file=/www/server/data/mysql-slow.log  #慢日志存储位置

四、mysql数据库超时退出时间

查询mysql超时退出时间

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

设置数据库超时退出

编辑vim /etc/my.cnf文件中的[mysqld]下加入以下命令,保存后重启/重载mysql服务。

interactive_timeout=31536000
wait_timeout=31536000

五、mysql登录错误次数限制connection_control插件

安装插件

mysql> Install plugin connection_control soname "connection_control.so";

查看当前配置

mysql> show variables like "%connection_control%";
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 1000       |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)

插件配置

修改参数

mysql> SET GLOBAL connection_control_failed_connections_threshold = 5; #登录失败5次启用
mysql> SET GLOBAL connection_control_min_connection_delay = 120000; #20分钟
mysql> SET GLOBAL connection_control_max_connection_delay = 180000; #30分钟

-查看修改后的配置

mysql> show variables like 'connection_control%';
+-------------------------------------------------+-------+
| Variable_name                                   | Value |
+-------------------------------------------------+-------+
| connection_control_failed_connections_threshold | 5     |
| connection_control_max_connection_delay         | 240000|
| connection_control_min_connection_delay         | 120000|
+-------------------------------------------------+-------+
  • connection_control_failed_connections_threshold
  • 失败尝试的次数,默认为3,表示当连接失败3次后启用连接控制,0表示不开启
  • connection_control_max_connection_delay
  • 响应延迟的最大时间,默认约25天
  • connection_control_min_connection_delay
  • 响应延迟的最小时间,默认1000微秒,1秒

-- 该表记录登录失败的用户及失败次数,当用户登录成功后,登录失败的记录则会被删除。
-- 重新配置connection_control_failed_connections_threshold变量,该表记录会被删除(重置)
-- 如果使用不存在的用户登录,则该表记录用户名为空,但会记录具体登录的IP

use information_schema;
select * from connection_control_failed_login_attempts;

-- 连接控制的使用次数(可用户判断是否存在暴力登录尝试)
-- 重新配置connection_control_failed_connections_threshold变量,该表记录会被删除(重置)
mysql> show global status like 'Connection_control_delay_generated';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 5     |
+------------------------------------+-------+

六、mysql密码复杂度配置

mysql密码复杂度配置

os: centos 7.4

db: mysql 5.7.28

validate_password 是 mysql5.6以后可以引入的一个新密码校验插件, 管理用户密码长度、强度等。

2.查看mysql版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.28    |
+-----------+
1 row in set (0.00 sec)

mysql> 

2.启用validate_password

mysql 5.7 默认是安装启用 validate_password

ls -l /usr/lib64/mysql/plugin/vali*
-rwxr-xr-x. 1 root root 209512 Sep 27 16:05 /usr/lib64/mysql/plugin/validate_password.so

1
2
3
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL                 | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL                 | GPL     |
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)

低版本可以自己手动安装

mysql> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected, 1 warning (0.07 sec)

查看所有的validate_password相关的参数值

mysql> show global variables like 'validate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   | 默认关闭,设置为ON时可以将密码设置成当前用户名
| validate_password_dictionary_file    |       | 检查密码的字典文件的路径名
| validate_password_length             | 8     |默认为8,限制密码长度的最小字符数
| validate_password_mixed_case_count   | 1     |默认值为1,限制小写字符和大写字符个数 
| validate_password_number_count       | 1     |默认值为1,限制数字的个数
| validate_password_policy             | LOW   |默认值为1, 密码强度等级 [LOW:0| MEDIUM:1 | STRONG:2]
| validate_password_special_char_count | 1     |默认值为1,限制特殊字符个数
+--------------------------------------+-------+
7 rows in set (0.01 sec)
最后修改:2021 年 01 月 10 日 01 : 10 AM
如果觉得我的文章对你有用,请随意赞赏