MySQL 逻辑备份、物理备份、增量备份

备份

• 逻辑备份
  mysqldump -u用户 -p密码 -h服务器 ip --all-databases > xx.dump.sql

• 物理备份
  xtrabackup

MySQL逻辑备份mysqldump

逻辑备份特点

• 备份的是建表、建库、插入等操作所执行SQL语句(DDL,DMLDCL),适用于中小型数据库
• 效率相对较低

在日常工作中，我们会使用mysqldump命令创建SQL格式的存储文件来备份数据库。或者我们把数据导出后做数据迁移，主从复制等操作。
mysqldump是一个逻辑备份工具，复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。默认情况下，生成insert语句，也能生成其它分隔符的输出或XML格式的文件

特点
1.自动记录position位置

show master  status\G;

2. 可用性，一致性
   锁表机制
   用法

mysqldump -h 服务器 -u用户名 -p密码  数据库名>备份文件.sql

查看帮助
mysqldump  --help

常见参数

• --single-transaction 备份前启用一个事务，保证数据一致性。
  仅对 InnoDB 存储引擎有效。还有需要保证没有其他的连接正在使用以下语句： ALTER TABLE， CREATE TABLE，DROP TABLE， RENAME TABLE， TRUNCATE TABLE

• -l, --lock tables 对于不支持事务的存储引擎的表备份使用此选项，比如 MySAM, 可以保证备份期间的数据一致性。会依次对正在备份的每个数据库中的所有表进行锁表操作，此时只可以读。和 --single-transaction 互斥。

• -x, --lock-all-tables 锁定所有数据库中的所有表。这是通过在整个转储期间采用全局读锁来实现。

有几个选项控制mysqldump如何 处理存储的程序（存储过程和函数，触发器和事件）：

• --events：备份事件计划程序事件

• --routines：备份存储过程和函数

• --triggers：备份表的触发器

• --triggers默认情况下启用 该选项，以便在转储表时，它们伴随着它们具有的任何触发器。

• 默认情况下要禁用这些选项。需要明确设置这些选项：
  --skip-events， --skip-routines, --skip-triggers。

日常用法
备份所有的库

先配置用户名和密码
vim ./mysqluser.db
[mysqldump]
user=root
password=123

mysqldump --defaults-file=~/.mysql_user -h172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql

备份一个库里指定的之个表

mysqldump db1 t1 t3 t7 > dump.sql
db是库  t1 t3 t7 是表

备份多个库

mysqldump --databases db1 db2 > d1_d2.dump.sql
需要加--databases 不然会将db2 认为是db1的表

其他参数

• --master-data=0|1|2
  服务器的二进制日志必须打开
  0 不记录二进制日志文件及位置：
  1 以CHANGE MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器：
  2 以CHANGE MASTER TO 的方式记录位置，但默认被注释：

• --dump-slave 用于在slave上dump数据，建立新的slave。因为我们在使用mysqldump时会锁表，所以大多数情况下，我们的导出操作一般会在只读备库上做，为了获取主库的Relay_Master_Log_File(二进制日志)和Exec_Master_Log_Pos(主服务器二进制日志中数据所处的位置)，需要用到这个参数，不过这个参数只有在5.7以后的才会有

• --no-data, -d 不导出任何数据，只导出数据库表结构

• --lock-all-tables:锁定所有表 对MyISAM引擎的表开始备份前，先锁定所有表。

优势

mysqldump的优势：

1.可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。

2.不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。

3.不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。 对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复。

恢复

mysql -uroot -p123456   db1  < dump.sql

或者在mysql中使用source命令：

mysql> source dump.sql

如果文件不包含create databases 和 use 语句的单数据库转储，请先创建数据库

shell> mysqladmin create db1

然后在加载转储文件时指定数据库名称：

shell> mysql db1 < dump.sql

或者，在mysql中创建数据库，将其选为默认数据库，然后加载转储文件：

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql>source dump.sql

shell> mysql --defaults-file=~/.mysql_user < /backup/2016-12-08-04-mysql-all.sql

MySQL 物理备份： Innobackupex 和 xtrabackup（热备）

