此备份只适用于MySQL5版本的，MySQL8及以上版请点击链接查看：

(2条消息) 适用于MySQL8版本的两种备份方式（mysqldump，Xtrabackup8）_i鲸落i的博客-CSDN博客_mysql8备份数据库

文章目录：

第一种 mysqldump

接下来就是模拟：

模拟全备和误操作

mysqldump时间戳恢复：

XBK备份原理及使用（Xtrabackup）

xtrabackup的七大特点

接下来演示XBK的增量备份

接下来模拟数据库崩溃加误操作（XBK和GTID恢复）

恢复备份数据

第一种 mysqldump

mysqldump是mysql用于备份和数据转移的一个工具。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建你的数据库所需要的 SQL命令如CREATE DATABASE，CREATE TABLE，INSERT等等。

可以用来实现轻量级的快速迁移或恢复数据库。 mysqldump 是将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。

mysqldump一般在数据量很小的时候（几个G）可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份了。

注意使用mysqldump的时候，把二进制日志开启和GTID事务唯一ID号开启

mysqldump 参数：

-A 全库

-B 单库或多个单库

-R 存储过程和函数
-E 事件
--triggers 触发器

--master-data=2
(值为1：change master to 语句可以被slave直接执行；值为2：change master会被注释)
(1) 记录备份时刻的binlog信息
(2) 自动锁表
不加--single-transaction ,温备份
加了--single-transaction,对于InnoDB表不锁表备份(快照备份)

--single-transaction
对于InnoDB的表,进行一致性快照备份,不锁表.

在构建主从时,使用AUTO/ON
--set-gtid-purged=AUTO/ON 主从复制的时候在从上备份的时候删除事务ID号从新来

仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF

--max_allowed_packet=128M 控制的是备份时传输单个数据包的大小

接下来就是模拟：

模拟全备和误操作

首先创建库，加入数据，这里就随便加点就行了。

create database backup charset utf8mb4 collate utf8mb4_bin;
use backup
create table t1(id int primary key not null auto_increment);
insert into t1 values(1),(2),(3);
commit;

接下是全备：注意下面代码是在数据库内全备在外面的话去掉system

--set-gtid-purged=OFF（关闭事务id号处理）为了防止恢复数据的时候因事务ID号阻断恢复

--single-transaction （快照备份、热备份）

system mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction > /tmp/backup.sql

查看是否备份成功：

接下来在插入一组和一个新表t2数据：

use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;

然后模拟故障,删除表(只是模拟，不代表生产操作)

drop database backup;

删除后查看自己的日志：

show binary logs;

查看日志详情：

show binlog events in 'mysql-bin.000001';

可以看到删除是在最后一条（已用黄色笔标出）根据GTID号来实现恢复

接下来截取二进制日志：这里注意进入mysql日志的位置我的在/usr/local/mysql/data/下

mysqlbinlog --skip-gtids --include-gtids='9b7faedd-505e-11ec-a38f-000c29411d38:4-6' mysql-bin.000001 > /tmp/a.sql

接下来就是恢复：

注意：恢复的时候避免产生大量日志数据所以这里将日志记录关闭再恢复（关闭以后记得开启）

set sql_log_bin=0;

可以看到没有backup数据库

接下来导入刚才的备份(进入数据库导入)：

source /tmp/backup.sql

导入后查询一下库表：

select * from backup.t1;

可以看到还有部分数据没有恢复，接下来导。入刚才截取日志的备份即可

导入我们刚才截取的日志：

source /tmp/a.sql

导入后我们看到两个表：

可以看到数据以及恢复了。以上只是个一个简单的练习切勿在实际环境这样子查表哦

mysqldump时间戳恢复：

以上面的表为例，查看内容：

然后删除一部分

delete from  t1 limit 3;

在往里面插入几条数据：

insert into t1 values(44),(55),(66);

进入到存放日志的地方（/usr/local/mysql/data）没有设定目录

查看日志详情，这里注意直接用cat和vim 是无法查看内容的

--base64-output=decode-rows
# 看不到DML语句的"伪" SQL语句
# 看不到DML语句具体操作了什么数据

