20、MySQL数据库备份与恢复

2021/11/13 2:41:24

编程Tag： mysql root 选项 20 备份 xtrabackup --

本文主要是介绍20、MySQL数据库备份与恢复，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

数据备份介绍

在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.

硬件故障
软件故障
自然灾害
黑客攻击
误操作 (占比最大)

须知在生产环境中，服务器的硬件坏了可以维修或者换新，软件崩溃可以修复或重新安装, 但是如果数据没了那可就毁了，生产环境中最重要的应该就是数据了。所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据。

备份什么

一般情况下, 我们需要备份的数据分为以下几种

二进制日志, InnoDB事务日志
代码(存储过程、存储函数、触发器、事件调度器)
服务器配置文件

备份的类型

按照备份时数据库的运行状态，可以分为三种，分别是：冷备、温备、热备。、

冷备：停库、停服务来备份，即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线。
温备：不停库、不停服务来备份，会(锁表)阻止用户的写入，即当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作。
热备：不停库、不停服务来备份，也不会(锁表)阻止用户的写入即当数据库进行备份时, 数据库的读写操作均不是受影响。

MySQL中进行不同类型的备份还要考虑存储引擎是否支持？

存储引擎	冷备	温备	热备
InnoDB	支持	支持	支持
MyISAM	支持	支持	不支持

逻辑备份与物理备份

按照备份的内容分，可以分为两种，分别是逻辑备份与物理备份

1、物理备份：直接将底层物理文件备份
2、逻辑备份：通过特定的工具从数据库中导出sql语句或者数据，可能会丢失数据精度

备份方式之全量、差异、增量

按照每次备份的数据量，可以分为全量备份、差异备份以及增量备份。

全量备份/完全备份（Full Backup）：备份整个数据集( 即整个数据库 )
部分备份：备份部分数据集(例如: 只备份一个表的变化)

差异备份
增量备份

# 1、差异备份（Differential Backup）
每次备份时，都是基于第一次完全备份的内容，只备份有差异的数据(新增的、修改的、删除的)，例如

第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第三次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第四次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第五次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
。。。

# 2、增量备份（Incremental Backup ）
每次备份时，都是基于上一次备份的内容（注意是上一次，而不是第一次），只备份有差异的数据(新增的、修改的、删除的)，所以增量备份的结果是一条链，例如

第一次备份：完全备份
第二次备份：以当前时间节点的数据为基础，备份与第一次备份内容的差异
第三次备份：以当前时间节点的数据为基础，备份与第二次备份内容的差异
第四次备份：以当前时间节点的数据为基础，备份与第三次备份内容的差异
第五次备份：以当前时间节点的数据为基础，备份与第四次备份内容的差异
。。。

针对上述三种备份方案，如何恢复数据呢?

# 1、全量备份的数据恢复
只需找出指定时间点的那一个备份文件即可，即只需要找到一个文件即可

# 2、差异备份的数据恢复
需要先恢复第一次备份的结果，然后再恢复最近一次差异备份的结果，即需要找到两个文件

# 3、增量备份的数据恢复
需要先恢复第一次备份的结果，然后再依次恢复每次增量备份，直到恢复到当前位置，即需要找到一条备份链

综上，对比三种备份方案
1、占用空间：全量 > 差异 > 增量
2、恢复数据过程的复杂程度：增量 > 差异 > 全量

备份的工具

备份工具	备份速度	恢复速度	便捷性	适用存储引擎	支持的备份类型	功能	应用场景
cp、tar等（物理）	快	快	一般	所有	冷备、全量、差异、增量	很弱	少量数据备份
lvm2快照（物理）	快	快	一般	所有	支持几乎热备（即差不多是热备，哈哈），是借助文件系统管理工具进行的备份	一般	中小型数据量的备份
xtrabackup（物理）	较快	较快	是一款非常强大的热备工具	由percona提供，只支持InnoDB/XtraDB	热备、全量、差异、增量	强大	较大规模的备份
mysqldump（逻辑）	慢	慢	一般	所有	支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备	一般	中小型数据量的备份

