Merging databases – adding rows that not existing in one db to another

I have 2 databases from WordPress website. There was happenned issue and 50% of my posts dissapeared.

I have database 1 copy from 03.03.21 And existing database 2 of website from 24.03.21

So in database 1 i have many posts thats was deleted And the database 2 has some new posts that not exist in older database 1

Is there any software or a way to merge these 2 database. To compare databases and add entries to the newer database that are in the older database?

I could do this manullay but one post has entries in a many tables and its gonna be hard to recover deleted posts

Answer

There is no easy solution but you could try to make a “merge” locally for testing purposes.
Here’s how I would do it, I can’t guarrantee it will work.

1. Load the oldest backup into the server, let’s say in a database named merge_target.

2. Load the 2nd backup (the most recent one) into the same server, let’s say in a merge_source database.

3. Define a logical order to execute the merge for each table, this depends on the presence of foreign keys:

If a table A has a foreign key referencing table B, then you will need to merge table B before table A.

This may not work depending on your database structure (and I never worked with WordPress myself).

4. Write and execute queries for each table, with some rules:

• SELECT from the merge_source database
• INSERT into the merge_target database
• if a row already exists in merge_target (i.e. they have the same primary key or unique key), you can use MySQL features depending on what you want to do:
  • INSERT ON DUPLICATE KEY UPDATE if the existing row should be updated
  • INSERT IGNORE if the row should just be skipped
  • REPLACE if you really need to delete and re-insert the row

This could look like the following query (here with ON DUPLICATE KEY UPDATE):

INSERT INTO merge_target (col_a, col_b, col_c)
SELECT
    col_a
    , col_b
    , col_c
FROM merge_source
ON DUPLICATE KEY UPDATE
    merge_target.col_b = merge_source.col_b

Documentation:

• INSERT ... SELECT
• ON DUPLICATE KEY UPDATE
• REPLACE
• INSERT IGNORE is in the INSERT documentation page

---

Not sure it will help but I wrote a database migration framework in PHP, you can still take a look: Fregata.