Best strategy to backup last 90 days of events and positions

Amado2 years ago

Hello. I'm running traccar 5.8 with mysql db. About 300 reporting devices

I need a strategy to backup the last 90 days of events and positions. I have some ideas and I would like to hear comments/suggestions:

  1. Can I use mysqldump with a "where" clause on fields fixtime (for tc_positions) and eventtime (for tc_events) to filter records?
  2. If I use methos (1) since booth tables are not indexed on the requiered fields for "where" clause., is it best to create them to speed up mysqldump operation?
  3. Any other idea?
Anton Tananaev2 years ago

I think your idea should work, but you definitely should create those indexes.

Track-trace2 years ago

Why dont you backup the whole database ? I use phpmyadmin for it. It is very easy to use with its web interface to backup anything you need.

Amado2 years ago

Hi Track-trace
Thank you for your suggestions
I just need to preserve history for 90 days so after de first backup I will delete anything beyond 90 days. After that I plan to do a daily full backup with a 90 day sliding window.
phpmyadmin is a very good tip, I will give it a try

Amado2 years ago

Hi Anton
Thank you for your answer

Alia year ago

Amado I believe using the rest API you could make a DELETE request to positions endpoint and delete all the positions within a timestamp.
You could possibly come up with a web script instead of playing around your database.