1. 前言
为了安全和操作的可追溯性考虑,越来越多的公司加入了审计功能。mysql5.5推出了相关的审计功能,到5.6.20功能进一步完善,算是勉强可用了,虽然细粒度方面做的不是太好,但是后续版本还是可以期待一下的。这里主要介绍下相关的功能和特性。2. 开启审计
2.1 配置文件加载
mysql5.6中的审计是通过audit_log插件来实现的,我们可以在配置文件中加载该插件来开启。[mysqld] plugin-load=audit_log.so
如果希望数据库强制开启审计功能,如果不开启的话server不启动,或者审计功能不能进行时server挂住,加入
[mysqld] plugin-load=audit_log.so audit-log=FORCE_PLUS_PERMANENT
2.2 加载插件列表
审计功能的开启还有另外一种方式,就是在命令行中安装审计插件。确保在数据库的插件目录中存在audit_log.so。[i686数据库的插件目录默认是/usr/lib/mysql/plugin,中,也可以指定参数plugin_dir]mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
3. 参数介绍
审计参数如下:
3.1 audit_log_buffer_size
audit_log_buffer_size :审计缓存,建议设置为4096的倍数,该参数只有在audit_log_strategy为ASYNCHRONOUS时生效。3.2 audit_log_connection_policy
audit_log_connection_policy:记录了连接审计的信息。包含三个参数Value | Description |
---|---|
ALL |
Log all connection events |
ERRORS |
Log only failed connection events |
NONE |
Do not log connection events |
3.3 audit_log_current_session
audit_log_current_session:标志当前会话是否进入审计,是个只读参数,只能通过 audit_log_exclude_accounts和 audit_log_include_accounts来控制哪儿些进入会话审计。3.4 audit_log_exclude_accounts/audit_log_include_accounts
audit_log_exclude_accounts:控制哪儿些用户可以不进入审计,字符串类型,默认可以使用逗号分隔。audit_log_include_accounts:控制哪儿些用户可以进入审计,字符串类型,默认可以使用逗号分隔。
exclude和include同时只有一个参数生效。
3.5 audit_log_file
audit_log_file:可以用于控制审计日志的名称和路径。3.6 audit_log_flush
audit_log_flush:控制审计日志的归档,只有在audit_log_rotate_on_size=0的时候生效,在手工重命名审计日志归档后,可以指定audit_log_flush=1来生成新的审计日志。3.7 audit_log_format
audit_log_format:审计日志的格式,分为OLD和NEW(NEW格式在5.6.14才出现)。当更改格式的时候需要进行3个步骤:1 :关闭数据库 2:重命名当前的audit.log文件
3:更改audit_log_format参数,并重启mysql,重启后会自动生成一个新的audit.log文件
防止NEW格式和OLD格式在同一个审计日志中,会导致审计功能错误。 audit_log_policy :记录了审计日志的控制策略:
Value | Description |
---|---|
ALL |
Log all events |
LOGINS |
Log only login events |
QUERIES |
Log only query events |
NONE |
Log nothing (disable the audit stream |
3.8 audit_log_statement_policy
audit_log_statement_policy:记录了语句的审计策略,可能会被audit_log_policy给覆盖:Value | Description |
---|---|
ALL |
Log all statement events |
ERRORS |
Log only failed statement events |
NONE |
Do not log statement events |
3.9 audit_log_rotate_on_size
audit_log_rotate_on_size:审计日志的文件大小。当参数大于0的时候,当审计日志超过限制后,会自动的重命名为加时间戳后缀的日志文件。同时创建新的审计日志。3.10 audit_log_strategy
audit_log_strategy:审计日志的刷新策略分为:Value | Meaning |
---|---|
ASYNCHRONOUS |
Log asynchronously, wait for space in output buffer |
PERFORMANCE |
Log asynchronously, drop request if insufficient space in output buffer |
SEMISYNCHRONOUS |
Log synchronously, permit caching by operating system |
SYNCHRONOUS |
Log synchronously, call sync() after each request
|
4. 日志格式
审计日志格式是XML的形式,NEW要比OLD的标签详细一些。具体的标签信息如下: 实例:<!--?xml version="1.0" encoding="UTF-8"?--> <timestamp>2013-09-17T15:03:24 UTC</timestamp> <record_id>1_2013-09-17T15:03:24</record_id> <name>Audit</name> <server_id>1</server_id> <version>1</version> <startup_options>/usr/local/mysql/bin/mysqld --socket=/usr/local/mysql/mysql.sock --port=3306</startup_options> <os_version>x86_64-osx10.6</os_version> <mysql_version>5.7.2-m12-log</mysql_version> </audit_record> <timestamp>2013-09-17T15:03:40 UTC</timestamp> <record_id>2_2013-09-17T15:03:24</record_id> <name>Connect</name> <connection_id>2</connection_id> <status>0</status> <status_code>0</status_code> <user>root</user> <os_login></os_login> <host>localhost</host> <ip>127.0.0.1</ip> <command_class>connect</command_class> <priv_user>root</priv_user> <proxy_user></proxy_user> <db>test</db> </audit_record> ... <timestamp>2013-09-17T15:03:41 UTC</timestamp> <record_id>4_2013-09-17T15:03:24</record_id> <name>Query</name> <connection_id>2</connection_id> <status>0</status> <status_code>0</status_code> <user>root[root] @ localhost [127.0.0.1]</user> <os_login></os_login> <host>localhost</host> <ip>127.0.0.1</ip> <command_class>drop_table</command_class> <sqltext>DROP TABLE IF EXISTS t</sqltext> </audit_record> <timestamp>2013-09-17T15:03:41 UTC</timestamp> <record_id>5_2013-09-17T15:03:24</record_id> <name>Query</name> <connection_id>2</connection_id> <status>0</status> <status_code>0</status_code> <user>root[root] @ localhost [127.0.0.1]</user> <os_login></os_login> <host>localhost</host> <ip>127.0.0.1</ip> <command_class>create_table</command_class> <sqltext>CREATE TABLE t (i INT)</sqltext> </audit_record> ... <timestamp>2013-09-17T15:03:41 UTC</timestamp> <record_id>7_2013-09-17T15:03:24</record_id> <name>Quit</name> <connection_id>2</connection_id> <status>0</status> <status_code>0</status_code> <user></user> <os_login></os_login> <host></host> <ip></ip> <command_class>connect</command_class> </audit_record> ... <timestamp>2013-09-17T15:03:47 UTC</timestamp> <record_id>9_2013-09-17T15:03:24</record_id> <name>Shutdown</name> <connection_id>3</connection_id> <status>0</status> <status_code>0</status_code> <user>root[root] @ localhost [127.0.0.1]</user> <os_login></os_login> <host>localhost</host> <ip>127.0.0.1</ip> <command_class></command_class> </audit_record> <timestamp>2013-09-17T15:03:47 UTC</timestamp> <record_id>10_2013-09-17T15:03:24</record_id> <name>Quit</name> <connection_id>3</connection_id> <status>0</status> <status_code>0</status_code> <user></user> <os_login></os_login> <host></host> <ip></ip> <command_class>connect</command_class> </audit_record> <timestamp>2013-09-17T15:03:49 UTC</timestamp> <record_id>11_2013-09-17T15:03:24</record_id> <name>NoAudit</name> <server_id>1</server_id> </audit_record> </audit>
:文件的根标签为,并以 为结束标签
:包含一系列的必选标签和可选标签,可选标签是否出现取决于audit record类型。
以下标签audit record类型决定是否出现
5. 审计限制
审计日志默认存放在data路径下,由于XML文件没有经过加密,官网建议通过参数指定到特殊路径下,设置相应人员权限,进行安全控制。 此外审计功能有如下情况不能进行记录: 1)只有top-level(无变量定义)的语句才能进行审计,存储程序如存储过程,触发器,函数等不审计; 2)涉及到外部文件的语句无法进行审计,如load data infile。
MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

SublimeText3 English version
Recommended: Win version, supports code prompts!

Atom editor mac version download
The most popular open source editor

Dreamweaver Mac version
Visual web development tools