Thesqlbinlog recovery table wise

MySQLmysqlbinlogrestore

I am going to restore my MySQL database table using log files. My database name is "decsys" .

I have already created sql using log files

C:\wamp\bin\mysql\mysql5.5.8\bin>mysqlbinlog  -d decsys C:\wamp\bin\mysql\mysql5.5.8\data\mysql-bin.0000xx > sql1.sql (file size 8GB)

I want recover not whole database, only single table. (table name :cm).

Best Answer

The most error-proof approach is to restore all databases -- the entire server -- to a different machine, by loading a backup (dump) file and playing back the appropriate logs, beginning at the binlog coordinates where the dump began... and then using mysqldump to extract the one table of interest and copy it to the desired destination.

In any event, you do have to restore something from a backup, first -- which I assume you understand, but have not mentioned. Playing back the binary logs allows you to capture the events that occurred between the restored backup and the end of the log.

Playing back a single table's row events is, to my knowledge, not supported. But, even restoring a single database (schema) this way is a complex operation.

The --database or -d option for mysqlbinlog can only definitively identify the desired database with sufficient accuracy when the binlog_format on the server that created the log entries was set to ROW, or, in the case where the binlogs were generated by a replica with log_slave_updates enabled, at least one of the servers in the cascade, including the last one, had its binlog_format set to ROW, because this coerces the replication events into ROW format, which is a one-way process.

If the events were not logged in ROW format, then playing back the binary logs can't be guaranteed to work correctly without all of the other data on the server also being present. It is also not possible to be certain that the --database or -d option will catch all of the statements, if they were not logged in ROW format, because of interactions with the USE statement and other intricacies of how MySQL interprets replication events.

This does not mean that mysqlbinlog is broken, only that it's an advanced tool that can only be used within the constraints of the way replication works.

The binary log format was designed for replicating changes from one server to another, and only contains enough information to do that when the two servers in question begin with the same data set -- hence the reference to restoring from a backup.

Also, playing back a binary log into your production system is really not something you would ever want to do unless your production system had been damaged so badly that you had no choice but to completely reload it from a backup and wanted to play-forward through the available logs to minimize the amount of lost data.

MyISAM

For a MyISAM table mydb.mytable, you should have three files

\bin\mysql\mysql5.6.12\data\mydb\mytable.frm
\bin\mysql\mysql5.6.12\data\mydb\mytable.MYD
\bin\mysql\mysql5.6.12\data\mydb\mytable.MYI

They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.

InnoDB

Take a look at this Pictorial Representation of InnoDB

InnoDB Architecture

The only thing that attaches ibdata1 to the .ibd files is the data dictionary.

Your mission, should you decide to accept it, is to create each table and swap in the .ibd

Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another

Here is a sample

Suppose you have a database mydb with the table mytable. This means

You have the folder \bin\mysql\mysql5.6.12\data\mydb
Inside that folder, you have
- mytable.frm
- mytable.ibd

You need the .frm. If you look at my post How can extract the table schema from just the .frm file?, you can download a MySQL utility that can generate the SQL needed to create the table.

You should now do the following

Move mytable.ibd to \bin\mysql\mysql5.6.12\data
Run the SQL to create the InnoDB table
Login to mysql and run ALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)
Copy \bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydb
Login to mysql and run ALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)

After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:

SELECT * FROM mydb.mytable LIMIT 10;

Give it a Try !!!

DRINK (Data Recovery Incorporates Necessary Knowledge) Responsibly

Thesqlbinlog failing with “ ERROR 1032 (HY000) at line 48: Can’t find record in “ ” Error

PROBLEM

You are starting from the wrong log. You should start with mysql-bin.000034 not mysql-bin.000035

The timestamp on each binlog shows the last time a binlog event was written to it

mysql-bin.000033 was last written Dec 27 18:26
mysql-bin.000034 was last written Feb 2 18:58. That's 18 hrs 58 min after Feb 2 00:00
mysql-bin.000035 had to be created Feb 2 18:58

Given the timestamps mentioned, mysql-bin.000034 is the log you need to start with.

SOLUTION

You should collect the binlog events from Feb 2 00:00

Please run the following

MYCHANGES=ChangesSinceFeb2Backup.sql
BINLOGS="mysql-bin.000034 mysql-bin.000035"
cd /var/lib/mysql
mysqlbinlog --start_datetime="2014-02-02 00:00:00" ${BINLOGS} > ${MYCHANGES}

Restore the Feb 2 mysqldump Backup, then play all the events

mysql -u root -p db < ${MYCHANGES}

This will bring back everything up to Feb 18 15:50

If you want to do the PITR up to today at 12:45, you do this

MYCHANGES=ChangesSinceFeb2Backup.sql
cd /var/lib/mysql
mysqlbinlog --start_datetime="2014-02-02 00:00:00" --stop_datetime="2014-02-18 12:45:00" ${BINLOGS} > ${MYCHANGES}

SUGGESTION

Note that mysql-bin.000035 contains several days worth of binlog events.

Before you run the midnight backup, you should run

FLUSH BINARY LOGS;

That way, you can clearly identify what binary logs to use in PITR