MySQL保障安全不公开root账户授权

MySQL保障安全不公开root账户授权

为了确保MySQL数据库的安全性，建议不要公开root账户的授权，以下是一些建议和方法来实现这一目标：

1、创建新用户并授权

创建一个具有特定权限的新用户，而不是使用root账户进行操作，可以创建一个名为newuser的用户，并为其分配适当的权限。

“`sql

CREATE USER ‘newuser’@’localhost’ IDENTIFIED BY ‘password’;

GRANT ALL PRIVILEGES ON *.* TO ‘newuser’@’localhost’ WITH GRANT OPTION;

FLUSH PRIVILEGES;

“`

2、限制远程访问

如果需要从远程主机访问MySQL数据库，请确保仅允许特定的IP地址或主机名进行连接，可以通过修改MySQL配置文件（如my.cnf或my.ini）来实现这一点。

在[mysqld]部分添加以下内容：

“`

bindaddress = 127.0.0.1

“`

这将限制MySQL服务器仅接受来自本地主机的连接，如果要允许特定的远程主机连接，可以使用以下配置：

“`

bindaddress = 192.168.1.100

“`

3、使用SSL加密连接

为了提高安全性，建议使用SSL加密连接，需要在MySQL服务器上生成证书和密钥，将证书和密钥文件存储在安全的位置，并在客户端配置中指定它们。

在MySQL服务器上生成证书和密钥：

“`bash

sudo mysql_ssl_rsa_setup datadir=/var/lib/mysql/ certfile=/etc/mysql/servercert.pem keyfile=/etc/mysql/serverkey.pem

“`

在客户端配置中指定证书和密钥：

“`bash

[client]

user = newuser

password = password

sslca = /etc/mysql/servercert.pem

sslcert = /etc/mysql/clientcert.pem

sslkey = /etc/mysql/clientkey.pem

“`

4、定期更新密码和权限

为了确保数据库的安全，建议定期更新用户的密码和权限，可以使用以下命令来更改用户的密码：

“`sql

ALTER USER ‘newuser’@’localhost’ IDENTIFIED BY ‘newpassword’;

“`

5、监控和审计日志

启用MySQL的审计插件以记录所有对数据库的访问尝试，这有助于检测和防止未经授权的访问，要启用审计插件，请按照以下步骤操作：

安装审计插件：sudo aptget install libauditpluginsmysql（Debian/Ubuntu）或sudo yum install auditlibsmysql（CentOS/RHEL）

编辑MySQL配置文件（如my.cnf或my.ini），在[mysqld]部分添加以下内容：

“`

log_output = TABLE audit_log_file = /var/log/mysql/audit.log general_log = 1 local_general_log = 1 general_log_file = /var/log/mysql/general.log long_query_time = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log server_id = 1 skipnameresolve skiphostcache skipshowdatabase skipevents_statements_application_latencies skipstatus update user set global event_scheduler = ON on audit_log_policy = ALL enable_audit_log_trigger = ON audit_log_filter = NULL audit_log_format = JSON audit_log_file_maintenance = ON audit_log_expire_date = NONE audit_log_rotation_age = 0 audit_log_rotation_size = 0 audit_log_space_limit = 0 audit_log_strategy = ALL audit_log_handlers = JSON,UNIX_LOGFILE,EXTENDED audit_connections = ON audit_tmpdir = /tmp audit_max_file_size = 1G audit_max_queued_connections = 500 audit_min_length = 8 audit_tablespaces = INNODB,ARIA,CSV,NONE audit_flush = IMMEDIATE audit_syslog = ON audit_logsyslog = ON audit_logerror = ON audit_hostname = %HOSTNAME% audit_pid = %PID% audit_socket = /var/run/mysqld/mysqld.sock audit_port = 3306 audit_enable_statechanges = ON audit_enforcedprivileges = NONE audit_skippedhosts = NONE audit_skippedusers = NONE audit_skippeddbs = NONE audit_skippedtables = NONE audit_skippedcolumns = NONE audit_skippedevents = NONE audit_ignoredusers = NONE audit_ignoreddbs = NONE audit_ignoredtables = NONE audit_ignoredcolumns = NONE audit_ignoredevents = NONE audit_ignoredcommands = NONE audit_ignoredconnections = NONE audit_ignoredstatements = NONE audit_ignoredresultsets = NONE audit_ignoredwarnings = NONE audit_ignorederrors = NONE audit_ignoredtimeouts = NONE audit_ignorednoops = NONE audit_ignoredauthentications = NONE audit_ignoredlocks = NONE audit_ignoredmetadatachanges = NONE audit_ignoredtransactions = NONE audit_ignoredtemporalchanges = NONE audit_ignoredautoincchanges = NONE audit_ignoredbinlogchanges = NONE audit_ignoredxachanges = NONE audit_ignoredenginechanges = NONE audit_ignoredrowlevelevents = NONE audit_ignoredstatementthrottles = NONE audit_ignoredreplicationapplierdelays = NONE audit_ignoredreplicationappliererrors = NONE audit_ignoredreplicationapplierwarnings = NONE audit_ignoredreplicationapplierstatusupdates = NONE audit_ignoredreplicationapplierheartbeats = NONE audit_ignoredreplicationapplierstatusmessages = NONE audit_ignoredreplicationapplierschemachanges = NONE audit