Restore Data In MySQL DB from Another MySQL DB

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.

HTH!

Posted in MySQL