How to use mysql binlog to restore data

Before using mysql binlog for data recovery, binlog must be enabled first. Set up a test environment here to learn how to use mysql binlog to restore the database. The principle is relatively simple. Binlog will store changed data in mysql. For example, if you create a database and write some data, these will be stored in the binlog of mysql.

When you need to restore, you need to find two positions: a starting position and an ending position. Half of the ending position is the position before the data was destroyed or deleted. Mysql 8 has binlog enabled by default

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------+
| Variable_name                   | Value                    |
+---------------------------------+--------------------------+
| log_bin                         | ON                       |
| log_bin_basename                | /data/mysql/binlog       |
| log_bin_index                   | /data/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                      |
| log_bin_use_v1_row_events       | OFF                      |
| sql_log_bin                     | ON                       |
+---------------------------------+--------------------------+

You can see that log_bin has been enabled. At the same time, you can see that the storage location is in the /daba/mysql directory. The prefix is ​​binlog

ls /data/mysql/binlog.*
/data/mysql/binlog.000143  /data/mysql/binlog.000144  /data/mysql/binlog.000145  /data/mysql/binlog.000146  /data/mysql/binlog.index

You can see that there are several A binlog log file, because this is the test database (there is historical information, previous data is useless), in order to facilitate testing, the reset operation is performed directly. Be careful when deleting all binlog files. If it is a production environment, the value of the data is immeasurable.

show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000143 |       156 | No        |
| binlog.000144 |       200 | No        |
| binlog.000145 |       156 | No        |
| binlog.000146 |       156 | No        |
+---------------+-----------+-----------+
4 rows in set (0.01 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)


ls /data/mysql/binlog.*
/data/mysql/binlog.000001  /data/mysql/binlog.index

After executing the reset (reset master), you can see that the previous binlog file has been deleted and a new binlog file is generated.

You can check the contents of this file

$mysqlbinlog binlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
...

At this time, we add some data

mysql <<EOT
create database test_liuhaolin_com;
select now();
EOT

mysql <<EOT
use test_liuhaolin_com;

create table if not exists test(
	\`id\` int unsigned not null auto_increment primary key,
	\`key\` varchar(100),
	\`val\` varchar(255)
) engine=myisam charset=utf8mb4;
EOT

mysql <<EOT
use test_liuhaolin_com;
insert into test  values (&#39;1&#39;,&#39;website&#39;, &#39;https://www.liuhaolin.com&#39;);
EOT

At this time, we accidentally deleted the database test_liuhaolin_com

mysql> drop database test_liuhaolin_com;
Query OK, 1 row affected (0.09 sec)

Now we need to solve a practical problem, how to restore this database

First of all, in order to prevent interference, execute flush logs to generate a new binlog file.

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1594 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     1594 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

To recover data, you must first find where the data was deleted.

mysqlbinlog binlog.000001 | grep -n  "drop database"
113:drop database test_liuhaolin_com

You can see that there is a delete statement at line 113. Finally, you can find two places

1. The starting position of the data that needs to be restored

2. The ending position of the data that needs to be restored

The starting position here is where the database is created, and the ending position is where the database is deleted.

mysqlbinlog --set-charset=utf8  binlog.000001 > tmp.sql

You can know the starting and ending positions in the file tmp.sql

binlog

You can see that the starting position is 233

binlog

You can see that the end position is 1371, so perform data recovery.

mysqlbinlog -v binlog.000001 --start-position=233 --stop-position=1371 | mysql

As a verification, you can check whether the database exists before execution.

mysql> use test_liuhaolin_com;
ERROR 1049 (42000): Unknown database 'test_liuhaolin_com'

# 执行恢复操作
mysqlbinlog -v  binlog.000001  --start-position=233  --stop-position=1371 | mysql 

# 再次检查，可以发现数据已经恢复
mysql>
mysql> use test_liuhaolin_com;
Database changed

The above is the detailed content of How to use mysql binlog to restore data. For more information, please follow other related articles on the PHP Chinese website!