0

Here is my issue. I've a backup system, that creates an SQL Dump from a DB cluster every day. Works fine, yade yade. The cluster died one day and we realized, the sql dump were wrong for the past 1 month. They were created and everything, just outdated data. (no details, this is not the question) The question is: Is there a way to check if the sqldump really has the most up-to-date data in it? On an sql server, I usually run a select query ordered by the "last_login_at" and just limit 5, see if the last 5 logins are recent or not. IDK if this could be used with an SQL file.

Any ideas? Thansk !

Bert
  • 1,028
  • 1
  • 16
  • 33
  • That's a very unusual requirement. The direct answer is no. You'd have to load it into a database first. But, I think you'll get a more insightful answer if you explain how you managed to make dumps of outdated data. – Halfgaar Nov 28 '22 at 12:45
  • I don't want to answer that. Let's just say I was incompetent and I want to make it right by doing everything to monitor the database. Not just the status, but also the relevance of the data. – Bert Nov 28 '22 at 15:28

1 Answers1

2

Is there a way to check if the sqldump really has the most up-to-date data in it

Short answer: No.

Longer answer: Yes ...
... but only after restoring it into a running mysql instance.

Unless, of course, your dump file is taken in a readable (i.e. SQL Text) format, in which case (with a suitable [text] editor) you can open the dump file directly and search it for the data you would otherwise query.

Phill W.
  • 1,479
  • 7
  • 7
  • Yeah, I was affraid of this. xD The SQL file is +2GB and the HW is not very strong, so editing the SQL file would take long minutes. I'm thinking of automating the import of the data and then making select queries, read the output and verify if it is good or bad. – Bert Nov 28 '22 at 15:27
  • Linux? `grep '2022-11-27` dump file | head` to see if any rows have that date in them. If you don't have any date columns, you may be SOL. – Rick James Nov 28 '22 at 18:02
  • I do have date columns. "last_login_at" is the best indicator. grep is nood idea, but the hardware is very weak, so not the best idea. But I'll keep this in mind. – Bert Nov 29 '22 at 09:06
  • I don't think the restoring step is *optional* or too much to ask. A different way of phrasing this would be *"Unless you also do practice regular restore & validation, you do not truly have a **backup** anyway, you have only exercised part of the preparation for one."* – anx Dec 07 '22 at 07:46