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

第一种 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.com/software/percona-xtrabackup；

xtrabackup的七大特点

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

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

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

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

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

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

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

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

wget https://www.percona.com/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库内的表是否恢复。

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