文章目录
- mysql通过binlog恢复数据
- 数据恢复方法1
- 数据恢复方法2
mysql通过binlog恢复数据
注意：恢复数据之前最好先备份一下当前数据，包括binlog日志，以便恢复失败后还能继续恢复
mysql开启binlog：
#在配置文件添加下列配置
$ cat /etc/my.cnf
[mysqld]
server-id=0 #必须要，不然会启动会报错
log_bin=/var/lib/mysql/bin-log
log_bin_index=/var/lib/mysql/mysql-bin.index
expire_logs_days=30
binlog_format=row查看binlog是否开启：
$ mysql -uroot -p -e "show variables like 'log_bin%'"
生成测试数据：
#进入mysql： mysql -uroot -p123456
mysql> create database test_db;
mysql> create table test_db.test_table ( id int primary key, name varchar(255), age tinyint);
mysql> insert into test_db.test_table(id, name, age) values(1, 'a', 1), (2, 'b', 2), (3, 'c', 3);假如此时删除了数据：
mysql> delete from test_table;数据恢复方法1
- 此方法原理为重复执行bin_log中的数据变更操作，即必须有删除数据对应的插入操作才能恢复，并且mysqlbinlog的参数只有–database，检索数据操作变更是精确到库的，没有精确到表，也就是说如果涉及到一个库中含有多个表时，需要使用grep去过滤
- 如果binlog日志比较齐全（即从建库开始的日志都在），可以先备份该库，并在数据库中删除，使用mysqlbinlog按顺序从binlog提取关于该库相关的操作并重新执行（mysqlbinlog --database=‘test_db’ /var/lib/mysql/bin-log.000001 | mysql -uroot -p）
单个bin-log恢复数据
#查看当前master转态信息，主要关注bin-log的Position
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000001 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
#生成测试数据
mysql> create database test_db;
mysql> create table test_db.test_table ( id int primary key, name varchar(255), age tinyint);
mysql> insert into test_db.test_table(id, name, age) values(1, 'a', 1), (2, 'b', 2), (3, 'c', 3);
#再次查询状态
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000001 | 828 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#查看bin-log中记录的事件，各个操作后的pos已在Info列说明，也可以每一次操作后就查询一次pos，了解一下bin-log记录事件的机制
mysql> show binlog events in 'bin-log.000001';
+----------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------+
| bin-log.000001 | 4 | Format_desc | 0 | 123 | Server ver: 5.7.36-log, Binlog ver: 4 |
| bin-log.000001 | 123 | Previous_gtids | 0 | 154 | (起始pos) |
| bin-log.000001 | 154 | Anonymous_Gtid | 0 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000001 | 219 | Query | 0 | 322 | (创建库后pos)create database test_db |
| bin-log.000001 | 322 | Anonymous_Gtid | 0 | 387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000001 | 387 | Query | 0 | 545 | (创建表后pos)create table test_db.test_table ( id int primary key, name varchar(255), age tinyint) |
| bin-log.000001 | 545 | Anonymous_Gtid | 0 | 610 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000001 | 610 | Query | 0 | 678 | BEGIN |
| bin-log.000001 | 678 | Table_map | 0 | 738 | table_id: 115 (test_db.test_table) |
| bin-log.000001 | 738 | Write_rows | 0 | 797 | table_id: 115 flags: STMT_END_F |
| bin-log.000001 | 797 | Xid | 0 | 828 | (插入数据后pos)COMMIT /* xid=208 */ |
+----------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------+
11 rows in set (0.00 sec)
#假设现在删除表中的数据
mysql> delete from test_db.test_table;
#根据上面查询的pos我们可以知道，数据插入部分是创建表后pos 545到插入数据后pos 828，所以我们只需重新执行这两个pos之间的操作就行
$ mysqlbinlog --start-position='545' --stop-position='828' --database='test_db' /var/lib/mysql/bin-log.000001 | mysql -uroot -p
#数据库中可以看到数据恢复了
mysql> select * from test_db.test_table;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
+----+------+------+
3 rows in set (0.00 sec)多个binlog恢复数据
#将之前的数据清理掉
mysql> drop database test_db;
mysql> reset master;
#查看转态可以看到是初始的信息了
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000001 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
#第一个binlog创建库，创建表，插入数据
mysql> create database test_db;
mysql> create table test_db.test_table ( id int primary key, name varchar(255), age tinyint);
mysql> insert into test_db.test_table(id, name, age) values(1, 'a', 1), (2, 'b', 2), (3, 'c', 3);
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000001 | 828 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#开启一个新的binlog记录数据变更操作
mysql> flush logs;
#可以看到binlog后缀加1了并且pos变成初始状态了
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 154 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#使用第二个binlog记录插入和更新操作
mysql> insert into test_db.test_table(id, name, age) values(4, 'd', 4);
mysql> update testdb.test_table set id=10 where age=1;
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 697 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#最后开启一个binlog进行删除操作
mysql> flush logs;
#查看转态
mysql> show master status;
#不小心删除了数据库
mysql> drop database test_db;
#查看当前有的binlog文件
mysql> show master logs;
#这时候就可以从最初的binlog进行数据恢复，如果当前binlog还有相应的数据需要恢复可参考上面的单个binlog恢复数据
#注意mysqlbinlog加了参数-d会忽略掉创建表的过程，所以可以先用grep找出建表
mysql> create database test_db;
mysql> create table test_db.test_table ( id int primary key, name varchar(255), age tinyint);
#如果忘记了命令可以从以下命令中获取操作
mysql> show binlog events in 'bin-log.000001';
+----------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------+
| bin-log.000001 | 4 | Format_desc | 0 | 123 | Server ver: 5.7.36-log, Binlog ver: 4 |
| bin-log.000001 | 123 | Previous_gtids | 0 | 154 | |
| bin-log.000001 | 154 | Anonymous_Gtid | 0 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000001 | 219 | Query | 0 | 322 | create database test_db |
| bin-log.000001 | 322 | Anonymous_Gtid | 0 | 387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000001 | 387 | Query | 0 | 545 | create table test_db.test_table ( id int primary key, name varchar(255), age tinyint) |
| bin-log.000001 | 545 | Anonymous_Gtid | 0 | 610 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin-log.000001 | 610 | Query | 0 | 678 | BEGIN |
| bin-log.000001 | 678 | Table_map | 0 | 738 | table_id: 116 (test_db.test_table) |
| bin-log.000001 | 738 | Write_rows | 0 | 797 | table_id: 116 flags: STMT_END_F |
| bin-log.000001 | 797 | Xid | 0 | 828 | COMMIT /* xid=286 */ |
| bin-log.000001 | 828 | Rotate | 0 | 873 | bin-log.000002;pos=4 |
+----------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------+
#第一个binlog恢复可以从上述信息中看到第一个插入数据的事务开始的pos是545（即建表完成后的end log pos）
$ mysqlbinlog --database='test_db' --start-position='545' /var/lib/mysql/bin-log.000001 | mysql -uroot -p
#查看mysql数据发现第一个binlog日志恢复了
$ mysql -uroot -p -e 'select * from test_db.test_table;'
Enter password:
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
+----+------+------+
#继续恢复第二个
$ mysqlbinlog --database='test_db' /var/lib/mysql/bin-log.000002 | mysql -uroot -p
#查看数据，可以看到与删除之前的数据一致
mysql> select * from test_db.test_table;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 10 | a | 1 |
+----+------+------+
4 rows in set (0.00 sec)数据恢复方法2
此方法原理为根据删除操作的执行对应的插入操作，在直接删除表和删除库的情况下无法恢复，可参考方法1
通过mysqlbinlog获取删除数据的伪sql操作：
#可根据时间筛选
$ mysqlbinlog -v --start-datetime '2021-09-27 3:26:05' --stop-datetime '2021-09-27 3:28:05' /var/lib/mysql/bin-log.000001 >tmp.log保留sql语句部分：
### DELETE FROM `test_db`.`test_table`
### WHERE
### @1=1
### @2='a'
### @3=1
### DELETE FROM `test_db`.`test_table`
### WHERE
### @1=2
### @2='b'
### @3=2
### DELETE FROM `test_db`.`test_table`
### WHERE
### @1=3
### @2='c'
### @3=3在文本编辑器中进行批量操作变成插入操作的sql语句：
INSERT INTO `test_db`.`test_table`
VALUES(
1
,'a'
,1
); INSERT INTO `test_db`.`test_table`
VALUES(
2
,'b'
,2
); INSERT INTO `test_db`.`test_table`
VALUES(
3
,'c'
,3
);
最后通过sql命令导入到原表中：
$ mysql -uroot -p < tmp.log
Enter password:
$ mysql -uroot -p -e "select * from test_db.test_table"
Enter password:
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
+----+------+------+
$
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
