MySQL based on point-in-time and location recovery

Based on point-in-time and location recovery

The use of binary log can be implemented based on time and location recovery, for example, due to the deletion of a table by mistake, the full recovery is useless, because there is still error in the log, we need to revert to the state before the wrong operation, and then skip the wrong operation of the data, and then restore the back operation statement
Suppose you need to insert two data into the database, but due to misoperation, the data is deleted in the middle of the two INSERT statements, and this data should not be deleted.

create database cai; //创建新数据了 创建表 ，并且写入两行化开启二进制日志完全备份一次mysqladmin -u root -p flush-log  //生成日志文件，此内容为添加的zhangsan，lisi的信息mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/info.txt  //将二进制文件解密后生成生一个新的文本文件.txt

When you feel that the operation is at risk, you can record it with a point-in-time recovery log, of course, or open the binary log.

Text generated after decryption

mysqlbinlog --no-defaults --bases64-output=decode-rows -v mysql-bin.000002 >/opt/info.txt   //把解密后二进制文件重新生成一个文本文档vim /opt/infon.txt  //查看里面需要的参数/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;........./////省略  时间点 操作了对info表进行了添加# at 341  //标记号 也可以居位置标记号恢复 也可也基于时间点恢复180707 12:05:31 server id 1  end_log_pos 391 CRC32 0x7a99109e  Write_rows: table id 108 flags: STMT_END_F### INSERT INTO `cai`.`info`### SET###   @1=‘xiaokeai01‘###   @2=88.00# at 391#180707 12:05:31 server id 1  end_log_pos 422 CRC32 0x8be308f6  Xid = 35错误标记号 时间点# at 609#180707 12:06:12 server id 1  end_log_pos 657 CRC32 0xe5821dc6  Delete_rows: table id 108 flags: STMT_END_F### DELETE FROM `cai`.`info`### WHERE###   @1=‘zhangsan‘###   @2=88.00# at 657正确操作 标记号 时间点# at 875#180707 12:06:16 server id 1  end_log_pos 925 CRC32 0x60ef5b40  Write_rows: table id 108 flags: STMT_END_F### INSERT INTO `cai`.`info`### SET###   @1=‘xiaokeai02‘###   @2=88.00# at 925

Restore based on point in time

mysqlbinlog --no-defaults --stop-datetime=‘2018-07-07 12:06:12‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p  //基于时间恢复 从开头到指定的时间停止 之前的都会执行操作mysqlbinlog --no-defaults --start-datetime=‘2018-07-07 12:06:16‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p  //基于正确的时间恢复 从指定的时间点到结尾都会执行操作

Based on line number recovery

mysqlbinlog --no-defaults --stop-position=‘558‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p  //错误操作 从开头的内容一直碰到 指定的错误行号便会停下 之前的都会执行mysqlbinlog --no-defaults --stop-position=‘558‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p  //正确操作 从指定的正确的行号到结尾都会执行# at 558  //标记号  stop--position=‘558‘ #180707 12:06:12 server id 1  end_log_pos 609 CRC32 0xcf4ae275  Table_map: `cai`.`info` mapped to number 108# at 609 ///误删标记行开始#180707 12:06:12 server id 1  end_log_pos 657 CRC32 0xe5821dc6  Delete_rows: table id 108 flags: STMT_END_F### DELETE FROM `cai`.`info`### WHERE###   @1=‘zhangsan‘###   @2=88.00# at 657  

MySQL based on point-in-time and location recovery