MySQL -- binlog log data recovery

The important command to recover data is mysqlgt; flushlogs; the default log is the mysql-bin.000001, And now refresh and re-open one, there is an additional mysql-bin.000

The important command to recover data is mysqlgt; flush logs; the default log is the mysql-bin.000001, And now refresh and re-open one, there is an additional mysql-bin.000

Important commands for data recovery are as follows:
Mysql> flush logs; the default log is the mysql-bin.000001, And now refresh and re-open one, there will be an additional mysql-bin.000002
./Mysqlbinlog -- no-defaults binlog log name to view logs
[Root @ localhost bin] #./mysqlbinlog -- no-defaults ../var/mysql-bin.000001 | more // view the content of the bin-log
[Root @ localhost bin] #./mysqlbinlog -- no-defaults ../var/mysql-bin.000001 |./mysql-uroot-p // restore the contents of the mysql-bin.000001 log
To restore data from a point to a point, use the following operations:
Positioning: -- start-position start point
-- Stop-position end point
-- Start-date start Time
-- Stop-date end time
Resume mysql-bin.000002 recovery now, from to 134
[Root @ localhost bin] #./mysqlbinlog -- no-defaults -- start-position 134 -- stop-position = 386 ../var/mysql-bin.000002 |./mysql-uroot-p

/** Mysqlbinlog data restoration experiment ****/
// Check the content below var, there is no binlog similar to the mysql-log.000001
[Root @ localhost var] # ls
Brocms ibdata1 ib_logfile1 localhost. pid mysql-bin.index
Brotherblog ib_logfile0 localhost. err mysql test
[Root @ localhost var] # ../bin/mysql-uroot-p // log on to the database
Mysql> use test; // use the test database
Mysql> flush logs; // refresh binlog, new, now generates a mysql-bin.000001 file under the var directory, the following operations will record it

// Create a table
Mysql> create table user (
-> Id int auto_increment primary key,
-> Username char (30 ),
-> Password char (32 ))
-> Engine = myisam default charset = utf8;
// Insert several test data records
Mysql> insert into user (username, password) values (1, 2 );
Mysql> insert into user (username, password) values (1, 2 );
Mysql> insert into user (username, password) values (1, 2 );
// Open a new binlog log, now a file named mysql-bin.000002 is generated, and the following operations are recorded in the file of the mysql-bin.000002

Mysql> flush logs;
// Query the content
Mysql> select * from user;
+ ---- + ---------- +
| Id | username | password |
+ ---- + ---------- +
| 1 | 1 | 2 |
| 2 | 1 | 2 |
| 3 | 1 | 2 |
+ ---- + ---------- +
Mysql> delete from user; // delete data now
Mysql> drop table user; // delete a table
Mysql> select * from user; // view the content in the table
Mysql> \ q
[Root @ localhost var] # ls
Brocms ibdata1 ib_logfile1 localhost. pid mysql-bin.000001 mysql-bin.index
Brotherblog ib_logfile0 localhost. err mysql mysql-bin.000002 test
[Root @ localhost var] # ../bin/mysqlbinlog -- no-defaults mysql-bin.000001 | more // view the content in the mysql-bin.000001
[Root @ localhost var] # ../bin/mysqlbinlog -- no-defaults mysql-bin.000002 | more // view the content in the mysql-bin.000002
[Root @ localhost var] # ../bin/mysqlbinlog -- no-defaults mysql-bin.000001 | ../bin/mysql-uroot-p // recover data with a mysql-bin.000001
Enter password:
[Root @ localhost var] # ../bin/mysql-uroot-p // View data in the database
Mysql> use test;
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| User |
+ ---------------- +
1 row in set (0.00 sec)

Mysql> select * from user; // view the data. The data is returned.
+ ---- + ---------- +
| Id | username | password |
+ ---- + ---------- +
| 1 | 1 | 2 |
| 2 | 1 | 2 |
| 3 | 1 | 2 |
+ ---- + ---------- +
3 rows in set (0.00 sec)

Mysql> \ q
To restore data from a point to a point, use the following operations:
Positioning: -- start-position start point
-- Stop-position end point
-- Start-date start Time
-- Stop-date end time

Resume mysql-bin.000002 recovery now, from to 134
[Root @ localhost bin] #./mysqlbinlog -- no-defaults -- start-position 134 -- stop-position = 386 ../var/mysql-bin.000002 |./mysql-uroot-p