My ESP8266 devices are generating huge amount of logs so after some time LogAnalyzer database had over 5M records.
I've decided to delete old data, but before run purge script I want to backup these records.
The solution was quite simple:
mysqldump Syslog SystemEvents --where \ 'FromHost LIKE "ESP" AND DATE(ReceivedAt) < "2018-01-01"' \ > /data/Dokumenty/Syslog-ESP-2017.sql
where Syslog is database, and SystemEvents is table.
You can also export to CSV (TSV) format if You prefer:
mysql -e "SELECT * FROM SystemEvents \ WHERE FromHost LIKE 'ESP' AND DATE(ReceivedAt) < '2018-01-01' \ INTO OUTFILE '/data/Dokumenty/Syslog-ESP-2017.csv' \ FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' \ LINES TERMINATED BY '\n'" Syslog
After this I could delete whole year, instead of using script:
mysql -e "DELETE FROM SystemEvents \ WHERE FromHost LIKE "ESP" AND DATE(ReceivedAt) < '2018-01-01'" \ Syslog
Now I've only about 600k records ;-)
Add comment