删库跑路不怕,用mysqldump和mysqlbinlog进行数据恢复

mysqldump全量恢复

1.创建douyin数据库、tbl_douyin_author数据库表、插入测试数据。

create table tbl_douyin_author(
id int not null auto_increment,
author_name varchar(50) not null,
PRIMARY key (id)
)

insert into tbl_douyin_author values(null, "程序员小马");
select * from tbl_douyin_author;

tbl_douyin_author表数据.png

2.利用mysqldump进行备份,涉及到的参数就不一一开讲了。

mysqldump --single-transaction 
--master-data --triggers --routines 
--databases douyin 
--set-gtid-purged=OFF --flush-logs -uroot -pxiaoma 
> douyin-dump.sql
  • 无论是完整备份,还是部分备份。如果不设置 --set-gtid-purged=OFF这个参数,最终的备份文件中会有这样一句话:SET @@GLOBAL.GTID_PURGED='de3a31d3-e97e-11e8-be60-144f8aee24be:1-318402'; 这个的区间是主库中当前所完成的所有事务号。这条语句在备份被恢复的时候,起到的作用是:不再从主库同步1-318402这个范围内的事务了。如果我们是部分备份,我们不应该阻止从库同步1-318402全部区间的数据。因此部分备份是加上--set-gtid-purged=OFF这句,不强行指定跳过这些操作。
  • --single-transaction--master-data=2--flush-logs达到了减少锁定时间的目的,同时又达到了一致性的备份的目的,而且该一致性备份和 flush的binlog日志也是一致的。话外音:--single-transaction利用事务的特性来得到一致性备份,将隔离级别设置为:REPEATABLE READ。并且随后再执行一条START TRANSACTION语句,让整个数据在dump过程中保证数据的一致性,这个选项对InnoDB的数据表很有用,且不会锁表。但是这个不能保证MyISAM表和MEMORY表的数据一致性。
  • 为了确保使用--single-transaction命令时,保证dump文件的有效性。需没有下列语句ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE,因为一致性读不能隔离上述语句。所以如果在dump过程中,使用上述语句,可能会导致dump出来的文件数据不一致或者不可用。

3.删除douyin数据库

drop database douyin;

4.利用douyin-dump.sql进行恢复

mysql -uroot -pxiaoma < douyin-dump.sql

image.png

mysqlbinlog增量恢复

1.我们接着在tbl_douyin_author插入4条数据

insert into tbl_douyin_author values(null, "达达");
insert into tbl_douyin_author values(null, "麻辣德子");
insert into tbl_douyin_author values(null, "翔翔大作战");
insert into tbl_douyin_author values(null, "五月天");
select * from tbl_douyin_author;

添加完数据的tbl_douyin_author表.png

2.如果我们在这个时候,不一小心进行了drop DATABASE douyin;的删库操作。想必大家的第一反应是收拾行李,赶紧跑路把!但是饭要慢慢吃,路要慢慢走,我们静下心来还是能够解决这个棘手的问题的!怎么解决呢?首先用douyin-dump.sql全量备份恢复到tbl_douyin_author表只有一条数据的状态

image.png

douyin数据库恢复成功、tbl_douyin_author表数据恢复成功.png

3.看到这里,你肯定又会疑问了,这才恢复了一条数据啊,还有4条数据呢!不要急,看我操作就行了。查看binlog中的事件。

show binlog events in 'mysql-bin.000047'\g

image.png

4.我们已经确定了1641点的操作导致了删除douyin数据库,所以我们要开始恢复的点是1576至以前。接下来我们就可以从binlog日志恢复数据了。

      恢复语法格式:
      # mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

        常用选项:
          --start-position=55                    起始pos点
          --stop-position=555                   结束pos点
          --start-datetime="2019-11-29 13:00:00" 起始时间点
          --stop-datetime="2019-11-29 17:00:00"  结束时间点
          --database=douyin                   指定只恢复douyin数据库(一台主机上往往有多个数据库,只限本地log日志)
            
        不常用选项:    
          -u --user=name              Connect to the remote server as username.连接到远程主机的用户名
          -p --password[=name]        Password to connect to remote server.连接到远程主机的密码
          -h --host=name              Get the binlog from server.从远程主机上获取binlog日志
          --read-from-remote-server   Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志

5.执行mysqlbinlog --stop-position=1576 --skip-gtids=true --database=douyin mysql-bin.000047 | mysql -uroot -pxiaoma命令,tbl_douyin_author里面的数据恢复成功。

  • 如果我们是要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例,那么就要使用--skip-gtids=true参数。如果不带该参数的话,是无法恢复成功的。因为包含的 GTID 已经在源数据库执行过了,根据 GTID 特性,一个 GTID 信息在一个数据库只能执行一次,所以不会恢复成功。

tbl_douyin_author数据恢复成功.png


尾言

不管是神还是恶魔都不会对不抗争的人伸出援手。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券