Dump subset of one table from MySQL database
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 ;-)
Comments
Log in or create a user account to comment.