Percona XtraBackup是一款基于MySQL的热备份的开源实用程序，它可以备份5.1到5.7版本里InnoDB,XtraDB,MyISAM存储引擎的表， Xtrabackup有两个主要的工具：xtrabackup、innobackupex 。

使用Yum安装
1.确保安装 EPEL 源

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

2.安装libev
为了成功安装Percona XtraBackup libev包需要先安装

yum - y install libev

3.安装Percona存储库

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

4.安装软件

yum install percona-xtrabackup-24

全备

下面的命令均假设没有在 my.cnf 中配置任何关于xtrabackup 的选项

要执行备份需要指定 备份数据放置的位置，就是目录，假如目录不存在，则会自动创建；==注意这个目录不会被递归创建，仅仅会创建最后一级目录；==假如存在，就会直接开始备份，并且不会覆盖原来的数据。

1.开始备份

xtrabackup  --backup  --user=root  --password='xxxx'   --target-dir=/backup/full

--target-dir指定备份路径 
注意:备份只会创建最后一层目录，如果backup没有需要手动创建

2.查看备份文件

root@mysql-master ~]# ls -lh /backups/full
总用量 13M
-rw-r----- 1 root root  487 8月  18 09:44 backup-my.cnf
-rw-r----- 1 root root  293 8月  18 09:44 ib_buffer_pool
-rw-r----- 1 root root  12M 8月  18 09:44 ibdata1
drwxr-x--- 2 root root 4.0K 8月  18 09:44 mysql
drwxr-x--- 2 root root   88 8月  18 09:44 one_db
drwxr-x--- 2 root root 8.0K 8月  18 09:44 performance_schema
drwxr-x--- 2 root root   58 8月  18 09:44 shark_db
drwxr-x--- 2 root root 8.0K 8月  18 09:44 sys
-rw-r----- 1 root root  115 8月  18 09:44 xtrabackup_checkpoints
-rw-r----- 1 root root  446 8月  18 09:44 xtrabackup_info
-rw-r----- 1 root root 2.5K 8月  18 09:44 xtrabackup_logfile

进入目录后，可以看到一些目录，这些目录与我们数据库的名称相同，没错，这些就是各个数据库的数据文件备份目录。

还有一个innodb的共享表空间文件，ibdata1，注意，如果想要使用xtrabackup备份众多数据库中的某一个，那么必须保证在创建这个数据库时，已经开启了innodb_file_per_table参数，否则将无法单独备份数据库服务器中的某一个数据库。

除了刚才描述的这些数据文件，xtrabackup还为我们生成了一些文件，我们来看看这些文件都有什么用（不同版本的xtrabackup生成的文件可能不同）。

1.backup-my.cnf
此文件中包含了my.cnf中的一些设置信息，但是，并不是my.cnf中的所有信息都会包含在此文件中，此文件中只包含了备份时需要的信息。
xtrabackup_binlog_info
需要开启二进制日志
此文件中记录了备份开始时二进制日志文件的"位置(position)"
xtrabackup_checkpoints

如果你要备份的数据量巨大，那么备份时长会变长，期间备份的事务日志容量有可能会很大。
那么，我们可以使用--use-memory选项，加速准备工作的完成，在不指定内存大小的情况下，准备工作默认会占用100MB的内存，如果服务器有一定的空闲内存，那么我们可以让xtrabackup使用指定大小的内存完成准备工作，以提升准备工作完成的速度，示例语句如下。

shell> xtrabackup --prepare --use-memory=512M --target-dir=/backups/full

准备备份时不建议中断xtrabackup进程，因为这可能会导致数据文件损坏，备份将无法使用。如果准备过程中断，则无法保证备份有效性。
准备备份数据完成后，应该会看到如下信息。

InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13596200
180818 10:09:19 completed OK!

恢复

xtrabackup 在执行copyback时会读取数据库的my.cnf中的配置，但是如果my.cnf中没有配置datadir，那么--datadir选项必须存在，而且，datadir目录必须为空目录，其中不能存在数据，否则在执行上述命令时会报错，--copy-back选项对应的目录就是我们准备好的可用数据的目录。

