备份与恢复学习记录

mysql备份工具

官方工具

mysqldump 单线程备份 目前仍然兼容
mysqlpump 多线程备份 5.7之后

mysql enterprise backup 5.7之后 企业版

社区

mydumper mysqldump的衍生，时间消耗约是后者二分之一，但慢于mysqlpump

系统工具

cp/tar

第三方

innobackup 收费
xtrabackup 免费，常用

---

备份策略

中小型数据库

拷贝文件

cp、tar

mysqldump

完全备份+增加备份，速度较慢，适合中小型数据库

备份相关

mkdir -p /data/mysqldump/`date +%Y-%m-%d`
mysqldump -uroot -p test > /data/mysqldump/`date +%Y-%m-%d`/test.db

打开文件可以发现，实际上这个备份文件实际上会删掉原库中的表然后重新建表插数据，必然效率低下且空间占用更大

-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.26

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test01`
--

DROP TABLE IF EXISTS `test01`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test01` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test01`
--

LOCK TABLES `test01` WRITE;
/*!40000 ALTER TABLE `test01` DISABLE KEYS */;
INSERT INTO `test01` VALUES (1,'xiaoming'),(2,'xiaoming01'),(3,'xiaoming02');
/*!40000 ALTER TABLE `test01` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

参数名	缩写	含义
–host	-h	服务器IP地址
–port	-P	服务器端口号
–user	-u	MySQL 用户名
–pasword	-p	MySQL 密码
–no-data	-d	只导出表结构
–databases		指定要备份的数据库，备份多个数据库使用，否则被视为数据库名接表明的形式
–all-databases	-A	备份mysql服务器上的所有数据库
–compact		压缩模式，产生更少的输出
–comments		添加注释信息
–complete-insert		输出完成的插入语句
–lock-tables		备份前，锁定所有数据库表
–no-create-db/–no-create-info		禁止生成创建数据库语句
–force		当出现错误时仍然继续备份操作
–default-character-set		指定默认字符集
–add-locks		备份数据库表时锁定数据库表

还原相关

-- 进入数据库
-- 如果库被删掉了要重建然后进入
create database test;
use test
source /data/mysqldump/2021-02-28/test.db

mysqldump

mysqlpump 的使用和 mysqldump 基本一致，因其多线程新增命令

参数名	含义
–-default-parallelism=N	每个并行处理队列的默认线程数
–parallel-schemas=[N:]db_list	用于并行备份多个数据库： N为使用的线程数，如果没有设置，则使用 --default-parallelism 参数的值 db_list 是一个或多个以逗号分隔的数据库名称列表；
–compress-output=ZLIB|LZ4	对备份文件进行压缩，支持 LZ4 和 ZLIB 两种格式 压缩后的文件可以占用更少的存储空间，但是却不能直接用于备份恢复，需要先进行解压

xtrabackup

几乎完全热备，配合二级制日志实现增量备份，适合比较繁忙数据库

xtrabackup目前有两个主要版本xtrabackup8.0和xtrabackup2.4

xtrabackup8.0只支持mysql8.0以上数据库

mysql5.7可以使用xtrabackup2.4

下载

官网下载

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

Percona-XtraBackup-8.0-5-r40ec8a3-el7-x86_64-bundle.tar
# 包内包含
percona-xtrabackup-80-8.0.5-1.el7.x86_64.rpm
percona-xtrabackup-80-debuginfo-8.0.5-1.el7.x86_64.rpm
percona-xtrabackup-test-80-8.0.5-1.el7.x86_64.rpm

安装依赖

# 缺依赖报错
	libev.so.4()(64bit) is needed by percona-xtrabackup-80-8.0.5-1.el7.x86_64
	perl(DBD::mysql) is needed by percona-xtrabackup-80-8.0.5-1.el7.x86_64
	perl(Digest::MD5) is needed by percona-xtrabackup-80-8.0.5-1.el7.x86_64
# 下载依赖
yum -y install libev perl-DBD-MySQL perl-Digest-MD5

安装

rpm -ivh percona-xtrabackup-80-8.0.5-1.el7.x86_64.rpm
rpm -qa | grep xtrabackup

备份

xtrabackup --defaults-file=/etc/my.cnf --datadir=/data/mysql/data -S /tmp/mysql.sock --user=root --password=1 --backup --target-dir=/data/backup/fullback-`date +%Y-%m-%d` --parallel=2

# 出现completed OK!则为成功

还原

rsync -avrp /data/backup/fullback2021-03-05/* /data/mysql/data
chown -R /data/mysql
# 授权很重要

坑

Table doesnt exist

ib系列文件为引擎生成的文件，如果被删除或丢失会出现表存在但不能查询显示损坏的情况，之前因为还原后没有进行授权，导致库无法启动，然后按照一些博客的教程删掉了几个ib家族文件，结果导致了更大的问题