Today a client of mine had an issue where some scripts ran amuck and some data (mainly some timestamp rows) was set to 0. So I had to restore from a backup to fix the issues. The problem is that I couldn’t restore the entire database because today’s data would get lost. It was also only a few columns in a few tables that needed restoring. So here’s what I did.
First, I grabbed a copy of extract_sql.pl, a handy script to have when you want to extract out certain tables from a large MySQL dump file (in this case a 7.7 GB dump file). I used it like this (replace table_name with your table you want to extract):
./extract_sql.pl -t table_name -r daily_backup_2017-03-30_03h00m_Thursday.sql > table_name.sql
I then created a database named `restore` on the same server, and imported the data from the `table_name.sql` file to create and populate the table (my backups include DROP and CREATE statements). Now my `table_name` was in my `restore` database with all of the data up to the last backup.
I then backed up the current table, just in case!
mysqldump -u myuser -p real_database table_name> table_name_backup.sql
Finally, I needed to update just the rows that I wanted, based on conditions in the current database. After searching, I found and modified a solution that worked:
UPDATE real_database.table_name live INNER JOIN restore.table_name old ON live.pk = old.pk SET live.add_date = old.add_date, live.update_date = old.update_date, live.completed_date = old.completed_date WHERE live.add_date = 0
Once I ran the above, all of the data in the restore database updated the data in the live datatbase up to where the backup was made. From there I had to adjust today’s dates, but that was strait forward. I then repeated these steps for each table that needed fixing.