2020-08-03 c9cf5138dc15277d5b0e3811bfafbd37 99+ 1 小时 8.3 k 0次访问

mysql备份

mysql备份讲解

1.1 备份的作用

论数据的重要性

1.2 如何实现备份？都有哪些方法可以实现数据的备份？

bin-log

使用二进制日志的方式进行备份，主要实现过程通过实现mysql的主从备份即可

备注：建议将bin-log实时备份至远程服务器中，以防机器故障而导致日志丢失

mysqldump

mydumper

Xtrabackup

1.mysqldump

一、mysqldump 简介

mysqldump 是 MySQL 自带的逻辑备份工具。

它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。

常用参数说明：

--version, -V       输出mysqldump版本信息并退出
--help				显示帮助信息并退出

--password, -p		连接数据库密码

--user, -u		连接数据库的用户名

--port, -P		连接数据库端口号

--protocol		使用的连接协议，例如：tcp udp socket

mysqldump  -uroot -p  -B m2 --protocol=tcp

二、备份命令

2.1 命令格式

1	mysqldump [选项] 数据库名 [表名] > 脚本名

或

1	mysqldump [选项] --数据库名 [选项表名] > 脚本名

或

1	mysqldump [选项] --all-databases [选项] > 脚本名

命令格式：

1	mysqldump -uroot -p -A >/mysqlback/mysql_all.sql

作用：

将数据库所有内容备份至/root下，并设置文件名为mysql_all.sql

2.2 选项说明

参数名	缩写	含义
–host	-h	服务器IP地址
–port	-P	服务器端口号
–user	-u	MySQL 用户名
–pasword	-p	MySQL 密码
–databases	-B	指定要备份的数据库
–all-databases	-A	备份mysql服务器上的所有数据库
–compact		压缩模式，产生更少的输出
–comments	-i	添加注释信息
–complete-insert	-c	输出完成的插入语句
–lock-tables	-l	备份前，锁定所有数据库表
–no-create-db/–no-create-info		禁止生成创建数据库语句
–force	-f	当出现错误时仍然继续备份操作
–default-character-set		指定默认字符集
–add-locks		备份数据库表时锁定数据库表(默认开启)
–where	-w	导出时，在后方添加条件语句，只导出符合条件的数据
–xml	-x	导出时，将数据格式转换为XML
–flush-logs	-F	开始导出之前刷新bin-log日志。

备注：如果一次导出多个库，将会多次刷新bin-log，只有在同时使用–lock-all-tables或者–master-data才不会出现类似情况

2.3 实例

备份所有数据库：

1	mysqldump -uroot -p --all-databases > /backup/mysqldump/all.db

备份指定数据库：

1	mysqldump -uroot -p -B test > /backup/mysqldump/test.db

备份指定数据库指定表(多个表以空格间隔)

1	mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db

备份指定数据库排除某些表

1	mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db

导出复合条件的库

1	mysqldump -uroot -p -B db_name --where="user='root'"

三、还原命令

3.1 系统行命令

mysqladmin -uroot -p create db_name 
mysql -uroot -p  db_name < /backup/mysqldump/db_name.db

注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入。

3.2 soure 方法

1 2	mysql > use db_name mysql > source /backup/mysqldump/db_name.db

增量备份

以上备份均为全备方式，如果要实现增量备份，最简单的方法是通过mysqldump配合bin-log来实现

实现原理：通过mysqldump命令中配合使用–flush-logs -F参数，每次全备后都刷新一次bin-log

在全备后新的操作都将被记录到新的bin-log中，故而实现增量备份

过程如下：

先开启 log-bin=mysql-bin

server-id=1 然后重启，

第一次全备：

mkdir /myback

mysql > create database m2;		创建测试库

mysql > create table m2.a (id int(2),name char(10),sex char(1));

mysql > insert into m2.a values (1,'zhangsan','f'),(2,'lisi','m');	插入两条数据

mysql > select * from m2.a;

第一次全备然后有新的binlog日志了

1	mysqldump -uroot -p -B m2 -F >/myback/all.sql

然后再次插入数据

1
2
3

mysql >insert into m2.a  values (3,'wangwu','f');

