Назад | Перейти на главную страницу

Можно ли сохранить отчет TCDUMP (pt-query-digest) в базе данных?

Я хочу сохранить всю информацию, которую я получаю от команды tcpdump в терминале, и сохранить ее в базе данных, чтобы было легче искать и находить то, что я хочу.

Сначала я запускаю эту команду.

tcpdump -i любой порт 3306 -s 65535 -x -nn -q -tttt> tcpdump.out

После того, как я получу отслеживание нескольких пакетов, я запускаю pt-query-digest, чтобы увидеть файл.

pt-query-digest --report-format query_report --type tcpdump tcpdump.out

Затем я получил файл со всей необходимой мне информацией о золоте.

Полный отчет

# Query 1: 76.96k QPS, 133.37x concurrency, ID 0x0C9C70920DA7300E0B14E77CA2C09D3E at byte 31097
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.763692 to 12:09:37.763718
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 13 3ms 2ms 2ms 2ms 2ms 19us 2ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 8 74 37 37 37 37 0 37
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
SHOW SESSION STATUS LIKE 'Ssl_cipher'\G

# Query 2: 246.72k QPS, 369.47x concurrency, ID 0x2B0CD0DF4DE994CE0D39814CFEE11CA5 at byte 21115
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.521487 to 12:09:37.521495
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 11 3ms 1ms 2ms 1ms 2ms 7us 1ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 4 44 22 22 22 22 0 22
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
SET SQL_SAFE_UPDATES=1\G

# Query 3: 5.26 QPS, 0.00x concurrency, ID 0x80AEBDBE9DD8458C3AEAAD0E15D725B5 at byte 35663
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.117782 to 12:09:37.878811
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 4
# Exec time 11 3ms 624us 816us 721us 799us 94us 799us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 7 64 16 16 16 16 0 16
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (2/50%), 10.8.0.2 (2/50%)
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
set autocommit=1\G

# Query 4: 246.72k QPS, 295.45x concurrency, ID 0xDD749893CA4A219A6C099B0B73EA7633 at byte 34025
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.821399 to 12:09:37.821407
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 9 2ms 1ms 1ms 1ms 1ms 7us 1ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 3 32 16 16 16 16 0 16
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
USE `main_db`\G

# Query 5: 254.20k QPS, 284.20x concurrency, ID 0x873D3AAF7C4528CF9439C8E2DE90E5FF at byte 12537
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.289246 to 12:09:37.289254
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 8 2ms 1ms 1ms 1ms 1ms 16us 1ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 11 104 52 52 52 52 0 52
# Warning coun 0 0 0 0 0 0 0 0
# Boolean:
# No index use 100% yes, 0% no
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
SHOW SESSION VARIABLES LIKE 'lower_case_table_names'\G

# Query 6: 220.75k QPS, 223.29x concurrency, ID 0xC226CFE4707C2F17F8DAC04E6F33E7B5 at byte 22783
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.579012 to 12:09:37.579021
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 7 2ms 1ms 1ms 1ms 1ms 7us 1ms
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 4 44 22 22 22 22 0 22
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms ################################################################
# 10ms
# 100ms
# 1s
# 10s+
# EXPLAIN /*!50100 PARTITIONS*/
SELECT CONNECTION_ID()\G

# Query 7: 254.20k QPS, 236.15x concurrency, ID 0xDA991D86702B9DDD1385D0139C73A7AA at byte 24851
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.637366 to 12:09:37.637374
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 7 2ms 924us 934us 929us 934us 7us 929us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 9 88 44 44 44 44 0 44
# Warning coun 0 0 0 0 0 0 0 0
# Boolean:
# No index use 100% yes, 0% no
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
show character set where charset = 'utf8mb4'\G

# Query 8: 246.72k QPS, 217.36x concurrency, ID 0x68C6B480CBDB8159E2F7D83651089289 at byte 15727
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.347174 to 12:09:37.347182
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 6 2ms 876us 886us 881us 886us 7us 881us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 4 42 21 21 21 21 0 21
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
# EXPLAIN /*!50100 PARTITIONS*/
SELECT current_user()\G

# Query 9: 220.75k QPS, 173.40x concurrency, ID 0x3F64105F15925EFB479CD48F4076FE38 at byte 17819
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.404765 to 12:09:37.404774
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 6 2ms 780us 791us 785us 791us 7us 785us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 4 44 22 22 22 22 0 22
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SET CHARACTER SET utf8\G

# Query 10: 220.75k QPS, 155.19x concurrency, ID 0x8F2BCF3F7654FE83814513E415DE18DF at byte 29445
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.698783 to 12:09:37.698792
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 5 1ms 697us 709us 703us 709us 8us 703us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 4 38 19 19 19 19 0 19
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SET NAMES 'utf8mb4'\G

# Query 11: 199.73k QPS, 139.31x concurrency, ID 0x92D0FF69CAB4C1810FFF48EF5C7CEB31 at byte 37301
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.940752 to 12:09:37.940762
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 5 1ms 685us 710us 697us 710us 17us 697us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 22 202 101 101 101 101 0 101
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `main_db` LIKE 'TEXT'\G
# SHOW CREATE TABLE `main_db`.`platform_msg`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM X.TEXT WHERE ID = 5901 ORDER BY KEY, ID ASC
LIMIT 0, 2000
\G

# Query 12: 279.62k QPS, 150.16x concurrency, ID 0x7E5EC339926496EC6C2D4BD68F056F6B at byte 10665
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-01-27 12:09:37.175557 to 12:09:37.175564
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2
# Exec time 4 1ms 532us 542us 537us 542us 7us 537us
# Rows affecte 0 0 0 0 0 0 0 0
# Query size 12 110 55 55 55 55 0 55
# Warning coun 0 0 0 0 0 0 0 0
# String:
# Hosts 10.1.1.5 (1/50%), 10.8.0.2 (1/50%)
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ\G

Я хочу извлечь эту информацию в базу данных, но мне нужны только некоторые вещи, а не все.

Как вы видете #Query 11 получил все, что хочу. Запрос, который сделал пользователь.
Я хочу экспортировать его в базу данных примерно так:

ID - IP / HOST - TIMERANGE - ПОЛНЫЙ ЗАПРОС
1 - 10.8.0.2 - 2020-01-27 12:09 - ВЫБРАТЬ * (...)

Это вообще возможно?

Я получил этот скрипт на perl, если он помогает ... он сохраняет в базе данных некоторые поля, но не то, что я хочу ..

use DBI;
my $dns = "DBI:mysql:openvpn;localhost";
my $dbh = DBI->connect($dns,'user','password');
my $db = "openvpn";

while (<STDIN>) {
    chomp($data = <STDIN>);
    ($time, $ip.addr, $cap_len, $radiotap_len) = split " ", $data;
    my $sth = $dbh-> prepare("INSERT INTO openvpn (time,frame_len,cap_len,radiotap_len) VALUES ('$time', '$ip_addr', '$cap_len', '$radiotap_len')");
    $sth->execute;
}

#Terminate MySQL
$dbh->disconnect;

С уважением.