CentOS 7 下 MySQL 5.7.x 安全配置

CentOS 7 下 MySQL 5.7.x 安全配置

密码策略配置

• 查看现有密码策略

mysql> show VARIABLES like "%password%";
+---------------------------------------+--------+
| Variable_name                         | Value  |
+---------------------------------------+--------+
| default_password_lifetime             | 0      |
| disconnect_on_expired_password        | ON     |
| log_builtin_as_identified_by_password | OFF    |
| mysql_native_password_proxy_users     | OFF    |
| old_passwords                         | 0      |
| report_password                       |        |
| sha256_password_proxy_users           | OFF    |
| validate_password_check_user_name     | OFF    |
| validate_password_dictionary_file     |        |
| 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      |
+---------------------------------------+--------+
14 rows in set (0.00 sec)

审计日志配置

• mysql5.7添加日志审计插件audit-plugin
• Mysql5.7安装server_audit审计
• MariaDB Audit Plugin Options and System Variables
• 如何在CentOS 7上使用Linux审计系统
• CentOS 7 日志审计
• 博客园 – 行木辛 – audit

通用日志

• MySQL5.7 四种日志文件

• 显示版本号 && 存储引擎等

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.25                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.25                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

• 当前的通用日志是否开启

mysql> show variables like '%general%';
+------------------+-------------------------+
| Variable_name    | Value                   |
+------------------+-------------------------+
| general_log      | ON                      |
| general_log_file | /var/lib/mysql/demo.log |
+------------------+-------------------------+
2 rows in set (0.00 sec)

set global general_log = on;
set global general_log_file = "/home/mysql/logs/demo.log"

• 慢查询日志

mysql> show variables like '%quer%';
+----------------------------------------+------------------------------+
| Variable_name                          | Value                        |
+----------------------------------------+------------------------------+
| binlog_rows_query_log_events           | OFF                          |
| ft_query_expansion_limit               | 20                           |
| have_query_cache                       | YES                          |
| log_queries_not_using_indexes          | OFF                          |
| log_throttle_queries_not_using_indexes | 0                            |
| long_query_time                        | 10.000000                    |
| query_alloc_block_size                 | 8192                         |
| query_cache_limit                      | 1048576                      |
| query_cache_min_res_unit               | 4096                         |
| query_cache_size                       | 1048576                      |
| query_cache_type                       | OFF                          |
| query_cache_wlock_invalidate           | OFF                          |
| query_prealloc_size                    | 8192                         |
| slow_query_log                         | ON                           |
| slow_query_log_file                    | /var/lib/mysql/demo-slow.log |
+----------------------------------------+------------------------------+
15 rows in set (0.00 sec)

set global slow_query_log = on;

• 错误日志

mysql> show variables like '%log_error%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| binlog_error_action | ABORT_SERVER        |
| log_error           | /var/log/mysqld.log |
| log_error_verbosity | 3                   |
+---------------------+---------------------+
3 rows in set (0.00 sec)

• 二进制日志 – 否启用二进制日志

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
6 rows in set (0.00 sec)

• 二进制日志 – 所有的二进制参数

mysql> show variables like '%binlog%';
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+
22 rows in set (0.00 sec)

• 二进制日志 – 查看文件的位置

mysql> show variables like '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

用户权限

• 创建数据库超级管理员 – mysqlAdmin

CREATE USER 'mysqlAdmin'@'%' IDENTIFIED BY 'mysql@Admin1234!';
GRANT ALL ON *.* TO 'mysqlAdmin'@'%' WITH GRANT OPTION;

• 禁用 root 远程登录 – Test@MySQL1234!

mysql -u root -p 						# 登录数据库
use mysql;								# 切换到 mysql 数据库
select host,user from user; 			# 查看账户，可登录地址
update user set host = "localhost" where user = "root" and host = "%"; 		# 更改 root 登录权限
delete from user where user = "root" and host = "%"; 						# 直接删除此行
UPDATE user SET Host='%' WHERE User='root' AND Host='localhost' LIMIT 1; 	# ？？？
flush privileges; 						# 刷新更改

- [mysql 禁止root远程访问](https://www.jianshu.com/p/3eb0bcf1448d)

• 创建（单一数据库）管理员 – dbAdmin

CREATE USER 'dbAdmin'@'%' IDENTIFIED BY 'db@Admin1234!';
GRANT ALL PRIVILEGES ON test.* TO 'dbAdmin'@'%' WITH GRANT OPTION;

• 创建（单一数据库）读账户 – dbReader

CREATE USER 'dbReader'@'%' IDENTIFIED BY 'db@Reader1234!';
GRANT SELECT ON test.* TO 'dbReader'@'%';

• 创建（单一数据库）写账户 – dbWriter

CREATE USER 'dbWriter'@'%' IDENTIFIED BY 'db@Writer1234!';
GRANT CREATE,SELECT,Insert,Update,Delete ON test.* TO 'dbWriter'@'%';