Merging old backup in current database mysql

Question

I have a backup database that contains data up to the specific date 27-11-2023. Currently, I have the latest version of the database with updated records. However, the issue arises when I consider importing my backup database because it would overwrite my current database, which already includes data up to 27-11-2023 and any subsequent updates.

My question is: Is there any way to feasibly import my old backup, which is a self-contained SQL file, and only import the changes or records that are currently missing in my database? I am using SQL Workbench.

Answer 1

There is a solution to this, but you may not be able to employ it.

The solution is to use the binary logs, which contain serial events that updated data. The idea is that you would restore your backup, then use the mysqlbinlog tool to convert the binary log into a serial of SQL statements.

The crucial step is to know which part of the binary logs to replay.

That means when you create the backup, you should note the binary log position at the time of the backup. After you restore, you then replay binary logs starting at that position.

mysqldump has an option --source-data to do this. The binary log position is output in the dump file.

If you didn't note the binary log position when you created the backup, then you can estimate the timestamps of the binary log.

Read https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.html and its sub-pages for details on point it time recovery.

Be careful because restoring the backup will add more binary logs. So you should either copy the binary logs, or note the position where you should stop the point-in-time recovery as well as the start.