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!
Best Answer
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.
As a bonus, it can produce really eye-catching reports.
Hope that helps!