mysql > select * from m2.a;

将最新的log-bin备份

1	cp /var/lib/mysql-bin.000002 /myback

删除测试库

1	drop database m2;

恢复全备

1	mysql -p </myback/all.sql

查看

1	mysql > select * from m2.a;

恢复增备

1	mysqlbinlog /myback/mysql-bin.000002 \|mysql -p

再次查看

1	myslq -e -p select * from m2.a;

也可以直接通过备份bin-log来实现全量和增量备份，通过下列命令可以从指定位置恢复：

1、通过bin-log起始位置恢复

1	mysqlbinlog --start-position=4 --stop-position=798 mysql_bin.000001\|mysql -uroot -p

2、通过bin-log中记录时间恢复

1	mysqlbinlog --start-datetime='2018-09-20 00:00:00' --stop-datetime='2018-09-31 12:00:00' mysql_bin.000001\|mysql -uroot -p

2.mydumper备份

Mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具。

官网

1 2	https://launchpad.net/mydumper https://github.com/maxbube/mydumper

Mydumper主要特性：

轻量级C语言写的
多线程备份，备份后会生成多个备份文件
事务性和非事务性表一致的快照(适用于0.2.2以上版本)
快速的文件压缩
支持导出binlog
多线程恢复(适用于0.2.1以上版本)
以守护进程的工作方式，定时快照和连续二进制日志(适用于0.5.0以上版本)
开源 (GNU GPLv3)

1、mydumper安装

安装依赖

1 2	yum install glib2-devel mysql-devel zlib-devel glibc zlib pcre make pcre-devel openssl-devel gcc gcc-c++ cmake -y

下载源码包

0.97版本
git clone https://github.com/maxbube/mydumper.git  #这个最新的编译会报错

wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
# 这个需要先解压。编译过程都一样。

开始编译安装

cd mydumper/
mkdir bin 
cd bin/

开始编译
pwd
/root/mydumper/bin

cmake ../

make && make install 

ls ./
#安装完成后生成两个二进制文件mydumper和myloader位于/usr/local/bin目录下

在当前目录看到这两个文件就表示搞定了，创建软连接或者设置环境变量

2.1、mydumper常用参数说明

-B, --database              要备份的数据库，不指定则备份所有库
-T, --tables-list           需要备份的表，名字用逗号隔开
-o, --outputdir             备份文件输出的目录
-s, --statement-size        生成的insert语句的字节数，默认1000000
-r, --rows                  将表按行分块时，指定的块行数，指定这个选项会关闭 --chunk-filesize
-F, --chunk-filesize        将表按大小分块时，指定的块大小，单位是 MB
-c, --compress              压缩输出文件
-e, --build-empty-files     如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）
-x, --regex                 是同正则表达式匹配 'db.table'
-i, --ignore-engines        忽略的存储引擎，用都厚分割
-m, --no-schemas            不备份表结构
-k, --no-locks              不使用临时共享只读锁，使用这个选项会造成数据不一致
--less-locking              减少对InnoDB表的锁施加时间（这种模式的机制下文详解）
-l, --long-query-guard      设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）
--kill-long-queries         杀掉长查询 (不退出)
-b, --binlogs               导出binlog
-D, --daemon                启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份
-I, --snapshot-interval     dump快照间隔时间，默认60s，需要在daemon模式下
-L, --logfile               使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
--tz-utc                    跨时区是使用的选项，不解释了
--skip-tz-utc               同上
--use-savepoints            使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限
--success-on-1146           Not increment error count and Warning instead of Critical in case of table doesn't exist
-h, --host                  连接的主机名
-u, --user                  备份所使用的用户
-p, --password              密码
-P, --port                  端口
-S, --socket                使用socket通信时的socket文件
-t, --threads               开启的备份线程数，默认是4
-C, --compress-protocol     压缩与mysql通信的数据
-V, --version               显示版本号
-v, --verbose               输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2

注意：该命令**-u** 选项和后面的用户名必须有一个以上空格，否则将会报错

2.2 myloader 参数解释

