Master the five elements of MySQL database security

MySQL database security settings: comprehensively grasp the five core elements.

In today's digital age, data has become one of the most valuable assets of enterprises.

As a key tool for storing and managing data, the security of MySQL databases is of utmost importance.

However, many developers and administrators tend to focus only on preventing SQL injection attacks, while ignoring other important security aspects.

This article will introduce the five core elements to help you fully grasp the security settings of MySQL database, from password management to firewall settings, we will provide you with detailed guidance and advice.

\n#

1. Password management.

# Strong Password Policy # First, make sure all users (including root users) use strong passwords.

Strong passwords should contain uppercase and lowercase letters, numbers, and special characters, and be at least 12 characters in length.

Avoid using common words or phrases as passwords as these are easy to guess.

-- 创建一个新用户并设置强密码
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd';

# Change password regularly # Changing passwords regularly can reduce the risk of being cracked.

It is recommended to change the password every three months, or as soon as abnormal activity is detected.

# Limit login attempts # Prevent brute force attacks by limiting the number of login failures.

This can be achieved using MySQL's built-in features or third-party tools.

# 在my.cnf中添加以下配置项
[mysqld]
max_connect_errors = 10

\n#

2. User permission control.

# Minimum Permission Principle # Only the minimum permissions required for users to complete their tasks are granted.

For example, if a user only needs to read data, do not grant them write permission.

-- 授予SELECT权限给特定用户
GRANT SELECT ON database_name.* TO 'readonlyuser'@'localhost';

# Remove unnecessary users and permissions # Periodically review users and their permissions in the database, removing users who are no longer needed and redundant permissions.

This helps reduce potential security risks.

-- 删除一个用户
DROP USER 'unnecessaryuser'@'localhost';

\n#

3. Network level protection.

# Binding address # By default, MySQL listens for connection requests on all network interfaces.

To improve security, it can be bound to a specific IP address or only allow local connections.

# 在my.cnf中修改bind-address参数
[mysqld]
bind-address = 127.0.0.1

# Use firewall rules # Use the firewall function that comes with the operating system to restrict access to the MySQL port (usually 3306).

Only trusted IP addresses are allowed to access the port.

# 以Ubuntu为例，使用ufw命令设置规则
sudo ufw allow from trusted_ip to any port 3306
sudo ufw deny 3306

\n#

4. Data encryption and backup.

# Transport Layer Encryption # Enable TLS/SSL encrypted communication to ensure that the data transfer between the client and the server is secure.

This not only protects sensitive information, but also adds an additional layer of security.

# 在my.cnf中启用SSL支持
[mysqld]
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/server-cert.pem
ssl-key=/path/to/server-key.pem

# Regular backup # Backup the database regularly is a very important step to restore data even in the event of a catastrophic event.

You can use the mysqldump tool for physical backup, or use a logical backup tool like Percona XtraBackup.

# 使用mysqldump进行全量备份
mysqldump -u root -p --all-databases > all_databases_backup.sql

\n#

5. Audit logs and monitoring.

# Open audit log # Record all operations on the database, including successful and failed login attempts, and executed SQL statements, etc.

This helps track suspicious activity and respond in a timely manner.

# 在my.cnf中添加general_log相关配置项
[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql.log

# Implement real-time monitoring # Use monitoring tools such as Prometheus + Grafana or Nagios to continuously monitor system performance indicators and security status.

Once abnormal behavior is found, it can quickly locate the problem.

--- Through the comprehensive application of the above five core elements, you can significantly improve the security of MySQL databases.

Of course, as technology develops and new threats emerge, it is very important to remain vigilant and constantly update your security policy.

I hope this article can provide you with a valuable reference!