前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >mysqldump全量备份+mysqlbinlog二进制日志增量备份

mysqldump全量备份+mysqlbinlog二进制日志增量备份

原创
作者头像
大龄老码农-昊然
修改2021-04-23 15:19:25
1.3K0
修改2021-04-23 15:19:25
举报
文章被收录于专栏:互联网技术分享

日常的数据备份及恢复测试,是DBA工作重中之重的事情,所以要做好备份及测试,日常的备份常见有mysqldump+binlog备份、xtrabackup+binlog备份,无论那一种,几乎都少不了对binlog的备份,说明了binlog在数据恢复中的重要性,下面做个小测试,是工作中不少运维或者新人DBA容易犯的错。

创建一个测试表tb1

<test>(root@localhost) [xuanzhi]> show create table tb1\G

*************************** 1. row ***************************

Table: tb1

Create Table: CREATE TABLE `tb1` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`name` char(10) CHARACTER SET latin1 DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]>

往表里插入两条数据

<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('aa'),('bb');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

<test>(root@localhost) [xuanzhi]> show master logs;

+----------------------+-----------+

| Log_name | File_size |

+----------------------+-----------+

| localhost-bin.000001 | 329 |

+----------------------+-----------+

1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]>

对数据备份

如果是xtrabackup备份的话,会在有xtrabackup_binlog_info文件中记录此时备份是到那个binlog文件和pos点的,如果是mysqldump备份,则需要带上--master-data=2这个参数,下面我们的数据量少,用mysqldump备份:

[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql

Warning: Using a password on the command line interface can be insecure.

[root@localhost ~]# grep -i "CHANGE MASTER" xuanzhi.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329;

[root@localhost ~]#

继续模拟数据库有写入

这个时候是还是写在mysql-bin.000001

<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('cc'),('dd');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

<test>(root@localhost) [xuanzhi]> show master logs;

+----------------------+-----------+

| Log_name | File_size |

+----------------------+-----------+

| mysql-bin.000001 | 538 |

+----------------------+-----------+

1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> flush logs;

Query OK, 0 rows affected (0.01 sec)

<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('ee');

Query OK, 1 row affected (0.00 sec)

<test>(root@localhost) [xuanzhi]> show master logs;

+----------------------+-----------+

| Log_name | File_size |

+----------------------+-----------+

| mysql-bin.000001 | 589 | | mysql-bin.000002 | 321 | +----------------------+-----------+ 2 rows in set (0.00 sec) <test>(root@localhost) [xuanzhi]>

上面我们进行flush logs是为了模拟现在已经有多个binlog文件了,恢复时进行多个binlog一起恢复。

模拟误操作

把xunazhi库drop了

<test>(root@localhost) [xuanzhi]> drop database xuanzhi;

Query OK, 1 row affected (0.02 sec)

<test>(root@localhost) [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

<test>(root@localhost) [(none)]>

创建数据xuanzhi,把备份导入

代码语言:javascript
复制
<test>(root@localhost) [(none)]> create database xuanzhi;    
Query OK, 1 row affected (0.00 sec)
代码语言:javascript
复制
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql # 这个sql文件可以是绝对路径也可以是相对路径 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# 

查看数据

<test>(root@localhost) [(none)]> use xuanzhi

Database changed

<test>(root@localhost) [xuanzhi]> select * from tb1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

+----+------+

2 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]>

可以看到备份前的数据恢复了

接下来要结合Binlog来恢复

但前提要找出误操作前的pos点,也就是drop database xuanzhi前的pos点:

[root@localhost ~]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000002 |grep -C 10 -i "drop database"

### INSERT INTO `xuanzhi`.`tb1`

### SET

### @1=5

### @2='ee'

# at 290

#170327 21:10:55 server id 1313306 end_log_pos 321 CRC32 0x825a2f99 Xid = 78

COMMIT/*!*/;

# at 321

#170327 21:19:25 server id 1313306 end_log_pos 422 CRC32 0x8c139cac Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1490620765/*!*/;

drop database xuanzhi

/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql

Warning: Using a password on the command line interface can be insecure.

[root@localhost ~]#

从上面可以看到,误操作前的pos点是321,那我们现在通过binlog来进行数据恢复:

代码语言:javascript
复制
[root@localhost mysql-5.6]# mysqlbinlog --start-position=329 --stop-position=321 mysql-bin.000001 mysql-bin.000002 |mysql -uroot -p123456 xuanzhi 
# 这里的mysql-bin.000001和mysql-bin.000002等日志文件要从原来的位置拷贝出来,可以是绝对路径也可以是相对路径
代码语言:javascript
复制
Warning: Using a password on the command line interface can be insecure. 
[root@localhost mysql-5.6]#

--start-position是备份后记录下的pos点, --stop-position是误操前的pos点,如果批多个binlog文件,那么start-position是第一个binlog文件的pos点,stop-position是最后一个binlog的pos点,下面我们看下数据是否恢复回来了:

<test>(root@localhost) [xuanzhi]> select * from tb1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]>

