MySQL audit plug-in installation instructions

The purpose of the MySQL audit plug-in installation instructions is to help developers and DBAs install and use the MySQL audit plug-in, and to have a more comprehensive understanding of audit usage and audit granularity. This article describes the design concept, audit granularity, audit configuration instructions, installation and usage, and other content of the audit plug-in detail. On the one hand, it facilitates subsequent maintenance and development, and on the other hand, it provides convenient queries for use. The audit interface of www.2cto.com MySQL provides favorable conditions for the development of audit plug-ins. Based on the audit interface of MySQL, refer to the General log design idea and define the audit type of audit plug-ins: FILE and TABLE are two types used to write audit content to log files or database tables respectively. In addition, the granularity of audit is divided not only according to the type of operation commands, but also further divided into Query command types, divides DDL, DML, and dcl SQL statement types, audits specific users, audits only specified users, and audits user behavior based on audit granularity; audits specific objects (databases and data tables). Currently, only configuration is provided, which is not implemented in the source code. In addition, the audit granularity is marked by bit, and can be customized by bit coverage. The audit granularity audit plug-in refined the audit granularity to specific SQL statements, including: create, drop, alter, ddl (including create, drop, alter), insert, delete, update, modify (including insert, delete, update, replace), select, dml (including insert, delete, update, select), grant, revoke, dcl (including grant, revoke), set, cmd (including ddl, dml, dcl, set), connect, quit, connection (including connect, quit), server (including modifying server-related command operations, such as init db and sleep), full (including all the above operations), all (including all operations, including the extension of all the reserved audit granularity) and none (not audited). These granularity are prefixed with audit _ in the configuration file and the value is 1. /0 (OR on/off ). To control some commands more accurately, the audit_ops parameter is provided. The value of this parameter is an integer, and the specific audit granularity is controlled by the binary bit of an integer. The binary bit of the specific audit granularity corresponding to the integer is as follows: the granularity bit value AUDIT_ALL (~ Lost) lost (ulonglong) lost (AUDIT_CREATE <1) AUDIT_DROP (AUDIT_CREATE <2) lost (AUDIT_CREATE | AUDIT_ALTER | AUDIT_DROP) AUDIT_INSERT ignore (AUDIT_CREATE <3) AUDIT_UPDATE (AUDIT_CREATE <4) AUDIT_REPLACE (AUDIT_CREATE <5) AUDIT_DELETE (AUDIT_CREATE <6) Ignore (AUDIT_INSERT | AUDIT_UPDATE | AUDIT_DELETE | AUDIT_REPLACE) AUDIT_SELECT (ignore E <7) revoke (AUDIT_MODIFY | AUDIT_SELECT) AUDIT_GRANT (AUDIT_CREATE <8) revoke (AUDIT_CREATE <9) AUDIT_DCL (AUDIT_GRANT | revoke) AUDIT_SET (AUDIT_CREATE <10) AUDIT_CMD (AUDIT_DDL | databases | AUDIT_SET) AUDIT_CONNECT (AUDIT_CREATE <11) databases (AUDIT_CREATE <12) databases (AUDIT_CREATE <13) AUDIT_CONNECTION LL (AUDIT_CMD | AUDIT_CONNECTION) AUDIT_SLEEP (AUDIT_CREATE <16) reset (AUDIT_CREATE <17) AUDIT_FIELD_LIST (AUDIT_CREATE <18) AUDIT_REFRESH (AUDIT_CREATE <19) AUDIT_SHUTDOWN (AUDIT_CREATE <20) reset (AUDIT_CREATE <21) AUDIT_PROCESSLIST (AUDIT_CREATE <22) AUDIT_KILL (AUDIT_CREATE <23) AUDIT_DEBUG (AUDIT_CREATE <24) AUDIT_PING (AUDIT_CREATE <25) AUDIT_TIME (AUDIT_CREATE <26) AUDIT_D ELAY_INSERT (AUDIT_CREATE <27) values (AUDIT_CREATE <28) AUDIT_TABLE_DUMP (AUDIT_CREATE <29) AUDIT_CONNECT_OUT (AUDIT_CREATE <30) values (AUDIT_CREATE <31) AUDIT_PREPARE (AUDIT_CREATE <32) AUDIT_EXECUTE (AUDIT_CREATE <33) AUDIT_LONG_DATA (AUDIT_CREATE <34) hour (AUDIT_CREATE <35) www.2cto.com hour (AUDIT_CREATE <36) AUDIT_SET_OPTION (AUDIT_CREATE <37) AUDIT_FETCH (AUDIT_CREATE <38) AUDIT_DAEMON (AUDIT_CREATE <39) AUDIT_ERROR (AUDIT_CREATE <40) AUDIT_SERVER (~ AUDIT_FULL) in the preceding definition, many bits are reserved for further granularity. The designation of audit_ops poses a certain risk and requires calculation. Direct control is not recommended. Audit Configuration description for the independence of the audit plug-in, the audit is controlled by a separate configuration file, so that the configuration of the database server is not affected. In addition, the audit configuration is related to the audit granularity, audit user, audit file address, audit file name, audit file size, and audit type. The list of user names separated by "," is the Audit User format. The audit file address must be the mysql user name and user group in linux, and mysql must have at least W access permissions. If the audit file size is specified, the audit file name is mysql-audit by default, and files are created based on whether or not the audit file size is set. If the size of the audit file is set, the audit file uses the date as the folder and the file number as the suffix. After the file size reaches the specified file size, the file is re-created and the file number increases progressively. For example: 2012-6-20/mysql-audit.000000, if the file size is not set, the file name with the current date as the suffix, such as: mysql-audit. 2012-6-20; audit file size format is an integer, or an integer plus the unit, in the unit of K, M, G. For ease of viewing, it is not recommended to set this value too large; www.2cto.com audit types include FILE and TABLE. Currently, only FILE is valid. In addition, the configuration file supports dynamic modification and effective functions. After the configuration file is modified, the configuration option is reloaded. However, due to performance issues, it is not recommended to modify the configuration file frequently. Install and use the 1 source code for different MySQL source code and different operating systems. We recommend that you use the source code for compilation and installation. The plug-in is compiled and tested based on the 64-bit Linux operating system, MySQL 5.5.15/5.5.20 source code, and provides a binary dynamic library for Binary installation. For details about binary installation, see the next section. 1.1 Source Code Acquisition 1) Audit plug-in source code audit plug-in source code can be obtained through github the latest version. Github address: https://github.com/HengWang/mysql-audit . Github clone https://github.com/HengWang/mysql-audit 2) MySQL source code can be downloaded from the official website. This article recommends Percona Server 5.5. It is recommended that Percona Server improve MySQL source code, especially in terms of performance and Parameter options. 1.2 copy plug-in source code 1) decompress the database source code tar-xzf Percona-Server-<VERSION> .tar.gz 2) copy the plug-in to the source code cp-rf mysql-audit/Percona-Server-<VERSION>/plugin/and copy the plug-in source code to the plugin folder of the mysql database source code. 1.3 compile and install the source code of the database based on the Compilation parameters. Www.2cto.com cmake. -DCMAKE_INSTALL_PREFIX =/usr/local/mysqlmakemake install audit is displayed under lib/plugin/of MYSQL_HOME (installation path, set to/usr/local/mysql. so and audit. cnf file. 1.4 set audit parameters according to audit requirements, modify audit Configuration File audit. cnf, and set audit option parameters and audit granularity. The configuration file describes the functions of each audit parameter and can be set as needed. 1.5 create an audit path create an audit folder Based on the audit_dir path set in the configuration file. The audit folder must be an operable folder of mysql. If the audit file size is specified, the specified audit folder must have xw permission on mysql. 1.6 create an audit data table to start the MySQL database, log on to the database using the mysql client, execute the SQL script audit. SQL, and create the mysql. audit table to store audit content. 1.7 install the audit plug-in to start the MySQL database. After logging on to the database using the mysql client, run the install plug-in command. Install plugin audit soname 'audit. so '; after the installation is complete, <FILENAME> is generated in the specified audit folder. <DATE> file or <DATE>/<FILENAME>. 000000. If the file does not exist, the installation fails. 2 binary dynamic library for Binary installation currently only provides audit plug-in dynamic library compiled based on Linux 64-bit operating system and MySQL 5.5.15/5.5.20 source code. Www.2cto.com has a binary library in the mysql-audit/script folder and a configuration file in the mysql-audit/etc folder. Copy the configuration file audit. cnf to MYSQL_HOME/lib/plugin/, and copy the binary Library to the address specified by plugin_dir of MySQL Server. The subsequent operations are the same as those of the source code installation. Start from Step 1.4 and perform the following operations. 3. Automated installation and deployment: it is mainly for Binary File Installation. You can execute a script to complete the installation and deployment process. Therefore, the premise of using automated scripts is that the provided binary files meet application requirements. Specifically: the premise for executing the script is that you have obtained mysql-audit. In this case, you can run an automated script to successfully install and deploy the script. For specific script operations, run audit_build.sh-h to view the input parameters. For example :. /audit_build.sh -- mysqldir =/usr/local/mysql -- auditdir =/home/mysql/audit -- user = root -- password = "" -- host = localhost -- port = 3306 -- socket = /tmp/mysql. sock -- version = 5.5.20 -- type = release4 uninstall the audit plug-in to ensure database performance. Log on to the database server using mysql and run the following command: uninstall plugin audit; the file output format is in two formats: Connection audit and Operation audit. 1. The format of audit log information related to Connection audit www.2cto.com is as follows. Specific: [15:40:39]: audit time. [CONNECT]: Connection type; status: status of connection success (0) or failure (error code); user: user Name of connection; external-user: it is an external connection user and valid when using middleware or proxy services; proxy-user: the proxy service user name; host: indicates the connection host name; ip: The connected ip address; database: name of the connected database. [15:40:39] [CONNECT] status: #; thread_id: #; user: XXX; external-user: XXX; proxy-user: XXX; host: XXX; ip: ###. ###. ###. ###; database: XXX2. The format of audit log information related to actiontrail operations is as follows. Commands related to operations include CREATE, DROP, ALTER, INSERT, UPDATE, DELETE, GRANT, REVOKE, and other operations that affect data or databases. The following uses CREATE as an example to describe in detail. Specific: [15:40:39]: audit time. [CREATE] indicates the operation type. error_code: indicates the operation error code. The operation is successful as 0. thread_id: indicates the thread id of the operation to be executed. user: indicates the user name of the operation to be executed. command: the Operation Command type is defined inside the database. Data-related operations are generally Query; query: the Query statement of the operation. Www.2cto.com charset: the character set type of the operation. time: the time when the operation is executed. The value is a long integer since January 1, 1970. Rows: number of rows that affect data when an operation is executed. [15:40:39] [CREATE] error_code: #; thread_id: #; user: XXX; command: XXX; query: XXX; charset: XXX; time: ###; rows: # data TABLE definition create table mysql. audit ('audit _ time' timestamp not null default CURRENT_TIMESTAMP, 'command' varchar (20) not null default 'null', 'status' int (11) not null default '0', 'thread _ id' bigint (32) unsigned not null default '0', 'user' varchar (20) NOT NULL DEFAU LT 'null', 'external _ user' varchar (20) not null default 'null', 'proxy _ user' varchar (20) not null default 'null ', 'host' varchar (20) not null default 'null', 'IP' varchar (20) not null default 'null', 'query' varchar (255) not null default 'null', 'charset' varchar (20) not null default 'null', 'event _ time' timestamp not null default '2017-00-00 00:00:00 ', 'rows 'bigint (32) not null default '0') ENGI NE = csv default charset = utf8; www.2cto.com common error 1. unrecognized audit. so. Solution: Check the plugin_dir path and check whether audit. so exists under this path. 2. No audit content. Solution: First, check whether the permissions of the specified audit folder match. The audit folder must be a directory that mysql users can operate on. If the audit file size is specified, the specified audit folder must have xw permissions. Further work 1. Add the audit data object (database, data table) function. 2. Check the audit parameter status. The current parameter status is dynamically displayed in show status like 'audit %. Author king_wangheng