-d, --directory                   备份文件的文件夹
-q, --queries-per-transaction     每次事物执行的查询数量，默认是1000
-o, --overwrite-tables            如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构
-B, --database                    需要还原的数据库
-e, --enable-binlog               启用还原数据的二进制日志
-h, --host                        主机
-u, --user                        还原的用户
-p, --password                    密码
-P, --port                        端口
-S, --socket                      socket文件
-t, --threads                     还原所使用的线程数，默认是4
-C, --compress-protocol           压缩协议
-V, --version                     显示版本
-v, --verbose                     输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2

备份 yichen 库 到 /data/backup/mysql 文件夹中

 mydumper -u root -p '123.Shui!!'  -P 3306 -h 127.0.0.1 -B yichen  -o /data/backup/mysql/
 
 tree /data/backup/mysql/
/data/backup/mysql/
├── fan-schema-create.sql
├── fan.user-schema.sql
├── fan.user.sql
└── metadata

从上面可以可以看出

备份所生成的文件
目录中包含一个metadata文件

- 记录了备份数据库在备份时间点的二进制日志文件名，日志的写入位置，
- 如果是在从库进行备份，还会记录备份时同步至主库的二进制日志文件及写入位置
  每个表有两个备份文件：
- database.table-schema.sql 表结构文件
- database.table.sql 表数据文件

恢复 beta 库

# 删除 beta 库
mysql> drop database yichen;

# myloader 恢复
myloader -u root -p '123.Shui!!' -h 127.0.0.1 -B yichen -d /data/backup/mysql/

# 验证
mysql -p  -e  'show databases;'

已经恢复了。

备份原理

1、主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，保证数据的一致性
2、读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用
3、N个（线程数可以指定，默认是4）dump线程把事务隔离级别改为可重复读并开启读一致的事物
4、dump non-InnoDB tables, 首先导出非事物引擎的表
5、主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁
6、dump InnoDB tables, 基于事物导出InnoDB表
7、事物结束

3.xtrabackup 备份恢复

　MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabackup有2个工具，分别是xtrabakup、innobakupe。8.0只有xtrabkup了。

　　Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

xtrabackup和innobackupex两者间的区别：

xtrabackup可以对innodb表进行热备份（不对表加锁，备份期间数据可读写），但不能对myisam表进行备份；

innobackupex可以同时对上面两者进行备份，但针对myisam表依旧不能进行热备份（必须加锁后方能备份）

重要

版本8.0.6中添加了对MyRocks存储引擎的支持。

Percona XtraBackup 8.0不支持TokuDB存储引擎。

二、Xtrabackup优点

（1）备份速度快，物理备份可靠

（2）备份过程不会打断正在执行的事务（无需锁表）

（3）能够基于压缩等功能节约磁盘空间和流量

（4）自动备份校验

（5）还原速度快

（6）可以流传将备份传输到另外一台机器上

（7）在不增加服务器负载的情况备份数据

官方文档

1	https://www.percona.com/doc/percona-xtrabackup/8.0/index.html

下载地址(mysql8.0之前的最好下载2.4的版本。)