这里要提的是进行恢复前,要把需要恢复的binlog备份好,或者移动拷贝一份到另一个目录,因为进行数据导入时也会继续写binlog。假如你没有误操作的情况下,就是想测试一下数据的恢复,很多人的操作是导入备份,再从备份里记录的binlog文件名和pos点进行binlog恢复,发现步骤都很完美,也没报错,恢复后就是只有备份时的数据,没有备份后的数据,下面测试一下给大家看:

<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('aa'),('bb');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

<test>(root@localhost) [xuanzhi]> select * from tb1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

+----+------+

2 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]> show master logs;

+----------------------+-----------+

| Log_name | File_size |

+----------------------+-----------+

| localhost-bin.000001 | 329 |

+----------------------+-----------+

1 row in set (0.00 sec)

<test>(root@localhost) [xuanzhi]>

进行备份操作:

[root@localhost ~]# mysqldump -uroot -p123456 -R --events --triggers=true --master-data=2 --single-transaction xuanzhi > xuanzhi.sql

Warning: Using a password on the command line interface can be insecure.

[root@localhost ~]# grep -i "change master" xuanzhi.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000001', MASTER_LOG_POS=329;

[root@localhost ~]#

继续写localhost-bin.000001后进行flush logs生成新的binlog再继续写数据,这里只是想模拟localhost-bin.000001写满了切localhost-bin.000002,结合多个binlog一起恢复

<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('cc'),('dd');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

<test>(root@localhost) [xuanzhi]> flush logs;

Query OK, 0 rows affected (0.00 sec)

<test>(root@localhost) [xuanzhi]> insert into tb1 (name) value ('dd');

Query OK, 1 row affected (0.01 sec)

<test>(root@localhost) [xuanzhi]> show master logs;

+----------------------+-----------+

| Log_name | File_size |

+----------------------+-----------+

| localhost-bin.000001 | 589 |

| localhost-bin.000002 | 321 |

+----------------------+-----------+

2 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]>

下面进行恢复测试,正常来说先把备份导入:

代码语言:javascript
复制
[root@localhost ~]# mysql -uroot -p123456 xuanzhi <./xuanzhi.sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# 

查看数据,只有备份的那两条记录:

<test>(root@localhost) [xuanzhi]> select * from tb1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

+----+------+

2 rows in set (0.00 sec)

<test>(root@localhost) [xuanzhi]>

那现在通过localhost-bin.000001,localhost-bin.000002来恢复后面那3条数据,那么起始pos是那个呢,就是上面备份完后备份文件里的那个pos,我们进到binlog的存放路径:

可以看到备份后的数据是没有恢复回来的。为什么呢?因为导入备份的时候,又开始写binlog了,而你恢复时用的binlog也就是现在导入备份时正在写的binlog。大体过程是这样的:

1、导入备份后,备份的所有操作都写进最后一个binlog了,也就是上面的localhost-bin.000002

2、进行binlog恢复,从备份文件里的pos点开始,按理来说是可以恢复到最新数据的,但是上面导入了备份,导入时的所有操作都会记录到localhost-bin.000002

3、备份导入时会有DROP TABLE和CREATE TABLE的动作写进binlog里,所以最终得到的数据,还是备份时的数据。

补充-通过mysqldump+binlog进行增量备份与基于pos位置的恢复

模拟场景:删除数据库,并且所有操作都是在同一个日志文件中

步骤一:进行完全备份。执行如下命令完整备份一次数据库,备份到当前目录的backup20190612.sql文件中。

参数说明:

-B:备份时添加建库语句

-l:数据库表添加只读锁

-F:刷新bin-log日志文件,相当于执行flush logs指令,生成新的bin-log日志文件,此后日志都放在新的bin-log日志文件中,利于查找。

步骤二:登录进入MySQL server,查看当前使用的bin-log日志文件。当前使用的日志文件是DESKTOP-R7JK9T5-bin.000005,记录的位置是154。

步骤三:往数据库里面写入一条新的数据,学号是1,名字是lijingjing

步骤四:模拟删除数据库

drop database dreamhom;

步骤五:开始恢复。

先从完全备份中恢复,

查看数据库,dreamhom数据库恢复,但是表中的数据还是没有恢复

步骤六:需要从日志文件中恢复,先将当前文件转化问文本文件

步骤七:找到文本文件并且打开,搜索自己的删除数据库的点,搜索 drop database dreamhom

步骤七:在指定的时间点进行恢复数据库

步骤七:查看数据库是否完全恢复

数据库恢复成功!!

总结:

一、在恢复全备数据之前必须将该binlog文件移出,否则恢复过程中,会继续写入语句到binlog,最终导致增量恢复数据部分变得比较混乱

二、做好数据文件及binlog的备份至关重要,但不是备份完就算了,要定期进行数据恢复测试或演练

三、恢复时建议对外停止更新,即禁止更新数据库

参考文章

更多相关知识和参考文章来源可以关注我的博客网站-互联网技术教程

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 创建一个测试表tb1
  • 往表里插入两条数据
  • 对数据备份
  • 继续模拟数据库有写入
  • 模拟误操作
  • 创建数据xuanzhi,把备份导入
  • 查看数据
  • 接下来要结合Binlog来恢复
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档