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

用户2032165

发布于 2019-05-07 13:28:13

6910

发布于 2019-05-07 13:28:13

文章被收录于专栏：cmazxiaoma的架构师之路cmazxiaoma的架构师之路

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 信息在一个数据库只能执行一次,所以不会恢复成功。