此外，如果考虑到增量备份，还需要结合binlog日志（binlog只属于增量恢复），需要用到工具mysqlbinlog，相当于逻辑备份的一种。

备份实战

下面我们采用多种方法实战MySQL数据备份实战。

使用cp进行备份

mysql> FLUSH TABLES WITH READ LOCK; 
Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# mkdir /data
[root@localhost ~]# cp -a /usr/local/mysql-5.7.34/data/* /data/

模拟数据丢失

[root@localhost ~]# mv /usr/local/mysql-5.7.34/data .

数据恢复

[root@localhost ~]# mkdir /usr/local/mysql-5.7.34/data
[root@localhost ~]# chown mysql.mysql /usr/local/mysql-5.7.34/data
[root@localhost ~]# cp -a /data/* /usr/local/mysql-5.7.34/data/
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

使用mysqldump

MySQL数据库自带的一个很好用的备份命令。是逻辑备份，导出的是SQL语句。也就是把数据MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的过程。

语法

mysqldump  -h 服务器  -u用户名  -p密码  选项与参数 > 备份文件.sql

参数

参数	解释
-A --all-databases	导出全部数据库
-Y --all-tablespaces	导出全部表空间
--add-drop-database	每个数据库创建之前添加drop数据库语句。
--add-drop-table	每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
--add-locks	在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
--comments	附加注释信息。默认为打开，可以用--skip-comments取消
--compact	导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
-c --complete-insert	使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
-C --compress	在客户端和服务器之间启用压缩传递所有信息
-B --databases	导出几个数据库。参数后面所有名字参量都被看作数据库名。
--debug	输出debug信息，用于调试。
--debug-info	输出调试信息并退出
--default-character-set	设置默认字符集，默认值为utf8
--delayed-insert	采用延时插入方式（INSERT DELAYED）导出数据
-E --events	导出事件。
--master-data	在备份文件中写入备份时的binlog文件。值为1时，binlog文件名和位置没有注释，为2时，则在备份文件中将binlog的文件名和位置进行注释。
--flush-logs	开始导出之前刷新日志。请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
--flush-privileges	在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
--force	在导出过程中忽略出现的SQL错误。
-h --host	需要导出的主机信息
--ignore-table	不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
-x --lock-all-tables	提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
-l --lock-tables	开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
--single-transaction	适合innodb事务数据库的备份。保证备份的一致性，原理是设定本次会话的隔离级别为Repeatable read，来保证本次会话（也就是dump）时，不会看到其它会话已经提交了的数据。
-F	刷新binlog，如果binlog打开了，-F参数会在备份时自动刷新binlog进行切换。
-n --no-create-db	只导出数据，而不添加CREATE DATABASE 语句。
-t --no-create-info	只导出数据，而不添加CREATE TABLE 语句。
-d --no-data	不导出任何数据，只导出数据库表结构。
-p --password	连接数据库密码
-P --port	连接数据库端口号
-u --user	指定连接的用户名。
-R	备份存储过程和函数数据（如果开发写了函数和存储过程，就备，没写就不备）
--triggers	备份触发器数据（现在都是开发写触发器）

案例

# 在命令行执行命令，进行全量备份
[root@localhost mysql]# mysqldump -uroot -p123456 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql]# ll /tmp/full.sql.gz 
-rw-r--r--. 1 root root 191963 Oct 14 00:01 /tmp/full.sql.gz

# 在命令行执行命令，刷新binlog，便于日后查找
[root@localhost mysql]# mysql -uroot -p123456 -e "flush logs"
mysql: [Warning] Using a password on the command line interface can be insecure.

# 登录数据库，再插入一些数据，模拟增量，这些数据写入了新的binlog
mysql> insert t1 values(4),(5),(6);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


# 案例2：要求每天凌晨3点半的时候，做数据库备份

1、编写脚本
[root@localhost ~]# cat mysqldump.sh 
#!/bin/bash

USERNAME=root
PASSWORD=123456
DATABASE=linux14

/usr/local/mysql/bin/mysqldump -u${USERNAME} -p${PASSWORD} -R --triggers -B ${DATABASE} --master-data=2 --single-transaction  | gzip > /tmp/MySQL_`date +"%F".sql.gz`


