018、数据挖掘恢复
mysql binlog恢复数据
MySQL通过binlog恢复数据，需要通过mysqlbinlog命令恢复，有两种方法：
1、根据时间来进行恢复：start-datetime、stop-datetime
2、根据position号来进行恢复，建议使用这种方法恢复数据：start-position、stop-position
通过binlog挖掘进行恢复，就是查看binlog内容，查找被误删除的数据是什么时候插入的，重新应用insert时的position号。
误删数据
1、创建实验表，不使用主键：
mysql> use test;
Database changed
mysql> create table t2(id int);
Query OK, 0 rows affected (0.05 sec)2、插入测试数据
mysql> insert into t2 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t2 values(2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values(3);
Query OK, 1 row affected (0.01 sec)3、模拟误删除数据
mysql> delete from t2 where id=3;
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
--这时发现id=3的数据丢失了，不确定具体时间查看binlog内容
在数据库里面，通过show binlog events in '当前的binlog'查看当前的binlog文件包含的内容：
mysql> ? show binlog events; --?+命令，提示帮助
Name: 'SHOW BINLOG EVENTS'
Description:
Syntax:
SHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log. If you do not specify 'log_name',
the first binary log is displayed.
URL: http://dev.mysql.com/doc/refman/5.6/en/show-binlog-events.html以下是binlog内容：
mysql> show binlog events in 'mysql-bin.000010';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000010 | 4 | Format_desc | 3 | 120 | Server ver: 5.6.35-log, Binlog ver: 4 |
| mysql-bin.000010 | 120 | Query | 3 | 217 | use `test`; create table t2(id int) |
| mysql-bin.000010 | 217 | Query | 3 | 289 | BEGIN |
| mysql-bin.000010 | 289 | Table_map | 3 | 334 | table_id: 72 (test.t2) |
| mysql-bin.000010 | 334 | Write_rows | 3 | 374 | table_id: 72 flags: STMT_END_F |
| mysql-bin.000010 | 374 | Xid | 3 | 405 | COMMIT /* xid=18 */ |
| mysql-bin.000010 | 405 | Query | 3 | 477 | BEGIN |
| mysql-bin.000010 | 477 | Table_map | 3 | 522 | table_id: 72 (test.t2) |
| mysql-bin.000010 | 522 | Write_rows | 3 | 562 | table_id: 72 flags: STMT_END_F |
| mysql-bin.000010 | 562 | Xid | 3 | 593 | COMMIT /* xid=19 */ |
| mysql-bin.000010 | 593 | Query | 3 | 665 | BEGIN |
| mysql-bin.000010 | 665 | Table_map | 3 | 710 | table_id: 72 (test.t2) |
| mysql-bin.000010 | 710 | Write_rows | 3 | 750 | table_id: 72 flags: STMT_END_F |
| mysql-bin.000010 | 750 | Xid | 3 | 781 | COMMIT /* xid=20 */ |
| mysql-bin.000010 | 781 | Query | 3 | 853 | BEGIN |
| mysql-bin.000010 | 853 | Table_map | 3 | 898 | table_id: 72 (test.t2) |
| mysql-bin.000010 | 898 | Delete_rows | 3 | 938 | table_id: 72 flags: STMT_END_F | --注意此处的Event_type
| mysql-bin.000010 | 938 | Xid | 3 | 969 | COMMIT /* xid=28 */ |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
18 rows in set (0.00 sec)将binlog内容转储到文件
通过show binlog events in 可以查看被删除的position号，恢复的过程是将被删除的数据重新插入的过程，但是show binlog events不能直观地展示出该数据是什么时候被插入的，需要将binlog内容转存到文件，通过以下命令：
[root@localhost ~]# mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /u01/data/mysql/mysql_bin.000010 > /u01/scripts/1.txt查看文件内容：
[root@localhost ~]# cat /u01/scripts/1.txt
……
create table t2(id int)
/*!*/;
# at 217
#210628 9:25:43 server id 1113306 end_log_pos 289 CRC32 0x17e9c19c Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1624843543/*!*/;
BEGIN
/*!*/;
# at 289
#210628 9:25:43 server id 1113306 end_log_pos 334 CRC32 0x12e33080 Table_map: `test`.`t2` mapped to number 74
# at 334
#210628 9:25:43 server id 1113306 end_log_pos 374 CRC32 0xf2f26871 Write_rows: table id 74 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 374
#210628 9:25:43 server id 1113306 end_log_pos 405 CRC32 0x43b63c06 Xid = 13
COMMIT/*!*/;
# at 405
#210628 9:25:50 server id 1113306 end_log_pos 477 CRC32 0x9543b77e Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1624843550/*!*/;
BEGIN
/*!*/;
# at 477
#210628 9:25:50 server id 1113306 end_log_pos 522 CRC32 0x0fb0a2da Table_map: `test`.`t2` mapped to number 74
# at 522
#210628 9:25:50 server id 1113306 end_log_pos 562 CRC32 0x3979b7c7 Write_rows: table id 74 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
# at 562
#210628 9:25:50 server id 1113306 end_log_pos 593 CRC32 0x079b0de8 Xid = 14
COMMIT/*!*/;
# at 593
#210628 9:25:52 server id 1113306 end_log_pos 665 CRC32 0x1c445258 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1624843552/*!*/;
BEGIN
/*!*/;
# at 665
#210628 9:25:52 server id 1113306 end_log_pos 710 CRC32 0x6cc0caa7 Table_map: `test`.`t2` mapped to number 74
# at 710
#210628 9:25:52 server id 1113306 end_log_pos 750 CRC32 0xcfde797c Write_rows: table id 74 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 750
#210628 9:25:52 server id 1113306 end_log_pos 781 CRC32 0x871ff8f1 Xid = 15
COMMIT/*!*/;
# at 781
#210628 9:26:04 server id 1113306 end_log_pos 853 CRC32 0x63093fa2 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1624843564/*!*/;
BEGIN
/*!*/;
# at 853
#210628 9:26:04 server id 1113306 end_log_pos 898 CRC32 0x5220b0d8 Table_map: `test`.`t2` mapped to number 74
# at 898
#210628 9:26:04 server id 1113306 end_log_pos 938 CRC32 0x1820dcf6 Delete_rows: table id 74 flags: STMT_END_F
### DELETE FROM `test`.`t2`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
# at 938
#210628 9:26:04 server id 1113306 end_log_pos 969 CRC32 0x5a21f1b1 Xid = 16
COMMIT/*!*/;
# at 969
#210628 9:26:26 server id 1113306 end_log_pos 1041 CRC32 0x2aa4982f Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1624843586/*!*/;
BEGIN
/*!*/;
# at 1041
#210628 9:26:26 server id 1113306 end_log_pos 1086 CRC32 0x495e752a Table_map: `test`.`t2` mapped to number 74
# at 1086
#210628 9:26:26 server id 1113306 end_log_pos 1126 CRC32 0xf0518263 Write_rows: table id 74 flags: STMT_END_F
### INSERT INTO `test`.`t2`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 1126
#210628 9:26:26 server id 1113306 end_log_pos 1157 CRC32 0xa200e024 Xid = 19
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;根据binlog文件内容，可以发现建表语句和插入删除语句，其中@1=1代表第一个字段的值插入为1，@2='a'代表第二个字段的值插入为a。
重新应用binlog
应用binlog日志，插入被误删除的数据：
[root@localhost ~]# mysqlbinlog --start-position=593 --stop-position=781 /u01/data/mysql/mysql-bin.000010 >/u01/scripts/1.sql
[root@localhost ~]# mysql -uroot -proot test < /u01/scripts/1.sql
Warning: Using a password on the command line interface can be insecure.
--以上两句也可以合并为：
[root@localhost ~]# mysqlbinlog --start-position=593 --stop-position=781 /u01/data/mysql/mysql-bin.000010 | mysql -uroot -proot test
Warning: Using a password on the command line interface can be insecure.查看数据：
mysql> select * from t2;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 3 | -- 因为我应用了两次，所以数据被插入了两次。
+------+
4 rows in set (0.00 sec)如果数据是合并插入的，误删了其中一条数据，如何处理，如何恢复？
创建实验表含有主键，插入数据：
mysql> create table t3(
-> id int not null auto_increment,
-> name varchar(20),
-> primary key(id)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t3(name) values('a'),('b'),('c');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0误删除其中一条数据：
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
+----+------+
2 rows in set (0.00 sec)再使用上面的方法，通过binlog恢复，就会报错：
[root@localhost ~]# mysqlbinlog --start-position=1509 --stop-position=1716 /u01/data/mysql/mysql-bin.000010 | mysql -uroot -proot test
Warning: Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 32: Duplicate entry '1' for key 'PRIMARY'
--因为这三条数据是放在一个事务里的，恢复该事务就会同时插入三条数据，违反主键约束这个时候，需要将binlog内容转储到文件中，并查看文件内容：
[root@localhost ~]# mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /u01/data/mysql/mysql-bin.000010 > /u01/databak/1.txt
[root@localhost ~]# cat /u01/databak/1.txt
……
#210424 14:49:08 server id 3 end_log_pos 1509 CRC32 0x56c8c08e Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1619246948/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
create table t3(
id int not null auto_increment,
name varchar(20),
primary key(id)
)
/*!*/;
# at 1509
#210424 14:49:44 server id 3 end_log_pos 1581 CRC32 0xe2ffb111 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1619246984/*!*/;
BEGIN
/*!*/;
# at 1581
#210424 14:49:44 server id 3 end_log_pos 1629 CRC32 0x3e724b4e Table_map: `test`.`t3` mapped to number 78
# at 1629
#210424 14:49:44 server id 3 end_log_pos 1685 CRC32 0xa6f1b002 Write_rows: table id 78 flags: STMT_END_F
### INSERT INTO `test`.`t3`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='a' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `test`.`t3`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='b' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `test`.`t3`
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='c' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 1685
#210424 14:49:44 server id 3 end_log_pos 1716 CRC32 0xf8e189f9 Xid = 127
COMMIT/*!*/;
# at 1716
#210424 14:50:27 server id 3 end_log_pos 1788 CRC32 0xa4dbc04e Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1619247027/*!*/;
BEGIN
/*!*/;
# at 1788
#210424 14:50:27 server id 3 end_log_pos 1836 CRC32 0x9417f7b7 Table_map: `test`.`t3` mapped to number 78
# at 1836
#210424 14:50:27 server id 3 end_log_pos 1878 CRC32 0xfbd65ddd Delete_rows: table id 78 flags: STMT_END_F
### DELETE FROM `test`.`t3`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='b' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 1878
#210424 14:50:27 server id 3 end_log_pos 1909 CRC32 0xcf72a949 Xid = 128
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;根据binlog文件内容，可以发现建表语句和插入删除语句，其中@1=1代表第一个字段的值插入为1，@2='a'代表第二个字段的值插入为a.
这样，根据binlog文件的详细内容，再执行恢复操作(手动编写insert语句)，插入时指定主键的值：
mysql> insert into t3 values(2,'b');
Query OK, 1 row affected (0.01 sec)查看完整的sql语句
binlog_rows_query_log_events 该参数是mysql5.6的新特性，意义在于：在binlog row格式下，也可以看到完整的sql语句，只对该参数开启之后生效。
示例：
mysql> insert into t3(name) values('d');
Query OK, 1 row affected (0.01 sec)
mysql> update t3 set name='aa' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0查看binlog内容：
mysql> show binlog events in 'mysql-bin.000011';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000011 | 4 | Format_desc | 3 | 120 | Server ver: 5.6.35-log, Binlog ver: 4 |
| mysql-bin.000011 | 120 | Query | 3 | 192 | BEGIN |
| mysql-bin.000011 | 192 | Rows_query | 3 | 248 | # insert into t3(name) values('d') |
| mysql-bin.000011 | 248 | Table_map | 3 | 296 | table_id: 78 (test.t3) |
| mysql-bin.000011 | 296 | Write_rows | 3 | 338 | table_id: 78 flags: STMT_END_F |
| mysql-bin.000011 | 338 | Xid | 3 | 369 | COMMIT /* xid=187 */ |
| mysql-bin.000011 | 369 | Query | 3 | 441 | BEGIN |
| mysql-bin.000011 | 441 | Rows_query | 3 | 499 | # update t3 set name='aa' where id=1 |
| mysql-bin.000011 | 499 | Table_map | 3 | 547 | table_id: 78 (test.t3) |
| mysql-bin.000011 | 547 | Update_rows | 3 | 598 | table_id: 78 flags: STMT_END_F |
| mysql-bin.000011 | 598 | Xid | 3 | 629 | COMMIT /* xid=188 */ |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
11 rows in set (0.00 sec)binlog日志的格式建议使用row格式，来作为binlog的记录格式。statement格式容易丢失数据。
浙公网安备 33010602011771号