.::: Load log file & insert into table in MariaDB MySQL (sample mariadb audit) :::.

continue from http://teguhth.blogspot.com/2022/08/enable-server-audit-in-mariadb-mysql.html

1.    Create table tbl_mariadb_audit
create database teguhthtools;
use teguhthtools;

CREATE TABLE `tbl_mariadb_audit`
(   `date` varchar(64) DEFAULT NULL,
    `host` varchar(64) DEFAULT NULL,
    `userdb` varchar(64) DEFAULT NULL,
    `client` varchar(64) DEFAULT NULL,
    `id` int,
    `connection_id` int NULL,
    `type` varchar(64) DEFAULT NULL,
    `db` varchar(64) DEFAULT NULL,
    `sqltext` varchar(64) DEFAULT NULL,
    `status` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

MariaDB [teguhthtools]> select * from teguhthtools.tbl_mariadb_audit;
Empty set (0.00 sec)

MariaDB [teguhthtools]>

2.    Check contain server_audit.log
# cat /var/lib/mysql/server_audit.log

3.    Run load file to table from MariaDB
MariaDB [teguhth]> delete from teguhthtools.tbl_mariadb_audit;
MariaDB [teguhth]> LOAD DATA LOCAL INFILE  '/var/lib/mysql/server_audit.log' ignore INTO TABLE teguhthtools.tbl_mariadb_audit COLUMNS TERMINATED BY ',';
MariaDB [teguhth]> Select * from teguhthtools.tbl_mariadb_audit; ";

4.    Run load file to table from terminal linux
# mysql -u root -p -e "delete from teguhthtools.tbl_mariadb_audit; LOAD DATA LOCAL INFILE  '/var/lib/mysql/server_audit.log'
ignore INTO TABLE teguhthtools.tbl_mariadb_audit COLUMNS TERMINATED BY ','; Select * from teguhthtools.tbl_mariadb_audit; ";

5.    Checking table tbl_mariadb_audit

# mysql -u root -p -e "select * from teguhthtools.tbl_mariadb_audit; ";

or

MariaDB [teguhth]> select * from teguhthtools.tbl_mariadb_audit;