MySQL 备份

作者: 运维开发_西瓜甜 | 来源:发表于2019-01-15 17:04 被阅读1456次

部署

YUM 部署

下载 YUM 仓库文件

打开网址： https://dev.mysql.com/downloads/repo/yum/

![image.png](https://img.haomeiwen.com/i11414906/a9e1685374a93d61.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
或者右键后点击 复制链接地址, 之后用 wget 命令下载

# wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

安装 YUM Repo 文件

# yum localinstall mysql80-community-release-el7-1.noarch.rpm

选择具体的版本

5.5 5.6 5.7 8.0

// 查看 YUM 仓库关于 MySQL 的所有仓库列表
# yum repolist all | grep mysql

// 只查看启用的
# yum repolist enabled | grep mysql

// 安装 YUM 管理工具包，此包提供了 yum-config-manager 命令工具
# yum install yum-utils

// 禁用 8.0
# yum-config-manager --disable mysql80-community

// 启用 5.7
# yum-config-manager --enable mysql57-community

当然也可以手动修改

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1   # 这里改为 1, 当然其他的版本就需要改为 0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

再次确认启用的 MySQL 仓库

yum repolist enabled | grep mysql

image.png

开始安装 MySQL

# yum install -y  mysql-community-server

管理 MySQL 服务

// 启动
# systemctl start mysqld.service

// 查看状态
# systemctl status mysqld.service

// 开机自启动
# systemctl enable mysqld.server

// 查看监听端口，默认 3306
#  ss -natl |grep 3306

初始化 Mysql

MySQL服务器初始化（从MySQL 5.7开始）：

在 MySQL 服务器初始启动时，如果服务器的数据目录为空，则会发生以下情况：

MySQL 服务器已初始化。
在数据目录中生成SSL证书和密钥文件。

该validate_password插件安装并启用。
将创建一个超级用户帐户'root'@'localhost'。并会设置超级用户的密码，将其存储在错误日志文件中。要显示它，请使用以下命令：

grep 'temporary password' /var/log/mysqld.log

image.png

通过上面日志中的临时密码登录并为超级用户帐户设置自定义密码：
```
shell> mysqladmin -p'ZBEjTcaj6H!L' password  'QFedu123!'
```

注意:
MySQL的 validate_password 插件默认安装。这将要求密码包含至少一个大写字母，一个小写字母，一个数字和一个特殊字符，并且密码总长度至少为8个字符。

取消密码复杂度

编辑 my.cnf配置文件, 在 [mysqld]配置块儿中添加如下内容

plugin-load=validate_password.so 
validate-password=OFF

保存退出后，重启服务, 修改密码。

不使用密码

修改 my.cnf 文件，添加如下内容，之后重启服务

skip-grant-tables=true

MySQL 安全控制

DCL（Data Control Language 数据库控制语言）

用于数据库授权、角色控制等操作

GRANT 授权，为用户赋予访问权限

REVOKE 取消授权，撤回授权权限

用户管理

创建用户
    create user '用户名'@'IP地址' identified by '密码';
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址' to '新用户名'@'IP地址' ;
修改密码
    // 第一种方法：
    set password for '用户名'@'IP地址'=Password('新密码')

    // 第二种方法：
    alter user '用户名'@'IP地址' identified by '新密码';

   // 第三种方法(忘记密码时，必须使用此方法修改密码)：
    UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';
  

  
PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

权限管理

show grants for '用户'@'IP地址';              -- 查看权限
grant  权限 on 数据库.表  to  '用户'@'IP地址' identifed by '密码';   -- 授权并设置密码
revoke 权限 on 数据库.表 from '用户'@'IP地址'    -- 取消权限

假如是 MySQL8.x

CREATE USER '你的用户名'@'localhost' IDENTIFIED BY '你的密码';
#创建新的用户
GRANT ALL PRIVILEGES ON 你的数据库名.* TO '你的用户名'@'localhost';
#把刚刚创建的数据库的管理权限给予刚刚创建的MySQL用户
FLUSH PRIVILEGES;
#刷新权限，使用设置生效

关于权限

all privileges  除grant外的所有权限
select          仅查权限
select,insert   查和插入权限
...
usage                   无访问权限
alter                   使用alter table
alter routine           使用alter procedure和drop procedure
create                  使用create table
create routine          使用create procedure
create temporary tables 使用create temporary tables
create user             使用create user、drop user、rename user和revoke  all privileges
create view             使用create view
delete                  使用delete
drop                    使用drop table
execute                 使用call和存储过程
file                    使用select into outfile 和 load data infile
grant option            使用grant 和 revoke
index                   使用index
insert                  使用insert
lock tables             使用lock table
process                 使用show full processlist
show databases          使用show databases
show view               使用show view
update                  使用update
reload                  使用flush
shutdown                使用mysqladmin shutdown(关闭MySQL)
super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
replication client      服务器位置的访问
replication slave       由复制从属使用

关于数据库和表

对于目标数据库以及内部其他：
数据库名.*           数据库中的所有
数据库名.表          指定数据库中的某张表
数据库名.存储过程     指定数据库中的存储过程
*.*                所有数据库

关于用户和 IP

用户名@IP地址         用户只能在改IP下才能访问
用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
用户名@%.shark.com
用户名@%             用户可以再任意IP下访问(默认IP地址为%)

Example

create user 'shark'@'';
grant all privileges on *.*  to 'shark'@'%' identified by '123';

立刻生效

/*将数据读取到内存中，从而立即生效。*/
flush privileges

也可以在创建用户的同时直接授权

grant select on *.*     /*设置查询数据的权限在所有的库和表*/
 to 'shark_2'@"%"       /*指定用户名和来源 ip*/
 identified by '123';   /*设置密码*/

备份概述

为何要备份

由于机器故障导致数据丢失
- 主从复制
- 集群
- 把原始数据备份到异地（其他机房或者其他城市）
由于人为的误操作导致的数据丢失
- 把原始数据备份到其他媒介上，脱离当前的系统，避免人为的误操作

备份的目的

保持数据一致性
保障数据可用性

备份中种类

物理备份由目录的原始副本和存储数据库内容的文件组成。此类备份适用于需要在出现问题时快速恢复的大型重要数据库。
- 除数据库外，备份还可以包括任何相关文件，如日志或配置文件。
- 备份仅可移植到具有相同或类似硬件特征的其他计算机。
- 可以在MySQL服务器未运行时执行备份。如果服务器正在运行，则必须执行适当的锁定，以便服务器在备份期间不会更改数据库内容。
- 物理备份工具包括用于表的MySQL Enterprise Backup 的 mysqlbackupInnoDB或任何其他表，或文件系统级命令（如cp， scp，tar， rsync）
- MySQL Enterprise Backup还原InnoDB 以及它备份的其他表。
- 可以使用文件系统命令将在文件系统级别复制的文件复制回其原始位置。
- - MySQL Enterprise Backup还原InnoDB 以及它备份的其他表。
  - ndb_restore恢复 NDB表。
  - 可以使用文件系统命令将在文件系统级别复制的文件复制回其原始位置。
- - MySQL Enterprise Backup还原InnoDB 以及它备份的其他表。
  - ndb_restore恢复 NDB表。
  - 可以使用文件系统命令将在文件系统级别复制的文件复制回其原始位置。
- - MySQL Enterprise Backup还原InnoDB 以及它备份的其他表。
  - ndb_restore恢复 NDB表。
  - 可以使用文件系统命令将在文件系统级别复制的文件复制回其原始位置。
- - MySQL Enterprise Backup还原InnoDB 以及它备份的其他表。
  - ndb_restore恢复 NDB表。
  - 可以使用文件系统命令将在文件系统级别复制的文件复制回其原始位置。
逻辑备份保存表示为逻辑数据库结构（CREATE DATABASE， CREATE TABLE语句）和内容（INSERT语句或分隔文本文件）的信息。此类备份适用于较少量的数据，您可以在其中编辑数据值或表结构，或在不同的计算机体系结构上重新创建数据。

逻辑备份方法具有以下特征：

通过查询MySQL服务器来获取数据库结构和内容信息来完成备份。
备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出写在客户端，则服务器还必须将其发送到备份程序。
输出大于物理备份，特别是以文本格式保存时。
备份和还原粒度可在服务器级别（所有数据库），数据库级别（特定数据库中的所有表）或表级别中使用。无论存储引擎如何，都是如此。
备份不包括日志或配置文件，或其他不属于数据库的数据库相关文件。
以逻辑格式存储的备份与机器无关且具有高度可移植性。
在运行MySQL服务器的情况下执行逻辑备份。服务器未脱机。
逻辑备份工具包括mysqldump 程序和SELECT ... INTO OUTFILE语句。这些适用于任何存储引擎，甚至MEMORY。
要恢复逻辑备份，可以使用mysql客户端处理SQL格式转储文件。要加载分隔文本文件，请使用 LOAD DATA INFILE语句或 mysqlimport客户端

在线与离线备份

在MySQL服务器运行时进行联机备份，以便可以从服务器获取数据库信息。服务器停止时会发生脱机备份。这种区别也可以描述为“ 热 ”与 “ 冷 ”备份; 一个“ 温暖 ”的备份是一个在服务器保持运行，但锁定，以防止当你从外部访问数据库文件修改数据。

MySQL 逻辑备份 mysqldump

逻辑备份特点

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
效率相对较低

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

特点

自动记录position位置。

show master  status\G;

可用性，一致性

锁表机制

用法

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

/*查看帮助*/
mysqldump --help

日常用法

备份所有库

// 先配置用户名和密码
shell> vi ~/.mysql_user
[mysqldump]
user=root
password=123

shell> mysqldump --defaults-file=~/.mysql_user -h172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql
# 不包含 INFORMATION_SCHEMA,performance_schema,sys

备份指定的多个库

// 为了考虑篇幅，请自行添加指定用户名密码参数和指定服务器的参数
// --defaults-file=~/.mysql_user -hip
shell> mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql

备份指定库的指定几个表

shell> mysqldump db1 t1 t3 t7 > dump.sql

备份时不锁表

备份时希望转储和刷新日志到恰好在同一时刻发生，适用于 InnoDB 引擎

shell> mysqldump  --all-databases --single-transaction --flush-logs > `date +%FT%H_%M_%S`dump_all.sql

--flush-logs 在开始备份数据之前刷新MySQL服务器日志文件。
此选项需要 RELOAD权限。如果将此选项与选项结合使用 --all-databases，则会为每个转储的数据库刷新日志。会锁表。

--single-transaction 是针对 InnoDB 引擎的表，不锁表，也称热备。

其他参数

--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的优势：

可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。
不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。
不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复。

恢复

shell> mysql < dump.sql

或者，在mysql中，使用 source命令：

mysql> source dump.sql

如果文件是不包含CREATE DATABASE和 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

Example

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 。

原来的版本

（1）xtrabackup 只能备份InnoDB和XtraDB两种数据引擎的数据表，而不能备份MyISAM数据表

（2）innobackupex 则封装了 xtrabackup，是一个脚本封装，所以能同时备份处理innodb和myisam，但在处理myisam时需要加一个读锁。

新版本的变化

如果你安装了2.3之前版本的xtrabackup，那么在备份的过程中，你可能会用到两个常用的备份工具。

安装2.3版本之前的XtraBackup后，我们会得到两个主要的备份工具：

xtrabackup
innobackupex

xtrabackup是一个C程序。

innobackupex是一个perl脚本，它对xtrabackup这个C程序进行了封装，在备份innodb表时，此脚本会调用xtrabackup这个C程序。

如果使用xtrabackup这个C程序进行备份，则只能备份innodb和xtradb的表，不能备份myisam表。

如果使用innobackupex进行备份，则可以备份innodb或xtradb的表，同时也能够备份myisam表。

所以，一般在使用XtraBackup备份工具进行数据备份时，通常会选择使用innobackupex命令进行备份。

那么问题来了。

xtrabackup是一个C程序，innobackupex是一个perl脚本，当它们作为两个进程运行时，总是没有特别完美的方式让它们进行通讯，当它们作为一个整体进行工作时就不太尽如人意，如此情况，就导致了一些bug的出现，于是，官方决定使用C重写innobackupex，将它与xtrabackup这个C程序完美的整合在一起。这个想法在2.3版本的XtraBackup中实现。

官方手册解释

xtrabackup
一个已编译的C二进制文件，它提供了使用MyISAM，InnoDB和XtraDB表备份整个MySQL数据库实例的功能

而我们安装的就是2.4版本，此时，innobackupex的功能已经完全整合到了xtrabackup中，innobackupex不再是perl脚本了，但是，为了兼容之前用户的使用习惯，官方保留了innobackupex，它作为一个软连接，指向了xtrabackup，也就是说，在2.4版本中，不管我们使用innobackupex命令，还是xtrabackup命令，其实使用的都是这个xtrabackupC程序。虽然在实现上有所不同，但是在工作原理上，与之前的版本并没有什么不同。

下面我们用新的命令xtrabachup使用。

首先我们先来简单的了解一下xtrabackup 是怎么工作的。xtrabackup 基于innodb的crash-recovery（实例恢复）功能，先copy innodb的物理文件（这个时候数据的一致性是无法满足的），然后进行基于redo log进行恢复，达到数据的一致性。

使用 `YUM` 方式安装

地址
https://www.percona.com/downloads/XtraBackup/LATEST/

确保安装 EPEL 源

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

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

yum  install -y libev

安装Percona存储库

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

测试存储库的可用性

shell> yum list | grep percona

应该输出如下信息

...
percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-22.x86_64               2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64     2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test.x86_64             2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64
...

安装软件

shell> yum install percona-xtrabackup-24

验证安装

image.png

下载对应版本的软件包，在本地安装

点击下载页面，选择对应版本后进行下载

image.png

示例：

下载 2.4.4版本

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

安装

yum localinstall percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm

注意：像这样手动安装软件包时，您需要确保解决所有依赖项并自行安装缺少的软件包。

卸载

yum remove percona-xtrabackup

80 版本

注意：
这个版本只支持 MySQL8.0的数据进行备份，不支持 MySQL8.0版本之前的数据进行备份。

# 安装仓库文件
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

# 启用仓库
percona-release enable-only tools release

#安装软件
yum install percona-xtrabackup-80

报错解决

来自 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona 的无效 GPG 密钥：No key found in given key data

或者

源 "CentOS 7 - Percona" 的 GPG 密钥已安装，但是不适用于此软件包。请检查
源的公钥 URL 是否配置正确。


 失败的软件包是：Percona-Server-shared-56-5.6.43-rel84.3.el7.x86_64
 GPG  密钥配置为：file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona

修改仓库文件 percona-release.repo 不使用密钥认证

image.png

再次安装会看到如下报错信息


Transaction check error:
  file /etc/my.cnf from install of Percona-Server-shared-56-5.6.43-rel84.3.el7.x86_64 conflicts with file from package mysql-community-server-5.7.25-1.el7.x86_64

错误概要
-------------

需要安装如下软件

yum install -y   mysql-community-libs-compat

注意：
这个软件的源是 mysql57-community

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 文件内容如下：

点我传送门

再执行安装命令

yum install percona-xtrabackup-24

日常操作

条件：

在 MySQL 服务器本地安装 Xtrbackup 并执行相关操作。

给执行备份到用户进行相应的授权。

配置选项

配置选项可以在sh命令行中直接使用，也可以在 my.cnf 文件中配置

# my.cnf 文件的配置
[xtrabackup]
target_dir = /data/backups/mysql/   # 备份数据放置的位置

假如是编译安装的 mysql ，需要在配置文件my.cnf 中指定 socket 文件的路径。

[xtrabackup]
socket = /tmp/mysql.sock

1. 全备

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

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

开始备份

shell> xtrabackup --backup --user=root --password='123'  --target-dir=/backups/

# 备份完成后，可以看到备份时的LSN号，当下次进行增量备份时，xtrabackup就只备份大于此号的page即可。

查看备份文件

[root@mysql-master ~]# ls -lh /data/backups/
总用量 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生成的文件可能不同）。

backup-my.cnf
此文件中包含了my.cnf中的一些设置信息，但是，并不是my.cnf中的所有信息都会包含在此文件中，此文件中只包含了备份时需要的信息。
xtrabackup_binlog_info
需要开启二进制日志
此文件中记录了备份开始时二进制日志文件的"位置(position)"
xtrabackup_checkpoints
此文件中记录此次备份属于那种类型的备份，是全量还是增量，备份时起始的LSN号码，结束的LSN号码等信息。
xtrabackup_info
本次备份的概要信息，此文件中的信息还是比较全面的。
xtrabackup_logfile
记录了备份过程中的日志，在对数据进行prepare时需要通过日志将数据还原成一致的可用的数据。

准备恢复的数据

使用 xtrabackup --backup 选项进行备份后，并不能直接使用，首先需要准备它以便还原它。

如果您尝试使用这些数据文件启动InnoDB，它将检测损坏并自行崩溃，以防止您在损坏的数据上运行。

因为备份出的数据是不一致的，我们需要将同时备份出的事务日志应用到备份中，才能得到一份完整、一致、可用的数据，xtrabackup称这一步操作为prepare，直译过来就是"准备"。

xtrabackup --prepare 步骤使文件在一个时刻完全一致

shell> xtrabackup --prepare --target-dir=/data/backups/

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

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

==准备备份时不建议中断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选项对应的目录就是我们准备好的可用数据的目录。

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

停止数据库的服务
清理环境
修改权限
启动数据库

shell> systemctl stop mysqld.service

shell> rm -rf /var/lib/mysql/*

shell> xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/data/backups/

# 下面为完成后的输出结果
180818 10:59:25 [01]        ...done
180818 10:59:25 completed OK!
shell> chown  mysql.mysql -R /var/lib/mysql

或者使用 rsync 命令

shell> rsync -avrP /data/backup/ /var/lib/mysql/
shell> chown  mysql.mysql -R /var/lib/mysql

启动数据库

shell> systemctl start mysqld.service

innobackuper 命令实现

shell> innobackupex --defaults-file=/etc/my.cnf --host=192.168.1.146 --user=root --password=123123 /backup

shell> nnobackupex --apply-log --use-memory=4G /backup/2018-08-17_15-53-11

shell> systemctl stop mysqld.service

shell> rm -rf /var/lib/mysql/*

shell> innobackupex --datadir=/var/lib/mysql --copy-back 2018-08-17_15-53-11

shell> chown  mysql.mysql -R /var/lib/mysql

shell> systemctl start mysqld.service

全量备份思路总结

执行备份命令
- 指定数据库的用户名和密码
- 指定备份目录，注意只可以自动创建最后一级的目录
准备备份的数据
- 就是指： --prepare 参数, 保证数据的统一且完整性
停服务，并且把 mysql 的数据目录下的所有文件和文件夹清除。
- /var/lib/mysql/ 此目录必须是空的
恢复数据
- 本质上就是拷贝备份的文件到指定的 mysql 数据目录下
修改 mysql 数据目录的属主和属组为 MySQL 服务器进程启动的用户，默认是 mysql
启动服务

2. 增量备份

image.png

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

无论xtrabackup和innobackupex工具支持增量备份，这意味着它们可以只复制自上次备份以来发生变化的数据。

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

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

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

shell> cat /data/backups/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 13593159
last_lsn = 13593168
compact = 0
recover_binlog_info = 0

增量备份实际上并不将数据文件与先前备份的数据文件进行比较。事实上，如果你知道它的LSN，你可以使用 xtrabackup --incremental-lsn来执行增量备份，而不需要先前的备份。增量备份只是读取页面并将其LSN与最后一个备份的LSN进行比较。但是，您仍需要完整备份来恢复增量更改;如果没有完整备份作为基础，增量备份将毫无用处。

创建增量备份

要进行增量备份，请像往常一样以完整备份开始, 使用下面的命令创建基础的全量备份。

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

现在您已拥有完整备份，以后可以根据它进行增量备份。

向数据库中添加数据，以便于测试

mysql> select count(id) from shark_db.student;
+-----------+
| count(id) |
+-----------+
|     99213 |
+-----------+
1 row in set (0.04 sec)

mysql> insert into shark_db.student (name,age,phone) values('xiguatian',20,13149876789);
Query OK, 1 row affected (0.00 sec)

mysql> select count(id) from shark_db.student;
+-----------+
| count(id) |
+-----------+
|     99214 |
+-----------+
1 row in set (0.03 sec)

使用以下命令进行增量备份：

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

该/data/backups/inc1/目录现在应包含增量文件

ls -lh /data/backups/inc1/
总用量 116K
-rw-r----- 1 root root  487 8月  18 11:40 backup-my.cnf
-rw-r----- 1 root root  293 8月  18 11:40 ib_buffer_pool
-rw-r----- 1 root root  64K 8月  18 11:40 ibdata1.delta
-rw-r----- 1 root root   44 8月  18 11:40 ibdata1.meta
drwxr-x--- 2 root root 4.0K 8月  18 11:40 mysql
drwxr-x--- 2 root root  144 8月  18 11:40 one_db
drwxr-x--- 2 root root 8.0K 8月  18 11:40 performance_schema
drwxr-x--- 2 root root   88 8月  18 11:40 shark_db
drwxr-x--- 2 root root 8.0K 8月  18 11:40 sys
-rw-r----- 1 root root  120 8月  18 11:40 xtrabackup_checkpoints
-rw-r----- 1 root root  498 8月  18 11:40 xtrabackup_info
-rw-r----- 1 root root 2.5K 8月  18 11:40 xtrabackup_logfile

这个时候去查看增量备份的xtrabackup_checkpoints,会发现同样也记录了LSN 等信息

shell> cat /data/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）之间存在差异，这意味着在备份过程中服务器上存在一些流量

模拟增加数据

mysql> insert into shark_db.student (name,age,phone) values('xiguatian3',21,13149876789);
Query OK, 1 row affected (0.01 sec)

mysql> select count(id) from shark_db.student;                                     +-----------+
| count(id) |
+-----------+
|     99215 |
+-----------+
1 row in set (0.03 sec)

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

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

准备增量备份的数据

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

xtrabackup --apply-log-only

警告

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

要准备数据，需要从一开始就准备，现在回想一下我们都有那些备份

/data/backups/base
/data/backups/inc1
/data/backups/inc2

image.png

准备基础备份的数据

shell> xtrabackup --prepare --apply-log-only \
--target-dir=/data/backups/base
...省略...
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 13596441
InnoDB: Number of pools: 1
180818 11:56:55 completed OK!

注意:
即使已跳过回滚阶段，此备份实际上也可以按原样恢复。如果你恢复它并启动MySQL，InnoDB将检测到没有执行回滚阶段，它将在后台执行，因为它通常用于启动时的崩溃恢复。它会通知您数据库未正常关闭。

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

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

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

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

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

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

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

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

shell> xtrabackup --copy-back  --datadir=/var/lib/mysql  --target-dir=/data/backups/base/

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

启用压缩备份

压缩备份使用 --compress

// 压缩备份
shell> xtrabackup --backup --compress  --target-dir=/data/compressed 

// 可以同时启用 4 个线程进行压缩
shell> xtrabackup --backup --compress  --compress-threads=4    --target-dir=/data/compressed/

准备恢复数据

首先要解压备份文件

安装依赖的软件

shell>  wget -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0"http://www.quicklz.com/qpress-11-linux-x64.tar
shell> tar xvf qpress-11-linux-x64.tar
shell> cp qpress /usr/bin

使用压缩

shell> xtrabackup  --decompress   --target-dir=/data/compressed/

// --parallel 参数可以和 --decompress 参数一起使用，可以实现同时解压多个文件

解压后就可以进行准备恢复的数据操作了

shell> xtrabackup  --prepare --target-dir=/data/compressed/

恢复数据并更改文件权限

shell> xtrabackup --copy-back --target-dir=/data/backups/
shell> chown -R mysql:mysql /var/lib/mysql

创建加密备份

要创建加密的备份需要指定至少两个选项：

--encrypt=ALGORITHM 目前支持的算法是： AES128，AES192和AES256
--encrypt-key=ENCRYPTION_KEY 使用适当长度的加密密钥。如果命令行无法控制访问机器，则不建议使用此选项，因此可以将密钥视为流程信息的一部分。
--encrypt-key-file=KEYFILE 可以从中读取适当长度的原始密钥的文件的名称。该文件必须是一个简单的二进制（或文本）文件，其中包含要使用的密钥。

产生一个加密密钥

[root@mysql-master /]# openssl rand -base64 24
2t3RHNrCZfBNuzdqxCTaI80PS6kkPNVP

// 或者创建一个密钥文件
shell> echo -n "2t3RHNrCZfBNuzdqxCTaI80PS6kkPNVP" > 
 /data/backups/keyfile

在某些情况下，文本文件可能包含CRLF，这将导致密钥大小增大，从而使其无效。建议的方法是使用以下命令创建文件：

echo -n  "GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" > /data/backups/keyfile

创建加密的数据备份

xtrabackup --backup \
--target-dir=/data/backups \
--encrypt = AES256 \
--encrypt-key="2t3RHNrCZfBNuzdqxCTaI80PS6kkPNVP"
或者
--encrypt-key-file=/data/backups/keyfile

解密加密的备份数据

xtrabackup --decrypt=AES256 \
--encrypt-key="2t3RHNrCZfBNuzdqxCTaI80PS6kkPNVP" \ 
--target-dir=/data/backups/
或者
--encrypt-key-file=/data/backups/keyfile

剩余的操作和之前的恢复数据的操作一样

echo -n "GCHFLrDFVx6UAsRb88uLVbAVWbK+Yzfs" > /data/backups/keyfile

部分备份

xtrabackup支持在启用innodb_file_per_table选项时进行部分备份。创建部分备份有三种方法：

将表的名称与正则表达式匹配
在文件中提供它们的列表
提供数据库列表。

使用--tables 选项

创建部分备份

第一种方法是--tables选项。选项的值是一个正则表达式，它与表单中的完全限定的表名（包括数据库名称）相匹配.

比如，仅备份数据库 shark_db中的所有表

shell> xtrabackup --backup \
--user=root \
--password=123 \
--datadir=/var/lib/mysql \
--target-dir=/data/backups/shark_db_all \
--tables="^shark_db[.].* | ^mysql[.].*|^sys[.].*|^per.*|information_schema[.].*"

仅备份 shark_db.student 表

shell> xtrabackup --backup \
--user=root \
--password=123 \
--datadir=/var/lib/mysql \
--target-dir=/data/backups/shark_db.student \
--tables="^shark_db[.]student|^mysql[.].*|sys[.].*|^per.*|informatio_schema[.].*"

// 参考默认表名
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+-----

使用 --tables-file 选项

该--tables-file选项指定一个文件，该文件可以包含多个表名，文件中每行一个表名。仅备份文件中指定的表。名称完全匹配，区分大小写，没有模式或正则表达式匹配。表格名称必须是databasename.tablename格式完全限定的。

shell> echo  "mydatabase.mytable" > /tmp/tables.txt
shell> xtrabackup --backup --tables-file=/tmp/tables.txt

使用--databases和--databases-file选项

该--databases选项接受以空格分隔的要备份的数据库和表的列表.

该--databases-file选项指定一个文件，该文件可以包含databasename[.tablename]表单中的多个数据库和表，文件中每行一个元素名称。仅备份命名的数据库和表。名称完全匹配，区分大小写，没有模式或正则表达式匹配。

这两个选项和之前提到的--tables 和 --tables-file 选项是否方式几乎一致。
所不同的是：
talbes 仅可以指定具体的表。
databases 即可以指定数据库名，也可以指定某个数据库中的表。

2.恢复部分备份

在部分备份上使用--prepare选项时，您将看到有关不存在的表的警告。这是因为这些表存在于InnoDB内的数据字典中，但相应的.ibd文件不存在。它们未被复制到备份目录中。

==这些表将从数据字典中删除，当您还原备份并启动InnoDB时，它们将不再存在，==并且不会导致任何错误或警告打印到日志文件中。

因为你之前只备份了部分备份的数据而已，自然恢复的时候不能恢复没有备份的数据。

shell> xtrabackup --prepare   --target-dir=/data/backups/shark_db_all

恢复备份文件

方法一：

shell> xtrabackup --copy-back \
--datadir=/var/lib/mysql \
--target-dir=/data/backups/shark_db_all

// 更改权限
shell> chown mysql.mysql /var/lib/mysql -R

方法二:

适用于没有对 mysql 服务器默认的数据库(msql/sys/performance_schema)备份的情况

a. 初始化数据库
shell> mysqld --initialize --user=mysql

b. 复制文件准备好的备份文件到 mysql 的数据库目录
shell> rsync -avrP /data/backups/shark_db_all/ /var/lib/mysql/

c. 修改相关文件权限
shell> chown mysql.mysql /var/lib/mysql -R

d. 启动数据库
shell> systemctl start mysqld

e. 找到数据库密码,并登录数据库
shell> grep password /var/log/mysqld.log
shell> mysql -uroot -p   # 回车之后，输入在上一步找到的密码

f. 修改数据库密码
mysql> alter user 'root'@'localhost' identified by '123';

在本地执行备份，并且备份到远程服务器

假如目前 MySQL 服务器是 DB1
远程用于备份到服务器是 BK1

操作步骤

1. 建立`DB1` 到 `BK1` 的信任关系

shell> ssh-copy-id  user@DK1

2. 在 `DB1` 中安装支持多线程压缩和压缩算法的软件

shell> yum install pigz   # 支持多线程压缩

shell>  wget -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0"http://www.quicklz.com/qpress-11-linux-x64.tar
shell> tar xvf qpress-11-linux-x64.tar
shell> cp qpress /usr/bin

3. 在 `DB1` 中执行备份命令

xtrabackup  --backup --user=mysqluser --password=password --stream=xbstream | ssh user@BK1 "cat - > /mysql_backup.tar.gz"

上面命令的意思是，把备份压缩后的文件以数据流的格式传输给 ssh ，并通过 ssh 传输给远程服务器 BK1，备份后的文件名为 mysql_backup.tar.gz

--user 和 --password 指定的是在 MySQL 服务上经过授权的用户和密码
--stream 指定的是采用流格式进行同步压缩

恢复数据

先解压
压缩的时候是使用下xb 格式的数据流压缩的，所以解压的时候，也需要使用 xb 工具解压文件到本地硬盘。

mkdir   -p   /data/xb
xbstream    -x    <    mysql_backup.tar.gz   -C    /data/xb

之后再安装之前的步骤恢复数据

Q1：用innobackupex备份MySQL5.5版本的数据，恢复的时候用backu5.6恢复，出现ibdata1找不到，这是什么原因呢？

A1：跨版本恢复以后，恢复出来的数据的元信息还是以前低版本的，在起动服务器之前需要先执行一下mysql_upgrade。这其实也是一个升级的问题，我们当然可以采用innobackupex来做升级操作了，同样的mysqlbackup也是可以的。因为有些时候我们的库太大了，不适合导入导出来升级的操作。

Q2：你讲的方法适合集群吗？如果不适合集群的话，用什么方法？

A2：当然，其实都是一样的。原理只是对数据库的数据进行复制，主从、PXC、MHA等多种环境都是有在用的。

Q3：生产环境，1T数据量用什么备份好？

A3：我的建议是你可以使用mysqlbackup，比较下来，这个速度算是很快的，不过当你的数据量达到一个T时，也应该去考虑如何分库分表，当做到这一点的时候，也是化整为零，可以把备份和正常的负载都分摊到多台服务器上。

MySQL 备份

部署

YUM 部署

下载 YUM 仓库文件

安装 YUM Repo 文件

选择具体的版本

开始安装 MySQL

管理 MySQL 服务

初始化 Mysql

不使用密码

MySQL 安全控制

用户管理

权限管理

备份概述

备份中种类

在线与离线备份

MySQL 逻辑备份 mysqldump

逻辑备份特点

特点

用法

日常用法

优势

恢复

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

使用 YUM 方式安装

下载对应版本的软件包，在本地安装

卸载

80 版本

报错解决

日常操作

1. 全备

2. 增量备份

启用压缩备份

创建加密备份

部分备份

在本地执行备份，并且备份到远程服务器

操作步骤

1. 建立DB1 到 BK1 的信任关系

2. 在 DB1 中安装支持多线程压缩和压缩算法的软件

3. 在 DB1 中执行备份命令

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL好难啊

使用 `YUM` 方式安装

1. 建立`DB1` 到 `BK1` 的信任关系

2. 在 `DB1` 中安装支持多线程压缩和压缩算法的软件

3. 在 `DB1` 中执行备份命令