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?

  • 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 '15 at 6:53
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
  • 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 '15 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 '15 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 '15 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 '15 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.

  • The problem is that I don't want to rewrite over the newly added records. – Robert Gabriel Aug 25 '15 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 '15 at 5:37
  • Is the tool only for Windows? I am using MacOS. Thanks! – Robert Gabriel Aug 26 '15 at 6:57
  • Yes.But you can install it on Mac either using Wine port or Virtualization Manager. – Mathew Aug 26 '15 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 '15 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.

  • 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 '15 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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