[MySQL] MySQL & MariaDB bin log를 이용한 데이터 복구

[mysql@localhost ~]$ mysqlbinlog /var/log/mariadb/mysql-bin.000002 > ./allbinlog.sql
[mysql@localhost ~]$ vi ./allbinlog.sql

# Number of rows: 1
# at 483
# at 666
#200721 10:29:06 server id 1 end_log_pos 666 CRC32 0xdd7ac928 Annotate_rows:
#Q> insert into member (MEM_NO,MEM_CODE,MEM_ID,MEM_PASSWD,MEM_LAST_NAME,MEM_FIRST_NAME) VALUES(NEXTVAL(member_seq),'NOR','backup1',password('a123456!'),'백','업')
#200721 10:29:06 server id 1 end_log_pos 748 CRC32 0xe7f98c8d Table_map: `vtgshop`.`member` mapped to number 31
# at 748
#200721 10:29:06 server id 1 end_log_pos 855 CRC32 0x3dd7bf96 Write_rows: table id 31 flags: STMT_END_F

BINLOG '
4kQWXxMBAAAAUgAAAOwCAAAAAB8AAAAAAAEAB3Z0Z3Nob3AABm1lbWJlcgAMAw8KD/4SDw8PEv4P
EloAPAD+CQCWAJYAlgAA/gNaAK4FjYz55w==
4kQWXxcBAAAAawAAAFcDAAAAAB8AAAAAAAEADP//LPXMCwAAA+yXhQNOT1IHYmFja3VwMSkqMTY1
RDJCMUNCQ0VENUE5OTQwRDk3QjY3RDc1MTcwQTc0NUE3M0RDOJmm6qdGA+uwsZa/1z0=
'/*!*/;

======================================

중략

======================================

# Number of rows: 1
# at 3090
#200721 10:29:48 server id 1 end_log_pos 3121 CRC32 0x9c892f5a Xid = 50
COMMIT/*!*/;
# at 3121
#200721 10:29:54 server id 1 end_log_pos 3163 CRC32 0x8f34d88c GTID 0-1-7 trans
/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
BEGIN
/*!*/;
# at 3163
# at 3347
#200721 10:29:54 server id 1 end_log_pos 3347 CRC32 0x18efb32b Annotate_rows:
#Q> insert into member (MEM_NO,MEM_CODE,MEM_ID,MEM_PASSWD,MEM_LAST_NAME,MEM_FIRST_NAME) VALUES(NEXTVAL(member_seq),'NOR','backup7',password('a123456!'),'백','업7')
#200721 10:29:54 server id 1 end_log_pos 3429 CRC32 0x6051a46b Table_map: `vtgshop`.`member` mapped to number 31
# at 3429
#200721 10:29:54 server id 1 end_log_pos 3537 CRC32 0x1a1896db Write_rows: table id 31 flags: STMT_END_F

BINLOG '
EkUWXxMBAAAAUgAAAGUNAAAAAB8AAAAAAAEAB3Z0Z3Nob3AABm1lbWJlcgAMAw8KD/4SDw8PEv4P
EloAPAD+CQCWAJYAlgAA/gNaAK4Fa6RRYA==
EkUWXxcBAAAAbAAAANENAAAAAB8AAAAAAAEADP//LPXSCwAABOyXhTcDTk9SB2JhY2t1cDcpKjE2
NUQyQjFDQkNFRDVBOTk0MEQ5N0I2N0Q3NTE3MEE3NDVBNzNEQziZpuqndgPrsLHblhga
'/*!*/;

# Number of rows: 1
# at 3537

[mysql@localhost ~]$ mysqlbinlog --start-position=483 --stop-position=3537 /var/log/mariadb/mysql-bin.000002 > restorebinlog.sql
[mysql@localhost ~]$ mysql -u root -p < restorebinlog.sql
Enter password:

[mysql@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.23-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use vtgshop
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [vtgshop]> select mem_no,mem_id from member where mem_id like 'backup%';
+--------+---------+
| mem_no | mem_id |
+--------+---------+
| 3020 | backup1 |
| 3021 | backup2 |
| 3022 | backup3 |
| 3023 | backup4 |
| 3024 | backup5 |
| 3025 | backup6 |
+--------+---------+
6 rows in set (0.001 sec)