MySQL Backup – Point in Time Recovery for a Single Schema

backuplogsMySQLrestore

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?

Best Answer

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 !!!

STEP #1

Add the option to my.cnf

[mysqld]
innodb_file_per_table = 1

STEP #2

If you are running MySQL 5.6, login to mysql and run

mysql> SET GLOBAL innodb_file_per_table = 1;

If you are not running MySQL 5.6, restart mysqld

# service mysql restart

STEP #3

Write a script to create NULL ALTER TABLE commands

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"

SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='InnoDB'"
SQL="${SQL} ORDER BY (data_length+index_length)"
mysql ${MYSQL_CONN} -ANe"${SQL}" > Extract_InnoDB_From_ibdata1.sql

STEP #4

View the script

less Extract_InnoDB_From_ibdata1.sql

If you are OK with it, then run it

mysql ${MYSQL_CONN} < Extract_InnoDB_From_ibdata1.sql

CAVEAT

This will not shrink ibdata1. It merely extracts all InnoDB tables from it. ibdata1 will no longer grow beyond the 20GB due to the tables themselves.

If you want to shrink ibdata1, please refer to my post How do I shrink the innodb file ibdata1 without dumping all databases?

Best Answer

Related Solutions

Mysqldump –flush-logs results in small backup files

MySQL – Changing from system tablespace to file per table

STEP #1

STEP #2

STEP #3

STEP #4

CAVEAT

GIVE IT A TRY !!!

Related Question