1

We have a MySQL backup. Did some changes on our database and some data was lost. In the mean time new records were added.

We have to restore the mysql backup over our latest data. Some of the tables have auto increment columns which means that we will end up with the same primary key but different values in some tables. The majority of the tables are from a many-to-many or one-to-many relationships.

What is the best approach to resync the same schema between the backup and the current data?

Improve this question
1
  • In database_one we had a 24 hour window of lost data. That data was saved in the backup. The problem was that some incremental ids were rewritten. So we have to insert that 24 hour window of data (inserts and update) in database_one. On every record we have a created and updated datetime timestamp.
    – Robert Gabriel
    Aug 26, 2015 at 6:53

3 Answers 3

Reset to default
2
  1. Load your data in another database.
  2. Run LEFT JOINs one way, then the other way, to see what was deleted/new.
  3. Run a JOIN to see if the rows that exist in both are the same; fix if necessary.
  4. Run a multi-table DELETE to get rid of the the appropriate rows.
  5. Run INSERT...SELECT..LEFT JOIN to copy the missing rows in
Improve this answer
4
  • I think that is a good approach. But I have the same primary_keys with different data which requires a "merge" without written over the data just the updates if any.
    – Robert Gabriel
    Aug 26, 2015 at 6:56
  • Step 2 JOINs on the PK to see the data differences. UPDATE good JOIN delta USING (pk) SET good.x = delta.x, ...;
    – Rick James
    Aug 26, 2015 at 18:00
  • If your PK is AUTO_INCREMENT and you cannot use it to match up records, then ignore it. Instead use some UNIQUE key.
    – Rick James
    Aug 26, 2015 at 18:01
  • Thanks Rick. Actually we compose a unique key from multiple columns. The created datetime column helped a lot.
    – Robert Gabriel
    Aug 27, 2015 at 4:33
1

I use SQLyog to have a backup of the database,which would help me to rewrite the data into the database by importing the backed-up data. And not only that i can also import the data by executing a sql script containing the table syntax and data.

Improve this answer
6
  • The problem is that I don't want to rewrite over the newly added records.
    – Robert Gabriel
    Aug 25, 2015 at 18:17
  • You can use the feature "Schema Synchronization Tool" to compare and sync the schema of the back-up and current schema.
    – Mathew
    Aug 26, 2015 at 5:37
  • Is the tool only for Windows? I am using MacOS. Thanks!
    – Robert Gabriel
    Aug 26, 2015 at 6:57
  • Yes.But you can install it on Mac either using Wine port or Virtualization Manager.
    – Mathew
    Aug 26, 2015 at 7:09
  • I see. Thanks! Although I can not use it on my problem but for a one way synchronization looks interesting.
    – Robert Gabriel
    Aug 26, 2015 at 13:23
1

Before doing any changes and attempts, take complete backup of your databases.

If you have physical backup of your database:

  • Restore your database in a temporary instance.

  • Identify the point from binary logs where the changes were made and you want to ignore them. Extract DMLs after that point till latest to SQL file.

  • Run the SQL against the new mysql instance.

  • Once you're happy with the restored data, take mysqldump and restore to original database.

In case of logical backup you can extract single database from mysqldump and put it in a different database.

  • Extract DMLs for that database from binary logs (--database option of mysqlbinlog) for the required period.

  • Load SQL against new instance.

  • Once you're happy with the dataset newly created, dumpload it to original db.

Improve this answer
1
  • I think the answer is good, it's my fault I didn't ask the question right. I updated with a comment.
    – Robert Gabriel
    Aug 26, 2015 at 6:58

Your Answer

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

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