1

Is it possible to simply restore a single schema and bring it to a point in time (Say three hours ago) without bringing the entire database back?

I can think of a lengthy method of backing up each schema individually at present time, bringing the database back three hours and restoring all other schemas (except for the one you want to roll back) back to present time.

Is there any other method? Or am I not understanding binary logs correctly?

1 Answer 1

4

You can extract the SQL from a specific schema with the

Use mysqlbinlog utility against all binary logs that have the data and time ranges. You will have to name the database you are extracting:

For example, suppose you have the following scenario

  • You have these binary logs
  • mysql-bin.000019
  • mysql-bin.000020
  • mysql-bin.000021
  • mysql-bin.000022
  • Binary Logs span from 2013-01-30 09:30:00 to 2013-02-04 12:00:00
  • You have a full backup from 2013-02-03 00:00:00 (mysqldata-20130203.sql)
  • You want to restore MyProdDB from 02/03/13 at 2:45 PM 2013-02-03 14:45:00

#STEP 01) Get all DB Changes from the Binary Logs

DB=MyProdDB
BEG_DT="2013-02-03 00:00:00"
END_DT="2013-02-03 14:45:00"
BINLOGS="mysql-bin.000019 mysql-bin.000020 mysql-bin.000021 mysql-bin.000022"
mysqlbinlog -d ${DB} --start-datetime="${BEG_DT}" --stop-datetime="${END_DT}" ${BINLOGS} > mysqldata-delta.sql

#STEP 02) Load Data into Staging Database called MyProdDBStaging

mysql -u... -p... -DMyProdDBStaging < mysqldata-20130203.sql

#STEP 03) Load Delta into Staging Database called MyProdDBStaging

mysql -u... -p... -DMyProdDBStaging < mysqldata-delta.sql

Now, the target database will be the point and time instance for MyProdDB at 2:45 PM on Feb 3, 2013.

Give it a Try !!!

6
  • 2
    Rolando, you're a MySQL machine :)
    – Philᵀᴹ
    Feb 4, 2013 at 19:04
  • Awesome! You're the man. Time to go practice this. Feb 4, 2013 at 19:09
  • This implemented for cPanel would be a wonderful solution! Jul 27, 2016 at 3:06
  • Is there a way to perform this without using a staging database? By just selecting the bin-logs related to the MyProdDB and just apply those ones... Dec 21, 2020 at 12:28
  • @DavideCarta what you are asking for is already covered in STEP 01. The -d option of mysqlbinlog script selects binlogs events from the MyProdDB only. Dec 21, 2020 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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