1, Bin log backup function
The operation log of mysql binary database is the safe log of transaction.
2, Open bin log
Set the profile my inf
Add the following content to the configuration file

log-bin=/var/lib/mysql/mysql-bin  //Open and set the backup file directory and backup naming format
server-id=1  //The unique id of the database server in the cluster, which can be called and written freely in the non cluster environment. mysql5. Versions above 7 need to be filled in

Examples of other optional configuration items

binlog_format =mixed  //binlog file format, including row, statement and mixed
expire_logs_days = 10  //Automatically delete log files 10 days ago
max_binlog_size = 200M  //The maximum log file size is 200M
binlog-ignore-db = test  //The binary log of database test is not recorded.
binlog-do-db = test  //Only binary logs of database test are recorded.

3, View the data in binlog
You can first create a student table in the database and insert two pieces of data

1. View binlog file
   
2. View the binlog file currently being written
   
3. View binlog logs in mysql
   a. View the log of the specified binlog file

show binlog events in 'mysql-bin.000032'

b. The view starts from the specified location and displays row_count records, and use offset to control the position from which the data row is displayed in the result_ Count pieces of data.

show binlog events [in 'log_name'] [FROM pos] [limit [offset,] row_count]

1. Exit mysql and view the log on the command line.
   a. Output the log of specific database in binlog file as sql file in utf8 format

mysqlbinlog --database=test --set-charset=utf8 /www/server/data/mysql-bin.000032 > /leo/log.sql

In the exported sql file, you can see our operations on the database.
b. View logs from a specific time
– stop datetime = "XXXX XX XX XX: XX: XX" the deadline can be specified.

mysqlbinlog --start-datetime="2022-02-17 14:22:27" /www/server/data/mysql-bin.000032

c. View logs starting from the specified location
From the above sql, you can see the content of "at xxx", which is the location node. You can select the starting location of the log according to these nodes Stop position = "XXX" to specify the end position.

mysqlbinlog --start-position="386" /www/server/data/mysql-bin.000032

4, Recover data from binlog
Scenario 1 Overall data recovery
The existing database test has a table student in which there are two pieces of data

Delete the entire table and restore it through binlog.

1. Find and view the corresponding binlog log

mysql> show binlog events in 'mysql-bin.000032';
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                                                       |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000032 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.62-log, Binlog ver: 4                                                                                      |
| mysql-bin.000032 | 107 | Query       |         1 |         213 | CREATE DATABASE `test` CHARACTER SET 'utf8'                                                                                |
| mysql-bin.000032 | 213 | Query       |         1 |         386 | use `test`; CREATE TABLE `test`.`student`  (
  `id` int(0) NOT NULL,
  `name` varchar(15) NULL,
  PRIMARY KEY (`id`)
) |
| mysql-bin.000032 | 386 | Query       |         1 |         454 | BEGIN                                                                                                                      |
| mysql-bin.000032 | 454 | Query       |         1 |         577 | use `test`; INSERT INTO `test`.`student`(`id`, `name`) VALUES (1, 'Leo')                                                   |
| mysql-bin.000032 | 577 | Xid         |         1 |         604 | COMMIT /* xid=2879748 */                                                                                                   |
| mysql-bin.000032 | 604 | Query       |         1 |         672 | BEGIN                                                                                                                      |
| mysql-bin.000032 | 672 | Query       |         1 |         798 | use `test`; INSERT INTO `test`.`student`(`id`, `name`) VALUES (2, 'Sharry')                                                |
| mysql-bin.000032 | 798 | Xid         |         1 |         825 | COMMIT /* xid=2879751 */                                                                                                   |
| mysql-bin.000032 | 825 | Query       |         1 |         934 | use `test`; DROP TABLE `student` /* generated by server */                                                                 |
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+

After analysis, all the operations of the node from 213 to 825 are about the student table, which can be recovered directly. Run the following command:

mysqlbinlog /www/server/data/mysql-bin.000032 --start-position=213 --stop-position=825 | mysql -u root -p test

After entering the password and executing, check the database and find that the student table has been restored.

It is recommended to use the flush logs command in mysql before executing the recovery statement. This command either pauses the binlog file currently used and uses a new binlog file, so that subsequent operations will not pollute the previous log.