使用shell脚本将doDBA采集到的日志会话信息导入到MySQL数据库

【背景说明】

使用doDBA工具监控的会话信息导入到MySQL数据库的表中

【环境说明】

doDBA工具采集会话信息（之前有脚本说明）

【脚本说明】

处理dodba日志信息
将日志的innodb日志信息去除
审计日志的名称要改为原来的dodba.log名称
cd /data/backup/doDBA/log/
cp dodba_20231226_0957.log.tar.gz /data/backup/doDBA/analyze
cd /data/backup/doDBA/analyze
tar -zxvf dodba_20231226_0957.log.tar.gz
mv dodba_20231226_0957.log dodba.log

vim analyze_dodba_log.sh
#!/bin/bash
sed '/INNODB MONITOR OUTPUT/,/END OF INNODB MONITOR OUTPUT/d'  dodba.log > dodba_temp.log
sed 's/=====================================//g' dodba_temp.log >dodba_temp1.log
sed 's/============================//g' dodba_temp1.log >dodba_`date +%Y%m%d_%H%S`.log
rm -rf dodba_temp.log
rm -rf dodba_temp1.log
echo "The current file is: dodba_`date +%Y%m%d_%H%S`.log"

执行去除innodb信息的脚本
sh analyze_dodba_log.sh

如果需要按照分钟进行切割
cat dodba_20231226_090500_090510.log|grep 'processlist---------------'
然后根据上面的时间进行切割需要的时间会话信息
sed -n '/26 09:05:00 --/,/26 09:05:10 --/p' dodba_20231226_090500_090510.log > dodba_20231226_090500_090510.log


创建审计用户及数据库：
create database dodba_audit default charset utf8;
create user dodba_audit@'%'  identified by        'xxxxxx';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON dodba_audit.* to  dodba_audit@'%';

mysql -udodba_audit -p'xxxxxx'
CREATE TABLE dodba_audit.`dodba_audit_table` (
  `id` int(11) NOT NULL               COMMENT '会话id号',
  `timestamp` datetime DEFAULT NULL   COMMENT '会话时间',
  `user` varchar(50) DEFAULT NULL     COMMENT '会话用户名',
  `host` varchar(50) DEFAULT NULL     COMMENT '会话IP信息',
  `db` varchar(50) DEFAULT NULL       COMMENT '会话数据库名',
  `command` varchar(50) DEFAULT NULL  COMMENT '会话命令类型',
  `time` int(11) DEFAULT NULL         COMMENT '会话SQL执行时间,秒单位',
  `state` varchar(50) DEFAULT NULL    COMMENT '会话SQL执行状态',
  `info` text                         COMMENT '会话SQL语句详细信息'
) ENGINE=InnoDB COMMENT 'dodba审计信息表';

truncate table dodba_audit.`dodba_audit_table`;

创建审计导入MySQL脚本：
cd  /data/backup/doDBA/analyze

vim dodba_audit.sh
#!/bin/bash

logfile="/data/backup/doDBA/analyze/dodba_20231226_090500_090510.log"
mysql_host="localhost"
mysql_user="dodba_audit"
mysql_password="xxxxxx"
mysql_database="dodba_audit"
mysql_table="dodba_audit_table"

while IFS= read -r line
do
  if [[ $line =~ ^[0-9]{4}/[0-9]{2}/[0-9]{2}\ [0-9]{2}:[0-9]{2}:[0-9]{2}\ ----------------processlist---------------$ ]]; then
    timestamp=$(date -d "${line:0:19}" +"%Y-%m-%d %H:%M:%S")
  elif [[ $line =~ ^ID:([0-9]+)$ ]]; then
    id="${BASH_REMATCH[1]}"
  elif [[ $line =~ ^User:(.+)$ ]]; then
    user="${BASH_REMATCH[1]}"
  elif [[ $line =~ ^Host:(.+)$ ]]; then
    host="${BASH_REMATCH[1]}"
  elif [[ $line =~ ^DB:(.+)$ ]]; then
    db="${BASH_REMATCH[1]}"
  elif [[ $line =~ ^Command:(.+)$ ]]; then
    command="${BASH_REMATCH[1]}"
  elif [[ $line =~ ^Time:([0-9]+)$ ]]; then
    time="${BASH_REMATCH[1]}"
  elif [[ $line =~ ^State:(.+)$ ]]; then
    state="${BASH_REMATCH[1]}"
  elif [[ $line =~ ^Info:(.+)$ ]]; then
    info="${BASH_REMATCH[1]}"

  # Insert the log entry into MySQL
  escaped_info=$(echo "$info" | sed "s/'/''/g")
  mysql -h $mysql_host -u $mysql_user -p$mysql_password $mysql_database -e "INSERT INTO $mysql_table (timestamp, id, user, host, db, command, time, state, info) VALUES ('$timestamp', '$id', '$user', '$host', '$db', '$command', '$time', '$state', '$escaped_info');"
  fi
done < "$logfile"

执行导入MySQL表中
nohup sh  dodba_audit.sh &

数据库查看审计信息
select * from dodba_audit.`dodba_audit_table`;