mkdir /logs && mysqlbinlog --base64-output=decode-rows mysql-bin.000001 > /logs/1.sql

然后进入我创建的目录下面看一下刚才转译出来的日志

前面这个代表时间例如： 21年12月10日 14点28分32秒

接下来可以看到我们创建的的表命令

截取我圈起来的时间：

mysqlbinlog --stop-datetime 表示某个时间之前的全部执行

mysqlbinlog --start-datetime 表示某个时间开始执行下面的日志内容

mysqlbinlog --stop-datetime='2021-12-10 14:39:03' /usr/local/mysql/data/mysql-bin.000001 > 2.sql

这里我用stop 恢复之前的数据恢复之前先查看一下表；

没有之前插入的 1 2 3

我们现在source 一下刚才截取的日志

source /logs/2.sql

然后我们在查看数据：

成功！

mysqlbinlog --start-datetime 跳过出错或者误删除的时间然后开始截取下面的所有日志

这里是跟stop同理就不演示了

mysqldump备份有三种模式：

一、GTID号恢复

二、binlog日志恢复

三、时间戳恢复

XBK备份原理及使用（Xtrabackup）

前面介绍mysqldump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用mysqldump备份就不太适合了。这时就需要一种好用又高效的工具，xtrabackup就是其中一款，号称免费版的InnoDB HotBackup。 Xtrabackup实现是物理备份，而且是物理热备 目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup；ibbackup是商业软件，需要授权，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我们这里就来介绍xtrabackup的使用。 Xtrabackup提供了两种命令行工具： xtrabackup：专用于备份InnoDB和XtraDB引擎的数据； innobackupex：这是一个perl脚本，在执行过程中会调用xtrabackup命令，这样用该命令即可以实现备份InnoDB，也可以备份MyISAM引擎的对象。 Xtrabackup是由percona提供的mysql数据库备份工具，特点： (1)备份过程快速、可靠； (2)备份过程不会打断正在执行的事务； (3)能够基于压缩等功能节约磁盘空间和流量； (4)自动实现备份检验； (5)还原速度快。

官方链接地址：http://www.percona/software/percona-xtrabackup；

xtrabackup的七大特点

1.直接拷贝物理文件，备份和恢复数据的速度非常快、安全可靠

2.在备份期间执行的事务不会间断，备份InnoDB数据不影响业务

3.备份期间不增加太多数据库的性能压力

4.支持对备份的数据自动校验

5.支持全量、增量、压缩备份及流备份

6.技持在线迁移表以及快速创建新的从库

7.支持几乎所有版本的MySQL和MariaDB

接下来就是下载安装的方法：

wget https://www.percona/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

下载好后安装： yum装能自动装上依赖关系但是需要有网

yum -y localinstall percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

安装完成以后：

全备：

--user 用户名

--password 密码

-S 制定套接字

--no-timestamp 不要时间戳，避免恢复因为时间问题出错

mkdir /logs && innobackupex --user=root --password=123 -S /tmp/mysql.sock --no-timestamp /logs/full

备份完成后我们查看一下内容:

cd /logs/full && ls

文件内：mysql、t100w、sys、student、backup 都是已有的数据库

xtrabackup_binlog_info 记录备份时刻的二进制日志信息. 可以作为binlog截取的起点.

xtrabackup_checkpoints ：联系完备和增备之间的顺序

from_lsn : 备份中包含的事务日志序列号的起点,
to_lsn: 操作保存时的事务日志序列号的结束点
last_lsn: 备份结束时的事务日志序列号.下次增量备份的起始位置这里的数值会自动加9点

xtrabackup_info 信息
xtrabackup_logfile 日志文件

以上几个文件都是备份过程中新加入的数据存放点

恢复命令：

innobackupex --apply-log /backup/full/

接下来演示XBK的增量备份

再次之前我们清理一下日志方便查找

登录数据库加入新库和新表库名test 表名t1

create database test charset utf8mb4 collate utf8mb4_bin;
use test;
create table t1(id int primary key auto_increment comment '序 号',name varchar(20) comment '名字' );
insert into t1(name) values('张三'),('李四'),('王五');
select * from t1;

