1. Check plugin log
[root@teguhth data]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
2. Install log audit
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
Empty set (0.02 sec)
MariaDB [(none)]> INSTALL SONAME 'server_audit';
Query OK, 0 rows affected (0.05 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'server_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_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 |
+-------------------------------+-----------------------+
15 rows in set (0.00 sec)
MariaDB [(none)]>
3. If want to Uninstall plugin;
MariaDB [(none)]> uninstall soname 'server_audit';
Empty set (0.02 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
Empty set (0.02 sec)
Empty set (0.02 sec)
4. Configure server audit using cli or using configuration server.cnf or my.cnf to make change persistance
Configuring using CLI MariaDB
MariaDB [(none)]> set global server_audit_logging=on;
MariaDB [(none)]> set global server_audit_file_rotate_now=ON ;
MariaDB [(none)]> set global server_audit_file_rotate_size=1000000 ;
MariaDB [(none)]> set global server_audit_file_rotations=5 ;
MariaDB [(none)]> set global server_audit_logging=on;
MariaDB [(none)]> set global server_audit_file_path = '/var/lib/mysql/server_audit.log';
MariaDB [(none)]> set global server_audit_excl_users=mariatest;
MariaDB [(none)]> SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML_NO_SELECT,QUERY_DCL';
Configuring in server.cnf or my.cnf and restart service
[root@teguhth ~]# cat /etc/my.cnf.d/server.cnf
#………………….
server_audit_logging=on
server_audit_file_rotate_now=ON
server_audit_file_rotate_size=1000000
server_audit_file_rotations=5
server_audit_logging=on
server_audit_file_path = /var/lib/mysql/server_audit.log
server_audit_excl_users=mariatest
server_audit_events = 'CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML_NO_SELECT,QUERY_DCL'
#………………….
5. Check configuration
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'server_audit%';
+-------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+-------------------------------------------------------------+
| server_audit_events | CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DCL,QUERY_DML_NO_SELECT |
| server_audit_excl_users | |
| server_audit_file_path | /var/lib/mysql/server_audit.log |
| server_audit_file_rotate_now | ON |
| server_audit_file_rotate_size | 1000000 |
| server_audit_file_rotations | 5 |
| server_audit_incl_users | |
| 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 |
+-------------------------------+-------------------------------------------------------------+
15 rows in set (0.00 sec)
MariaDB [(none)]>
6. Check log server audit
[root@teguhth ~]# tail -f /var/lib/mysql/server_audit.log
7. Testing command
use teguhth;
select * from barang;
delete from table pembelian;
MariaDB [(none)]> use teguhth;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [teguhth]> select * from barang;
+-------------+-------------+---------------+-------------+
| KODE_BARANG | NAMA_BARANG | SATUAN_BARANG | STOK_BARANG |
+-------------+-------------+---------------+-------------+
| ELK-01 | RICE COOKER | BUAH | 20 |
| ELK-02 | LEMARI ES | UNIT | 8 |
| ELK-03 | TELEVISI | UNIT | 30 |
| ELK-04 | RADIO/TAPE | BUAH | 35 |
| ELK-05 | KOMPUTER | UNIT | 28 |
| ELK-06 | KIPAS ANGIN | BUAH | 38 |
+-------------+-------------+---------------+-------------+
6 rows in set (0.00 sec)
MariaDB [teguhth]> delete from pembelian;
Query OK, 0 rows affected (0.01 sec)
MariaDB [teguhth]>
8. Check server_audit.log
[root@teguhth ~]# tail -f /var/lib/mysql/server_audit.log
20220829 12:34:17,teguhth,root,localhost,11,25,QUERY,mysql,'create user lsa1234567890021114456',0
20220829 12:34:26,teguhth,root,localhost,11,26,QUERY,mysql,'create user lsa1234567890021114456',1396
20220829 12:34:36,teguhth,root,localhost,11,28,QUERY,mysql,'create user lsa1234567890021114456555',0
20220829 12:35:48,teguhth,root,localhost,11,29,QUERY,mysql,'create user lsa1234567890021114456555pp',0
20220829 12:35:53,teguhth,root,localhost,11,30,QUERY,mysql,'create user lsa1234567890021114456555pf',0
20220830 07:47:35,teguhth,root,localhost,8,2,QUERY,mysql,'create user th',0
20220830 07:48:45,teguhth,root,localhost,8,3,QUERY,mysql,'create user th1',0
20220907 11:09:49,teguhth,root,localhost,8,2,QUERY,,'create user 111',1064
20220907 11:09:54,teguhth,root,localhost,8,3,QUERY,,'create user 22',1064
20220907 11:09:59,teguhth,root,localhost,8,4,QUERY,mysql,'create user abl',0
20220829 12:34:26,teguhth,root,localhost,11,26,QUERY,mysql,'create user lsa1234567890021114456',1396
20220829 12:34:36,teguhth,root,localhost,11,28,QUERY,mysql,'create user lsa1234567890021114456555',0
20220829 12:35:48,teguhth,root,localhost,11,29,QUERY,mysql,'create user lsa1234567890021114456555pp',0
20220829 12:35:53,teguhth,root,localhost,11,30,QUERY,mysql,'create user lsa1234567890021114456555pf',0
20220830 07:47:35,teguhth,root,localhost,8,2,QUERY,mysql,'create user th',0
20220830 07:48:45,teguhth,root,localhost,8,3,QUERY,mysql,'create user th1',0
20220907 11:09:49,teguhth,root,localhost,8,2,QUERY,,'create user 111',1064
20220907 11:09:54,teguhth,root,localhost,8,3,QUERY,,'create user 22',1064
20220907 11:09:59,teguhth,root,localhost,8,4,QUERY,mysql,'create user abl',0
9. for save to table go to
No comments:
Post a Comment