为了能够正常的恢复数据，我们先确定数据库服务已经停止了，而且对应的数据目录中不存在数据，然后进行数据还原工作，删除数据目录中的文件与日志。

操作步骤
1.停止数据库的服务
2.清理环境
3.修改权限
4.启动数据库

systemctl stop mysqld.service  停止MySQL服务
rm -rf /var/lib/mysql/*   删除MySQL的所有文件

xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/full
恢复数据

或者使用rsync命令

rsync -avrP /backup/  /var/lib/mysql/
完成后需要将所有文件的权限递归修改成mysql
chown  mysql.mysql -R /var/lib/mysql 

增量备份

特点：每次备份，都对自上一次备份（注意是上一次，不是第一次）到此时备份之间有变化的文件，进行备份。所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时持续的时间长。

可以在每个完整备份之间执行许多增量备份，因此您可以设置备份过程，例如每周一次完整备份和每天增量备份，或每天完整备份和每小时增量备份。

增量备份有效，因为每个InnoDB页面都包含一个日志序列号或LSN。该LSN是整个数据库系统的版本号。每个页面的LSN
显示它最近的更改。

当我们做过全量备份以后会在目录下产生xtrabackup_checkpoints的文件 这里面记录了lsn和备份方式，我们可以基于这次的全量做增量的备份。

创建增量备份

1.创建全量备份

xtrabackup --backup --user=root --password=123 --target-dir=/backups/base

2.向数据库中添加数据

insert into t10(id,name)values(1,'abc');

3.创建增量备份

xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc1 --incremental-basedir=/backups/base

执行完命令后 去查看inc1此时目录下会包含增量文件

查看 增量备份的 xtrabackup_checkpoints文件里面的lsn等信息会发生改变

cat /backups/inc1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 13596423
to_lsn = 13596628
last_lsn = 13596637
compact = 0
recover_binlog_info = 0
这也意味着你可以在增量的备份上继续增量的备份。

from_lsn是备份的起始LSN，对于增量，它必须与前一个/基本备份的to_lsn（如果它是最后一个检查点）相同。

上面的情况是，to_lsn （上一个检查点LSN）和last_lsn（上次复制的LSN）之间存在差异，这意味着在备份过程中服务器上存在一些流量

再次在数据库中插入一条数据

insert into t10(id,name)values(2,'cbd');

添加完成后用inc1作为这次增量备份的基础

xtrabackup --backup --user=root --password=123 --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1

增量备份与完全备份步骤不同，在完全备份中，执行两种类型的操作以使数据库保持一致：从日志文件中针对数据文件重播已提交的事务，并回滚未提交的事务。在准备增量备份时，必须跳过未提交事务的回滚，因为备份时未提交的事务可能正在进行中，并且很可能它们将在下一次增量备份中提交。您应该使用该 选项来阻止回滚阶段.
简单来说 就是将相同的跳过 将增量备份文件中不同的数据添加进去

xtrabackup --apply-log-only

警告
如果不使用该 选项来阻止回滚阶段，那么增量备份将毫无用处。回滚事务后，无法应用进一步的增量备份。

1.准备基础备份的数据

xtrabackup --prepare --apply-log-only --target-dir=/backups/base

2.把第一次增量备份的数据合并到基础备份的数据中

xtrabackup --prepare --apply-log-only --user=root --password=123 --target=/backups/base --incremental-dir=/backups/inc1

3.再把第二次增量备份的数据也合并到基础备份的数据中

xtrabackup --prepare --user=root --password=123  --target-dir=/backups/base  --incremental-dir=/backups/inc2

注意： 最后一次操作不需要加--apply-log-only参数

4.停止MySQL服务，并删除数据目录和日志

systemctl stop mysqld
rm -rf /var/lib/mysql/*

5.开始恢复合并后的全部数据的数据库

xtrabackup --copy-back --datadir=/var/lib/mysql --target-dit=/backups/base/

6.更改数据库目录的权限并启动数据库

chown mysql.mysql -R /var/lib/mysql
systemctl start mysqld