Shulou(Shulou.com)06/01 Report--

Mariadb audit (mariadb server_audit.so installation)

In the database that we often contact, oracle, mysql, mariadb, sql server will have related audit requirements, but their operation mode of increasing audit is not quite the same. Based on my personal experience, I will talk about the installation of mariadb audit plug-in.

After experiments, it is found that the audit plug-in libaudit_plugin.so on mysql is not suitable for mariadb. Later in mariadb, it comes with plug-ins, but it hasn't been installed in the database yet, so we just need to start installing it.

1. First log in to the database to see if Mariadb has installed the audit plug-in

MariaDB [(none)] > show global variables like'% audit%'

Empty set (0.00 sec)

As shown above, it is not installed, so let's see where the path to install the audit plug-in is stored in the database.

MariaDB [(none)] > SHOW VARIABLES LIKE 'plugin_dir'

+-- +

| | Variable_name | Value |

+-- +

| | plugin_dir | / usr/lib64/mysql/plugin/ |

+-- +

1 row in set (0.00 sec)

The version of my database

MariaDB [none)] > select version ()

+-+

| | version () |

+-+

| | 5.5.52-MariaDB |

+-+

1 row in set (0.00 sec)

two。 Go to the path of the audit plug-in and see what the name of the audit plug-in is.

[root@~] cd / usr/lib64/mysql/plugin/

You will find a server_audit.so, which is the protagonist we are looking for.

3. Now that we know the name of the audit plug-in, we will install it directly in the database.

MariaDB [(none)] > install plugin server_audit soname 'server_audit.so'

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)] > flush privileges

Query OK, 0 rows affected (0.00 sec)

4. Check to see if the installation is complete

MariaDB [(none)] > show global variables like'% audit%'

+-+ +

| | Variable_name | Value |

+-- +

| | server_audit_events |

| | server_audit_excl_users |

| | server_audit_file_path | server_audit.log |

| | server_audit_file_rotate_now | OFF |

| | server_audit_file_rotate_size | 1000000 | |

| | server_audit_file_rotations | 9 | |

| | server_audit_incl_users |

| | server_audit_loc_info | OOOOOOOO. | OOO |

| | server_audit_logging | OFF |

| | server_audit_mode | 0 | |

| | server_audit_output_type | file |

| | server_audit_query_log_limit | 1024 | |

| | server_audit_syslog_facility | LOG_USER |

| | server_audit_syslog_ident | mysql-server_auditing |

| | server_audit_syslog_info |

| | server_audit_syslog_priority | LOG_INFO |

+-+

16 rows in set (0.00 sec)

5. Perform demand operations

Start the audit

MariaDB [(none)] > set global server_audit_logging=on

Set the audit log path

MariaDB [(none)] > set global server_audit_file_path='/data0/mariadb/auditlog/'

Set the contents of the action instructions for audit log events

MariaDB [(none)] > set global server_audit_events='QUERY_DDL,QUERY_DML'

Rotate logs after expanding the limited size of server_audit.log

MariaDB [(none)] > set global server_audit_file_rotate_size='200000000'

Increase the limit number of logs

MariaDB [(none)] > set global server_audit_file_rotations='200'

Set the users who need to be audited

MariaDB [(none)] > set global server_audit_incl_users='root'

Set up audit-free users

MariaDB [(none)] > set global server_audit_excl_users='z'

Set up ident as part of the syslog record

MariaDB [(none)] > set global server_audit_syslog_ident='mysql-server_auditing'

6. Check the status again

MariaDB [(none)] > show global variables like'% audit%'

+-+

| | Variable_name | Value |

+-- +

| | server_audit_events | QUERY_DDL,QUERY_DML |

| | server_audit_excl_users | z | |

| | server_audit_file_path | / data0/mariadb/auditlog/ |

| | server_audit_file_rotate_now | OFF |

| | server_audit_file_rotate_size | 200000000 | |

| | server_audit_file_rotations | 200 | |

| | server_audit_incl_users | root |

| | server_audit_loc_info | OOOOO … | OOOOOOOOO |

| | server_audit_logging | ON |

| | server_audit_mode | 0 | |

| | server_audit_output_type | file |

| | server_audit_query_log_limit | 1024 | |

| | server_audit_syslog_facility | LOG_USER |

| | server_audit_syslog_ident | mysql-server_auditing |

| | server_audit_syslog_info |

| | server_audit_syslog_priority | LOG_INFO |

+-+

16 rows in set (0.00 sec)

7. Check whether the audit plug-in is running

MariaDB [(none)] > show global variables like'% audit%'

+-+ +

| | Variable_name | Value |

+-|-+

| | server_audit_active | ON |

| | server_audit_current_log | server_audit.log |

| | server_audit_last_error |

| | server_audit_writes_failed | 0 | |

+-+ +

4 rows in set (0.00 sec)

From the results shown above, you can know the server_audit_active=on, indicating that the audit is in progress.

8. Setting audit settings in mariadb will fail when the database is restarted. In order not to fail, you can make an article on the database settings file.

Vim / etc/my.cnf

Add under [mysqld]

Server_audit=FORCE_PLUS_PERMANENT-prevent the audit plug-in from being uninstalled

Server_audit_logging=ON-Open the audit log

Server_audit_excl_users='z'-users who are not in the audit

Server_audit_file_rotate_size=2000000-audit log file rotation limit size

Server_audit_file_rotations=200-limit number of audit rotation logs

Server_audit_excl_users='root'-users including audit

Server_audit_events='query_ddl,query_dml';-the contents of the operation instructions for audit log events

Restart the mariadb database service.

The above steps are taken by me in CentOS72. Install, open, and configure the audit plug-in for mariadb-5.5.52 on the.

Attached: mariadb official website reference document: https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/

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.