Shulou(Shulou.com)06/01 Report--

Boring research binlog wrote information to extract the binlog of a single table to facilitate mistakenly deleting and restoring data. The purpose of doing this is to reduce errors that may be caused by other tables during recovery, and to shorten the recovery time.

First, study the format of binlog logs.

As follows: / *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 160229 15:19:52 server id 162 end_log_pos 123 CRC32 0xbbe9b551 Start: binlog v 4, server v 5.7.9-log created 160229 15:19:52 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACKUPUBERBACKUBUBUBUR

BINLOG'

GPHTVg+iAAAAdwAAAHsAAAABAAQANS43LjktbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAY8dNWEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AVG16bs=

'/ *! * /

# at 123

# 160229 15:19:52 server id 162 end_log_pos 154 CRC32 0xd473f6f4 Previous-GTIDs

# [empty]

# at 154

# 160229 15:18:07 server id 162 end_log_pos 219 CRC32 0xe86bb499 Anonymous_GTID last_committed=0 sequence_number=1

SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS.com

# at 219

# 160229 15:18:07 server id 162 end_log_pos 291 CRC32 0xe1803afe Query thread_id=102887 exec_time=341 error_code=0

SET timestamp 1456730287

SET @ @ session.pseudoplastics thread readreadable cards 102887pxxxx

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards modewords 524288 Universe accounts /

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C latin1 * /! * /

SET @ @ session. Session. Session setting client.session. Session. Collationalization connections8 session. Session. Collationalization server33.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

BEGIN

/ *! * /

# at 291

# 160229 15:18:07 server id 162 end_log_pos 343 CRC32 0xcfc435f2 Table_map: `test`.ty` mapped to number 127th

# at 343

# 160229 15:18:07 server id 162 end_log_pos 463 CRC32 0xb4ef18fe Write_rows: table id 127 flags: STMT_END_F

BINLOG'

Rbat DTVhOiAAANAAAFcBAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAP8jXEzwspell =

R/DTVh7iAAAAeAAAAM8BAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB

QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA

AAD+GO+0

'/ *! * /

# at 463

# 160229 15:18:07 server id 162 end_log_pos 494 CRC32 0xed1fb95b Xid = 53

If the first commit is necessary for the first part of binlog, the missing will report an error.

# at 494

# 160229 15:18:07 server id 162 end_log_pos 559 CRC32 0xef3ac14c Anonymous_GTID last_committed=1 sequence_number=2

SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS.com

# at 559

# 160229 15:18:07 server id 162 end_log_pos 631 CRC32 0xe834f1d8 Query thread_id=102887 exec_time=350 error_code=0

SET timestamp 1456730287

BEGIN

/ *! * /

# at 631

# 160229 15:18:07 server id 162 end_log_pos 683 CRC32 0xc64ac724 Table_map: `test`.ty` mapped to number

# at 683

# 160229 15:18:07 server id 162 end_log_pos 803 CRC32 0x4a5f1c75 Write_rows: table id 127 flags: STMT_END_F

BINLOG'

Rbat DTVhOiAAANAAAAKsCAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAPJMdKxghammer =

R/DTVh7iAAAAeAAAACMDAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB

QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA

AAB1HF9K

'/ *! * /

# at 803

# 160229 15:18:07 server id 162 end_log_pos 834 CRC32 0x96148022 Xid = 77

The next commit is the second part, a thing statement that can be missing.

# at 834

# 160229 15:18:07 server id 162 end_log_pos 899 CRC32 0xa1e52982 Anonymous_GTID last_committed=2 sequence_number=3

SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS.com

# at 899

# 160229 15:18:07 server id 162 end_log_pos 971 CRC32 0x89fc0e48 Query thread_id=102887 exec_time=359 error_code=0

SET timestamp 1456730287

BEGIN

/ *! * /

# at 971

# 160229 15:18:07 server id 162 end_log_pos 1023 CRC32 0xc1309696 Table_map: `test`.ty` mapped to number

# at 1023

# 160229 15:18:07 server id 162 end_log_pos 1143 CRC32 0x0993d08b Write_rows: table id 127 flags: STMT_END_F

