Can I run a Select query on an SQL dump?

Bert

11/28/23, 8:32 AM

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 !

278

1 + 2

mysql

backup

cluster

Halfgaar

11/28/23, 12:45 PM

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.

Bert

11/28/23, 3:28 PM

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.

Score:2

Server

Phill W.

11/28/23, 1:53 PM

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.

+ 4

Bert

11/28/23, 3:27 PM

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.

Rick James

11/28/23, 6:02 PM

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.

Bert

11/29/23, 9:06 AM

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.

anx

12/7/23, 7:46 AM

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."*

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Can I run a Select query on an SQL dump?

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.