Database single table incremental backup scheme

Scenario 1:

For a table, the simplest way to implement backups is to back up directly, which includes direct backup of data and structure, which is a row for smaller database tables, but there are some problems with this approach: for example, if the table has large data? Up to G, a few g, dozens of g and so on, so the backup time is very resource and time, so I think such a backup is not worth recommending.

So for the above problem, I use a trigger to achieve, the specific ideas are as follows:

1, assume that there is now a table mytest need to do backup, and then have a mytest_backup table to save the backup data, and then the structure of the two tables is the same;

For example:

MyTest

CREATE TABLE ' mytest ' (

' ID ' bigint (a) not NULL auto_increment,

' colum1 ' varchar DEFAULT NULL,

' colum2 ' varchar DEFAULT NULL,

' colum3 ' varchar DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=3 DEFAULT Charset=utf8

Mytest_backup:

CREATE TABLE mytest_backup like mytest;

2, the establishment of trigger Mytest_backup, whenever the mytest have data insertion, it is also inserted into the mytest_backup just the data, so that the implementation of the backup, the specific implementation of the following:

BEGIN

INSERT into Mytest_backup

SET id = new.id,

COLUM1 = New.colum1,

colum2 = new.colum2,

COLUM3 = new.colum3;

END

3, how to do when the update? Create a new table Mytest_update, create a new trigger, each time we modify the data, the modified each piece of data is recorded, the backup can only be performed when the data to update it:

Mytest_update:

CREATE TABLE ' Mytest_update ' (

' ID ' bigint (a) not NULL auto_increment,

' colum1 ' varchar DEFAULT NULL,

' colum2 ' varchar DEFAULT NULL,

' colum3 ' varchar DEFAULT NULL,

' update_id ' bigint () DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=4 DEFAULT Charset=utf8

The triggers are as follows:

BEGIN

INSERT into Mytest_update

Set update_id = New.id,

COLUM1 = New.colum1,

colum2 = new.colum2,

COLUM3 = new.colum3;

END

4, then delete the same method can be implemented, create a new Mytest_delete table, and then whenever there is a delete operation, we will save the ID, backup when the corresponding ID of the backup table is deleted after it can be:

Mytest_delete

CREATE TABLE ' Mytest_delete ' (

' ID ' bigint (a) not NULL auto_increment,

' delete_id ' bigint () not NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=2 DEFAULT Charset=utf8

Trigger:

BEGIN

INSERT into Mytest_delete

Set delete_id = Old.id;

END

5, Recovery: When the recovery, the Test_backup all the data records to the original table, and then the Test_update table all the data in the backup, and then perform the deletion according to all the IDs of Mytest_delete, so that the data of this table can be restored.

Database single table incremental backup scheme