BINLOG'

RDTVhOiAAANAAAAP8DAAAAH8AAAAAAAAEABHRlc3QAAnR5AAQQDDw8DBBQAFAAPlpYwwQratio =

R/DTVh7iAAAAeAAAAHcEAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB

QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA

AACL0JMJ

'/ *! * /

# at 1143

# 160229 15:18:07 server id 162 end_log_pos 1174 CRC32 0x7c2054fd Xid = 101

. Omit

The last commit must exist in the end.

SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * /! * /

DELIMITER

# End of log file

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

The idea is to extract the first part and the last part first, and then we csplite file (with commit _ binlog _ csplit; for delimiting marks, those who are not familiar with the binlog command should first familiarize themselves with the usage), insert the binlog information of a single table between the first and last parts to form a log file that conforms to the binlog format.

The code is as follows: [root@localhost chenliang] # more v1.sh

#! / bin/bash

# author:Jonnychen

# date:2016-3-1

# set-x

# get the key value of input

Get_key_value ()

{

Echo "$1" | sed's / ^-- [AmurZamurzhuang -] * = / /'

}

Use_help ()

{

Cat ${vbinlog} .txt

Filename=$ {vbinlog} .txt

# txt log files are cut according to commit field

Csplit $filename / COMMIT\ / *\ /-n 1-s {*}-f vcommit

Cou_comm= `ls-l | grep vcommit | awk-F "'{print $9}'| wc-l`

Let "maxseq=$ {cou_comm}-1"

# echo $maxseq

Let "sec_max=$maxseq-1"

# echo ${sec_max}

Cat vcommit0 > > ${vbinlog} .SQL # extract the first part

For ((item1witi > ${vbinlog} .sql

Fi

Done

Cat vcommit$maxseq > > ${vbinlog} .SQL # extract the last part

Rm-rf $filename vcommit*

Test:

1:mysql > select * from TEAMS; Raw data +-+

| | TEAMNO | PLAYERNO | DIVISION | |

+-+

| | 1 | 6 | first |

| | 2 | 27 | second |

| | 3 | 89 | asd |

| | 4 | 89 | BBBB |

| | 5 | 89 | DDDD |

| | 6 | 67 | GGGG |

| | 7 | 77 | KKKK |

The simulation makes a backup mysqldump-uroot-p`cat / etc/sqlpass `- F TENNIS > TENNIS.sql-at this time binlog reaches 19

Simulate and do some operations mysql > insert into TEAMS values (8, 7, 7, 5, 5, 7, 5, 5, 7, 7, 5, 7, 7, 5, 7, 5, 7, 7, 5, 7, 5, 4, 4, 4, 7, 7, 7, 5, 4, 4, 4, 4, 7, 7, 7, 5, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6

Query OK, 1 row affected (0.00 sec)

Mysql > insert into TEAMS values (9, 7, 7, 5, 9, 7, 7, 9, 7, 7, 9, 7, 7, 9, 7, 7, 9, 7, 7, 9, 7, 7, 4, 4, 4, 4, 9, 7, 7, 4, 2, 4, 4, 2, 3, 2, 3, 4, 4, 4, 4, 9, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,

Query OK, 1 row affected (0.00 sec)

Mysql > insert into TEAMS values (10, 7, 7, 10, 7, 10, 7, 10, 7, 10, 7, 7, 10, 7, 10, 7, 10, 7, 7, 10, 7, 7, 10, 7, 7, 4, 4, 4, 4, 4, 4, 4, 4, 4, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 6, 6, 6, 6, 9, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6,

Query OK, 1 row affected (0.01sec)

Mysql > update TEAMS set DIVISION='pppp' where TEAMNO in (9, 10);-misoperation

Query OK, 2 rows affected (0.01sec)

Rows matched: 2 Changed: 2 Warnings: 0

Restore:

Mysql > flush logs

Query OK, 0 rows affected (0.00 sec)

[root@localhost chenliang] # mysql-uroot-p`cat / etc/sqlpass `TENNIS

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.