Use MySQL Log Recovery

Using MySQL logs, You can redo some database operations:

D: \ mysql server 5.5 \ bin> mysqlbinlog "D: \ mysql server 5.5 \ data \ mysql-bin.000001"> D: \ test. SQL

Mysql-bin.000001 is a log file that is output to common SQL formats

/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4#110808 11:33:52 server id 1  end_log_pos 107 Start: binlog v 4, server v 5.5.11-log created 110808 11:33:52 at startup# Warning: this binlog is either in use or was not closed properly.ROLLBACK/*!*/;BINLOG 'IFk/Tg8BAAAAZwAAAGsAAAABAAQANS41LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAgWT9OEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA=='/*!*/;# at 107#110808 11:36:40 server id 1  end_log_pos 194 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312774600/*!*/;SET @@session.pseudo_thread_id=2/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C gbk *//*!*/;SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create database testdb/*!*/;# at 194#110808 11:44:33 server id 1  end_log_pos 319 Querythread_id=2exec_time=0error_code=0use testdb/*!*/;SET TIMESTAMP=1312775073/*!*/;create table student(name varchar(10), sex char(2), age int)/*!*/;# at 319#110808 11:45:48 server id 1  end_log_pos 389 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312775148/*!*/;BEGIN/*!*/;# at 389#110808 11:45:48 server id 1  end_log_pos 513 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312775148/*!*/;insert into student(name,sex,age) values('zhangsan','f',20)/*!*/;# at 513#110808 11:45:48 server id 1  end_log_pos 540 Xid = 22COMMIT/*!*/;# at 540#110808 11:48:48 server id 1  end_log_pos 610 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312775328/*!*/;BEGIN/*!*/;# at 610#110808 11:48:48 server id 1  end_log_pos 730 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312775328/*!*/;insert into student(name,sex,age) values('lisi','f',25)/*!*/;# at 730#110808 11:48:48 server id 1  end_log_pos 757 Xid = 24COMMIT/*!*/;# at 757#110808 11:49:00 server id 1  end_log_pos 827 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312775340/*!*/;BEGIN/*!*/;# at 827#110808 11:49:00 server id 1  end_log_pos 949 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312775340/*!*/;insert into student(name,sex,age) values('wangwu','w',18)/*!*/;# at 949#110808 11:49:00 server id 1  end_log_pos 976 Xid = 25COMMIT/*!*/;# at 976#110808 11:49:12 server id 1  end_log_pos 1046 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312775352/*!*/;BEGIN/*!*/;# at 1046#110808 11:49:12 server id 1  end_log_pos 1169 Querythread_id=2exec_time=0error_code=0SET TIMESTAMP=1312775352/*!*/;insert into student(name,sex,age) values('zhaoliu','w',22)/*!*/;# at 1169#110808 11:49:12 server id 1  end_log_pos 1196 Xid = 26COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Database Operations:

Mysql> Create Database testdb;

Mysql> Create Table student (name varchar (10), sex char (2), age INT );

Mysql> insert into student (name, sex, INT) values ('hangsan', 'F', 20 );

Mysql> insert into student (name, sex, age) values ('lisi', 'F', 25 );

Mysql> insert into student (name, sex, age) values ('wangwu', 'w', 18 );

Mysql> insert into student (name, sex, age) values ('zhaoliu', 'w', 22 );

Time postmark JavaScript conversion method:

 var i = 1312775328;  alert(new Date(1312775328*1000));

Restore logs at the specified location:

D: \ mysql server 5.5 \ bin> mysqlbinlog -- start-position = 1046 -- stop-position = 1169 "D: \ mysql server 5.5 \ data \ mysql-bin.000001 "| mysql-u root-P
Enter password:

Before recovery:

Mysql> select * from student;
+ ---------- + ------ +
| Name | sex | age |
+ ---------- + ------ +
| Zhangsan | f | 20 |
| Lisi | f | 25 |
| Wangwu | w | 18 |
| Zhaoliu | w | 22 |
+ ---------- + ------ +
4 rows in SET (0.00 Sec)

After recovery:

Mysql> select * from student;
+ ---------- + ------ +
| Name | sex | age |
+ ---------- + ------ +
| Zhangsan | f | 20 |
| Lisi | f | 25 |
| Wangwu | w | 18 |
| Zhaoliu | w | 22 |
| Zhaoliu | w | 22 |
+ ---------- + ------ +
5 rows in SET (0.00 Sec)

We can see that the so-called restoration is nothing more than redo the database operations within the range marked by the log. The start and end locations of log recovery operations are marked in the black text section:

