MySQL Forums
Forum List  »  Data Recovery

Restore from binlog
Posted by: Matthieu COUSSI
Date: January 21, 2022 07:14AM

Hi,

I'am curently testing backup restore scenario of a mysql database.

I tried to recover a record from a binlog.

In the example below :
1 - I switch the binlog
2 - I clean the binlog
3 - insert data in my table
4 - truncate the table


mysql> show binary logs;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| localhost-bin.000017 | 196 | No |
+----------------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> use tmatt;
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
mysql> INSERT INTO test_restauration(message,created_at) VALUES('TEST - RESTAURATION AFTER MYSQLDUMP TOTO',NOW());
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_restauration;
+----+------------------------------------------+---------------------+
| id | message | created_at |
+----+------------------------------------------+---------------------+
| 1 | TEST - RESTAURATION BEFORE MYSQLDUMP | 2022-01-21 10:32:04 |
| 2 | TEST - RESTAURATION AFTER MYSQLDUMP TOTO | 2022-01-21 11:24:44 |
+----+------------------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql> truncate table test_restauration;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from test_restauration;
Empty set (0.00 sec)



New, i backup the binlog and analyse the content to find the position from and to i need to recover to get my delete record (in my example, i have two record, ic onsidere that the first record will be recover the full backup)

[root@tmysql test_backup]# mysqlbinlog -u${MYSQL_USER} -h${MYSQL_HOST} --read-from-remote-server --raw localhost-bin.000017




[root@tmysql test_backup]# mysqlbinlog -v /tmp/test_backup/localhost-bin.000017

[...]

BINLOG '
7InqYRMBAAAASAAAAK8BAAAAANgAAAAAAAEABXRtYXR0ABF0ZXN0X3Jlc3RhdXJhdGlvbgADAw8S
A/8AAAABAQACAQhEJnje
7InqYR4BAAAAVgAAAAUCAAAAANgAAAAAAAEAAgAD/wACAAAAKFRFU1QgLSBSRVNUQVVSQVRJT04g
QUZURVIgTVlTUUxEVU1QIFRPVE+Zq+q2LE/rtW4=
'/*!*/;
### INSERT INTO `tmatt`.`test_restauration`
### SET
### @1=2
### @2='TEST - RESTAURATION AFTER MYSQLDUMP TOTO'
### @3='2022-01-21 11:24:44'
# at 517

[...]




And i restore my data from the binlog.


[root@tmysql test_backup]# mysqlbinlog --start-position=4 --stop-position=625 /tmp/test_backup/localhost-bin.000017 | mysql -u${MYSQL_USER} -h${MYSQL_HOST} tmatt


But my table is empty. I have insert before restore a record to ensure that the truncate in the binlog not apply.

mysql> select * from test_restauration;
+----+--------------------------------------------+---------------------+
| id | message | created_at |
+----+--------------------------------------------+---------------------+
| 1 | TEST - RESTAURATION AFTER MYSQLDUMP TOTO 2 | 2022-01-21 11:38:33 |
+----+--------------------------------------------+---------------------+
1 row in set (0.00 sec)


Howerver, my lost record [TEST - RESTAURATION AFTER MYSQLDUMP TOTO] doen't restore by the binlog apply.


Do you have an idea?

Thanks for your help,

Matt

Options: ReplyQuote


Subject
Views
Written By
Posted
Restore from binlog
105
January 21, 2022 07:14AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.