2、加入定时任务
30 03 * * *  /root/mysqldump.sh

模拟数据丢失

# 模拟数据丢失
mysql> drop database db1;

# 恢复数据
# 1、mysql数据导入时，临时关闭binlog，不要将恢复数据的写操作也记入
mysql> set sql_log_bin=0;

# 2、先恢复全量
mysql> source /tmp/MySQL_2021-10-15.sql

如果是压缩包呢，那就这么做
mysql> system zcat /tmp/MySQL_2021-10-15.sql.gz | mysql -uroot -p123456

# 3、模拟恢复数据
mysql> drop database db01;
Query OK, 1 row affected (0.01 sec)

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> system zcat /tmp/MySQL_2021-10-15.sql.gz | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use db01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

测试在线热备份

可以先准备一个存储过程，一直保持写入操作，然后验证热备。

#1. 准备库与表
create database if not exists db01;
use db01;
create table s1(
  id int,
  name varchar(20),
  gender char(6),
  email varchar(50)
);

#2. 创建存储过程，每隔3秒插入一条
delimiter $$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'shanhe','male',concat('shanhe',i,'@oldboy'));
     select concat('shanhe',i,'_ok') as name,sleep(3);
  set i=i+1;
    end while;
END$$
delimiter ;

#3. 查看存储过程
show create procedure auto_insert1\G

备份

 
# 1、先打开binlog日志
mysql> SET sql_log_bin=ON; 
Query OK, 0 rows affected (0.00 sec)

# 2、登录数据库，执行存储过程
mysql> use db01;
mysql> call auto_insert1();

若想杀死存储过程
mysql> show processlist; -- 查出id
mysql> kill id号;


# 3、在命令行执行下述命令，进行全量备份
[root@localhost mysql]# mysqldump -uroot -p123456  -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz 
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# 4、全量备份完毕后的一段时间里，数据依然插入，写入了mybinlog.000001中
#    然后我们在命令行刷新binlog，产生了新的mybinlog.000002
[root@localhost mysql]# mysql -uroot -p123456 -e "flush logs"
mysql: [Warning] Using a password on the command line interface can be insecure.

# 5、此时数据依然在插入，但都写入了最新的mybinlog.000002中，所以需要知道的是，增量的数据在mysqlbinlog.000001与mybinlog.000002中都有
我们登录数据库，杀掉存储过程，观察到最新的数据插到了id=55的行
mysql> show processlist; -- 查出id
mysql> kill id号;

删除数据

mysql> drop database db01;
Query OK, 1 row affected (0.12 sec)

恢复数据

# 登录数据库，先恢复全量
mysql> set sql_log_bin=0;
mysql> system zcat /tmp/full.sql.gz | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

-- 查看恢复到了id = 16
mysql> select * from db01.s1;
+------+--------+--------+-----------------+
| id   | name   | gender | email           |
+------+--------+--------+-----------------+
|    1 | shanhe | male   | shanhe1@oldboy  |
|    2 | shanhe | male   | shanhe2@oldboy  |
|    3 | shanhe | male   | shanhe3@oldboy  |
|    4 | shanhe | male   | shanhe4@oldboy  |
|    5 | shanhe | male   | shanhe5@oldboy  |
|    6 | shanhe | male   | shanhe6@oldboy  |
|    7 | shanhe | male   | shanhe7@oldboy  |
|    8 | shanhe | male   | shanhe8@oldboy  |
|    9 | shanhe | male   | shanhe9@oldboy  |
|   10 | shanhe | male   | shanhe10@oldboy |
|   11 | shanhe | male   | shanhe11@oldboy |
|   12 | shanhe | male   | shanhe12@oldboy |
|   13 | shanhe | male   | shanhe13@oldboy |
|   14 | shanhe | male   | shanhe14@oldboy |
|   15 | shanhe | male   | shanhe15@oldboy |
|   16 | shanhe | male   | shanhe16@oldboy |
+------+--------+--------+-----------------+
16 rows in set (0.00 sec)

物理备份之Xtrabackup

Xtrabackup 是一个对 InnoDB 做数据备份的工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具；支持在线热备份(备份时不影响数据读写)，是商业备份工具 InnoDB Hotbackup 的一个很好的替代品。