#1046
#110808 11:49:12 server Id 1 end_log_pos1169Query thread_id = 2 exec_time = 0 error_code = 0
Set timestamp = 1312775352 /*! */;
Insert into student (name, sex, age) values ('zhaoliu', 'w', 22)
/*! */;
# At 1169

References:

Using BINLOG Incremental Backup in MySQL: MySQL Date: Author: Yu Shang

MySQLExploitationBINLOGIncremental Backup

I. What is Incremental backup?Incremental Backup backs up new data. If you have 10 Gb of data in a database, you can add 10 MB of data every day. The database must be backed up once every day. Do you need to back up so much data? We only need to back up the added data. Obviously, I only need to back up the added data. This reduces the burden on the server.
2. Enable BINLOGVI my. CNF

Log-bin =/var/lib/MySQL/mysql-bin.log, if so log-bin = mysql-bin.log is under the datadir directory by default

[Root @ blackghost MySQL] # ls | grep mysql-bin
Mysql-bin.000001
Mysql-bin.000002
Mysql-bin.000003
Mysql-bin.000004
Mysql-bin.000005
Mysql-bin.000006
Mysql-bin.index

After mysql-bin is started, one or more files are generated.

Mysql-bin.000002 files store the data that is added to the database every day, where the incremental data of all databases is located.

3. check what is in a file like a mysql-bin.000002

[Root @ blackghost MySQL] # mysqlbinlog/var/lib/MySQL/mysql-bin.000002 & gt;/tmp/Add. SQL

An important index file is mysql-bin.index.

[Root @ blackghost MySQL] # Cat mysql-bin.index

Iv. Incremental backup and incremental Restoration

1. Incremental Backup

Now that we know that the newly added data in MySQL is in a file like mysql-bin, we only need to back up the file like mysql-bin.

CP/var/lib/MySQL-Bin */data/mysql_newbak/

2. incremental Restoration: some common and useful

A) restore-start-date,-Stop-date by Time

Mysqlbinlog-start-date = "2010-09-29 18:00:00"-Stop-date = "2010-09-29 23:00:00 ″\

/Var/lib/MySQL/mysql-bin.000002 | mysql-u root-P

B), restore Based on the starting position,-start-position,-Stop-Position

Mysqlbinlog-start-position = 370-stop-position = 440/var/lib/MySQL/mysql-bin.000002 | mysql-u root-P

Mysqlbinlog-start-position = 370-stop-position = 440/var/lib/MySQL/mysql-bin.000002

-Start-position = 370-stop-position = 440 where does the number come from?

Log Files
#370
#100929 21:35:25 server Id 1 end_log_pos440Query thread_id = 1 exec_time = 0 error_code = 0
Set timestamp = 1285767325 /*! */;

The above red bold is, one is start-position, the other is stop-Position

C) restore the database by name-d

Here is a lowercase D. Do not mix it with-D in mysqldump. Haha.

MySQL BINLOG-d test/var/lib/MySQL/mysql-bin.000002

D), which is divided into-H based on the IP address of the database.

Mysqlbinlog-H 1921381.102/var/lib/MySQL/mysql-bin.000002

E) According to the port occupied by the database,-P

Sometimes, we use port 3306 for MySQL. Note that it is an uppercase P

Mysqlbinlog-P 13306/var/lib/MySQL/mysql-bin.000002

F) restore-server-id based on the database serverid

In the database configuration file, there is a serverid and the serverid in the same cluster cannot be the same.

Mysqlbinlog-server-id = 1/var/lib/MySQL/mysql-bin.000002

Note: I have mentioned the above examples one by one. In fact, they can be arranged and combined. For example

Mysqlbinlog-start-position = "2010-09-29 18:00:00"-d test-H 127.0.0.1/var/lib/MySQL/mysql-bin.000002 | mysql-u root-P

5. Follow-up

One unpleasant thing about Incremental backup is that files such as MySQL-bin will be added every time MySQL is started. If you don't care about them, it will take a long time, it will fill up your disk.

./Mysqldump-flush-logs-u root myblog>/tmp/myblog. SQL

Back up the myblog database and clear the data about myblog In the Incremental backup.

./Mysqldump-flush-logs-u root-all-databases>/tmp/alldatabase. SQL

Back up all databases and clear Incremental Backup

Mysql-bin.index index, because the incremental data is not necessarily in a file such as a mysql-bin000, at this time, we will find an incremental file such as MySQL-bin according to the mysql-bin.index.

If BINLOG-do-DB = test1 is configured in MySQL, only data in the database test1 is available in the Incremental backup.