MySQL – How to Restore Old Backup Over New Data

MySQLrestore

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?

Best Answer

Load your data in another database.
Run LEFT JOINs one way, then the other way, to see what was deleted/new.
Run a JOIN to see if the rows that exist in both are the same; fix if necessary.
Run a multi-table DELETE to get rid of the the appropriate rows.
Run INSERT...SELECT..LEFT JOIN to copy the missing rows in

Related Solutions

Sql-server – How to restore database using old full backup and current log file

If your database is in simple recovery model, then you cannot perform a point-in-time recovery. If the database is not in simple-recovery model and you have log backups right upto the point of failure without a break in the log chain, then you can take a tail-log backup (if LDF is intact and the command succeeds) as pointed out by yrushka and perform a point-in-time recovery of the database without losing any data. If there are no log backups till now, then nothing can be done unfortunately.

Another option is that if you have the database in full/bulk logged-recovery model and no log backups have been taken, then the transaction log can be read using tools (eg. from Idera, LiteSpeed) which will allow you to generate the commands from the transaction log file which can be replayed on the database once it is restored.

Additionally, if you have a full backup and all the log backups till now, then you can restore them till the latest available log backup. Then using log reader tools above, you can generate the rest of the commands from the LDF file and replay them against the database.

MySQL Backup – Restoring to Latest Release

IMHO, when it comes to MySQL 5.5, you will definitely see much better performance from InnoDB than in past releases because dirty pages in the InnoDB Buffer Pool do not linger as long. Those dirty pages are flushed more robustly.

You also have the luxury of creating multiple InnoDB Buffer Pools (See innodb_buffer_pool_instances and innodb_buffer_pool_size). To reduce thread locking amongst the buffer pools, make sure set innodb_thread_concurrency to 0 to let InnoDB storage engine decide best how to handle thread allocation.

CAVEAT ON MIGRATION TO MySQL 5.5

Make sure you mysqldump all databases EXCEPT the mysql schema. There is a cleaner, crisper way to migrate User Grants out of the MySQL Schema. In fact, there are two options for accomplishing this:

OPTION 1 : Use mk-show-grants

This dumps out all the MySQL Grants as SQL statements, which is completely portable to any MySQL 5.x instance.

OPTION 2 : Run these commands (My personal emulation of what mk-show-grants does)

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Best Answer

Related Solutions

Sql-server – How to restore database using old full backup and current log file

MySQL Backup – Restoring to Latest Release

Related Question