Mysql 安全配置
EraserRain 2022/3/25 mysql
# Reference
【MySQL安全配置详解 - 简书 (jianshu.com) (opens new window)】
【MySQL 数据库安全加固 | Coding and Fixing (vxhly.github.io) (opens new window)】
# 概述
记录下 mysql 常用的安全配置。
mysql 配置文件： /etc/my.cnf
# 安全配置
# mysql 设置最大最小连接数
查看最大连接数
show variables like 'max_connections';
1
MySQL默认的最大连接数为100，MySQL服务器允许的最大连接数16384。
my.cnf 配置
[mysqld]
max_connections = 256
1
2
2
# 设置 SSL 连接
【Mysql配置ssl证书 - 知乎 (zhihu.com) (opens new window)】
MYSQL 5.7 版本可以通过 mysql_ssl_rsa_setup 自动生成 SSL 证书。
# 更改 root 用户名
use mysql;
select user,host from mysql.user;
update user set user='sysroot' where user='root';
flush privileges;
//or
rename user root@'localhost' to sysroot@'localhost';
flush privileges;
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
修改用户密码
set password for [user]@[ip]=password('password')
1
# 删除匿名账号
delete from mysql.user where user='';
delete from mysql.db where user='';
delete from mysql.tables_priv where user='';
delete from mysql.columns_priv where user='';
delete from mysql.procs_priv where user='';
1
2
3
4
5
2
3
4
5
删除登录主机可以是任意主机的MySQL账号
delete from mysql.user where host='%' or host='';
delete from mysql.db where host='%' or host='';
delete from mysql.tables_priv where host='%' or host='';
delete from mysql.columns_priv where host='%' or host='';
delete from mysql.procs_priv where host='%' or host='';
1
2
3
4
5
2
3
4
5
# 禁止远程连接
my.cnf 配置
[mysqld]
bind_address=localhost
skip-networking=on
1
2
3
2
3
# 日志配置
my.cnf 配置
[mysqld]
general_log_file=/server/abchosting/database/mysql-general.log
general_log=0
log_slow_queries=/server/abchosting/database/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
1
2
3
4
5
6
7
2
3
4
5
6
7
运行时开启
set global log=on; ## 打开日志功能
set global general_log = 'ON';
set global slow_query_log = 'ON';
1
2
3
2
3
# 用户权限配置
移除用户权限
revoke insert,update,create,drop on *.* from trunkey@'localhost';
1
# 打开安全验证
set global secure_auth=on;
1