How to Use binlog to restore valid data after MySQL Data is lost

How to Use binlog to restore valid data after MySQL Data is lost

Enable binlog, edit the my. cnf file, and add

Log-bin = mysql-bin # enable the binary log and specify the path and file name, if not set, by default in the data storage directory, the name is hostname-bin.xxxxx

Create a test database, create a table, and insert some data

Mysql> create database student;
Mysql> use student;
Mysql> create table student (id int, name varchar (10), age int );
Mysql> insert into student values ('1', 'tanglu', '28'), ('2', 'zouxiaolu', '28'), ('3 ', 'beibei ', '28 ');

Perform a complete backup so that the first three data items have been backed up.

Mysqldump-u root-p student> student. SQL

Insert 4, 5, and 6 data

Mysql> insert into student values ('4', 'douu', '8 ');
Query OK, 1 row affected (0.01 sec)

Mysql> insert into student values ('5', 'mengmeng', '28 ');
Query OK, 1 row affected (0.00 sec)

Mysql> insert into student values ('6', 'hahaha', '10 ');
Query OK, 1 row affected (0.01 sec)

Accidentally deleted the previously created data.

Mysql> delete from student;

Restart the database once or execute the database statement flush-logs to obtain a new binary log file, so that all the fault logs are in the previous file.

Mysql> flush logs;

Use the first backup to restore data, so that the first three data records are successfully restored.

Mysql-u root-p student <student. SQL

View binlog logs, analyze the position or operation time of some normal operations, and then export and restore these logs.

Hostname-bin.000001 for mysqlbinlog
Mysqlbinlog server1-bin.000003 -- start-position = 1170 -- stop-position = 1294> server2. SQL

Finally, go to the database and check that the data has been restored by entry!

Practices for restoring data using MySQL binlog

MySQL uses binlog to restore Data

MySQL uses backup and binlog for data recovery

MySQL Data Recovery-binlog

Clear binlog logs in MySQL

How to safely delete binlog logs under MySQL

MySQL -- binlog log data recovery

How does MySQL Delete binlog and restore Data?

Introduction and Analysis of Three MySQL binlog formats

MySQL uses binlog Incremental backup + restore instance

MySQL deletes binlog logs and restores data.

This article permanently updates the link address: