问题描述
安装完 MySQL 数据库后，如何进行安全性方面的配置？
问题分析
数据库作为存储应用数据的系统，安全性非常重要。首先要保证系统本身的安全，可以删除无效用户、空密码用户，密码加密，禁止本地文件读取等方面进行一些安全方面的配置。
解决方案
1. 将root用户的口令修改为复杂口令，如大小写字母、特殊字符、数字、12位
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'xxxx';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
2. 删除默认数据库和用户
将测试创建的用户 test 删除，如下：
mysql> delete from user where user='test';
Query OK, 0 rows affected (0.00 sec)
删除空密码的 root，如下：
mysql> delete from user where user='root' and authentication_string='';
Query OK, 0 rows affected (0.00 sec)
查看目前数据库用户，如下：
mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | *C039D10921BCFBE67405F907A64DCE5BC1E565EC |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)
3. 修改管理员用户名称
将 root 修改为 toor，如下：
mysql> update user set user='toor' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
进行登录测试，如下：
[root@iv-7gra07d5mdig9u8djm4x ~]# mysql -u toor -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.29 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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>
4. 将涉及密码的字段进行加密存储。
将 users 表中的密码字段加密存储，如下：
mysql> insert into users values (1,'test',SHA1(123456));
Query OK, 1 row affected (0.01 sec)
5. 不用使用root运行mysql，使用独立的mysql用户运行
[root@iv-7gra07d5mdig9u8djm4x ~]# ps -eo pid,user,group,euser,egroup,cmd | grep mysql
1905 mysql mysql mysql mysql /usr/sbin/mysqld
6. 禁止 MySQL 本地文件读取
load data local infile 命令可以泄露敏感信息。 示例：
mysql> load data local infile '/root/sqlfile' into table users fields terminated by ',';
Query OK, 2 rows affected (0.01 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
修改 /etc/my.cnf 文件，如下：
[mysqld]
local_infile=0
再进行测试，如下：
mysql> load data local infile '/root/sqlfile' into table users fields terminated by ',';
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
如果您有其他问题，欢迎您联系火山引擎技术支持服务