官方文档：http://www.percona.com/doc/percona-xtrabackup/2.1/

Xtrabackup有两个主要的工具：

xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。
innobackupex 是参考了 InnoDB Hotbackup 的 innoback 脚本修改而来的.innobackupex 是一个 perl 脚本封装，封装了 xtrabackup。主要是为了方便的同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。

Xtrabackup可以做些做什么？

在线(热)备份整个库的InnoDB、 XtraDB表。

在xtrabackup的上一次整库备份基础上做增量备份(innodb only) ，以流的形式产生备份，可以直接保存到远程机器上(本机硬盘空间不足时很有用) 。

MySQL数据库本身提供的工具并不支持真正的增量备份，二进制日志恢复是point-in-time(时间点)的恢复而不是增量备份。

Xtrabackup工具支持

对InnoDB存储引擎的增量备份，工作原理如下：

首先完成一个完全备份，并记录下此时检查点的LSN(Log Sequence Number)。
在进程增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN。

首先，在 logfile 中找到并记录最后一个 checkpoint(“last checkpoint LSN”)，然后开始从 LSN 的位置开始拷贝 InnoDB 的 logfile 到xtrabackup_logfile；接着，开始拷贝全部的数据文件.ibd；在拷贝全部数据文件结束之后，才停止拷贝logfile。因为logfile里面记录全部的数据修改情况，所以，即时在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。

XtraBackup备份的优点

个人理解：

无需停止数据库进行InnoDB热备,快速、可靠的完成备份
备份期间不间断事务处理
节省磁盘空间和网络带宽
自动对备份文件进行验证
快速恢复，保障在线运行时间持久性

官方说明：

在不停库的情况下，对InnoDB数据库进行热备
支持增量备份MySQL数据库
通过流压缩备份MySQL数据到另外一台服务器
在线MySQL服务器之间进行表空间迁移

Xtrabackup的备份原理图

innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件
xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log
再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）
xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）
innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。
当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态
xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成
innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES
最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。

Xtrabackup 安装

mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本。

mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本，xtrabackup8.0也只支持mysql8.0以上的版本。

比如，接触过一些金融行业，mysql版本还是多采用mysql 5.7，当然oracle官方对于mysql 8.0的开发支持力度日益加大，新功能新特性迭代不止。生产环境采用mysql 8.0的版本比例会日益增加。

下载地址：https://www.percona.com/downloads/

# 安装方式一

