mysql 数据恢复

1.首先保证mysql 开启了 binlog

查看binlog是否开启

mysql> show variables like '%log_bin%';

查看binlog 存储位置，以及binlog格式

mysql> show variables like '%log_bin%';
mysql>  show variables like 'binlog_format'

2.模拟测试

（1）创建测试数据库，数据表

create DATABASE  IF NOT EXISTS  Learn  CHARACTER SET 'utf8' ;

CREATE TABLE `personal` (
  `id` smallint(6) DEFAULT NULL,
  `username` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `personal` VALUES (1,'x',2),(2,'asd',3),(1,'x',2),(2,'asd',3),(5,'ksill',16),(6,'row',127),(7,'Xtrabackup',12);

（2）对数据库进行备份

-- 对mysql下所有数据库进行备份，并刷新binlog
mysqldump -uroot -px2014.mc  -A  --single-transaction  --flush-logs --master-data=2 >/opt/backup_all.sql

（3）继续插入语句

mysql> INSERT INTO `personal` VALUES (8,1223,127);

（4）模拟误操作

mysql> DELETE FROM `personal`  WHERE id=2;
mysql> DELETE FROM `personal`  WHERE id=3;

3.开始恢复

（1）从上次全备份文件导出 Learn 库的建表语句与INSERT 数据

sed -n '/^-- Current Database: `Learn`/,/^-- Current Database: `/p' /opt/backup_all.sql  > Learn.sql

（2）查找全备时二进制文件和日志点

mysql> show binary logs;
mysql> show variables like '%log_bin%';

（3）复制最新的binlog日志,因为后续的导入动作，会写入到最新的binlog，所以应该在恢复前先复制一份

cp  mysql-bin.000015   tmp-bin.log

（4）恢复到全备时的数据

mysql -uroot -px2014.mc < /opt/Learn.sql

(5）从最新的binlog日志导出有关Learn库的sql语句

mysqlbinlog   tmp-bin.log   -d Learn  > test.sql

(6)#找到上次备份完之后的pos点，还有删除语句之前的pos点，恢复数据

mysqlbinlog   --start-position=528 --stop-position=732  --database=Learn  /data/mysql/tmp-bin.log   | mysql -uroot -px2014.mc

mysqldump 命令

-A 备份所有的database
-B 备份哪几个数据库
-R 备份存储过程(-- routines)
-E 备份定时任务(-- events)
-d 只备份表结构
-w 备份过滤数据
-t 只备份数据
-q 直接读数据,绕过缓冲池,默认已加

a、导出整个数据库(包括数据库中的数据）
mysqldump -u username -p dbname > dbname.sql 
b、导出数据库结构（不含数据）
mysqldump -u username -p -d dbname > dbname.sql
c、导出数据库中的某张数据表（包含数据）
mysqldump -u username -p dbname tablename > tablename.sql
d、导出数据库中的某张数据表的表结构（不含数据）
mysqldump -u username -p -d dbname tablename > tablename.sql

 

mysqlbinlog 命令

常用参数：
--start-datetime=datetime 从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。
--stop-datetime=datetime 从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见--start-datetime选项。该选项可以帮助及时恢复。
--start-position=N 从二进制日志中第1个位置等于N参量时的事件开始读。
--stop-position=N 从二进制日志中第1个位置等于和大于N参量时的事件起停止读。
--base64-output=DECODE-ROWS 会显示出row模式带来的sql变更
-d 与 --database 效果相同，指定一个数据库名称。
--offset=N，-o N 跳过前N个条目。
 
注意:
1.不要查看当前正在写入的binlog文件
2.不要加--force参数强制访问
3.如果binlog格式是行模式的,请加 -vv参数
 
基于开始/结束时间
mysqlbinlog --start-datetime='2018-07-10 00:00:00' --stop-datetime='2018-07-10 01:01:01' -d 库名 二进制文件
 
基于pos值
mysqlbinlog --start-position=107 --stop-position=1000 -d 库名 二进制文件
 
转换为可读文本
mysqlbinlog --base64-output=DECODE-ROWS  -vv -d 库名 二进制文件

参考 

https://blog.csdn.net/weixin_39214481/article/details/90690659