MySQL数据备份和恢复(实战篇)

Percona

简介

Percona XtraBackup是世界上唯一开源、免费的MySQL热备份软件，可为InnoDB和XtraDB数据库执行非阻塞备份。Percona XtraBackup特点如下所示：

1、在不暂停数据库的情况下创建热InnoDB备份。
2、对MySQL进行增量备份。
3、将压缩的MySQL备份流式传输到另一台服务器。
4、在线在MySQL服务器之间移动表。
5、轻松创建新的MySQL复制副本。
6、不增加服务器负载的情况下备份MySQL。
7、Percona XtraBackup根据每秒的IO操作数进行节流。
8、Percona XtraBackup跳过二级索引页面并在准备紧凑备份时重新创建它们。
9、Percona XtraBackup甚至可以从完整备份中导出单个表，无论InnoDB版本如何。
10、FLUSH TABLES WITH READ LOCK备份锁是Percona Server中可用的轻量级替代品。XtraBackup使用它们自动复制非InnoDB数据，以避免阻塞修改InnoDB表的DML查询。

前置工具（如果直接安装失败了）

libev在安装Percona XtraBackup之前，请确保已安装该软件包 。

wget ftp://ftp.pbone.net/mirror/apt.sw.be/redhat/el6/en/x86_64/rpmforge/RPMS/libev-4.15-1.el6.rf.x86_64.rpm

rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm

yum install cmake gcc gcc-c++ libaio libaio-devel automake autoconf bzr \
bison libtool ncurses5-devel

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL

安装

# 启用存储库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only tools release
# 安装Percona XtraBackup
yum install percona-xtrabackup-80
yum install qpress
yum install zstd

创建备份用户

CREATE USER 'bkpuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Lolaage@backup2022' PASSWORD EXPIRE NEVER;
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
GRANT SELECT ON performance_schema.keyring_component_status TO bkpuser@'localhost';
GRANT SELECT ON performance_schema.replication_group_members TO bkpuser@'localhost';
FLUSH PRIVILEGES;

数据备份

全量备份

MySQL8.0

xtrabackup -H172.21.108.243 -P3310 -uroot -proot --backup --target-dir=/data/mysql_backup/`date "+%Y%m%d"` --no-server-version-check

MySQL5.0

sudo yum remove  percona-xtrabackup-80
sudo yum install -y percona-xtrabackup-24

xtrabackup --backup -H172.21.108.243 -P3310 --uroot --password=root --target-dir=/data/mysql_backup/`date "+%Y%m%d"`

全量恢复

xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/mysql_backup/20221214

mysqldump

备份

mysqldump -h172.21.108.243 -uroot -proot -P3310  halodb > halobak.sql

恢复

CREATE DATABASE halodb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

mysql -h172.21.108.243 -uroot -proot -P3310  halodb < halobak.sql

备份脚本

#!/bin/bash
#备份目录
BACKUP_DIR=/root/data
#备份文件名格式为"数据库名-日期-时间"
FILE_NAME=halobak-$(date +%F_%H-%M-%S).sql
mysqldump -h172.21.108.243 -uroot -proot -P3310  halodb > $BACKUP_DIR/$FILE_NAME

定时执行

#每天凌晨3点备份数据库
0 3 * * * /bin/bash /root/data/backup.sh >/dev/null 2>&1