@[TOC](第二十三章 MYSQL数据库 手册6-1 备份与恢复)
⽬的
备份与恢复，管理数据库。
前提
linux系统，已安装好数据库。
⽣成⼤数据，百万条记录
导入testlog.sql
7 ~]# rz -E
7 ~]# cat testlog.sql
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure pro_testlog()
begin
declare i int;
set i = 1;
while i < 100000
do insert into testlog(name,age) values (concat('wang',i),i);
set i = i +1;
end while;
end$$
delimiter ;
7 ~]# systemctl start mariadb
将表导⼊hellodb数据库
7 ~]# mysql < hellodb_innodb.sql
7 ~]# mysql hellodb < testlog.sql
执⾏
7 ~]# mysql hellodb
MariaDB [hellodb]> call pro_testlog;
Query OK, 1 row affected (50.07 sec)
⽣成
MariaDB [hellodb]> select count(*) from testlog;
+----------+
| count(*) |
+----------+
| 999999 |
+----------+
1 row in set (0.11 sec)
主键索引
MariaDB [hellodb]> show indexes from testlog\G
*************************** 1. row ***************************
Table: testlog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 1000544
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
推荐数据库配置
7 ~]# mkdir /data/bin
7 ~]# chown mysql.mysql /data/bin
7 ~]# vim /etc/my.cnf
[mysqld]
binlog_format=row #二进制日志记录的格式
log_bin=/data/bin/mysql-bin #建议二进制日志和数据库文件分开存放，提高可用性
innodb_file_per_table #存储引擎
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
7 ~]# systemctl restart mariadb
mariadb如有问题，查看错误⽇志：
7 ~]# tail /var/log/mariadb/mariadb.log
220531 1:38:25 InnoDB: Initializing buffer pool, size = 128.0M
220531 1:38:25 InnoDB: Completed initialization of buffer pool
220531 1:38:25 InnoDB: highest supported file format is Barracuda.
220531 1:38:25 InnoDB: Waiting for the background threads to start
220531 1:38:26 Percona XtraDB (http://www.percona.com) 5.5.59-MariaDB-38.11 started; log sequence number 12507743
220531 1:38:26 [Note] Plugin 'FEEDBACK' is disabled.
220531 1:38:26 [Note] Server socket created on IP: '0.0.0.0'.
220531 1:38:26 [Note] Event Scheduler: Loaded 0 events
220531 1:38:26 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.60-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
命令介绍 1、备份和恢复 1.1、为什么要备份？
灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
备份注意要点
能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据
还原要点
做还原测试，用于测试备份的可用性
还原演练
1.2、备份类型：
完全备份，部分备份
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
冷、温、热备份
冷备：读写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
1.3、备份时需要考虑的因素
备份时需要考虑的因素
温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长
备份什么
数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件
1.4、备份⼯具 1.4.1、cp, tar
cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
【例1】冷备份，停服务，tar、cp命令，复制数据库从主机192.168.37.7到主机192.168.37.8。
主机192.168.37.7
7 ~]# systemctl stop mariadb
7 ~]# ll /var/lib/mysql/
total 36892
-rw-rw---- 1 mysql mysql 16384 May 31 01:50 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 May 31 01:50 aria_log_control
drwx------ 2 mysql mysql 165 May 31 01:31 hellodb
-rw-rw---- 1 mysql mysql 27262976 May 31 01:50 ibdata1
-rw-rw---- 1 mysql mysql 5242880 May 31 01:50 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 May 31 01:31 ib_logfile1
drwx------ 2 mysql mysql 4096 May 31 01:30 mysql
drwx------ 2 mysql mysql 4096 May 31 01:30 performance_schema
drwx------ 2 mysql mysql 6 May 31 01:30 test
7 ~]# tar Jcvf /data/mysql.bak.xz /var/lib/mysql/ #备份数据库
7 ~]# ll /data/mysql.bak.xz -h
-rw-r--r-- 1 root root 4.4M Jun 2 00:07 /data/mysql.bak.xz
7 ~]# scp -p /data/mysql.bak.xz 192.168.37.8:/data/
root@192.168.37.8's password:
mysql.bak.xz 100% 4411KB 37.1MB/s 00:00
7 ~]# scp -p /etc/my.cnf 192.168.37.8:/data/
root@192.168.37.8's password:
my.cnf 100% 638 444.2KB/s 00:00
7 ~]# systemctl start mariadb
主机192.168.37.8
注意：还原数据库时、要版本一致、附则可能会发生不兼容等问题
7 ~]# yum install -y mariadb-server
7 ~]# ls /data #查看到刚刚传的两个文件
my.cnf mysql.bak.xz
7 ~]# cp /data/my.cnf /etc/my.cnf -b #'-b'选项先把旧的my.cnf备份、在把新的导入
cp：overwrite"/etc/my.cnf"？ y
7 ~]# ll /etc/my.cnf #点击两次TAB可以看到、之前旧的'my.cnf'备份变成'my.cnf~'
my.cnf my.cnf~ my.cnf.d/
7 ~]# tar xvf /data/mysql.bak.xz -C /data/ #把刚刚的备份文件、解压到'/datal'目录
var/lib/mysql/
var/lib/mysql/mysql/
var/lib/mysql/mysql/db.frm
var/lib/mysql/mysql/db.MYI
var/lib/mysql/mysql/db.MYD
...
7 ~]# rm -rf /var/lib/mysql/* #删除原数据库中内容、准备备份
7 ~]# ll /var/lib/mysql/
total 0
7 ~]# mv /data/var/lib/mysql/* /var/lib/mysql/ #将刚刚解压出来的数据库内容、移动到数据库、备份
7 ~]# ll /var/lib/mysql/ #查看一下属主属组权限
total 69660
-rw-rw---- 1 mysql mysql 16384 Jun 2 00:02 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Jun 2 00:02 aria_log_control
drwx------ 2 mysql mysql 165 Jun 1 23:40 hellodb
-rw-rw---- 1 mysql mysql 60817408 Jun 2 00:02 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jun 2 00:02 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 1 23:42 ib_logfile1
drwx------ 2 mysql mysql 4096 Jun 1 23:40 mysql
drwx------ 2 mysql mysql 4096 Jun 1 23:40 performance_schema
drwx------ 2 mysql mysql 6 Jun 1 23:40 test
主机192.168.37.8
7 ~]# mkdir /data/bin #创建mysql二进制日志文件存储目录
7 ~]# chown mysql.mysql /data/bin/ #设置相应的权限
主机192.168.37.7
7 ~]# systemctl stop mariadb
7 ~]# scp -p /data/bin/* 192.168.37.8:/data/bin/ #备份二进制日志文件
root@192.168.37.8's password:
mysql-bin.000001 100% 264 175.0KB/s 00:00
mysql-bin.index 100% 54 50.7KB/s 00:00
主机192.168.37.8
7 ~]# ll /data/bin/
total 12
-rw-rw---- 1 root root 264 Jun 1 00:09 mysql-bin.000001
-rw-rw---- 1 root root 245 Jun 2 00:43 mysql-bin.000002
-rw-rw---- 1 root root 54 Jun 1 00:12 mysql-bin.index
7 ~]# chown -R mysql.mysql /data/bin/ #更改所属主、属组
7 ~]# ll /data/bin/ #从刚刚的'root'变成'mysql'
total 12
-rw-rw---- 1 mysql mysql 264 Jun 1 00:09 mysql-bin.000001
-rw-rw---- 1 mysql mysql 245 Jun 2 00:43 mysql-bin.000002
-rw-rw---- 1 mysql mysql 54 Jun 1 00:12 mysql-bin.index
7 ~]# mysql hellodb
MariaDB [hellodb]> select * from students;
...
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> show master logs; #查看二进制日志
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 245 |
+------------------+-----------+
2 rows in set (0.00 sec)
1.4.2、LVM的快照
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
基于LVM的备份
(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
(3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;
(5) 挂载快照卷，执行数据备份
(6) 备份完成后，删除快照卷
(7) 制定好策略，通过原卷备份二进制日志
1.4.3、mysqldump
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合
binlog的增量备份
mysqldump工具：客户端命令，通过mysql协议连接至mysql服务器进行备份
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]
mysqldump常见选项：
-A,--all-databases 备份所有数据库，含create database
-B,--databases db_name… 指定备份的数据库，包括create database语句
-E,--events：备份相关的所有event scheduler
-R,--routines：备份所有存储过程和自定义函数
--triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 指定字符集
--master-data[=#]： 此选项须启用二进制日志
1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
2：记录为注释的CHANGE MASTER TO语句此选项会自动关闭--lock-tables功能，自动打开-x | --
lock-all-tables功能（除非开启--single-transaction）
-F,--flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A
或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--singletransaction或-x，
--master-data 一起使用实现，此时只刷新一次日志
--compact 去掉注释，适合调试，生产不使用
-d,--no-data 只备份表结构
-t,--no-create-info 只备份数据,不备份create table
-n,--no-create-db 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql或相关时需要使用
-f,--force 忽略SQL错误，继续执行
--hex-blob 使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY，BLOB，BIT的数据类型的列时
使用，避免乱码
-q,--quick 不缓存查询，直接输出，加快备份速度
【例2】mysqldump备份数据库,⽆法复制原数据库的格式不推荐使⽤。
备份
7 ~]# vim /etc/my.cnf
[mysqld]
#log_bin=/data/bin/mysql-bin #注释掉此项
7 ~]# mkdir /data/backup
7 ~]# mysqldump hellodb > /data/backup/hellodb.sql #备份
删除数据库
7 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 ~]# mysql -e 'drop database hellodb' #删除hellodb数据库
7 ~]# mysql -e 'show databases' #发现hellodb数据库已删除
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
创建数据库，导⼊备份。
7 ~]# mysql -e 'create database hello'
7 ~]# mysql hello < /data/backup/hellodb.sql
7 ~]# mysql hello
MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-----------------+
7 rows in set (0.00 sec)
【例3】mysqldump备份数据库中的表,⽆法复制原数据库的格式不推荐使⽤。
7 ~]# mysqldump hello students > /data/backup/students.sql
7 ~]# cat /data/backup/students.sql
-- MySQL dump 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: hello
-- ------------------------------------------------------
-- Server version 5.5.60-MariaDB
/*!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 `students`
--
DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint(3) unsigned DEFAULT NULL,
`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `students`
--
LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (1,'Shi Zhongyu',22,'M',2,3),(2,'Shi Potian',22,'M',1,7),(3,'Xie Yanke',53,'M',2,16),(4,'Ding Dian',32,'M',4,4),(5,'Yu Yutong',26,'M',3,1),(6,'Shi Qing',46,'M',5,NULL),(7,'Xi Ren',19,'F',3,NULL),(8,'Lin Daiyu',17,'F',7,NULL),(9,'Ren Yingying',20,'F',6,NULL),(10,'Yue Lingshan',19,'F',3,NULL),(11,'Yuan Chengzhi',23,'M',6,NULL),(12,'Wen Qingqing',19,'F',1,NULL),(13,'Tian Boguang',33,'M',2,NULL),(14,'Lu Wushuang',17,'F',3,NULL),(15,'Duan Yu',19,'M',4,NULL),(16,'Xu Zhu',21,'M',1,NULL),(17,'Lin Chong',25,'M',4,NULL),(18,'Hua Rong',23,'M',7,NULL),(19,'Xue Baochai',18,'F',6,NULL),(20,'Diao Chan',19,'F',7,NULL),(21,'Huang Yueying',22,'F',6,NULL),(22,'Xiao Qiao',20,'F',1,NULL),(23,'Ma Chao',23,'M',4,NULL),(24,'Xu Xian',27,'M',NULL,NULL),(25,'Sun Dasheng',100,'M',NULL,NULL);
/*!40000 ALTER TABLE `students` 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 */;
-- Dump completed on 2022-06-02 16:33:30
【例4】mysqldump备份数据库-B，⾃动创建数据库，保存原数据库结构。
7 ~]# mysqldump -B hello > /data/backup/hello.sql
7 ~]# cat /data/backup/hello.sql
-- MySQL dump 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: hello
-- ------------------------------------------------------
-- Server version 5.5.60-MariaDB
/*!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 */;
--
-- Current Database: `hello`
...
删除数据库hello
7 ~]# mysql -e 'drop database hello'
7 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
导⼊数据库，原格式
7 ~]# mysql < /data/backup/hello.sql
7 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| hello | <--hello数据库还原、原格式
| mysql |
| performance_schema |
| test |
+--------------------+
【例5】mysqldump备份数据库-A，备份mysql和⾃定义数据库，推荐使⽤。
7 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| hello |
| mysql |
| performance_schema |
| test |
+--------------------+
7 ~]# mysqldump -A > /data/backup/all.sql
7 ~]# grep '^CREATE DATABASE' /data/backup/all.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hello` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
【例6】mysqldump恢复数据库
7 ~]# rm -rf /var/lib/mysql/*
7 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/data/bin/mysql-bin
7 ~]# systemctl restart mariadb
7 ~]# ll /var/lib/mysql/
total 37852
-rw-rw---- 1 mysql mysql 16384 Jun 2 18:18 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Jun 2 18:18 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 Jun 2 18:18 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jun 2 18:18 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jun 2 18:18 ib_logfile1
drwx------ 2 mysql mysql 4096 Jun 2 18:18 mysql
srwxrwxrwx 1 mysql mysql 0 Jun 2 18:18 mysql.sock
drwx------ 2 mysql mysql 4096 Jun 2 18:18 performance_schema
drwx------ 2 mysql mysql 6 Jun 2 18:18 test
恢复时会产⽣⼤量⼆进制⽇志，建议恢复时禁⽤⼆进制⽇志
7 ~]# cat /etc/my.cnf [mysqld] #log_bin=/data/bin/mysql-bin #把此项注释掉即可
7 ~]# ll /data/bin/
total 940
-rw-rw---- 1 mysql mysql 26792 Jun 2 18:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql 81 Jun 2 18:21 mysql-bin.index
还原
7 ~]# mysql < /data/backup/all.sql
7 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| hello |
| mysql |
| performance_schema |
| test |
+--------------------+
7 ~]# ll /data/bin
total 252
-rw-rw---- 1 mysql mysql 26792 Jun 2 18:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql 81 Jun 2 18:21 mysql-bin.index
【例7】恢复数据库，临时禁⽤⼆进制⽇志。不能退出mysql。
7 ~]# vim /etc/my.cnf
[mysqld]
log_bin=/data/bin/mysql-bin
7 ~]# systemctl restart mariadb
7 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
还原⼆进制⽇志
MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)
禁⽤⼆进制⽇志
MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
导⼊备份
MariaDB [(none)]> source /data/backup/all.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
···
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hello |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
启⽤⼆进制⽇志
MariaDB [test]> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
【例8】mysqldump备份数据库,-R备份所有存储过程和⾃定义函数。
把testlog.sql导⼊数据库
7 ~]# mysql hello < /data/testlog.sql
执⾏存储过程
7 ~]# mysql hello
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [hello]> call pro_testlog;
Query OK, 1 row affected (20.16 sec)
-A，全部备份
7 ~]# mysqldump -A > /data/backup/all_A.sql
7 ~]# grep pro_testlog /data/backup/all_A.sql
INSERT INTO `proc` VALUES ('hello','pro_testlog','PROCEDURE','pro_testlog','SQL','CONTAINS_SQL','NO','DEFINER','','','begin \ndeclare i int;\nset i = 1; \nwhile i < 100000 \ndo insert into testlog(name,age) values (concat(\'wang\',i),i); \nset i = i +1; \nend while; \nend','root@localhost','2022-06-02 10:34:13','2022-06-02 10:34:13','','','utf8','utf8_general_ci','latin1_swedish_ci','begin \ndeclare i int;\nset i = 1; \nwhile i < 100000 \ndo insert into testlog(name,age) values (concat(\'wang\',i),i); \nset i = i +1; \nend while; \nend');
-R，备份所有存储过程和⾃定义函数
7 ~]# mysqldump -R hello > /data/backup/all_R.sql
7 ~]# grep pro_testlog /data/backup/all_R.sql
/*!50003 DROP PROCEDURE IF EXISTS `pro_testlog` */;
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_testlog`()
【例10】恢复误删除的表
⼆进制⽇志随着操作，不断增长。
7 ~]# mysql -e 'show master logs'
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 245 |
+------------------+-----------+
⽣成完全备份
7 ~]# mysqldump -A > /data/backup/all_A_masterdata.sql
7 ~]# mysql hello
MariaDB [hello]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 245 | <--245
+------------------+-----------+
3 rows in set (0.00 sec)
MariaDB [hello]> insert teachers(name,age)values('a',20);
Query OK, 1 row affected (0.01 sec)
MariaDB [hello]> insert teachers(name,age)values('b',24);
Query OK, 1 row affected (0.00 sec)
MariaDB [hello]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 699 | <--添加两条记录以后、从'245'变成'699'
+------------------+-----------+
3 rows in set (0.00 sec)
删除，表teachers
MariaDB [hello]> drop table teachers;
Query OK, 0 rows affected (0.01 sec)
继续操作，改其他的表。
MariaDB [hello]> insert students (name,age)value('jerry',22);
Query OK, 1 row affected (0.00 sec)
MariaDB [hello]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
...
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | jerry | 22 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
恢复，表teachers。完全备份和⼆进制⽇志。暂时离线，防⽕墙
7 ~]# systemctl stop mariadb
7 ~]# rm -rf /var/lib/mysql/*
7 ~]# systemctl start mariadb
⽣成增量备份
ins_date +%F.sql
#我们是从'245'这个点开始进行增量备份的
7 ~]# mysqlbinlog --start-position=245 /data/bin/mysql-bin.000001 > /data/backup/inc.sql
打开⽣成增量备份，找到删除操作的那⾏，注释掉
7 ~]# vim /data/backup/ins.sql
...
# at 707
#220604 14:27:52 server id 1 end_log_pos 820 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1654324072/*!*/;
#DROP TABLE `teachers` /* generated by server */ #找到删除操作的那⾏，用'#'注释掉
/*!*/;
# at 820
...
7 ~]# mysql
临时关闭⼆进制⽇志
MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
恢复完全备份，但是数据不是最新的，还要恢复增量备份。
MariaDB [(none)]> source /data/backup/all_A_masterdata.sql
MariaDB [(none)]> source /data/backup/inc.sql
MariaDB [(none)]> set sql_log_bin=on;
1.4.3.1、MyISAM备份选项：
支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
锁定方法如下：
-x,--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加--singletransaction或--lock-tables
选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-locktables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
MyISAM建议备份策略
mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --defaultcharacter-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
1.4.3.2、InnoDB备份选项：
支持热备，可用温备但不建议用
--single-transaction
此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目
前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正
确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME
TABLE，TRUNCATE TABLE此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥备份大型表
时，建议将--single-transaction选项和--quick结合一起使用
InnoDB建议备份策略
mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql
【例11】推荐的InnoDB备份选项
7 ~]# mysqldump -A --single-transaction --master-data=1 --hex-blob > /data/backup/fullbak_`date +%F`.sql
7 ~]# ll /data/backup/fullbak_2022-06-04.sql
-rw-r--r-- 1 root root 515085 Jun 4 15:10 /data/backup/fullbak_2022-06-04.sql
【例12】连备份并压缩利⽤压缩的备份恢复
7 ~]# mysqldump -A --single-transaction --master-data=1 | gzip > /data/backup/fullbak_`date +%F`.sql.gz
7 ~]# ll /data/backup/fullbak_2022-06-04.sql.gz
-rw-r--r-- 1 root root 139648 Jun 4 15:13 /data/backup/fullbak_2022-06-04.sql.gz
7 ~]# rm -rf /var/lib/mysql/*
7 ~]# systemctl restart mariadb
7 ~]# gzip -d /data/backup/fullbak_2022-06-04.sql.gz
7 ~]# ll /data/backup/fullbak_2022-06-04.sql
-rw-r--r-- 1 root root 515085 Jun 4 15:13 /data/backup/fullbak_2022-06-04.sql
导⼊备份
7 ~]# mysql < /data/backup/fullbak_2022-06-04.sql
数据回来了
7 ~]# mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
【例13】分库备份，通⽤脚本
7 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
7 ~]# mysql -e 'show databases'|grep -Evi 'information_schema|performance_schema|test|Database'
hellodb
mysql
7 ~]# for db in `mysql -e 'show databases'|grep -Evi '^(information_schema|performance_schema|test|Database)$'`;do mysqldump -B ${db} --single-transaction --master-data=2 |gzip > /data/backup/${db}_`date +%F`.sql.gz;done
#可以看到刚刚备份的两个数据库、'-t'按时间排序
7 ~]# ll -t /data/backup/
total 144
-rw-r--r-- 1 root root 1983 Jun 4 15:31 hellodb_2022-06-04.sql.gz
-rw-r--r-- 1 root root 139602 Jun 4 15:31 mysql_2022-06-04.sql.gz
脚本格式：
7 ~]# vim /data/back.sh
#!/bin/bash
for db in `mysql -e 'show databases'|grep -Evi '^(information_schema|performance_schema|test|Database)$'`;do
mysqldump -B ${db} --single-transaction --master-data=2 |grep > /data/backup/${db}_`date +%F`.sq
l.gz
done
删除数据库，
7 ~]# mysql -e 'drop database hello'
7 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
解压缩、恢复
7 ~]# gzip -d /data/backup/hellodb_2022-06-04.sql.gz /data/backup/
7 ~]# ll /data/backup/hellodb_2022-06-04.sql
-rw-r--r-- 1 root root 7946 Jun 4 15:31 /data/backup/hellodb_2022-06-04.sql
7 ~]# mysql < /data/backup/hellodb_2022-06-04.sql
7 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
【例14】假如周⽇做了完全备份，放到其他的主机上，周⼀上午10点硬盘损坏，怎么还原。
完全备份/data/backup/all.sql
7 ~]# mysqldump -A --single-transaction --master-data=2 |xz > /data/backup/all.sql.xz
10点硬盘损坏
7 ~]# rm -rf /var/lib/mysql/*
7 ~]# systemctl restart mariadb
解压缩备份
7 ~]# xz -d /data/backup/all.sql.xz /data/backup/
打开备份⽂件，查找备份点
7 ~]# vim /data/backup/all.sql
-- MySQL dump 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.60-MariaDB
···
-- Position to start replication or point-in-time recovery from
--
#备份点('mysql-bin.000007', MASTER_LOG_POS=1050458)
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1050458;
--
-- Current Database: `hello`
--
增量备份/data/backup/inc.sql
7 ~]# ll /data/bin/
total 4188
-rw-rw---- 1 mysql mysql 1060 Jun 5 13:07 mysql-bin.000001
-rw-rw---- 1 mysql mysql 30373 Jun 5 13:08 mysql-bin.000002
-rw-rw---- 1 mysql mysql 1038814 Jun 5 13:08 mysql-bin.000003
-rw-rw---- 1 mysql mysql 264 Jun 5 13:12 mysql-bin.000004
-rw-rw---- 1 mysql mysql 30373 Jun 5 13:12 mysql-bin.000005
-rw-rw---- 1 mysql mysql 1038814 Jun 5 13:12 mysql-bin.000006
-rw-rw---- 1 mysql mysql 1050477 Jun 5 15:19 mysql-bin.000007
-rw-rw---- 1 mysql mysql 30373 Jun 5 15:19 mysql-bin.000008
-rw-rw---- 1 mysql mysql 1038814 Jun 5 15:19 mysql-bin.000009
-rw-rw---- 1 mysql mysql 245 Jun 5 15:19 mysql-bin.000010
-rw-rw---- 1 mysql mysql 270 Jun 5 15:19 mysql-bin.index
#注意：从mysql-bin.000007的1050458开始，到（mysql-bin.000008、mysql-bin.000009）包括全部结束
7 ~]# mysqlbinlog --start-position=1050458 /data/bin/mysql-bin.000007 > /data/backup/inc.sql
7 ~]# mysqlbinlog /data/bin/mysql-bin.000008 >> /data/backup/inc.sql
7 ~]# mysqlbinlog /data/bin/mysql-bin.000009 >> /data/backup/inc.sql
恢复完全备份和增量备份
7 ~]# mysql
MariaDB [hello]> set sql_log_bin=off;
MariaDB [hello]> source /data/backup/all.sql #完全备份
MariaDB [hello]> source /data/backup/inc.sql #增量备份、mysql-bin.000007的1050458开始、到（mysql-bin.000008、mysql-bin.000009）包括全部结束
MariaDB [hello]> set sql_log_bin=on;
【例15】假如周⽇做了完全备份，放到其他的主机上，周⼀上午10点有⼈破坏了数据，怎么还原。还原需要时间，最好不 要出现。
做完全备份，-F刷新⼆进制⽇志
7 ~]# mysqldump -A -F --single-transaction --master-data=2 > /data/backup/all.sql
7 ~]# ll /data/backup/
-rw-r--r-- 1 root root 521680 Jun 4 17:44 all.sql #主要看时间
7 ~]# ll /data/bin/ #生成了最新时间二进制文件
-rw-rw---- 1 mysql mysql 288 Jun 4 17:44 mysql-bin.000016
-rw-rw---- 1 mysql mysql 245 Jun 4 17:44 mysql-bin.000017
-rw-rw---- 1 mysql mysql 459 Jun 4 17:44 mysql-bin.index
启动数据库，做操作
7 ~]# mysql hello
MariaDB [hello]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | a | 20 | NULL |
| 6 | b | 24 | NULL |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
#添加两条信息分别是'小明'，'小红'
MariaDB [hello]> insert teachers (name,age)value('xiaoming',30);
Query OK, 1 row affected (0.00 sec)
MariaDB [hello]> insert teachers (name,age)value('xiaohong',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hello]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | a | 20 | NULL |
| 6 | b | 24 | NULL |
| 7 | xiaoming | 30 | NULL |
| 8 | xiaohong | 20 | NULL |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)
⼗点，数据被⼈为删除
MariaDB [hello]> drop table teachers;
Query OK, 0 rows affected (0.00 sec)
没有⼈发现，继续使⽤数据库
MariaDB [hello]> insert students (name,age)value('aa',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hello]> insert students (name,age)value('bb',30);
Query OK, 1 row affected (0.00 sec)
MariaDB [hello]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | jerry | 22 | F | NULL | NULL |
| 27 | aa | 20 | F | NULL | NULL |
| 28 | bb | 30 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (0.01 sec)
有⼈发现数据库访问问题，停⽌数据库⾊运⾏，防⽕墙。
MariaDB [hello]> exit
Bye
开始恢复数据库
7 ~]# ll /data/bin/
-rw-rw---- 1 mysql mysql 30373 Jun 4 17:14 mysql-bin.000014
-rw-rw---- 1 mysql mysql 1038814 Jun 4 17:14 mysql-bin.000015
-rw-rw---- 1 mysql mysql 288 Jun 4 17:44 mysql-bin.000016
-rw-rw---- 1 mysql mysql 1299 Jun 4 18:15 mysql-bin.000017
-rw-rw---- 1 mysql mysql 459 Jun 4 17:44 mysql-bin.index
7 ~]# rm -rf /var/lib/mysql/*
7 ~]# systemctl restart mariadb
查看完全备份的位置
7 ~]# vim /data/backup/all.sql
-- MySQL dump 10.14 Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.5.60-MariaDB
···
--
-- Position to start replication or point-in-time recovery from
--
#备份点（'mysql-bin.000011', MASTER_LOG_POS=245）
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000011', MASTER_LOG_POS=245;
--
-- Current Database: `hello`
把完全备份以后的⽇志导出来⽣成增量备份，在增量备份中找到删除命令，注释掉
7 ~]# mysqlbinlog --start-position=245 /data/bin/mysql-bin.000011 > /data/backup/inc.sql
7 ~]# mysqlbinlog /data/bin/mysql-bin.000012 >> /data/backup/inc.sql
7 ~]# mysqlbinlog /data/bin/mysql-bin.000013 >> /data/backup/inc.sql
7 ~]# vim /data/backup/inc.sql
# at 686
#220605 15:35:33 server id 1 end_log_pos 713 Xid = 13262
COMMIT/*!*/;
# at 713
#220605 15:35:50 server id 1 end_log_pos 824 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1654414550/*!*/;
#DROP TABLE `teachers` /* generated by server */ <--找到刚刚误操作的命令、用'#'注释掉
/*!*/;
# at 824
启动数据库
7 ~]# mysql
MariaDB [mysql]> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> source /data/backup/all.sql
MariaDB [mysql]> source /data/backup/inc.sql
MariaDB [mysql]> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
【例16】mysqldump命令的来源
7 ~]# which 'mysqldump'
/usr/bin/mysqldump
7 ~]# rpm -qf /usr/bin/mysqldump
mariadb-5.5.60-1.el7_5.x86_64
1.4.4、xtrabackup
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
特点：
备份还原过程快速、可靠
备份过程不会打断正在执行的事务
能够基于压缩等功能节约磁盘空间和流量
自动实现备份检验
开源，免费
Xtrabackup2.2版之前包括4个可执行文件：
innobackupex: Perl 脚本
xtrabackup: C/C++ 编译的二进制
xbcrypt: 加解密
xbstream: 支持并发写的流文件格式
xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQLServer 没有交互
innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和
MySQL Server 发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。即
innobackupex是在xtrabackup 之上做了一层封装实现的
xtrabackup安装：
yum install percona-xtrabackup 在EPEL源中
最新版本下载安装：https://www.percona.com/downloads/XtraBackup/LATEST/
备份：innobackupex [option] BACKUP-ROOT-DIR
选项说明：https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
--user：该选项表示备份账号
--password：该选项表示备份的密码
--host：该选项表示备份数据库的地址
--databases：该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；
如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。
如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--defaults-file：该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir
--incremental-basedir：该选项指定为前一次全备份或增量备份的目录，与 --incremental同时使用
--incremental-dir：该选项表示还原时增量备份的目录
--include=name：指定表名，格式：databasename.tablename
Prepare：innobackupex --apply-log [option] BACKUP-DIR
选项说明：
--apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未
提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通
过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--use-memory：和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，
单位字节，也可1MB,1M,1G,1GB等，推荐1G
--export：表示开启可导出单独的表之后再导入其他Mysql中
--redo-only：此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一
个增量备份的合并
还原：innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选
项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
还原注意事项：
1.datadir 目录必须为空。除非指定innobackupex --force-non-emptydirectorires选项指定，否则-
-copy-backup选项不会覆盖
2.在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
3.由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建
备份的用户chown -R mysql:mysql /data/mysql
以上需要在用户调用innobackupex之前完成--force-non-empty-directories：指定该参数时候，使得
innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copyback和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败
备份生成的相关文件
使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文
件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文
件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：
(1)xtrabackup_info：innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份
LSN(log sequence number日志序列号)，BINLOG的位置
(2)xtrabackup_checkpoints：备份类型（如完全或增量）、备份状态（如是否已经为prepared状
态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统
版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
(3)xtrabackup_binlog_info：MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进
制日志事件的位置，可利用实现基于binlog的恢复
(4)backup-my.cnf：备份命令用到的配置选项信息
(5)xtrabackup_logfile：备份生成的日志文件
【例17】安装xtrabackup xtrabackup下载
7 ~]# ll /data/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 7829340 May 24 17:31 /data/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum安装解决依赖性
到下面网站可找对应libev包
安装libev包、解决依赖关系
7 ~]# rpm -ivh /data/libev4-4.24-8.1.x86_64.rpm
yum安装
7 ~]# yum install /data/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm -y
7 ~]# ll /usr/bin/innobackupex
lrwxrwxrwx 1 root root 10 Jun 5 17:32 /usr/bin/innobackupex -> xtrabackup
【例18】使⽤xtrabackup完全备份，完全还原。建议⽤新版本。
7 ~]# xtrabackup --backup --target-dir=/data/backup/
7 ~]# ll /data/backup/
total 18460
-rw-r----- 1 root root 431 Jun 6 00:39 backup-my.cnf
drwxr-x--- 2 root root 146 Jun 6 00:39 hellodb
-rw-r----- 1 root root 18874368 Jun 6 00:39 ibdata1
drwxr-x--- 2 root root 4096 Jun 6 00:39 mysql
drwxr-x--- 2 root root 4096 Jun 6 00:39 performance_schema
drwxr-x--- 2 root root 20 Jun 6 00:39 test
-rw-r----- 1 root root 22 Jun 6 00:39 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Jun 6 00:39 xtrabackup_checkpoints
-rw-r----- 1 root root 468 Jun 6 00:39 xtrabackup_info
-rw-r----- 1 root root 2560 Jun 6 00:39 xtrabackup_logfile
7 ~]# cat /data/backup/xtrabackup_binlog_info
mysql-bin.000003 245
7 ~]# cat /data/backup/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1597945
last_lsn = 1597945
compact = 0
recover_binlog_info = 0
7 ~]# cat /data/backup/xtrabackup_info
uuid = a0d89ae0-e4b2-11ec-ae7c-000c2907f78f
name =
tool_name = xtrabackup
tool_command = --backup --target-dir=/data/backup/
tool_version = 2.4.12
ibbackup_version = 2.4.12
server_version = 5.5.60-MariaDB
start_time = 2022-06-05 17:33:58
end_time = 2022-06-05 17:33:59
lock_time = 0
binlog_pos =
innodb_from_lsn = 0
innodb_to_lsn = 1597945
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
将备份⽂件和备份软件传到新的主机
7 ~]# scp -rp /data/backup/ 192.168.37.8:/data/
7 ~]# scp -rp /data/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm 192.168.37.8:/data/
7 ~]# scp -rp /data/libev4-4.24-8.1.x86_64.rpm 192.168.37.8:/data/
在新主机（192.168.37.8）上恢复数据库
7 ~]# rpm -ivh /data/libev4-4.24-8.1.x86_64.rpm
7 ~]# yum install -y /data/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
预整理
7 ~]# xtrabackup --prepare --target-dir=/data/backup/
确保/var/lib/mysql/为空,同时mariadb没有启动
7 ~]# ll /var/lib/mysql
total 0
7 ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: inactive (dead)
恢复
7 ~]# xtrabackup --copy-back --target-dir=/data/backup/
7 ~]# ll /var/lib/mysql
total 40976
-rw-r----- 1 root root 18874368 Jun 5 18:26 ibdata1
-rw-r----- 1 root root 5242880 Jun 5 18:26 ib_logfile0
-rw-r----- 1 root root 5242880 Jun 5 18:26 ib_logfile1
-rw-r----- 1 root root 12582912 Jun 5 18:26 ibtmp1
drwxr-x--- 2 root root 4096 Jun 5 18:26 mysql
drwxr-x--- 2 root root 4096 Jun 5 18:26 performance_schema
drwxr-x--- 2 root root 20 Jun 5 18:26 test
-rw-r----- 1 root root 468 Jun 5 18:26 xtrabackup_info
-rw-r----- 1 root root 1 Jun 5 18:26 xtrabackup_master_key_id
7 ~]# chown -R mysql:mysql /var/lib/mysql
7 ~]# ll /var/lib/mysql/
total 40976
-rw-r----- 1 mysql mysql 18874368 Jun 5 18:26 ibdata1
-rw-r----- 1 mysql mysql 5242880 Jun 5 18:26 ib_logfile0
-rw-r----- 1 mysql mysql 5242880 Jun 5 18:26 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jun 5 18:26 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Jun 5 18:26 mysql
drwxr-x--- 2 mysql mysql 4096 Jun 5 18:26 performance_schema
drwxr-x--- 2 mysql mysql 20 Jun 5 18:26 test
-rw-r----- 1 mysql mysql 468 Jun 5 18:26 xtrabackup_info
-rw-r----- 1 mysql mysql 1 Jun 5 18:26 xtrabackup_master_key_id
7 ~]# systemctl start mariadb
【例19】使⽤xtrabackup完全备份，增量备份，完全还原和增量还原。建议⽤新版本。
7 ~]# mkdir -pv /data/backup/{base,inc{1,2}}
7 ~]# tree /data/backup/
/data/backup/
├── base
├── inc1
└── inc2
3 directories, 0 files
完全备份
7 ~]# xtrabackup --backup --target-dir=/data/backup/base
7 ~]# ll /data/backup/base/
total 18460
-rw-r----- 1 root root 431 Jun 6 00:21 backup-my.cnf
drwxr-x--- 2 root root 146 Jun 6 00:21 hellodb
-rw-r----- 1 root root 18874368 Jun 6 00:21 ibdata1
drwxr-x--- 2 root root 4096 Jun 6 00:21 mysql
drwxr-x--- 2 root root 4096 Jun 6 00:21 performance_schema
drwxr-x--- 2 root root 20 Jun 6 00:21 test
-rw-r----- 1 root root 21 Jun 6 00:21 xtrabackup_binlog_info
-rw-r----- 1 root root 113 Jun 6 00:21 xtrabackup_checkpoints
-rw-r----- 1 root root 472 Jun 6 00:21 xtrabackup_info
-rw-r----- 1 root root 2560 Jun 6 00:21 xtrabackup_logfile
完全备份后，继续修改数据库
7 ~]# mysql
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> insert teachers (name,age)value('tom',20);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 20 | NULL |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
增量备份1
7 ~]# xtrabackup --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base
7 ~]# ll /data/backup/inc1
total 240
-rw-r----- 1 root root 431 Jun 6 00:45 backup-my.cnf
drwxr-x--- 2 root root 146 Jun 6 00:45 hellodb
-rw-r----- 1 root root 212992 Jun 6 00:45 ibdata1.delta
-rw-r----- 1 root root 44 Jun 6 00:45 ibdata1.meta
drwxr-x--- 2 root root 4096 Jun 6 00:45 mysql
drwxr-x--- 2 root root 4096 Jun 6 00:45 performance_schema
drwxr-x--- 2 root root 20 Jun 6 00:45 test
-rw-r----- 1 root root 22 Jun 6 00:45 xtrabackup_binlog_info
-rw-r----- 1 root root 117 Jun 6 00:45 xtrabackup_checkpoints
-rw-r----- 1 root root 519 Jun 6 00:45 xtrabackup_info
-rw-r----- 1 root root 2560 Jun 6 00:45 xtrabackup_logfile
增量备份1后，继续修改数据库
MariaDB [hellodb]> insert teachers (name,age)value('alice',18);
Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 20 | NULL |
| 6 | alice | 18 | NULL |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
增量备份2
7 ~]# xtrabackup --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1
7 ~]# ll /data/backup/inc2
total 240
-rw-r----- 1 root root 431 Jun 6 00:48 backup-my.cnf
drwxr-x--- 2 root root 146 Jun 6 00:48 hellodb
-rw-r----- 1 root root 212992 Jun 6 00:48 ibdata1.delta
-rw-r----- 1 root root 44 Jun 6 00:48 ibdata1.meta
drwxr-x--- 2 root root 4096 Jun 6 00:48 mysql
drwxr-x--- 2 root root 4096 Jun 6 00:48 performance_schema
drwxr-x--- 2 root root 20 Jun 6 00:48 test
-rw-r----- 1 root root 22 Jun 6 00:48 xtrabackup_binlog_info
-rw-r----- 1 root root 117 Jun 6 00:48 xtrabackup_checkpoints
-rw-r----- 1 root root 519 Jun 6 00:48 xtrabackup_info
-rw-r----- 1 root root 2560 Jun 6 00:48 xtrabackup_logfile
把备份内容复制的新主机
7 ~]# ll /data/backup/
total 0
drwxr-xr-x 6 root root 217 Jun 6 00:42 base
drwxr-xr-x 6 root root 243 Jun 6 00:45 inc1
drwxr-xr-x 6 root root 243 Jun 6 00:48 inc2
7 ~]# scp -rp /data/backup/ 192.168.37.8:/data/
新主机
7 ~]# du -sh /data/backup/*
20M /data/backup/base
1.5M /data/backup/inc1
1.5M /data/backup/inc2
整理备份
7 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base
7 ~]# du -sh /data/backup/*
28M /data/backup/base <--文件变大
1.5M /data/backup/inc1
1.5M /data/backup/inc2
合并第1次增量备份到完全备份
7 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1
7 ~]# du -sh /data/backup/*
28M /data/backup/base
9.5M /data/backup/inc1 <--文件也变大
1.5M /data/backup/inc2
合并第2次增量备份到完全备份
7 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2
7 ~]# du -sh /data/backup/*
28M /data/backup/base
9.5M /data/backup/inc1
9.5M /data/backup/inc2 <--文件也变大
复制到数据库⽬录，注意数据库⽬录必须为空，MySQL服务不能启动
7 ~]# xtrabackup --copy-back --target-dir=/data/backup/base
还原属性
7 ~]# ll /var/lib/mysql/
7 ~]# chown -R mysql.mysql /var/lib/mysql
7 ~]# ll /var/lib/mysql/
启动服务
7 ~]# systemctl start mariadb
7 ~]# mysql hellodb
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | tom | 20 | NULL |
| 6 | alice | 18 | NULL |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)
1.4.5、其他备份⼯具
MariaDB Backup： 从MariaDB 10.1.26开始集成，基于PerconaXtraBackup 2.3.8实现 mysqlbackup：热备份， MySQL Enterprise Edition组件 mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和 cp或scp来快速备份数据库
