Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand MySQL server security". The explanation content in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian slowly and deeply to study and learn "how to understand MySQL server security" together!

MySQL Server Security

MySQL server installation for self-built computer rooms will go through network configuration, storage planning, installation of servers, and installation of MySQL.

storage security

Self-built MySQL server

For self-built MySQL servers, RAID disk redundant arrays are generally used in order to avoid single point failure of disks.

There are two modes: Hardware RAID and software RAID, hardware RAID needs to buy RAID card support, software RAID can be generated by software package mdadm, production environment recommended to choose hardware RAID, generally need to do RAID for multiple disks, for database servers, usually choose RAID10(also known as RAID 1 + 0, first mirror RAID1, then stripe RAID0 storage), read and write performance is better.

In the actual production environment, there are usually 8 disks, each disk 800G, when the server starts, first do disk RAID partition, usually divided into two volume groups (volume group), one is the operating system built-in directory using rootvg, the other is MySQL data-related volume group datavg, the main steps of LVM operation are as follows:

Through the above storage division and configuration RAID10, MySQL generated data will be written to the/data directory, synchronized to the logical volume lvdata, and then synchronized to the physical volume, and finally synchronized to the physical disk mirror storage, effectively avoiding the single disk damage caused by data unavailable.

Public cloud RDS

For RDS, the online environment generally selects the high availability version, that is, one master and one slave mode. By default, Slave only performs failover(failover), mainly because the disks of Master and Slave servers are not RAID.

network security

Set Whitelist

For self-built computer rooms, hardware firewalls are generally used for network isolation and IP whitelist restrictions. Only specified application servers are allowed to connect to MySQL Server through port 3306. Other malicious requests should be intercepted at the firewall level. For example, the data security of RDS console can be added with specified IP.

public network access

The database server only needs to be accessed from the intranet. It is forbidden to open an external network address or a public network address.

operating system security

After installing the operating system (such as CentOS 7.4), security vulnerability scanning and regular inspections should be carried out, mainly in the following aspects:

root Start MySQL

After MySQL Server is installed, if MySQL is started with root, any user with FILE system permissions can read and write files under root user, which will cause serious security risks. You should set the software directory owner to root user, and the data directory owner to mysql user. Use mysql user to start MySQL instance.

Account weak password

All account passwords of Linux server cannot be weak passwords (for example, passwords are pure numbers, pure letters, part of the account, etc., the length is too short). It is recommended that all accounts be set to 20 digits in length, including numbers, upper case letters, lower case letters and special characters.

Open port

For MySQL database servers, except for port 3306, which is open to specified application servers, other ports should be disabled, such as ssh, ftp, and telent service ports.

service vulnerability

When using green alliance or other tools for security scanning, some system vulnerabilities will be found, such as ntp vulnerabilities and vsftpd vulnerabilities. At this time, you need to stop to patch the repair, scan again, and install MySQL until all pass.

password login

For the production server, it is strictly prohibited to log in directly through the password locally. It is recommended to use the jumpserver mode. Each user logs in with his/her jumpserver account and then selects the server he/she wants to access (the administrator needs to be informed in advance to add). The default login user (such as the server user) has only read-only permission and cannot make any modification operations.

audit function

All user logins to the production server via jumpserver should be audited and screened, and replayed periodically to ensure that each step is optimal and that there are no unnecessary multiple actions.

Operation Command History

All the commands that users log on to Linux servers will be recorded in history. Set the history larger to save more command records.

resource constraints

Limit user resource usage by configuring the/etc/security/limits.conf file, such as the number of open processes, the number of files, file size, and memory.

server backup

Regular backup of the server, the backup content mainly includes Linux built-in directory (/usr /var /lib) and MySQL data directory (data file, binlog, undolog, redolog), at the same time to regularly check the validity of the backup and recovery drills, the following is Alibaba Cloud ECS snapshot, it is recommended to take a snapshot every day, keep 7 days.

MySQL Database Security

account security

account password

It is recommended that all accounts be 20 characters long, including numbers, upper case letters, lower case letters and special characters, such as ^N9UxOR&ydQWCBvIwqql

account separation

For distributed systems, there will be multiple services, and there will be invocation relationships between services, such as trade-service will invoke payment-service.

For security reasons, create a database for each service, assign your own account, and prohibit cross-database access.

account permissions

Ensure that account permissions are minimized. For business accounts, you only need to grant permission to connect, query, add, and modify.

use an SSL connection

For businesses with high data security requirements, it is recommended to enable SSL connection, so that even if you use the package capture tool to analyze, you cannot see the specific SQL text. However, from the perspective of SSL implementation, handshake, encryption, decryption and other operations are required when establishing a connection. The connection establishment stage is time-consuming. If a connection pool or a long connection is used, there will be little impact. If it is a short connection, the performance loss is relatively large.

system privileges

mysql.user permissions

