dnsmasq入库

2024-01-01

提前准备 #

cat dns.log | grep -v "miwifi" | grep "query\[A]" > dnstemp.log
awk '{printf "2025 %s %s %s,%s,%s\n", $1, $2, $3, $6, $8}' dnstemp.log > dns.csv

cat dns.csv #

2025 Jun 22 00:43:39,update.googleapis.com,120.229.173.236
2025 Jun 22 00:43:41,api-access.pangolin-sdk-toutiao1.com,120.229.173.236
2025 Jun 22 00:43:48,ulogs.umeng.com,120.229.173.236

cat dns.sql #

-- 使用数据库
USE dns_logs;

-- 导入数据
LOAD DATA INFILE '/dns.csv'
INTO TABLE dns_queries
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(@log_time_str, domain_name, client_ip)
SET log_time = STR_TO_DATE(@log_time_str, '%Y %b %d %H:%i:%s');

执行sql #

mysql -h 14.103.224.46 -u zian -p < dns.sql

数据库建表 #

-- 创建数据库
CREATE DATABASE IF NOT EXISTS dns_logs;
-- 使用数据库
USE dns_logs;

-- 创建表
CREATE TABLE IF NOT EXISTS dns_queries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_time DATETIME NOT NULL,
    domain_name VARCHAR(255) NOT NULL,
    client_ip VARCHAR(45) NOT NULL
);