MySQL Restore from a Year Old Backup

backupMySQLrestore

I have two backups created via mysqlhotcopy:

backup-before-deletion from August 7, 2014 and
backup-after-deletion from February 3, 2016

A deletion event occurred on July 17, 2015 which resulted in the deletion of about 15,000 rows across 7 different tables.

Because this segment of data was hardly ever touched it wasn't noticed until a recent audit.

The good news is the deletion event can be simulated but it would have to be done on the backup-before-deletion.

What is the most cost effective way (paid for tools are on the table) to restore the deleted data?

Best Answer

Consider

Load the data into a different database, or even a different MySQL instance.
Locate the deleted rows.
Export them in some manner -- INSERT .. SELECT .. into another table; SELECT INTO OUTFILE ..., mysqldump, etc.
Load them into your real database.

Cost: A bunch of your time.

Related Solutions

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

Mysql – BACKUP and RESTORE MySQL Database

As from your question if the scenario is like that.

You are on your local machine and accessing the remote servers A and B.

Process is simple

Step1 : Connect to server A where A is username of server

ssh A@192.168.9.214

Step2 : Generate the database dump of the server A as

mysqldump -uroot -proot  db1  > database_db1.sql

Step3 : Copy the dump on the server B using SCP as

scp database_db1.sql B@192.168.9.215:/home/user/Desktop

Step4 : Connect to the Server B as

ssh B@192.168.9.215

Step5 : Restore the copied dump here using mysql client utility

mysql -uroot -proot db < /home/user/Desktop/database_db1.sql