88 lines
3.2 KiB
Markdown
88 lines
3.2 KiB
Markdown
---
|
|
title: "Xtrabackup 笔记"
|
|
date: 2019-10-30T00:54:15+08:00
|
|
lastmod: 2019-10-30T00:54:15+08:00
|
|
tags: ["xtrabackup", "备份", "mysql"]
|
|
categories: ["database"]
|
|
---
|
|
|
|
## 环境
|
|
- CentOS7
|
|
- MySQL 5.27.22
|
|
- XtraBackup 2.4
|
|
|
|
## 安装
|
|
- 创建 percona.repo 文件，内容如下
|
|
```ini
|
|
[percona-release-x86_64]
|
|
name = Percona Original release/x86_64 YUM repository
|
|
#baseurl = http://repo.percona.com/percona/yum/release/$releasever/RPMS/x86_64
|
|
baseurl = https://mirrors.cloud.tencent.com/percona/percona/yum/release/$releasever/RPMS/x86_64
|
|
enabled = 1
|
|
gpgcheck = 0
|
|
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
|
|
[percona-release-noarch]
|
|
name = Percona Original release/noarch YUM repository
|
|
#baseurl = http://repo.percona.com/percona/yum/release/$releasever/RPMS/noarch
|
|
baseurl = https://mirrors.cloud.tencent.com/percona/percona/yum/release/$releasever/RPMS/noarch
|
|
enabled = 1
|
|
gpgcheck = 0
|
|
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
|
|
[percona-release-sources]
|
|
name = Percona Original release/sources YUM repository
|
|
#baseurl = http://repo.percona.com/percona/yum/release/$releasever/SRPMS
|
|
baseurl = https://mirrors.cloud.tencent.com/percona/percona/yum/release/$releasever/SRPMS
|
|
enabled = 0
|
|
gpgcheck = 0
|
|
gpgkey = file:///etc/pki/rpm-gpg/PERCONA-PACKAGING-KEY
|
|
```
|
|
- 安装
|
|
```bash
|
|
yum clean all
|
|
yum makecache fast
|
|
yum install percona-xtrabackup-24
|
|
```
|
|
|
|
## 创建数据库备份用户
|
|
```sql
|
|
create user percona@localhost identified by '123456';
|
|
grant reload, lock tables, process, replication client on *.* to percona@localhost;
|
|
flush privileges;
|
|
```
|
|
|
|
## 全量备份
|
|
- 按天创建全量备份目录
|
|
```bash
|
|
xtrabackup --backup --rsync --parallel=4 --user=percona --password=123456 \
|
|
--target-dir=/backup/$(date +mysql.%Y%m%d)
|
|
# --databases='db1 db2.t3', 只导出 db1 数据库和 db2 中的 t3 表
|
|
# --databases-exclude='db4 db5.t6', 不导出 db4 数据库和 db5 中的 t6 表，优先级高于 --database
|
|
# --databases-file=/path/to/filename, 指定包含要备份的数据库的文件名，每行一个 db 或者 db.tb
|
|
# --tables='db1.t2 db3.t4', 只导出 db1 中的 t2 表和 db3 中的 t4 表
|
|
# --tables-exclude='db5.t6 db7.t8', 不导出 db5 数据库的 t6 表和 db7 的 t8 表
|
|
# --tables-file=/path/to/filename, 指定包含要备份的数据表的文件名，每行一个 db.tb
|
|
# --safe-slave-backup, 备份前刷新 slave 临时表，安全退出 slave sql 线程
|
|
# --slave-info 记录 slave 读取的 master binlog 的位置
|
|
# NFS 挂载时，需使用 sync 模式
|
|
# 保留最近 3 天的全量备份
|
|
ls -d mysql.* | head -n -3 | xargs rm -rf
|
|
```
|
|
|
|
## 恢复数据
|
|
- 准备一个 xtrabackup 备份目录
|
|
```bash
|
|
xtrabackup --prepare --use-memory=2G --target-dir=/backup/20190711
|
|
# --use-memory=2G 允许使用 2G 内存用于 prepare 数据，默认 100M
|
|
```
|
|
- 停止 mysql 服务，移除数据目录
|
|
```bash
|
|
systemctl stop mysqld
|
|
cd /var/lib && [ -d mysql ] && mv mysql mysql_origin
|
|
```
|
|
- 恢复 mysql 数据
|
|
```bash
|
|
xtrabackup --copy-back --rsync --parallel=4 --target-dir=/backup/20190711
|
|
chown -R mysql.mysql /var/lib/mysql
|
|
```
|
|
|