Day.7 保有日常备份重要性 - binlog 解析 &备份资料 (mysqldump / binlog)

回顾一下上篇提到的binlog纪录作用，可以用来做资料复原和主从机制下的重要数据纪录。

开始前先了解一下如何使用<< mysqldump >>指令做数据库备份

常用的几个举例如下：
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

(资料备份)
备份单一资料库
# mysqldump -u root -p db_name > back_up.sql;
备份资料库中一or多个资料表
# mysqldump -u root -p db_name tbl_name tbl_name2 > back_up.sql;
备份所有资料库
# mysqldump -u root -p --all-databases > back_up.sql;

(资料还原)
复原多个资料库
# mysql -u root -p < back_up.sql
复原单一资料库
# mysql -u root -p database_name < back_up.sql

当然以上只是基础的备份～还有像只备结构或数据等...参数使用参考文章。 mysqldump参数用法

有了平时的备份数据，当发生误删数据状况时，如何藉由binlog纪录找到发生点做到数据恢复?

• 了解binlog大致内容。
• 如何过滤binlog找到问题语法。
• 发生误删动作时，如何透过平时备份数据复原。

了解binlog内容，利用 << mysqlbinlog >> 找events pos纪录位置。

以下 2 种浏览binlog方式:

1.原始binlog纪录档 (撷取执行SQL: delete from members where id =5 的一段完整纪录)
2.利用 Mysql cli 查询binlog纪录。

纪录档 mysql-bin.00000x

• commit: 提交事件结束点。可以快速判断下一个事件的开始位置
• at xxx: 就是我们要找的事件位置～ex. at 456 =事件纪录开始位置为456
• GTID事件: 在5.7版本中，执行事务之前，都会纪录一个GTID Event，用於主从同步复制。参考图.2
• end_log_pos: 当前事件结束位置。同时也是下一个事件开始的位置。 参考图.2
• 纪录方式: 目前设定为row模式，所以可以看到当我执行delete语法时只带了id=5的条件，但该笔资料的其他栏位资讯也会被完整纪录。
• #210810 2:59:55 server id 1 : 事件发生时间和服务标示ID
• Delete_rows : 事件类型

图.1

# at 425
#210810  2:38:58 server id 1  end_log_pos 456 CRC32 0xc26fb981 	Xid = 605
COMMIT/*!*/;       
# at 456   
#210810  2:59:55 server id 1  end_log_pos 521 CRC32 0x93934176 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;  
# at 521
#210810  2:59:55 server id 1  end_log_pos 593 CRC32 0x368339f5 	Query	thread_id=6	exec_time=0	error_code=0    
SET TIMESTAMP=1628564395/*!*/;
BEGIN
/*!*/;
# at 593
#210810  2:59:55 server id 1  end_log_pos 646 CRC32 0x799d4af6 	Table_map: `user`.`members` mapped to number 110  
# at 646
#210810  2:59:55 server id 1  end_log_pos 705 CRC32 0xfa28c00a 	Delete_rows: table id 110 flags: STMT_END_F

BINLOG '
q+sRYRMBAAAANQAAAIYCAAAAAG4AAAAAAAEABHVzZXIAB21lbWJlcnMAAwj8CAECAPZKnXk=
q+sRYSABAAAAOwAAAMECAAAAAG4AAAAAAAEAAgAD//gFAAAAAAAAAAUAc2lhbmdYYMUJAAAAAArA
KPo=
'/*!*/;
### DELETE FROM `user`.`members`  
### WHERE
###   @1=5
###   @2='siang'
###   @3=163930200
# at 705
#210810  2:59:55 server id 1  end_log_pos 736 CRC32 0xe959995d 	Xid = 607
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

图.2

指定POS查询
mysql> show binlog events in 'mysql-bin.000001' from 425 limit 10;

不过在生产环境上资料是持续在异动的，所以log纪录会很庞大，不会直接打开binlog档去找操作纪录位置。得透过一些过滤的查询方便我们在找纪录上快速定位。

ex. 查询这句SQL指令POS位置 (delete from members where id =5 ) 的2种方式～

(一)藉由SQL指令关键字

1. 从binlog档案中找出关键字对应行数

    # mysqlbinlog -v mysql-bin.000001 | cat -n | grep -iw 'delete'   
查询结果:  
    74	### DELETE FROM `user`.`members` 

2. 藉由过滤後的内容上下文就能找到pos点做後续复原动作

    # mysqlbinlog -v mysql-bin.000001 | cat -n | sed -n '50,81p'

查询结果:  
     50	###   @3=163930200

       ....

     62	BEGIN
       ....
     65	#210810  2:59:55 server id 1  end_log_pos 646 CRC32 0x799d4af6 	Table_map:             `user`.`members` mapped to number 110
     66	# at 646
     67	#210810  2:59:55 server id 1  end_log_pos 705 CRC32 0xfa28c00a 	Delete_rows:           table id 110 flags: STMT_END_F

       ....

     74	### DELETE FROM `user`.`members`
     75	### WHERE
     76	###   @1=5
     77	###   @2='siang'
     78	###   @3=163930200

       ....

     81	COMMIT/*!*/;

(二)藉由限制发生时间和指定资料库

  # mysqlbinlog -v --start-datetime="2021-08-10 02:59:00" --database=user --base64-output=decode-rows mysql-bin.000001

下集预告: 知道备份和binlog纪录怎麽使用後，明天来准备跑模拟流程的资料