Except MySQL root account, any other account has modification permission to mysql.user table, which brings security risk: account password is modified and authentication plug-in is modified.

Change account password

Change password authentication method

MySQL defaults to mysql_native_password, which is a local password authentication plug-in. If it is changed to auth_socket, no password is required. Just check whether the user uses UNIX sockets to connect, and then compare the user names.

authority risk

Generally, ordinary developers are allowed to log in to the transit machine through jumpserver, and then use mysql -h -u -p to access the database as a read-only account. Sometimes, they also want to see relevant information such as long transactions (innodb_trx) under information_schema. It is necessary to grant process system management permission, which will bring some security risks.

In this way, a read-only account can also view SQL statements being executed by all business accounts in innodb_trx table. SQL may contain sensitive information.

In previous versions of MySQL, users with process system privileges could also lock system tables, such as lock table mysql.user read, causing normal modification of user actions.

FILE permissions

With FILE permission, you can pass data through SELECT... INTO OUTFILE….. Write to the directory on the server with write permission, store it as text format, or import text file data into the data table by LOAD DATA INFILE.

audit function

After installing MySQL Server, you should configure and open Audit Plugin, so that all SQL executed by MySQL Server will be recorded, on the one hand, you can eliminate problems, on the other hand, you can do performance monitoring analysis (such as QPS of a SQL within a period of time, TPS).

data backup

You need to backup data files and binlogs regularly. For self-built MySQL servers, you can use xtrabackup to do daily physical full backup. For RDS, you need to configure backup policies and check the integrity and effectiveness of backups regularly.

remote disaster recovery

For core business systems, such as transactions, payments, etc., it is necessary to do a good job in remote disaster recovery of databases to avoid unrecoverable data caused by natural disasters such as earthquakes.

data security

Data is a valuable resource for enterprises. The data generated by each business domain and the data needed are different. How to ensure safe access to data is very important. Generally, large companies have their own data management platforms, such as Ali's iDB, which is now a service DMS(Data Management Service) that can manage various types of databases.

data access normalization

All data changes should go through the approval process. The initiator and the approver cannot be the same. Important and sensitive data operations need to submit a work order for multi-level approval. Only after confirming that there are no problems can they be executed. Before execution, backup should be made so that they can be used in rollback.

It is recommended to use the database management platform to manage the database. Generally, users need to register accounts, apply for permissions (data export, structure change, data change), serve Owner approval, submit work orders to enter the approval process, etc. All operation links will be recorded in the operation log.

data masking

Sensitive fields of core business tables need to be desensitized, such as name, email address, telephone number and ID card number, which can be processed by using an intermediate platform. For example, DMS of Alibaba Cloud can configure data desensitization and support three levels of database, table and field. By default, queries on the platform are displayed with *. If you want to view plaintext, you need to apply for sensitive data permission and submit a work order. Only after approval can you view it. The permission time defaults to one day. No longer than 7 days.

Before doing marketing activities, database performance pressure test will be done. It is necessary to import the full amount of PROD database into UAT environment, which will involve some sensitive data. Generally, desensitization processing needs to be done through the middle layer. For example, the first 3 digits and the last 3 digits of mobile phone number can be coded, 15222210123 desensitized into 152***123.

data encryption

Starting with MySQL 5.7, InnoDB supports encryption of static data in independent tablespaces. It is an encryption method at the internal data page level of the engine. It encrypts data pages when they are written to the file system and decrypts them when they are read from the data file to memory. It works based on rotating key files and is very useful for data protection.

The master key file is stored on disk and backed up. For encrypted tables, the table space key is stored in the head of the table space file by the master key. The encryption algorithm used is AES, and the encryption mode is block encryption.

Let's look at the difference between ** encrypted ENCRYSTATION ='Y' and unencrypted ENCRYSTATION ='N'**

It can be seen from this that through the encryption method of Keyring, the master key is stored in the header information of the table space file, which enhances the security of the data file. For chat messages generated by some social software, this method can be used to encrypt the data file to prevent the data file from being stolen and then recovered.

delayed slave library

Oracle database has two features: recycle bin and flashback function, drop table If you do not add the purge option, Oracle will rename this table to a new table name and store it in the recycle bin. When you need to restore, you can execute flashback table.

Can delete be restored?

It is also possible to use flashback query to get the data before the deletion time in Undo Log, and insert it back to the original table. These two functions are great, but MySQL does not have them.

Usually MySQL master-slave replication is a real-time consistent replication, and changes performed by the Master are immediately synchronized to Slave execution through binlog replication.

If you don't want Slave to execute Master changes so quickly, you can configure delayed slave libraries, which can be used to achieve rapid recovery of data deletion errors, such as delaying one hour.

Thank you for reading, the above is "how to understand MySQL server security" content, after the study of this article, I believe we have a deeper understanding of how to understand MySQL server security, the specific use of the situation also needs to be verified. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.