foreword
📫 About the author: June Blizzard Fei Lihua, focusing on Java research, working as a back-end intermediate engineer in a technology company, good at back-end architecture design
🔥 Sanlian Support: If this article is not bad, please ❤️ Follow, 👍 Like, 👉 Favorite Sanlian, and support the blogger~
1 Background overview
1.1 Authorized users cannot query the table?
It's like this. A few days ago, a colleague of mine needed to create a user and authorize several business tables to be used by colleagues in the partner group. Script creation, providing authorization. When it was used by a partner, the response from the partner group was that the query table could not be found, and a whole oolong was given. Since it was a project that was temporarily accepted in the middle of the road, I didn't know what my predecessor was messing with. After checking the logs, it was found that the MySQL audit was set up. Well, let's talk about it in a shallow way today, as a growth.
1.2 What is MySQL auditing? What specification to follow?
MySQL Enterprise Edition includes MySQL Enterprise Audit, implemented using a server plug-in. MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity performed on specific MySQL servers. Designed to meet Oracle Audit Specifications, MySQL Enterprise Audit provides an out-of-the-box, easy-to-use auditing and compliance solution for applications subject to internal and external regulatory guidelines. Once installed, the audit plugin enables the MySQL server to generate log files containing audit records of server activity. Log content includes when clients connect and disconnect, and what they do while connecting, such as the databases and tables they access.
For the specification of Oracle audit, you can refer to the official white paper document.
Audit overview: https://docs.oracle.com/cd/E26926_01/html/E25889/auditov-1.html#scrolltoc
Audit White Paper: https://www.oracle.com/cn/a/tech/docs/technical-resources/bwp-security-audit-vault.pdf
1.3 What problems does MySQL audit solve?
// to do
2 Local experimental environment
For MySQL Professional Edition (MySQL Enterprise Edition) audit, please refer to the official solution and operation steps: MySQL Enterprise Edition Audit , let's talk about MySQL Community (GPL) How is the community version self-built test environment audit set up? This article is an experiment under the GNU/Linux (CentOS 7 x86_64) environment.
MySQL auditing provides several ways to talk about it, and lists them one by one according to the steps of their own experimental operations, and provides them with reference for everyone. Comments are welcome, and I am very grateful. also hope MySQL Advanced Skill Tree Advanced can have this practice.
Three ways of MySQL audit:
- General Query Log
- BinLog + init_connect
- Audit plugin
Let's talk about this general_log first.
2.1 general_log (not recommended)
By default, MySQL does not enable General Query Log; after enabling General Query Log, MySQL records all SQL statements that arrive at MySQL Server.
about MySQL logs , the official has given a clear explanation and description. From the literal meaning, there are usually the following logs:
| log type | name | ] log information |
|---|---|---|
| Error log | error log | Problems starting, running, or stopping mysqld |
| General query log | General query log | Established client connections and statements received from clients |
| Binary log | binary log | Statements that change data (also used for replication) |
| Relay log | relay log | Data changes received from the replication source server |
| Slow query log | slow query log | Query that takes longer than long_query_time to execute for a few seconds |
| DDL log (metadata log) | DDL log (metadata log) | Metadata operations performed by DDL statements |
2.2.1 View general_log settings
Execute the SQL command to see that the default general_log is OFF, and the SQL keyword statements and attribute values are case-insensitive (this can be set).
mysql> show variables like 'general_log';
2.2.2 View log files
After the General Query Log is opened, all SQL accesses will be recorded in the log file pointed to by general_log_file. Regarding the value of the global variable general_log_file and the location of the log file, you can execute the following command to get it.
mysql> show variables like 'general_log_file';
By default, the log file will be placed under your MySQL file, and the naming rule is machine name.log. For example, my mysql log is placed in /usr/local/mysql-8.0.18-macos10.14-x86_64/data/ (Usually, we call /usr/local/mysql-8.0.18-macos10.14-x86_64/ MySQ_HOME) directory, the machine name is Macbook, then the log file here is Macbook.log.
First, we can take a look at what is stored in this file.
Aion.Liu $ tail -200f /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log tail: /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log: No such file or directory
No such file or directory This prompts us that there is no such file or directory? In fact, this also verifies the accuracy of our first step, which is turned off by default.
2.2.3 Manually open General Query Log
When executing mysql> set global general_log = on;, we set the global variable general_log to the open state, and check again that the general_log is open and is in the ON state.
When we opened it, we followed the steps of 2.2.2 to check the contents of the log file again. The surprise came, and there was text in it (I intercepted the part below).
Aion.Liu $ tail -200f /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log alert_notification.is_default, alert_notification.disable_resolve_message, alert_notification.send_reminder, alert_notification.frequency FROM alert_notification WHERE alert_notification.org_id = 1 AND ((alert_notification.is_default = '1')) 2022-08-30T14:19:49.933094Z 3678 Prepare SELECT alert_notification.id, alert_notification.uid, alert_notification.uid FROM alert_notification WHERE alert_notification.org_id = 1 AND alert_notification.id = 1 2022-08-30T14:20:35.933719Z 3608 Close stmt 2022-08-30T14:20:35.933977Z 3678 Prepare SELECT `id`, `org_id`, `version`, `name`, `type`, `access`, `url`, `password`, `user`, `database`, `basic_auth`, `basic_auth_user`, `basic_auth_password`, `with_credentials`, `is_default`, `json_data`, `secure_json_data`, `read_only`, `uid`, `created`, `updated` FROM `data_source` WHERE `id`=? AND `org_id`=? LIMIT 1 2022-08-30T14:20:35.934139Z 3678 Execute SELECT `id`, `org_id`, `version`, `name`, `type`, `access`, `url`, `password`, `user`, `database`, `basic_auth`, `basic_auth_user`, `basic_auth_password`, `with_credentials`, `is_default`, `json_data`, `secure_json_data`, `read_only`, `uid`, `created`, `updated` FROM `data_source` WHERE `id`=4 AND `org_id`=1 LIMIT 1 2022-08-30T14:20:35.934361Z 3678 Close stmt 2022-08-30T14:20:35.935332Z 3608 Prepare SELECT alert_notification.id, alert_notification.uid, alert_notification.org_id, alert_notification.name, alert_notification.type, alert_notification.created, alert_notification.updated, alert_notification.settings, alert_notification.secure_settings, alert_notification.is_default, alert_notification.disable_resolve_message, alert_notification.send_reminder, alert_notification.frequency FROM alert_notification WHERE alert_notification.org_id = ? AND ((alert_notification.is_default = ?)) 2022-08-30T14:20:35.935422Z 3608 Execute SELECT alert_notification.id, alert_notification.uid, alert_notification.org_id, alert_notification.name, alert_notification.type, alert_notification.created, alert_notification.updated, alert_notification.settings, alert_notification.secure_settings, alert_notification.is_default, alert_notification.disable_resolve_message, alert_notification.send_reminder, alert_notification.frequency ......
Check the size of the current log file, which is 324K. Here, after we open the log, the log will be written to the log file in real time.
Aion.Liu $ du -h /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log 324K /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log
2.2.4 Experiment
1) We arbitrarily execute some statements in the command line tool window. Here I will query the number of table records in table1.
2) At this time, let's see how the contents of the log file are recorded
It's easy to understand, the query type and the content of the executed statement have been recorded in the log.
3) Use the client tool to execute SQL commands to query the two column data of the abc table in my local experimental database study.
4) At this time, we query the log files in the past.
Is it easy to understand, the operations performed on the server at this time will be recorded in the set log file Macbook.log.
5) We turn off the global log settings and try
Execute the command as follows
mysql> set global general_log = off; Query OK, 0 rows affected (0.00 sec)
View log file contents
tail -200f /usr/local/mysql-8.0.18-macos10.14-x86_64/data/Macbook.log
Since I have enabled scheduling events and some triggers here, the SQL statement has been executed, so a lot of log information will be printed. When we close again, the log will no longer be printed, and the last line tells us that the log has been closed. Will all operations be recorded in the log? If you are interested, you can try it locally. At this point, there is no need for me to say more about the // to do left behind.
3 Summary
When the General Query Log is turned on, MySQL will record the log as long as the user performs the operation, regardless of right or wrong. In this case, the log volume will be very large, which will affect the efficiency of the database. Therefore, we generally do not recommend enabling the open function. In some cases, it may be temporarily opened for a period of time for troubleshooting.