# 安装yum仓库
[root@localhost ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y

# 安装XtraBackup命令
[root@localhost ~]# yum install percona-xtrabackup-24 -y


# 安装方式二
#下载epel源
[root@localhost ~]# wget -O /etc/yum.repos.d/epel.repo  https://mirrors.aliyun.com/repo/epel-7.repo

#安装依赖
[root@localhost ~]# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

#下载Xtrabackup
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm

# 安装
[root@localhost ~]# yum localinstall -y percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm

安装完后会生成命令

xtrabackup      以前使用该命令
innobackupex    现在使用该命令

innobackupex是xtrabackup的前端配置工具，使用innobackupex备份时, 会调用xtrabackup备份所有的InnoDB表, 复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件, 同时还会备份触发器和数据库配置文件信息相关的文件, 这些文件会被保存至一个以时间命名的目录。

Xtrabackup 备份方式（物理备份）

1.对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。

2.对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。

3.备份时读取配置文件/etc/my.cnf

Xtrabackup 参数说明

--host                                  指定主机
--user                                  指定用户名
--password                          指定密码
--port                                  指定端口
--databases                         指定数据库
--incremental                       创建增量备份
--incremental-basedir       指定包含完全备份的目录
--incremental-dir           指定包含增量备份的目录   
--apply-log                     对备份进行预处理操作。一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
--redo-only                         不回滚未提交事务
--copy-back                         恢复备份目录

Xtrabackup全量备份与恢复

使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件，这些文件会被保存到一个以时间命名的目录当中。

#1、创建备份目录，会把mysql的datadir中的内容备份到改目录中
[root@localhost ~]# mkdir /backup

#2、全备
#2.1 在本地执行下述命令，输入登录数据的本地账号与密码
#2.2 指定备份目录为/backup下的full目录
[root@localhost ~]# innobackupex --user=root  --password=123456 /backup/full

#3、查看：默认会在备份目录下生成一个以时间戳命名的文件夹
[root@localhost ~]# cd /backup/full/
[root@localhost full]# ls
2021-10-14_22-39-02
[root@localhost full]# ls 2021-10-14_22-39-02 #备份目录
。。。
[root@localhost full]# ls /var/lib/mysql # 数据目录
。。。

# 4、去掉时间戳，让备份数据直接放在备份目录下
我们在写备份脚本和恢复脚本，恢复的时候必须指定上一次备份的目录，如果备份目录带着时间戳，该时间戳我们很难在脚本中确定，无为了让脚本编写更加方便，我们可以使用选项--no-timestamp去掉时间戳，让备份内容直接放置于我们指定的目录下（ps：金融公司喜欢每天全备，每小时增备，如果备份目录带着时间戳，看似合理，但确实会很让头疼）
[root@localhost full]# rm -rf 2021-10-14_22-39-02
[root@localhost backup]# innobackupex --user=root --password=123456 --no-timestamp /backup/full

[root@localhost backup]# ll /backup/full/
total 12340
-rw-r-----. 1 root root      487 Oct 14 22:42 backup-my.cnf
drwxr-x---. 2 root root       48 Oct 14 22:42 db01
drwxr-x---. 2 root root       48 Oct 14 22:42 db02
-rw-r-----. 1 root root      646 Oct 14 22:42 ib_buffer_pool
-rw-r-----. 1 root root 12582912 Oct 14 22:42 ibdata1
drwxr-x---. 2 root root     4096 Oct 14 22:42 mysql
drwxr-x---. 2 root root     8192 Oct 14 22:42 performance_schema
drwxr-x---. 2 root root     8192 Oct 14 22:42 sys
-rw-r-----. 1 root root       20 Oct 14 22:42 xtrabackup_binlog_info
-rw-r-----. 1 root root      135 Oct 14 22:42 xtrabackup_checkpoints
-rw-r-----. 1 root root      484 Oct 14 22:42 xtrabackup_info
-rw-r-----. 1 root root     2560 Oct 14 22:42 xtrabackup_logfile


# 如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';　　#创建用户
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser';　　#回收此用户所有权限
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost';　　#授权刷新、锁定表、用户查看服务器状态
mysql> FLUSH PRIVILEGES;　　#刷新授权表

实战

1、全量备份
[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/　　
#在master上进行全库备份#语法解释说明：
#--user=root 指定备份用户
#--password=123456  指定备份用户密码
#--host　　指定主机
#/backups　　指定备份目录

2、恢复
[root@slave tools]# innobackupex --apply-log /backups/2021-10-14_11-01-37/   #合并数据，使数据文件处于一致性的状态
[root@slave ~]# rm -rf /usr/local/mysql/data/　　#在slave上删除原有的数据
[root@slave ~]# vim /etc/my.cnf　　#配置my.cnf的数据目录路径，否则会报错，要和master一致
datadir=/usr/local/mysql/data
[root@slave ~]# innobackupex --copy-back /backups/2021-10-14_11-01-37/　　#在slave上数据恢复
[root@slave ~]# ll /usr/local/mysql/data/　　#slave上查看数据目录，可以看到数据已经恢复，但是属主会有问题，需要进行修改，所以一般使用mysql的运行用户进行恢复，否则需要进行修改属主和属组信息
[root@slave ~]# chown -R mysql.mysql /usr/local/mysql/data/　　#修改属主属组
[root@slave ~]# mysql -uroot -p -e "show databases;"　　#查看数据，是否恢复

总结全库备份与恢复三步曲：

innobackupex全量备份，并指定备份目录路径。
在恢复前，需要使用--apply-log参数先进行合并数据文件，确保数据的一致性要求。
恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。

Xtrabackup增量备份与恢复

使用innobackupex进行增量备份，每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。在进行增量备份时，首先要进行一次全量备份，第一次增量备份是基于全备的，之后的增量备份都是基于上一次的增量备份的，以此类推。

要实现第一次增量备份，可以使用下面的命令进行：

基于全量备份的增量备份与恢复，做一次增量备份（基于当前最新的全量备份）

--incremental                       创建增量备份
--incremental-basedir       指定包含完全备份的目录
--incremental-dir           指定包含增量备份的目录 



innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental /backups/ --incremental-basedir=/backups/2021-10-14_22-39-02

1. 准备基于全量
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2021-10-14_22-39-02

2. 准备基于增量
innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37 --incremental-dir=/backups/2021-10-14_22-39-02

3. 恢复
innobackupex --copy-back --defaults-file=/etc/my.cnf /opt/2021-10-14_22-39-02

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

案例

1、备份数据
[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/   #全备数据
[root@master ~]# mysql -uroot -p　　#在master上创建student库并创建testtb表插入若干数据
Enter password: 
mysql> create database student;
Query OK, 1 row affected (0.03 sec)

mysql> use student;
Database changed
mysql> create table testtb(id int);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into testtb values(1),(10),(99);
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from testtb;
+------+
| id   |
+------+
|    1 |
|   10 |
|   99 |
+------+
3 rows in set (0.00 sec)

mysql> quit;

#使用innobackupex进行增量备份
[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2021-10-15_11-01-37/
[root@localhost 2021-10-15_17-10-01]# cat xtrabackup_info 
innodb_from_lsn = 0                         # 备份开始的ID
innodb_to_lsn = 998385358               # 全量备份结束的ID
[root@localhost 2021-10-15_17-10-01]# cat /backup/full3/2021-10-15_17-14-04/xtrabackup_info 
innodb_from_lsn = 998385358     # 增量备份开始的ID
innodb_to_lsn = 998388146           # 增量备份结束的ID


2、增量备份后数据恢复演示
（1）模拟mysql故障，删除数据目录所有数据
[root@master ~]# /etc/init.d/mysqld stop　　#模拟mysql故障，停止mysql
Shutting down MySQL.. SUCCESS! 
[root@master ~]# rm -rf /usr/local/mysql/data/*　　#删除数据目录中的所有数据

（2）合并全备数据目录，确保数据的一致性
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/one/2021-10-15_17-25-10/

（3）将增量备份数据合并到全备数据目录当中
[root@localhost ~]# innobackupex --apply-log --redo-only /backup/one/2021-10-15_17-25-10/   --incremental-dir=/backup/two/2021-10-15_17-26-58/

（4）恢复数据
[root@localhost backup]# innobackupex --copy-back /backup/one/2021-10-15_17-25-10/

[root@localhost /]# chown -R mysql.mysql /mysql_data/　　#更改数据的属主属组
[root@master ~]# /etc/init.d/mysqld start　　#启动mysql
Starting MySQL.Logging to '/usr/local/mysql/data/master.err'.
.. SUCCESS! 
[root@master ~]# mysql -uroot -p -e "show databases;"　　#查看数据是否恢复
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kim                |
| mysql              |
| performance_schema |
| repppp             |
| student            |
| wordpress          |
+--------------------+

总结：

增量备份需要使用参数--incremental指定需要备份到哪个目录，使用incremental-dir指定全备目录。
进行数据备份时，需要使用参数--apply-log redo-only先合并全备数据目录数据，确保全备数据目录数据的一致性。
再将增量备份数据使用参数--incremental-dir合并到全备数据当中
最后通过最后的全备数据进行恢复数据。

注意：如果有多个增量备份，需要逐一合并到全备数据当中，再进行恢复。

数据的快速导入与导出

在公司中，如果运营或者产品手里有几千万甚至几亿条数据，要求你将其导入数据中，请问如何做？

如果你依据运营或产品交给你的数据文件直接使用insert语句，一行一行地批量插入，那至少需要1-2天时间才能插入完毕，显然是不可行的。

此时我们可以用LOAD DATA INFILE语句。LOAD DATA INFILE语句可以从一个文本文件中，将数据以很高的速度读入一个表中。MySQL官方文档也说明了，该方法比一次性插入一条数据性能快20倍。

此外，mysql也支持快速导出语句SELECT INTO OUTFILE，使用MySQL的SELECT INTO OUTFILE 、LOAD DATA INFILE快速导出导入数据，12G的数据导出用时3分钟左右，导入用时4分钟左右（执行时间根据机器的配置会有所不同，不具有参考价值）。

快速导入与导出的语法

快速导出

语法：
SELECT... INTO OUTFILE 导出文本文件

要想导出成功，需要设置安全目录才行
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp

示例：
SELECT * FROM db1.t1
    INTO OUTFILE '/tmp/db1_t1.txt'
    FIELDS TERMINATED BY ','      -- 定义字段分隔符
    OPTIONALLY ENCLOSED BY '"'    -- 定义字符串使用什么符号括起来
    LINES TERMINATED BY '\n';     -- 定义换行符

快速导入

语法
LOAD DATA INFILE 导入的文本文件路径

示例
mysql> DELETE FROM student1;
mysql> create table new_t1(表结构与文件中数据保持一致);
mysql> LOAD DATA INFILE '/tmp/db1_t1.txt'
            INTO TABLE new_db.new_t1
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '"'
            LINES TERMINATED BY '\n';

数据库迁移

数据库迁移是我们经常可遇到的问题，对于少量的数据，迁移基本上不会有什么问题。生产环境中，有以下情况需要做迁移工作：

磁盘空间不够比如一些老项目，选用的机型并不一定适用于数据库。随着时间的推移，硬盘很有可能出现短缺。
业务出现瓶颈比如项目中采用单机承担所有的读写业务，业务压力增大，不堪重负。如果 IO 压力在可接受的范围，会采用读写分离方案。
机器出现瓶颈机器出现瓶颈主要在磁盘 IO 能力、内存、CPU，此时除了针对瓶颈做一些优化以外，选择迁移是不错的方案。
项目改造某些项目的数据库存在跨机房的情况，可能会在不同机房中增加节点，或者把机器从一个机房迁移到另一个机房。再比如，不同业务共用同一台服务器，为了缓解服务器压力以及方便维护，也会做迁移。

附录：参数

--compress：该选项表示压缩innodb数据文件的备份。
--compress-threads：该选项表示并行压缩worker线程的数量。
--compress-chunk-size：该选项表示每个压缩线程worker buffer的大小，单位是字节，默认是64K。
--encrypt：该选项表示通过ENCRYPTION_ALGORITHM的算法加密innodb数据文件的备份，目前支持的算法有ASE128,AES192,AES256。
--encrypt-threads：该选项表示并行加密的worker线程数量。
--encrypt-chunk-size：该选项表示每个加密线程worker buffer的大小，单位是字节，默认是64K。
--encrypt-key：该选项使用合适长度加密key，因为会记录到命令行，所以不推荐使用。
--encryption-key-file：该选项表示文件必须是一个简单二进制或者文本文件，加密key可通过以下命令行命令生成：openssl rand -base64 24。
--include：该选项表示使用正则表达式匹配表的名字[db.tb]，要求为其指定匹配要备份的表的完整名称，即databasename.tablename。
--user：该选项表示备份账号。
--password：该选项表示备份的密码。
--port：该选项表示备份数据库的端口。
--host：该选项表示备份数据库的地址。
--databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。
--tables-file：该选项表示指定含有表列表的文件，格式为database.table，该选项直接传给--tables-file。
--socket：该选项表示mysql.sock所在位置，以便备份进程登录mysql。
--no-timestamp：该选项可以表示不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹。
--ibbackup：该选项指定了使用哪个xtrabackup二进制程序。IBBACKUP-BINARY是运行percona xtrabackup的命令。这个选项适用于xtrbackup二进制不在你是搜索和工作目录，如果指定了该选项,innoabackupex自动决定用的二进制程序。
--slave-info：该选项表示对slave进行备份的时候使用，打印出master的名字和binlog pos，同样将这些信息以change master的命令写入xtrabackup_slave_info文件。可以通过基于这份备份启动一个从库。
--safe-slave-backup：该选项表示为保证一致性复制状态，这个选项停止SQL线程并且等到show status中的slave_open_temp_tables为0的时候开始备份，如果没有打开临时表，bakcup会立刻开始，否则SQL线程启动或者关闭知道没有打开的临时表。如果slave_open_temp_tables在--safe-slave-backup-timeount（默认300秒）秒之后不为0，从库sql线程会在备份完成的时候重启。
--rsync：该选项表示通过rsync工具优化本地传输，当指定这个选项，innobackupex使用rsync拷贝非Innodb文件而替换cp，当有很多DB和表的时候会快很多，不能--stream一起使用。
--kill-long-queries-timeout：该选项表示从开始执行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的这些查询之间等待的秒数。默认值为0，不会kill任何查询，使用这个选项xtrabackup需要有Process和super权限。
--kill-long-query-type：该选项表示kill的类型，默认是all，可选select。
--ftwrl-wait-threshold：该选项表示检测到长查询，单位是秒，表示长查询的阈值。
--ftwrl-wait-query-type：该选项表示获得全局锁之前允许那种查询完成，默认是ALL，可选update。
--galera-info：该选项表示生成了包含创建备份时候本地节点状态的文件xtrabackup_galera_info文件，该选项只适用于备份PXC。
--stream：该选项表示流式备份的格式，backup完成之后以指定格式到STDOUT，目前只支持tar和xbstream。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--defaults-extra-file：该选项指定了在标准defaults-file之前从哪个额外的文件读取MySQL配置，必须在命令行的第一个选项的位置。一般用于存备份用户的用户名和密码的配置文件。
----defaults-group：该选项表示从配置文件读取的组，innobakcupex多个实例部署时使用。
--no-lock：该选项表示关闭FTWRL的表锁，只有在所有表都是Innodb表并且不关心backup的binlog pos点，如果有任何DDL语句正在执行或者非InnoDB正在更新时（包括mysql库下的表），都不应该使用这个选项，后果是导致备份数据不一致，如果考虑备份因为获得锁失败，可以考虑--safe-slave-backup立刻停止复制线程。
--tmpdir：该选项表示指定--stream的时候，指定临时文件存在哪里，在streaming和拷贝到远程server之前，事务日志首先存在临时文件里。在 使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。
--history：该选项表示percona server 的备份历史记录在percona_schema.xtrabackup_history表。
--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir。
--incremental-basedir：该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录，与--incremental同时使用。
--incremental-dir：该选项表示增量备份的目录。
--incremental-force-scan：该选项表示创建一份增量备份时，强制扫描所有增量备份中的数据页。
--incremental-lsn：该选项表示指定增量备份的LSN，与--incremental选项一起使用。
--incremental-history-name：该选项表示存储在PERCONA_SCHEMA.xtrabackup_history基于增量备份的历史记录的名字。Percona Xtrabackup搜索历史表查找最近（innodb_to_lsn）成功备份并且将to_lsn值作为增量备份启动出事lsn.与innobackupex--incremental-history-uuid互斥。如果没有检测到有效的lsn，xtrabackup会返回error。
--incremental-history-uuid：该选项表示存储在percona_schema.xtrabackup_history基于增量备份的特定历史记录的UUID。
--close-files：该选项表示关闭不再访问的文件句柄，当xtrabackup打开表空间通常并不关闭文件句柄目的是正确的处理DDL操作。如果表空间数量巨大，这是一种可以关闭不再访问的文件句柄的方法。使用该选项有风险，会有产生不一致备份的可能。
--compact：该选项表示创建一份没有辅助索引的紧凑的备份。
--throttle：该选项表示每秒IO操作的次数，只作用于bakcup阶段有效。apply-log和--copy-back不生效不要一起用。

--apply-log：该选项表示同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
--use-memory：该选项表示和--apply-log选项一起使用，prepare 备份的时候，xtrabackup做crash recovery分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)，推荐1G。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--export：这个选项表示开启可导出单独的表之后再导入其他Mysql中。
--redo-only：这个选项在prepare base full backup，往其中merge增量备份（但不包括最后一个）时候使用。

这篇关于20、MySQL数据库备份与恢复的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！