退出数据库增量备份

innobackupex --user=root --password=123 --no-timestamp --incremental -S /tmp/mysql.sock --incremental-basedir=/logs/full /logs/test

--no-timestamp ：不生成时间戳

--incremental ：增量

-S ：指定套接字

--incremental-basedir：增备依赖于那个备份进行增倍

然后我们做一个对比看看全备和增倍的事务日志序列号

进入全备的目录内查看检查点文件

cat /logs/full/xtrabackup_checkpoints 
cat /logs/test/xtrabackup_checkpoints

全备：

增倍：

可以看到是基于全备to_lsn的号码进行的增倍

接下来模拟数据库崩溃加误操作（XBK和GTID恢复）

注意我这里清空一下日志然后插入新数据企业环境误用！！

我将日志文件放在自己建立的文件 /mysqldata/log 下面记得修改目录将属主属组

reset master;

插入行新数据

create database test01 charset utf8mb4 collate utf8mb4_bin;
use test01;
create table t1(id int primary key auto_increment comment ' 序 号',name varchar(20) comment '名字' );
insert into t1(name) values('怡宝'),('康师傅'),('农夫三拳');
select * from t1;

这一次我们不做增量备份直接让数据库崩溃然后恢复

killall mysqld

因为XBK是物理备份所以这里我们模拟删除mysql的数据文件

rm -rf /usr/local/mysql/data/*

现在进行恢复：首先用XBK整理一下刚才的全备文件

innobackupex --apply-log --redo-only /logs/full/

--redo-only 表示只做重做前滚操作跳过回滚操作,整理全备跟增量合并时用,注意最后一次增量合并时不加该参数

然后将增备加入到全备里面，方便一次性恢复：

innobackupex --apply-log --incremental-dir=/logs/test /logs/full

--incremental-dir 跟增量备份的目录

最后整理恢复：

innobackupex --apply-log  /logs/full

查看一下GTID号，来恢复没有增备的问题：日志位置：/mysqldata/log

mysqlbinlog --base64-output=decode-rows mysql-bin.000001 |grep 'SET'

mysqlbinlog --skip-gtids --include-gtids='9b7faedd-505e-11ec-a38f-000c29411d38:1-3' mysql-bin.000001 > /logs/binlog.sql

--skip-gtids 忽略原有的GTID信息避免恢复因唯一事务ID号导致恢复失败

恢复备份数据

复制刚才全备的内容到数据目录下：

cp -a /logs/full/* /usr/local/mysql/data/

然后将属主属组更改：

chown -R mysql:mysql /usr/local/mysql/

然后去启动数据库两种方式第一种：

/etc/init.d/mysqld start

第二种：

systemctl start mysqld

启动后登录：

设置临时关闭日志记录

set sql_log_bin=0;

导入截取日志：

source /logs/binlog.sql

验证看看是否全部恢复：

show databases;

库恢复，然后看表：

use test01;
show tables;
select * from t1;

验证一下test库内的表是否恢复。

以上就是两种备份的全部内容

更多推荐

mysql5.7数据库的两种备份方式（mysqldump，XBK）超详细

第一种 mysqldump

接下来就是模拟：

模拟全备和误操作

mysqldump时间戳恢复：

XBK备份原理及使用（Xtrabackup）

xtrabackup的七大特点

接下来演示XBK的增量备份

接下来模拟数据库崩溃加误操作（XBK和GTID恢复）

恢复备份数据

发布评论取消回复

最近发表

热门文章

标签列表

mysql5.7数据库的两种备份方式（mysqldump，XBK）超详细

第一种 mysqldump

接下来就是模拟：

模拟全备和误操作

mysqldump时间戳恢复 ：

XBK备份原理及使用（Xtrabackup）

xtrabackup的七大特点

接下来演示XBK的增量备份

接下来模拟数据库崩溃加误操作（XBK和GTID恢复）

恢复备份数据

相关文章

发布评论取消回复

最近发表

热门文章

标签列表

mysqldump时间戳恢复：