0

I work with three MySQL databases (a, b & c) on a single server, that are nightly dumps of production databases. I have a fourth database (d) on that same server, that I have been given for creating new tables that connect and synthesize data from databases a, b & c.

I need to begin creating reports that reflect changes in databases a, b & c from one nightly dump to the next.

What would be the best way to capture the records from databases a, b & c, into database d, that:

example 1: records all of the unique records in tonight's dump that were not found in the last night's dump. (e.g. new account numbers in a table)

example 2: records all of the changes in value for records in tonight's dump that were different in last night's dump. (e.g. an account_status value that changes from 1 to 0).

I am new to data work and am hoping to find resources and direction on what the best practices are for such things?

Many thanks!

2 Answers 2

1

What you're referring to is called a delta, incremental, or differential backup, which is certainly natively supported by MySQL. You might want to consider backing up the deltas to an intermediary database that you can run your reports off of. That data can then be added to the up-to-date DB.

I would suggest using an administration tool like Navicat for MySQL for setting up the backups. It makes it easy to schedule and configure the backup rules. You can compare and sync data between databases/schemas with detailed analytical wizard process.

enter image description here

As a bonus, it can produce really eye-catching reports.

Hope that helps!

2
  • 1
    Thanks Rob! I upvoted your response, but my rep value is too low for it to be shown. This is brilliant for my work. I started using Navicat recently and will look into those tools. These databases (a, b & c) are a nightly complete refresh dump of the production dbs, so the fourth database (d) would be the intermediary database for backup, and also add to other tables as an "up-to-date" db. If I am following you correctly, is incremental backup the optimal type of backup for the approach I've described? Nov 21, 2018 at 21:46
  • Hi Samuel, yes, incremental backup would be the optimal type of backup to pick up all the latest data in your three databases since their last backups. Nov 25, 2018 at 19:34
0

I don't think there is any 'package' that can provide what you ask.

With TRIGGERs, you could track all changes as they occur. That is, it could record more of an 'audit trail' than of a 'diff dump'. Triggers would require another table(s) to record the changes in, plus adding trigger code to each table that you want to monitor. You would write in the trigger code to check for differences and INSERT into the new table.

If you are new to databases, this may be more work than you want to tackle. Let me ask why you need to monitor the nightly dumps. (Sometimes digging into the reasons for a requirement leads to a simpler solution.)

1
  • Thanks Rick. I've written thousands of lines of queries, but meant new in that I haven't done anything like this yet. The end goal is to isolate the MRR changes represented by new signups, cancellations, plan upgrades and downgrades in a billing system that does not have all of the necessary time-stamp records that would provide the data points that would simplify isolating that set of records. I'm now starting to think that using a scripting language would provide an easier solution outside of MySQL. Nov 13, 2018 at 5:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.