2.4的安装

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

    tar zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

   cd percona-xtrabackup-2.4.4-Linux-x86_64/ && cp bin/* /usr/bin/

   yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5 

   cd /root/ && wget  https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

   rpm -vih percona-toolkit-2.2.19-1.noarch.rpm

8.0的安装

1
2
3

https://www.percona.com/downloads/Percona-XtraBackup-LATEST/

wget https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.13/binary/tarball/percona-xtrabackup-8.0.13-Linux-x86_64.el8.libgcrypt183.tar.gz

安装依赖

1 2	yum install libaio libaio-devel -y yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5

可以通过yum源来安装

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

启用存储库： percona-release enable-only tools release

如果打算将Percona XtraBackup与上游MySQL Server结合使用，则只需启用tools 存储库：。percona-release enable-only tools
通过运行以下命令安装Percona XtraBackup： yum install percona-xtrabackup-80

警告

在CentOS 6上libev安装Percona XtraBackup之前，请确保已安装软件包。对于此操作系统，libev可从EPEL存储库中获得该软件包。

为了能够进行压缩备份，请安装qpress软件包：

1	yum install qpress

1.Xtrabackup 常用参数

-H, --host=name     该选项表示备份数据库的地址默认是本地
--user：				该选项表示备份账号
-p, --password：		该选项表示备份的密码
-P, --port 			指定端口
--databases         指定数据库
--incremental       创建增量备份

--defaults-file		指定mysql主配置文件路径，备份参数会从配置文件中自动读取（datadir basedir等）
    
--defaults-extra-file	使用该参数时，不只是读取主配置文件，还会读取这里指定的配置文件
     
--target-dir	指定备份存放路径，如不指定，则备份到当前目录下的xtrabackup_backupfiles目录
     
--backup		备份到--target-dir指定路径（必选），如不选则不备份，只打印过程
     
--prepare		实施对备份文件进行恢复前的准备(生成InnoDB log file)
     
--print-param	打印备份或恢复时需要的参数
     
--suspend-at-end	在备份目录中生成一个xtrabackup_suspended文件，将xtrabackup进程挂起，不停地将数据文件的变化同步到备份文件，直到用户手工删除xtrabackup_suspended文件
     
--tables		备份指定的表--tables=table_name
     
--datadir		MySQL数据目录路径，一般不用指定
     
--incremental-basedir	该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
    
--incremental-lsn	增量备份时，指定to_lsn位置

xtarbackup备份命令格式：

备份：

Xtrabackup全备

xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/mysql/ -uroot --port=3306 -S /usr/local/mysql/data/mysql.sock -p'123.Shui!!'	

将数据库全备至/data/backup/mysql/目录
可以不指定-S  默认会去my.cnf文件找。

xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/mysql/ -u root -P 3306 -p'123.Shui!!'

备份好会生成相关文件

全备完成

各文件说明：

(1)xtrabackup_checkpoints 备份类型（如完全或增量）、备份状态（如是否已经为 prepared 状态）和 LSN(日志序列号)范围信息；每个 InnoDB 页(通常为 16k 大小)都会包含一个日志序列号，即 LSN。LSN 是整个数据库

系统的系统版本号，每个页面相关的 LSN 能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info mysql 服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3) xtrabackup_binlog_pos_innodb 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前 position。

(4) xtrabackup_binary 备份中用到的 xtrabackup 的可执行文件；

(5)backup-my.cnf 备份命令用到的配置选项信息；在使用 innobackupex 进行备份时，还可以使用–no-timestamp 选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex 命令将会创建一个 BACKUP-DIR 目录来存储备份数据注意：相关选项说明：其中， –user 指定连接数据库的用户名， –password 指定连接数据库的密码， –defaults-file 指定数据库的配置文件，innobackupex 要从其中获取 datadir 等信息；–database 指定要备份的数据库，这里指定的数据库只对 MyISAM 表有效，对于 InnoDB 数据来说都是全备（所有数据库中的 InnoDB 数据都进行了备份，不是只备份指定的数据库，恢复时也一样）；/opt/mysqlbackup/full 是备份文件的存放位置。

注意：备份数据库的用户需要具有相应权限，如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

create user 'bkpuser'@'localhost' identified mysql_native_password by '123.shui!!@#';

grant reload,lock tables,replication client, process on *.* to 'bkpuser'@'localhost';
flush privileges;

全备恢复

模拟数据丢失环境

停止

1
2
3

systemctl stop mysqld

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

如果尝试使用这些数据文件启动InnoDB，它将检测到损坏并停止工作，以避免在损坏的数据上运行。该--prepare步骤可以使文件在单个时刻完美地保持一致，因此您可以在文件上运行 InnoDB。

该准备步骤使用这种“嵌入式InnoDB的”对复制的数据文件执行崩溃恢复，使用复制的日志文件。该prepare步骤的使用非常简单：您只需使用选项运行xtrabackup--prepare并告诉它要准备的目录，例如，准备先前进行的备份运行：

恢复前准备（初始化备份目录）

1	xtrabackup --prepare --target-dir=/data/backup/mysql/

恢复备份

警告

必须先准备好备份，然后才能还原它。

为方便起见，xtrabackup二进制文件具有将--copy-back 备份复制到服务器的数据目录的选项：

1	xtrabackup --datadir=/usr/local/mysql/data --copy-back --target-dir =/data/backup/mysql/

如果您不想保存备份，则可以使用 --move-back将备份数据移动到datadir的选项。

如果不想使用以上任何选项，则可以另外使用 rsync或cp来还原文件。

注意

恢复备份之前，datadir必须为空。同样重要的是要注意，在执行还原之前需要关闭MySQL服务器。您不能还原到正在运行的mysqld实例的数据目录（导入部分备份时除外）。

可用于还原备份的rsync命令示例如下所示：

 rsync -avrP /data/backup/mysql/ /usr/local/mysql/data/
或者

\cp -a /data/backup/mysql/* /usr/local/mysql/data/

在cp指令前面加反斜杠可以不弹出是否覆盖的询问而直接覆盖！

您应该检查恢复的文件是否具有正确的所有权和权限。

由于将保留文件的属性，因此在大多数情况下，mysql在启动数据库服务器之前，您需要将文件的所有权更改为，因为它们将由创建备份的用户拥有：

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

现在，数据已还原，您可以启动服务器。

1	systemctl start mysqld

增量备份

要进行增量备份，请像往常一样从完整备份开始。该 xtrabackup二进制写入一个名为xtrabackup_checkpoints到备份的目标目录。该文件包含一行，显示 to_lsn，这是备份结束时数据库的LSN。使用以下命令创建完整备份：

1 2	xtrabackup --backup --target-dir=/data/backup/mysql/ -u root -P 3306 -p'123.Shui!!'

查看xtrabackup_checkpoints文件

cat /data/backup/mysql/xtrabackup_checkpoints 

backup_type = full-backuped  类型是全备
from_lsn = 0				本次备份开始点
to_lsn = 18566199			本次备份结束点
last_lsn = 18566209			下次备份开始点
flushed_lsn = 0

第一次增量备份

增量备份时两个参数的说明：
--target-dir			指定增备目录（需要备份到哪儿）

--incremental-basedir		指定上一次备份目录（第一次增量备份时，该参数=全备目录；
第二次增量备份时，该参数=第一次增量备份目录）

现在您已拥有完整备份，您可以基于它进行增量备份。使用以下命令：(先模拟新插入数据)

1
2
3

xtrabackup --backup --target-dir=/data/backup/mysqlinc/inc1  --incremental-basedir=/data/backup/mysql/  -uroot -p'123.Shui!!'

新指定的目录不存在，会自动创建。

查看新的xtrabackup_checkpoints文件

cat /data/backup/mysqlinc/inc1/xtrabackup_checkpoints 

backup_type = incremental   备份类型是增量备份
from_lsn = 18566199			上次备份的结束位置
to_lsn = 18567314			本次备份结束的位置
last_lsn = 18569708			最后的位置
flushed_lsn = 0

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

第二次增量备份

现在可以使用此目录作为另一个增量备份的基础：

1 2	xtrabackup --backup --target-dir=/data/backup/mysqlinc/inc2 \ --incremental-basedir=/data/backup/mysqlinc/inc1 -uroot -p'123.Shui!!'

此文件夹还包含xtrabackup_checkpoints：查看信息

 cat /data/backup/mysqlinc/inc2/xtrabackup_checkpoints 

backup_type = incremental
from_lsn = 18567314      来自上次备份的结束位置。
to_lsn = 18570792
last_lsn = 18573267
flushed_lsn = 0

全量恢复（全备+增备）

模拟数据丢失环境

1 2	systemctl stop mysqld rm /usr/local/mysql/data/* -rf

恢复前准备（初始化备份目录）

增量备份的步骤与完全备份的步骤不同。在完全备份中，执行两种类型的操作以使数据库保持一致：已提交的事务相对于数据文件从日志文件中重放，未提交的事务被回滚。准备增量备份时，必须跳过未提交事务的回滚，因为在备份时未提交的事务可能正在进行中，并且很有可能将在下一次增量备份中提交。您应该使用该 --apply-log-only选项来防止回滚阶段。

警告

如果不使用该 选项阻止回滚阶段，则增量备份将无用。事务回滚后，不能再应用增量备份。--apply-log-only

从创建的完整备份开始，您可以准备它，然后将增量差异应用于它。回想一下，您有以下备份：

1
2
3

/data/backup/mysql
/data/backup/mysqlinc/inc1
/data/backup/mysqlinc/inc2

要准备基本备份，您需要--prepare照常运行，但要防止回滚阶段：

1	xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql

输出应以类似于以下内容的文本结尾：

1
2
3

Shutdown completed; log sequence number 18566229
Number of pools: 1
200721 14:10:03 completed OK!

日志序列号应与to_lsn您先前看到的基本备份的序列号匹配。

要将第一个增量备份应用于完整备份，请运行以下命令：

1	xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql --incremental-dir=/data/backup/mysqlinc/inc1

Percona XtraBackup不支持使用相同的增量备份目录来准备备份的两个副本。不要--prepare使用相同的增量备份目录（–incremental-dir的值）运行超过一次。

准备第二个增量备份是一个类似的过程：将增量应用到（已修改的）基础备份，您将及时将其数据前滚到第二个增量备份的点：

1 2	xtrabackup --prepare --target-dir=/data/backup/mysql \ --incremental-dir=/data/backup/mysqlinc/inc2

注意

--apply-log-only合并除最后一个以外的所有增量时应使用。这就是为什么上一行不包含该 --apply-log-only选项的原因。即使--apply-log-only在最后一步中使用了，备份仍将保持一致，但在这种情况下，服务器将执行回滚阶段。

一旦准备好增量备份就与完整备份相同，可以用相同的方式还原它们。

拷贝全备目录下所有内容到数据目录中（此时的全备目录已经拥有全量数据）

用于还原备份的rsync命令示例如下所示：

 rsync -avrP /data/backup/mysql/ /usr/local/mysql/data/
或者

\cp -a /data/backup/mysql/* /usr/local/mysql/data/

在cp指令前面加反斜杠可以不弹出是否覆盖的询问而直接覆盖！

授权

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

现在，数据已还原，可以启动服务器查看。

1	systemctl start mysqld

增量恢复完成

公司备份实战

创建备份目录
mkdir /mysqlbackup/{wanquan,zengliang} -p

xtrabackup --backup --target-dir=/mysqlbackup/wanquan/$(date "+%Y-%m-%d") -u root -P 3306 -H 192.168.224.11 -p'123.Shui!!'

现在可以使用此目录作为另一个增量备份的基础：

1	xtrabackup --backup --target-dir=/mysqlbackup/zengliang/$(date "+%Y-%m-%d") --incremental-basedir=/mysqlbackup/wanquan/`ls /mysqlbackup/wanquan/` -uroot -H 192.168.224.11 -p'123.Shui!!'

第二次增量备份

1	xtrabackup --backup --target-dir=/mysqlbackup/zengliang/$(date "+%Y-%m-%d") --incremental-basedir=/mysqlbackup/zengliang/$(date "+%Y-%m-%d") -uroot -H 192.168.224.11 -p'123.Shui!!'

备份脚本

vim mysqlbackup.sh

#!/bin/bash
yum -y install wget lrzsz >>/dev/null
#今天
Date_today=$(date "+%w")
#昨天
Yester_day=$(date "+%w" -d '1 day ago')
#七天前
Sevendays_ago=$(date "+%w" -d '7 day ago')
#今天星期几
week=`date +%w`
mysqluser="root"
mysqlpass="123.Shui!!"
mysqlcon=/etc/my.cnf
host="192.168.224.11"

first=`date "+%Y-%m-%d"`

if [ $first = 2020-07-21 ];then
	yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm 2&>/dev/null
  
	percona-release enable-only tools release
	yum install -y  percona-xtrabackup-80 qpress 1> /dev/null

	
	mkdir -p /mysqlbackup/{wanquan,zengliang}

fi


#判断今天是不是周日 如果是周日进行全量备份

if [ $week -eq 0 ];then

    rm -rf /mysqlbackup/wanquan/$Sevendays_ago >>/dev/null 


    xtrabackup --backup --default-file=${mysqlcon} --target-dir=/mysqlbackup/wanquan/$Date_today  --user=${mysqluser}  --host=${host} --password=${mysqlpass} 1>/dev/null
    if [ $? -eq 0 ];then

         rm -rf /mysqlbackup/zengliang/*

    fi

else

    dir=`ls /mysqlbackup/zengliang/ | wc -l | awk '{print $1}'`
#判断是否有目录，没有就进行第一次增量备份。
        if [ $dir -eq 0 ];then
     xtrabackup --backup --default-file=${mysqlcon} --target-dir=/mysqlbackup/zengliang/$Date_today --incremental-basedir=/mysqlbackup/wanquan/`ls /mysqlbackup/wanquan/`  --user=${mysqluser}  --host=${host} --password=${mysqlpass} 1>/dev/null


	else
     
     xtrabackup --backup --default-file=${mysqlcon} --target-dir=/mysqlbackup/zengliang/$Date_today --incremental-basedir=/mysqlbackup/zengliang/${Yester_day}  --user=${mysqluser}  --host=${host} --password=${mysqlpass} 1>/dev/null


	fi

fi

先修改/etc/my.cnf 文件，打开log-bin=mysql-bin

server-id=1

开始访问定时任务每天早上8点进行备份

crontab -e

 00 8 * * *   /usr/bin/sh  /root/mysqlbackup.sh

#每天8点钟执行这个脚本做备份

模拟周二数据删除后恢复

1
2
3

systemctl stop mysqld

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

恢复前准备（初始化备份目录）

初始化全备

1	xtrabackup --prepare --apply-log-only --target-dir=/mysqlbackup/wanquan/0/

初始化周一的增量备份

1	xtrabackup --prepare --apply-log-only --target-dir=/mysqlbackup/wanquan/0/ --incremental-dir=/mysqlbackup/zengliang/1

初始化周二的增量备份

1	xtrabackup --prepare --target-dir=/mysqlbackup/wanquan/0/ --incremental-dir=/mysqlbackup/zengliang/2

拷贝全备目录下所有内容到数据目录中（此时的全备目录已经拥有全量数据）

1	cp -a /mysqlbackup/wanquan/0/* /usr/local/mysql/data/

授权

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

启动

1	systemctl start mysqld

此时数据已经恢复成功。

2.innobackupex 常用参数(2.4版本)

mysql不能高于5.7版本。

--apply-log 	恢复时，通过xtrabackup_logfile在备份目录中创建一个备份（恢复备份时必选）

--copy-back		从备份目录中以拷贝的形式恢复到数据目录中
  
--move-back		从备份目录中以移动的形式恢复到数据目录中
 
--defaults-file	指定mysql主配置文件路径
   
--remote-host	使用ssh将备份文件拷贝至远程服务器上
   
--stream		备份时启用打包
  
--compress		备份时启用压缩
  	
--tables		指定要备份的表

--databases		指定要备份的库
  
--uncompress	解压以压缩方式备份的备份文件
 
--incremental			创建一个增量备份，在增量备份时使用
 
--incremental-basedir	增量备份时，指定全量备份的路径，以确定全量备份的终止位置
 
--incremental-dir		将增量备份与全量备份合并后放入新的目录

--incremental-lsn		增量备份时，指定增量备份的起点
 
--no-lock				备份时，关闭自动锁表功能
 
--safe-slave-backup		备份时，先停止slave进程

--no-timestamp			备份时，不自动创建以日期和时间的目录

使用案例：

备份最好不要用root用户，需要新建用户

1
2
3

create user 'backup_mysql'@'%' identified with mysql_native_password by '123.Shui!!@#'; 

grant select,process,reload,super on *.* to 'backup_mysql'@'%' ;

开始备份

1	mkdir /mysqlbackup/{wanquan,zengliang} -p

1	innobackupex --defaults-file=/etc/my.cnf --user=backup_mysql --password=123.Shui!!@# /mysqlbackup/wanquan/$(date "+%Y-%m-%d") --no-timestamp

$(date “+%Y-%m-%d”) #表示已今天日期命名这样备份会多创建一个目录，所以需要加一个参数 –no-timestamp 告诉innobackupex不要创建一个时间戳目录来存储备份

停止数据库

测试，删除一个表或库，模拟误操作或者直接删除数据库数据目录

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

恢复前准备（初始化数据）

1	innobackupex --defaults-file=/etc/my.cnf --apply-log /mysqlbackup/wanquan/$(ls /mysqlbackup/wanquan/)

恢复数据到数据目录中；–datadir 指定数据目录位置，如果配置文件中有指定，则不需要在此指定

1	innobackupex --defaults-file=/etc/my.cnf --copy-back --datadir=/usr/local/mysql/data /mysqlbackup/wanquan/$(ls /mysqlbackup/wanquan/)

给目录授权

1	chown -R mysql.mysql /usr/local/mysql/data/

启动数据库

1	systemctl start mysqld

全备及全备恢复完成！

innobackupex 增量备份

已经初始化的全备目录的数据不能再做为增量备份的基础数据，下面需要增量备份，所以这里需要重新备份，

全备

1	innobackupex --defaults-file=/etc/my.cnf --user=backup_mysql --password=123.Shui!!@# /mysqlbackup/wanquan/$(date "+%Y-%m-%d") --no-timestamp

新增数据

1	mysql -p -e 'create database b; use b;create table b1 (id int);show tables;'

第一次增量备份

1	innobackupex --incremental /mysqlbackup/zengliang/$(date "+%Y-%m-%d") --incremental-basedir=/mysqlbackup/wanquan/`ls /mysqlbackup/wanquan/` --user=backup_mysql --password=123.Shui!!@# --no-timestamp

–incremental-basedir 指定全量备份目录，因为增量备份需要知道前一次全量是到哪里结束的

–incremental 指定增量备份的目录，会在指定目录下创建一个以当前日期和时间命名的

再次新增数据

1 2	创建C 库 mysql -p -e 'create database c; use c;create table c1 (id int);show tables;'

第二次增量备份

目录日期不一样，还有–incremental=basedir= 指定的目录是第一次增量备份的目录

innobackupex --incremental /mysqlbackup/zengliang/$(date "+%Y-%m-%d-%H") --incremental-basedir=/mysqlbackup/zengliang/`ls /mysqlbackup/zengliang/` --user=backup_mysql --password=123.Shui!!@#  --no-timestamp

增量备份完成！

增量备份恢复

1 2	systemctl stop mysqld rm /usr/local/mysql/data/* -rf

初始化全备目录

1	innobackupex --apply-log --redo-only /mysqlbackup/wanquan/`ls /mysqlbackup/wanquan/`

查看有多少增量备份目录

ls /mysqlbackup/zengliang/

初始化第一次增备目录

1	innobackupex --apply-log --redo-only /mysqlbackup/wanquan/`ls /mysqlbackup/wanquan/` --incremental-dir=/mysqlbackup/zengliang/2019-10-16

把增量备份的数据整合到全备了

初始化第二次增备目录（注意：这一步不需要加 –redo-only 选项）最后一次不需要加

1	innobackupex --apply-log /mysqlbackup/wanquan/`ls /mysqlbackup/wanquan/` --incremental-dir=/mysqlbackup/zengliang/2019-10-17-23

开始完整恢复

1	innobackupex --copy-back /mysqlbackup/wanquan/`ls /mysqlbackup/wanquan/`

过程：先将两次增量备份所备份的数据整合到全备目录中，然后进行一次性恢复

重新授权

1	chown -R mysql.mysql /usr/local/mysql/data/

启动数据库

1	systemctl start mysqld

至此，innobackupex 增量备份与恢复完成！

本文标题：mysql备份
本文作者：yichen
本文链接：https://yc6.cool/2020/08/03/mysql备份/
版权声明：本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明出处！

mysql备份

mysql备份讲解

1.mysqldump

增量备份

2.mydumper备份

1、mydumper安装

2.1、mydumper常用参数说明

2.2 myloader 参数解释

备份原理

3.xtrabackup 备份恢复

1.Xtrabackup 常用参数

Xtrabackup全备

增量备份

2.innobackupex 常用参数(2.4版本)

喜欢这篇文章？打赏一下作者吧

评论

最新评论

最新文章

分类

归档

标签

订阅更新