MySQL的备份与恢复

使用mysqldump+binlog备份

备份素材：

create database school;
use school

CREATE TABLE stu (
Sno INT PRIMARY KEY,
Sname VARCHAR(50),
Ssex ENUM('Male', 'Female'),
Sage INT,
Sdept VARCHAR(50)
);

INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept)
VALUES (1, 'Alice', 'Female', 20, 'Mathematics'),
(2, 'Bob', 'Male', 22, 'Computer Science'),
(3, 'Charlie', 'Male', 21, 'Engineering'),
(4, 'David', 'Female', 19, 'Literature');

完全备份：

[root@node1 ~]# mysqldump -B school > /tmp/fullbackup.sql
[root@node1 ~]# cat /tmp/fullbackup.sql 
-- MySQL dump 10.13  Distrib 8.0.36, for Linux (x86_64)
--
-- Host: localhost    Database: school
-- ------------------------------------------------------
-- Server version    8.0.36

/*!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 */;
/*!50503 SET NAMES utf8mb4 */;
/*!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: `school`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `school`;

--
-- Table structure for table `stu`
--

DROP TABLE IF EXISTS `stu`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stu` (
  `Sno` int NOT NULL,
  `Sname` varchar(50) DEFAULT NULL,
  `Ssex` enum('Male','Female') DEFAULT NULL,
  `Sage` int DEFAULT NULL,
  `Sdept` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stu`
--

LOCK TABLES `stu` WRITE;
/*!40000 ALTER TABLE `stu` DISABLE KEYS */;
INSERT INTO `stu` VALUES (1,'Alice','Female',20,'Mathematics'),(2,'Bob','Male',22,'Computer Science'),(3,'Charlie','Male',21,'Engineering'),(4,'David','Female',19,'Literature');
/*!40000 ALTER TABLE `stu` 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 2024-02-21 14:49:43

全量备份恢复：

[root@node1 ~]# mysql -e 'drop database school'  #删库模拟数据丢失
[root@node1 ~]# mysql -e 'show databases'
+-----------------------------+
| Database                    |
+-----------------------------+
| information_schema   |
| mysql                          |
| performance_schema |
| sys                               |
+------------------------------+
[root@node1 ~]# mysql < /tmp/fullbackup.sql   #恢复
[root@node1 ~]# mysql -e 'show databases'
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema     |
| mysql                            |
| performance_schema   |
| school                           |
| sys                                |
+-------------------------------+
[root@node1 ~]# mysql -e 'select * from school.stu'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

#也可以使用source恢复
[root@node1 ~]# mysql -e 'drop database school'
[root@node1 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@node1 ~]# mysql -e 'source /tmp/fullbackup.sql'
[root@node1 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
[root@node1 ~]# mysql -e 'select * from school.stu'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

增量备份：

MySQL 8.0 默认开启binglog日志
素材准备

#第一次增量备份
INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (5, 'Emma', 'Female', 20, 'Mathematics'),(6, 'Tom', 'Male', 22, 'Computer Science');

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.01 sec)

#第二次增量备份
INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (7, 'Lily', 'Female', 21, 'Engineering'),(8, 'Jack', 'Male', 19, 'Literature');

mysql> select sleep(30);
+-----------+
| sleep(30) |
+-----------+
|         0 |
+-----------+
1 row in set (30.00 sec)

mysql> select * from stu;
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
|   7 | Lily    | Female |   21 | Engineering      |
|   8 | Jack    | Male   |   19 | Literature       |
+-----+---------+--------+------+------------------+
8 rows in set (0.00 sec)

mysql> drop database school;

#再增量备份前一定有一次完全备份
mysql> source /tmp/fullbackup.sql  #先恢复增量备份
mysql> select * from school.stu;
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+
4 rows in set (0.00 sec)

增量备份恢复：

1、检查全备后的所有binlog
[root@node1 ~]# ll /var/lib/mysql/bin*
-rw-r-----. 1 mysql mysql  157 Feb 21 15:31 /var/lib/mysql/binlog.000006
-rw-r-----. 1 mysql mysql 3717 Feb 21 15:40 /var/lib/mysql/binlog.000007
-rw-r-----. 1 mysql mysql   32 Feb 21 15:31 /var/lib/mysql/binlog.index

2.保护现场立即刷新日志
[root@node1 ~]# mysqladmin flush-logs 
[root@node1 ~]# ll /var/lib/mysql/bin*
-rw-r-----. 1 mysql mysql  157 Feb 21 15:31 /var/lib/mysql/binlog.000006
-rw-r-----. 1 mysql mysql 3761 Feb 21 15:41 /var/lib/mysql/binlog.000007
-rw-r-----. 1 mysql mysql  157 Feb 21 15:41 /var/lib/mysql/binlog.000008
-rw-r-----. 1 mysql mysql   48 Feb 21 15:41 /var/lib/mysql/binlog.index
[root@node1 ~]# mysql -e 'show binary logs;'
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000006 |       157 | No        |
| binlog.000007 |      3761 | No        |
| binlog.000008 |       157 | No        |
+---------------+-----------+-----------+
#现在不会再往binlog.000007里记录数据了
[root@node1 ~]# cp /var/lib/mysql/binlog.000007 /mysqlbak  #做个冷备

3、查看刷新的binlog日志,
#注意5.7版本，insert语句已经加密，默认看不到，查看时加上选项 --base64-output=DECODE-ROWS -vv

[root@node1 ~]# mysqlbinlog --base64-output=decode-rows -vv  /var/lib/mysql/binlog.000007                                                  
#找到后来的insert语句的部分

BEGIN
/*!*/;
# at 1196
#240221 15:35:27 server id 1  end_log_pos 1262 CRC32 0xc14ac894         Table_map: `school`.`stu` mapped to number 90
# has_generated_invisible_primary_key=0
# at 1262
#240221 15:35:27 server id 1  end_log_pos 1355 CRC32 0x21da1ca0         Write_rows: table id 90 flags: STMT_END_F
### INSERT INTO `school`.`stu`
### SET
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Emma' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
###   @4=20 /* INT meta=0 nullable=1 is_null=0 */
###   @5='Mathematics' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### INSERT INTO `school`.`stu`
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Tom' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
###   @4=22 /* INT meta=0 nullable=1 is_null=0 */
###   @5='Computer Science' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
# at 1355
#240221 15:35:27 server id 1  end_log_pos 1386 CRC32 0x5bd46bfd         Xid = 48
COMMIT/*!*/;
# at 1386
#240221 15:36:47 server id 1  end_log_pos 1465 CRC32 0x93fd67b9         Anonymous_GTID  last_committed=4        sequence_number=5        rbr_only=yes    original_committed_timestamp=1708501007176819   immediate_commit_timestamp=1708501007176819      transaction_length=341
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1708501007176819 (2024-02-21 15:36:47.176819 CST)
# immediate_commit_timestamp=1708501007176819 (2024-02-21 15:36:47.176819 CST)
/*!80001 SET @@session.original_commit_timestamp=1708501007176819*//*!*/;
/*!80014 SET @@session.original_server_version=80036*//*!*/;
/*!80014 SET @@session.immediate_server_version=80036*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1465
#240221 15:36:47 server id 1  end_log_pos 1542 CRC32 0x756150f3         Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1708501007/*!*/;
BEGIN
/*!*/;
# at 1542
#240221 15:36:47 server id 1  end_log_pos 1608 CRC32 0xefda7f38         Table_map: `school`.`stu` mapped to number 90
# has_generated_invisible_primary_key=0
# at 1608
#240221 15:36:47 server id 1  end_log_pos 1696 CRC32 0x972dfd69         Write_rows: table id 90 flags: STMT_END_F
### INSERT INTO `school`.`stu`
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Lily' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=2 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
###   @4=21 /* INT meta=0 nullable=1 is_null=0 */
###   @5='Engineering' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
### INSERT INTO `school`.`stu`
### SET
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Jack' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @3=1 /* ENUM(1 byte) meta=63233 nullable=1 is_null=0 */
###   @4=19 /* INT meta=0 nullable=1 is_null=0 */
###   @5='Literature' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
# at 1696
#240221 15:36:47 server id 1  end_log_pos 1727 CRC32 0x75a1b152         Xid = 50
COMMIT/*!*/;
# at 1727
#240221 15:39:01 server id 1  end_log_pos 1804 CRC32 0x1fc4c31d         Anonymous_GTID  last_committed=5        sequence_number=6        rbr_only=no     original_committed_timestamp=1708501141321225   immediate_commit_timestamp=1708501141321225      transaction_length=187

4、基于时间恢复
#开始时间为begin后的第一个，结束时间为COMMIT后的第一个
[root@node1 ~]# mysqlbinlog /var/lib/mysql/binlog.000007 --start-datetime='2024-02-21 15:35:27' --stop-datetime='2024-02-21 15:36:47' > /tmp/incrementbackup1.sql
[root@node1 ~]# mysql < /tmp/incrementbackup1.sql 
[root@node1 ~]# mysql -e 'select * from school.stu;'   
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
+-----+---------+--------+------+------------------+

4、基于位置恢复
#起始位置为begin后的第一个，终止位置为COMMIT后的第一个
[root@node1 ~]# mysqlbinlog --start-position=1542 --stop-position=1727 /var/lib/mysql/binlog.000007 > /tmp/incrementbackup2.sql
[root@node1 ~]# mysql -e 'source /tmp/incrementbackup2.sql'  #恢复
[root@node1 ~]# mysql -e 'select * from school.stu;'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
|   7 | Lily    | Female |   21 | Engineering      |
|   8 | Jack    | Male   |   19 | Literature       |
+-----+---------+--------+------+------------------+

binglog日志的gtid的新特性

开启gtid

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | OFF       |
| gtid_executed                    |           |
| gtid_executed_compression_period | 0         |
| gtid_mode                        | OFF       |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
#开启gtid
mysql> system vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true

mysql> system systemctl restart mysqld 
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed                    |           |
| gtid_executed_compression_period | 0         |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
9 rows in set (0.00 sec)

使用binlog的GTID特性进行增量备份

在一个干净的环境将刚才的素材导入这里省略
mysql> drop database school;
Query OK, 1 row affected (0.02 sec)

mysql> show master status\G   #一个DDL或者一个事务产生一个GTID
*************************** 1. row ***************************
             File: binlog.000008
         Position: 1914
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 10fdd06f-b851-11ee-8253-000c29ae0c7f:1-6

mysql> flush logs;  #保护现场
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000009
         Position: 197
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 10fdd06f-b851-11ee-8253-000c29ae0c7f:1-6
1 row in set (0.00 sec)

binlog的GTID特性进行恢复

mysql> source /tmp/fullbackup.sql   #恢复全量备份
mysql> select * from stu;
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+
4 rows in set (0.00 sec)

mysql> show binlog events in 'binlog.000008'\G  #查看
*************************** 1. row ***************************
   Log_name: binlog.000008
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 126
       Info: Server ver: 8.0.36, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: binlog.000008
        Pos: 126
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 157
       Info: 
*************************** 3. row ***************************
   Log_name: binlog.000008
        Pos: 157
 Event_type: Gtid
  Server_id: 1
End_log_pos: 234
       Info: SET @@SESSION.GTID_NEXT= '10fdd06f-b851-11ee-8253-000c29ae0c7f:1'
*************************** 4. row ***************************
   Log_name: binlog.000008
        Pos: 234
 Event_type: Query
  Server_id: 1
End_log_pos: 348
       Info: create database school /* xid=3 */
*************************** 5. row ***************************
   Log_name: binlog.000008
        Pos: 348
 Event_type: Gtid
  Server_id: 1
End_log_pos: 427
       Info: SET @@SESSION.GTID_NEXT= '10fdd06f-b851-11ee-8253-000c29ae0c7f:2'
*************************** 6. row ***************************
   Log_name: binlog.000008
        Pos: 427
 Event_type: Query
  Server_id: 1
End_log_pos: 636
       Info: use `school`; CREATE TABLE stu (
Sno INT PRIMARY KEY,
Sname VARCHAR(50),
Ssex ENUM('Male', 'Female'),
Sage INT,
Sdept VARCHAR(50)
) /* xid=8 */
*************************** 7. row ***************************
   Log_name: binlog.000008
        Pos: 636
 Event_type: Gtid
  Server_id: 1
End_log_pos: 715
       Info: SET @@SESSION.GTID_NEXT= '10fdd06f-b851-11ee-8253-000c29ae0c7f:3'
*************************** 8. row ***************************
   Log_name: binlog.000008
        Pos: 715
 Event_type: Query
  Server_id: 1
End_log_pos: 792
       Info: BEGIN
*************************** 9. row ***************************
   Log_name: binlog.000008
        Pos: 792
 Event_type: Table_map
  Server_id: 1
End_log_pos: 858
       Info: table_id: 90 (school.stu)
*************************** 10. row ***************************
   Log_name: binlog.000008
        Pos: 858
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 1009
       Info: table_id: 90 flags: STMT_END_F
*************************** 11. row ***************************
   Log_name: binlog.000008
        Pos: 1009
 Event_type: Xid
  Server_id: 1
End_log_pos: 1040
       Info: COMMIT /* xid=9 */
*************************** 12. row ***************************
   Log_name: binlog.000008
        Pos: 1040
 Event_type: Gtid
  Server_id: 1
End_log_pos: 1119
       Info: SET @@SESSION.GTID_NEXT= '10fdd06f-b851-11ee-8253-000c29ae0c7f:4'
*************************** 13. row ***************************
   Log_name: binlog.000008
        Pos: 1119
 Event_type: Query
  Server_id: 1
End_log_pos: 1196
       Info: BEGIN
*************************** 14. row ***************************
   Log_name: binlog.000008
        Pos: 1196
 Event_type: Table_map
  Server_id: 1
End_log_pos: 1262
       Info: table_id: 90 (school.stu)
*************************** 15. row ***************************
   Log_name: binlog.000008
        Pos: 1262
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 1355
       Info: table_id: 90 flags: STMT_END_F
*************************** 16. row ***************************
   Log_name: binlog.000008
        Pos: 1355
 Event_type: Xid
  Server_id: 1
End_log_pos: 1386
       Info: COMMIT /* xid=49 */
*************************** 17. row ***************************
   Log_name: binlog.000008
        Pos: 1386
 Event_type: Gtid
  Server_id: 1
End_log_pos: 1465
       Info: SET @@SESSION.GTID_NEXT= '10fdd06f-b851-11ee-8253-000c29ae0c7f:5'
*************************** 18. row ***************************
   Log_name: binlog.000008
        Pos: 1465
 Event_type: Query
  Server_id: 1
End_log_pos: 1542
       Info: BEGIN
*************************** 19. row ***************************
   Log_name: binlog.000008
        Pos: 1542
 Event_type: Table_map
  Server_id: 1
End_log_pos: 1608
       Info: table_id: 90 (school.stu)
*************************** 20. row ***************************
   Log_name: binlog.000008
        Pos: 1608
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 1696
       Info: table_id: 90 flags: STMT_END_F
*************************** 21. row ***************************
   Log_name: binlog.000008
        Pos: 1696
 Event_type: Xid
  Server_id: 1
End_log_pos: 1727
       Info: COMMIT /* xid=50 */
*************************** 22. row ***************************
   Log_name: binlog.000008
        Pos: 1727
 Event_type: Gtid
  Server_id: 1
End_log_pos: 1804
       Info: SET @@SESSION.GTID_NEXT= '10fdd06f-b851-11ee-8253-000c29ae0c7f:6'
*************************** 23. row ***************************
   Log_name: binlog.000008
        Pos: 1804
 Event_type: Query
  Server_id: 1
End_log_pos: 1914
       Info: drop database school /* xid=51 */
*************************** 24. row ***************************
   Log_name: binlog.000008
        Pos: 1914
 Event_type: Rotate
  Server_id: 1
End_log_pos: 1958
       Info: binlog.000009;pos=4
24 rows in set (0.00 sec)

确定起始范围：1-5,注意不要把drop写进去就行

这里注意
GTID的幂等性 
    • 开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了 
    • 就想恢复?怎么办? 
    • --skip-gtids  #跳过gtid
接着上面，截取日志时添加--skip-gtids。
[root@node1 ~]# mysqlbinlog --skip-gtids --include-gtids='10fdd06f-b851-11ee-8253-000c29ae0c7f:4-5' /var/lib/mysql/binlog.000008 > /tmp/gtid.sql
[root@node1 ~]# mysql < /tmp/gtid.sql
[root@node1 ~]# mysql -e 'select * from school.stu'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
|   7 | Lily    | Female |   21 | Engineering      |
|   8 | Jack    | Male   |   19 | Literature       |
+-----+---------+--------+------+------------------+

LVM快照备份：

创建逻辑卷

[root@node1 ~]# lsblk   #添加一块硬盘
NAME                     MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTS
sda                        8:0    0   20G  0 disk 
├─sda1                     8:1    0    1G  0 part /boot
└─sda2                     8:2    0   19G  0 part 
  ├─openeuler_node1-root 253:0    0   17G  0 lvm  /
  └─openeuler_node1-swap 253:1    0    2G  0 lvm  [SWAP]
sdb                        8:16   0   20G  0 disk 
sr0                       11:0    1 17.1G  0 rom  
[root@node1 ~]# vgcreate vg01 /dev/sdb 
  Physical volume "/dev/sdb" successfully created.
  Volume group "vg01" successfully created
[root@node1 ~]# lvcreate -n lv_mysql -L 4G vg01
  Logical volume "lv_mysql" created.
[root@node1 ~]# vgcreate vg01 /dev/sdb
  Physical volume "/dev/sdb" successfully created.
  Volume group "vg01" successfully created
[root@node1 ~]# lvcreate -n lv_mysql -L 4G vg01
  Logical volume "lv_mysql" created.
[root@node1 ~]# mkfs.ext4 /dev/vg01/lv_mysql   #格式化
mke2fs 1.46.4 (18-Aug-2021)
Creating filesystem with 1048576 4k blocks and 262144 inodes
Filesystem UUID: 0db7faf1-88a8-4669-bc3e-ccdb4f9e9090
Superblock backups stored on blocks: 
        32768, 98304, 163840, 229376, 294912, 819200, 884736

Allocating group tables: done                            
Writing inode tables: done                            
Creating journal (16384 blocks): done
Writing superblocks and filesystem accounting information: done 
 

环境创建：

[root@node1 ~]# systemctl stop mysqld.service   #先停止服务
[root@node1 ~]# cd /var/lib/mysql
[root@node1 mysql]# tar czf /tmp/mysqlall.tar.gz *
[root@node1 mysql]# cd
[root@node1 ~]# mount /dev/vg01/lv_mysql /var/lib/mysql
[root@node1 ~]# ll /var/lib/mysql  #该文件会被清空，等于把数据都清空了
total 0
[root@node1 ~]# tar xf /tmp/mysqlall.tar.gz -C /var/lib/mysql #将mysql的文件内容解压到逻辑卷中

[root@node1 ~]# ll -d  /var/lib/mysql   ##用户组为root此时mysql无法启动
drwxr-xr-x. 3 root root 17 Feb 21 20:24 /var/lib/mysql
[root@node1 ~]# chown -R mysql.mysql /var/lib/mysql
[root@node1 ~]# ll -d  /var/lib/mysql 
drwxr-xr-x. 3 mysql mysql 17 Feb 21 20:24 /var/lib/mysql
[root@node1 ~]# systemctl start mysqld.service   #直接起服务

[root@node1 ~]# mysql -e 'select * from school.stu'  #数据都在
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

使用LVM快照备份数据库

1、创建快照
#给数据库加读锁 
[root@node1 ~]# mysql -e 'flush table with read lock'  
#给mysql的数据库所在的逻辑卷创建快照

[root@node1 ~]# lvcreate -n lv_mysql_s -L 500M -s /dev/vg01/lv_mysql 
    Logical volume "lv_mysql_s" created.
#解锁数据库 
[root@node1 ~]# mysql -e 'unlock tables'
以上1-3需要在一个会话完成 

将快照挂载到临时目录里 
[root@node1 ~]# mkdir /mnt/mysql && mount /dev/vg01/lv_mysql_s /mnt/mysql/ 

2、备份数据 
[root@node1 ~]# yum install rsync -y  #下载备份工具
[root@node2 ~]# mkdir /backup && rsync -av /mnt/mysql/ /backup 
卸载快照并删除 
[root@node1 ~]# umount /mnt/mysql/ && lvremove /dev/vg01/lv_mysql_s 

方法二：
这里建议直接写一个shell脚本（事先下号rsync备份工具），因为写脚本可以直接指定contab计划任务
[root@node2 ~]# vim mysql.sh 
#!/bin/bash                                                                               
back_dir=/backup/`date +%F` #创建一个备份目录                                               
[ -d ${back_dir} ]|| mkdir -p ${back_dir}  #如果没有就创建  

echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 500M -s /dev/vg01/lv_mysql; unlock tables;" | mysql -uroot -p1234 &>/dev/null #锁表只读,创建快照,做完后解锁         

[ -d /mnt/mysql/ ] || mkdir -p /mnt/mysql   #判断挂载点书否存在                             
mount /dev/vg01/lv_mysql_s /mnt/mysql  #挂载逻辑卷快照 

rsync -a /mnt/mysql/ ${back_dir}  #备份                                                     
if [ $? -eq 0 ];then                                                                     
        umount /mnt/mysql/ && lvremove -f /dev/vg01/lv_mysql_s &>/dev/null  #卸载,移除       
fi

[root@node1 ~]# chmod +x mysql.sh 
[root@node1 ~]# ./mysql.sh
[root@node1 ~]# ll /backup/2024-02-22/   #这里就备份好了
total 94680
-rw-r-----. 1 mysql mysql       56 Jan 21 19:42  auto.cnf
-rw-r-----. 1 mysql mysql      180 Feb 22 20:04  binlog.000004
-rw-r-----. 1 mysql mysql     1063 Feb 22 20:22  binlog.000005
-rw-r-----. 1 mysql mysql      157 Feb 22 20:23  binlog.000006
-rw-r-----. 1 mysql mysql       48 Feb 22 20:23  binlog.index
-rw-------. 1 mysql mysql     1676 Jan 21 19:42  ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jan 21 19:42  ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jan 21 19:42  client-cert.pem
-rw-------. 1 mysql mysql     1676 Jan 21 19:42  client-key.pem
-rw-r-----. 1 mysql mysql   196608 Feb 22 20:25 '#ib_16384_0.dblwr'
-rw-r-----. 1 mysql mysql  8585216 Jan 21 19:42 '#ib_16384_1.dblwr'
-rw-r-----. 1 mysql mysql     3428 Feb 22 20:22  ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Feb 22 20:23  ibdata1
-rw-r-----. 1 mysql mysql 12582912 Feb 22 20:23  ibtmp1
drwxr-x---. 2 mysql mysql     4096 Feb 22 20:23 '#innodb_redo'
drwxr-x---. 2 mysql mysql     4096 Feb 22 20:23 '#innodb_temp'
drwx------. 2 mysql mysql     4096 Feb 22 20:21  lost+found
drwxr-x---. 2 mysql mysql     4096 Jan 21 19:42  mysql
-rw-r-----. 1 mysql mysql 29360128 Feb 22 20:23  mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 Feb 22 20:23  mysql.sock
-rw-------. 1 mysql mysql        5 Feb 22 20:23  mysql.sock.lock
drwxr-x---. 2 mysql mysql     4096 Jan 21 19:42  performance_schema
-rw-------. 1 mysql mysql     1676 Jan 21 19:42  private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jan 21 19:42  public_key.pem
drwxr-x---. 2 mysql mysql     4096 Feb 22 20:21  school
-rw-r--r--. 1 mysql mysql     1112 Jan 21 19:42  server-cert.pem
-rw-------. 1 mysql mysql     1676 Jan 21 19:42  server-key.pem
drwxr-x---. 2 mysql mysql     4096 Jan 21 19:42  sys
-rw-r-----. 1 mysql mysql 16777216 Feb 22 20:25  undo_001
-rw-r-----. 1 mysql mysql 16777216 Feb 22 20:25  undo_002

[root@node1 ~]# vim /etc/my.cnf   ##将datadir文件暂时指向备份的文件，最后记得改回来
#datadir=/var/lib/mysql
datadir=/backup/2024-02-22/
[root@node2 ~]# systemctl start mysqld
[root@node1 ~]# mysql -e 'select * from school.stu'  ##该备份文件可用 
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

mydumper备份数据库：

安装部署：

[root@node1 ~]# wget -c https://github.com/mydumper/mydumper/releases/download/v0.15.1-3/mydumper-0.15.1-3.el8.x86_64.rpm
[root@node1 ~]# yum install mydumper-0.15.1-3.el8.x86_64.rpm -y

备份：

[root@node1 ~]# mydumper -B school -o /bak
[root@node1 ~]# ll /bak
total 16
-rw-r--r--. 1 root root 262 Feb 22 20:49 metadata
-rw-r--r--. 1 root root 155 Feb 22 20:49 school-schema-create.sql
-rw-r--r--. 1 root root   0 Feb 22 20:49 school-schema-triggers.sql
-rw-r--r--. 1 root root 283 Feb 22 20:49 school.stu.00000.sql
-rw-r--r--. 1 root root 379 Feb 22 20:49 school.stu-schema.sql

恢复：

[root@node1 ~]# mysql -e 'drop database school'
[root@node1 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@node1 ~]# myloader -d /bak/ -o  #恢复
[root@node1 ~]# mysql -e 'select * from school.stu'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

mydumper常用参数：

    · -B, --database：指定要备份的数据库。
    · -T, --tables-list：指定要备份的表的以逗号分隔的列表。
    · -o, --outputdir：指定备份文件的输出目录。
    · -s, --statement-size：尝试将INSERT语句的大小限制为指定的字节数，默认为1000000字节。
    · -r, --rows：将表拆分为指定行数的分块。此选项会关闭--chunk-filesize选项。
    · -F, --chunk-filesize：将表拆分为指定输出文件大小的分块。该值以MB为单位。
    · -c, --compress：压缩输出文件。
    · -e, --build-empty-files：即使表中没有数据，也构建备份文件。
    · -x, --regex：用于匹配’数据库.表’的正则表达式。
    · -i, --ignore-engines：指定要忽略的存储引擎的逗号分隔列表。
    · -m, --no-schemas：不备份表结构，只备份数据。
    · -d, --no-data：不备份表数据，只备份表结构。
    · -G, --triggers：备份触发器。
    · -h, --host：要连接的主机名。
    · -u, --user：用于运行备份的具有特权的用户名。
    · -p, --password：用户密码。
    · -P, --port：要连接的TCP/IP端口。
    · -S, --socket：要使用的UNIX域套接字文件进行连接。
    · -t, --threads：要使用的线程数，默认为4。
    · -C, --compress-protocol：在MySQL连接上使用压缩协议。
    · -V, --version：显示程序版本并退出。
    ·-v --verbose：输出的详细程度，0 = 静默，1 = 错误，2 = 警告，3 = 信息，默认为2。

Xtrabackup 备份数据库

安装部署和环境准备：

openEuler和xtrabackup不兼容，所以只能用通用二进制安装、而且最高只支持到 MySQL 8.0.35

[root@node1 ~]# wget -c https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/tarball/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
[root@node1 ~]# tar xf percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17.tar.gz
[root@node1 ~]# ln -sv /root/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup /usr/bin/xtrabackup
'/usr/bin/xtrabackup' -> '/root/percona-xtrabackup-8.0.35-30-Linux-x86_64.glibc2.17/bin/xtrabackup'

创建用户并授权
CREATE USER 'bkpuser'@'%'IDENTIFIED with mysql_native_password  BY 'Bak@123.com';

GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 

GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'%';

GRANT SELECT ON performance_schema.keyring_component_status TO bkpuser@'%';

GRANT SELECT ON performance_schema.replication_group_members TO bkpuser@'%';

GRANT SELECT ON performance_schema.replication_group_members TO bkpuser@'%';

增量备份：

[root@node1 ~]# mkdir /data
[root@node1 ~]# xtrabackup --backup --backup --target-dir=/data/backups/ -u bkpuser -p'Bak@123.com' -H192.168.110.142  #完全备份

第一次增量备份
INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (5, 'Emma', 'Female', 20, 'Mathematics'),(6, 'Tom', 'Male', 22, 'Computer Science');

[root@node1 ~]# xtrabackup --backup --target-dir=/data/incr1/ -u bkpuser -p'Bak@123.com' -H192.168.110.142 --incremental-basedir=/data/backups/  #第一次增量备份
    --backup：指定进行备份操作。
    --target-dir=/data/backups/：指定备份文件的目标目录。
    -u bkpuser：指定用于连接 MySQL 服务器的用户名。
    -p'Bak@123.com'：指定用户的密码。
    -H192.168.110.142：指定要连接的 MySQL 服务器的主机名或 IP 地址。
    --incremental-basedir=/data/backups/：增量备份的基础目录

[root@node1 ~]# ll /data/incr1/
total 324
-rw-r-----. 1 root root   447 Feb 22 22:02 backup-my.cnf  #备份配置文件
-rw-r-----. 1 root root   157 Feb 22 22:02 binlog.000008  #二进制日志文件
-rw-r-----. 1 root root    16 Feb 22 22:02 binlog.index   #二进制日志索引文件
-rw-r-----. 1 root root  3427 Feb 22 22:02 ib_buffer_pool #InnoDB 缓冲池文件
-rw-r-----. 1 root root 32768 Feb 22 22:02 ibdata1.delta  #InnoDB 系统表空间的增量和元数据文件
-rw-r-----. 1 root root    64 Feb 22 22:02 ibdata1.meta   #InnoDB 表相关的增量和元数据文件
drwxr-x---. 2 root root  4096 Feb 22 22:02 mysql          #MySQL 数据库的相关文件
-rw-r-----. 1 root root 49152 Feb 22 22:02 mysql.ibd.delta #与InnoDB表相关的增量和元数据文件
-rw-r-----. 1 root root    73 Feb 22 22:02 mysql.ibd.meta  
drwxr-x---. 2 root root  4096 Feb 22 22:02 performance_schema
drwxr-x---. 2 root root  4096 Feb 22 22:02 school         ##school数据库的相关文件
drwxr-x---. 2 root root  4096 Feb 22 22:02 sys
-rw-r-----. 1 root root 98304 Feb 22 22:02 undo_001.delta
-rw-r-----. 1 root root    69 Feb 22 22:02 undo_001.meta
-rw-r-----. 1 root root 81920 Feb 22 22:02 undo_002.delta
-rw-r-----. 1 root root    69 Feb 22 22:02 undo_002.meta
-rw-r-----. 1 root root    18 Feb 22 22:02 xtrabackup_binlog_info 
-rw-r-----. 1 root root   139 Feb 22 22:02 xtrabackup_checkpoints
-rw-r-----. 1 root root   531 Feb 22 22:02 xtrabackup_info
-rw-r-----. 1 root root  2560 Feb 22 22:02 xtrabackup_logfile
-rw-r-----. 1 root root    39 Feb 22 22:02 xtrabackup_tablespaces

第二次增量备份
INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (7, 'Lily', 'Female', 21, 'Engineering'),(8, 'Jack', 'Male', 19, 'Literature');
[root@node1 ~]# xtrabackup --backup --target-dir=/data/incr2/ -u bkpuser -p'Bak@123.com' -H192.168.110.142 --incremental-basedir=/data/incr1/
#每一次增量备份都在上一次增量都都要指定上一次备份的基础目录

恢复：

[root@node1 ~]# mysql -e 'drop database school'  #删库
[root@node1 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

1、准备完全备份
[root@node1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups

2、应用第一次增量备份到完全备份
[root@node1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/ --incremental-dir=/data/incr1

3、应用第二次增量备份到完全备份
[root@node1 ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backups/ --incremental-dir=/data/incr2

4、最后执行
[root@node1 ~]# xtrabackup --prepare --target-dir=/data/backups/  #用于准备 xtrabackup 备份
--prepare 选项指示 xtrabackup 进行备份的准备工作。这通常包括将备份数据转换为可用于恢复的格式，并确保备份的一致性和完整性。
--target-dir=/data/backups/ 指定了准备后生成的文件将被放置的目标目录。
通过执行这个命令，xtrabackup 将在指定的目标目录中进行准备操作，以便后续可以使用这些备份进行数据库的恢复。

5、验证
[root@node1 ~]# systemctl stop mysqld.service 
[root@node1 ~]# rm -rf /var/lib/mysql/*  #要保证这个文件为空
[root@node1 ~]# xtrabackup --copy-back --target-dir=/data/backups/  #将备份文件copy到该目录
[root@node1 ~]# chown -R mysql:mysql /var/lib/mysql  #更改目录下所有文件的权限
[root@node1 ~]# systemctl restart mysqld.service 
[root@node1 ~]# mysql -e 'select * from school.stu;'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
|   7 | Lily    | Female |   21 | Engineering      |
|   8 | Jack    | Male   |   19 | Literature       |
+-----+---------+--------+------+------------------+

MySQLBackup备份数据库

安装部署：

[root@node5 ~]# wget -c https://edelivery.oracle.com/osdc/softwareDownload?fileName=V1040085-01.zip
[root@node5 ~]# ll
total 22776
-rw-------. 1 root root     1066 Jan 21 14:59 anaconda-ks.cfg
-r--r--r--. 1 root root 23316009 Feb 24 18:50 V1040085-01.zip
[root@node5 ~]# unzip V1040085-01.zip
[root@node5 ~]# yum install mysql-commercial-backup-8.0.36-1.1.el8.x86_64.rpm -y

配置备份管理员:

mysql> create user 'mysqlbackup'@'localhost' identified by 'MySQL@123';
Query OK, 0 rows affected (0.09 sec)
mysql> grant all on *.* to 'mysqlbackup'@'localhost';
Query OK, 0 rows affected (0.03 sec)

backup-to-image方式备份数据库:

全量备份：

[root@node5 ~]# mkdir /data  #创建备份目录
[root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock \
 --backup-image=my_full_bak.mbi  \
 --backup-dir=/data/backup \
 --show-progress \
 --compress \
 backup-to-image
参数解释：
 –backup-image:生成的备份image名称
 –backup-dir:生成的备份image所在目录
 –show-progress:显示备份进度[可选项]
 –compress:对备份image进行压缩节省空间[可选项]
 –with-timestamp:在backup-dir目录下生成’年-月-日-时-分-秒’的目录存储备份image以及其它相关文件，写脚本就不要加
 backup-to-image：声明这是备份为image的备份

[root@node5 ~]# ll /data/backup/  #查看备份后的文件
total 4408
-rw-r--r--. 1 root root     255 Feb 24 19:59 backup-my.cnf
drwxr-x---. 2 root root    4096 Feb 24 19:59 datadir
drwxr-x---. 2 root root    4096 Feb 24 19:59 meta
-rw-r-----. 1 root root 4475730 Feb 24 19:59 my_full_bak.mbi
-rw-r-----. 1 root root   19936 Feb 24 19:59 server-all.cnf
-rw-r-----. 1 root root     632 Feb 24 19:59 server-my.cnf

 全量备份还原：

[root@node5 ~]# mysqlbackup --backup-image=/data/backup/my_full_bak.mbi list-image
#通过list-image查看备份image中的文件内容
[root@node5 ~]# mysqlbackup --backup-image=/data/backup/my_full_bak.mbi validate
#通过validate验证备份image的有效性

[root@node5 ~]# mysql -e 'drop database school'  #删库
[root@node5 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

还原image：

#这两步必做
[root@node5 ~]# systemctl stop mysqld.service  #关闭mysqld服务
[root@node5 ~]# rm -rf /var/lib/mysql/*   #清空mysql的datadir目录
[root@node5 ~]# mysqlbackup --datadir=/var/lib/mysql --backup-image=/data/backup/my_full_bak.mbi \
 --backup-dir=/data/backup/tmp \
 --uncompress \
 copy-back-and-apply-log

参数解释：
--datadir=/var/lib/mysql：指定MySQL服务器的数据目录
--backup-image=/data/backup/my_full_bak.mbi：指定备份镜像的文件名和路径。
--backup-dir=/data/backup/tmp：指定备份过程中临时文件的存储目录。这些临时文件在备份过程中被创建，用于存储备份数据的副本。
--uncompress：在恢复时不压缩备份镜像。默认情况下，mysqlbackup会尝试压缩备份镜像以节省空间。
copy-back-and-apply-log：这是mysqlbackup的一个操作模式。它指示mysqlbackup在恢复过程中首先将备份镜像复制回原始数据目录，然后应用任何未应用的binlog事件。

[root@node5 ~]# chown -R mysql.mysql /var/lib/mysql/*  #更改权限
[root@node5 ~]# systemctl start mysqld.service 
[root@node5 ~]# mysql -e 'select * from school.stu'  #确认恢复
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

增量备份：

1、第一次增量备份
mysql> INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (5, 'Emma', 'Female', 20, 'Mathematics'),(6, 'Tom', 'Male', 22, 'Computer Science');

[root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock \
 --compress \
 --incremental \
 --incremental-base=dir:/data/backup \
 --backup-dir=/data/backup/incr1 \
 --backup-image=my_inc1_bak.mbi \
 backup-to-image

参数解释：
--user=mysqlbackup：指定用于连接到MySQL服务器的用户名。
--password=MySQL@123：指定用于连接到MySQL服务器的密码。
--socket=/var/lib/mysql/mysql.sock：指定MySQL服务器的Unix套接字文件路径。
--compress：指示mysqlbackup在创建备份镜像时进行压缩，以节省磁盘空间。
--incremental：指示mysqlbackup进行增量备份，只备份自上次备份以来的更改。
--incremental-base=dir:/data/backup：指定增量备份的基础备份目录。这个目录应该包含了上次的完整备份。
--backup-dir=/data/backup/incr1：指定备份过程中临时文件的存储目录。
--backup-image=my_inc1_bak.mbi：指定备份镜像的文件名和路径。
backup-to-image：这是mysqlbackup的一个操作模式。它指示mysqlbackup创建一个备份镜像，包含了自上次备份以来的所有更改。

[root@node5 ~]# ll /data/backup/incr1/
total 1792
-rw-r--r--. 1 root root     255 Feb 24 20:22 backup-my.cnf
drwxr-x---. 2 root root    4096 Feb 24 20:22 datadir
drwxr-x---. 2 root root    4096 Feb 24 20:22 meta
-rw-r-----. 1 root root 1796619 Feb 24 20:22 my_inc1_bak.mbi
-rw-r-----. 1 root root   19936 Feb 24 20:22 server-all.cnf
-rw-r-----. 1 root root     505 Feb 24 20:22 server-my.cnf

2、第二次增量备份
mysql> INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (7, 'Lily', 'Female', 21, 'Engineering'),(8, 'Jack', 'Male', 19, 'Literature');

[root@node5 ~]# mysqlbackup  --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock \
 --compress \
 --incremental \
 --incremental-base=history:last_backup \
 --backup-dir=/data/backup/incr2 \
 --backup-image=my_inc_bak.mbi \
 backup-to-image
#--incremental-base 可以写成history:last_backup不用写上次备份的dir，两个方法都可以

[root@node5 ~]# ll /data/backup/incr2/
total 1700
-rw-r--r--. 1 root root     255 Feb 24 20:28 backup-my.cnf
drwxr-x---. 2 root root    4096 Feb 24 20:28 datadir
drwxr-x---. 2 root root    4096 Feb 24 20:28 meta
-rw-r-----. 1 root root 1696167 Feb 24 20:28 my_inc_bak.mbi
-rw-r-----. 1 root root   19936 Feb 24 20:28 server-all.cnf
-rw-r-----. 1 root root     632 Feb 24 20:28 server-my.cnf

增量备份还原：

[root@node5 ~]# mysql -e 'drop database school'  #删库
[root@node5 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@node5 ~]# systemctl stop mysqld.service  #关闭mysqld服务
[root@node5 ~]# rm -rf /var/lib/mysql/*   #清空mysql的datadir目录

1、先还原完全备份
[root@node5 ~]# mysqlbackup --datadir=/var/lib/mysql --backup-image=/data/backup/my_full_bak.mbi \
 --backup-dir=/data/backup/tmp1 \
 --uncompress \
 copy-back-and-apply-log
#注意backup-dir刚才是/data/backup/tmp，这里得换一个

2、第一次增量备份还原
[root@node5 ~]# mysqlbackup --backup-image=/data/backup/incr1/my_inc1_bak.mbi \
 --backup-dir=/data/backup/incr1/tmp2 \
 --datadir=/var/lib/mysql/ \
 --incremental \
 copy-back-and-apply-log

3、第二次增量备份还原
[root@node5 ~]# mysqlbackup --backup-image=/data/backup/incr2/my_inc_bak.mbi \
 --backup-dir=/data/backup/incr2/tmp3 \
 --datadir=/var/lib/mysql/ \
 --incremental \
 copy-back-and-apply-log

[root@node5 ~]# chown -R mysql.mysql /var/lib/mysql/*
[root@node5 ~]# systemctl start mysqld.service
[root@node5 ~]# mysql -e 'select * from school.stu'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
|   7 | Lily    | Female |   21 | Engineering      |
|   8 | Jack    | Male   |   19 | Literature       |
+-----+---------+--------+------+------------------+

datafile方式备份数据库：

**环境：最好恢复初始环境，或只把上次备份的目录删了**

完全备份：

[root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock \
 --backup-dir=/data/backup \
 --show-progress \
 --compress \
 backup 

[root@node5 ~]# ll /data/backup/
total 36
-rw-r--r--. 1 root root   255 Feb 24 20:53 backup-my.cnf
drwxr-x---. 6 root root  4096 Feb 24 20:53 datadir
drwxr-x---. 2 root root  4096 Feb 24 20:53 meta
-rw-r-----. 1 root root 19936 Feb 24 20:53 server-all.cnf
-rw-r-----. 1 root root   632 Feb 24 20:53 server-my.cnf

完全备份恢复：

[root@node5 ~]# mysql -e 'drop database school'
[root@node5 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@node5 ~]# systemctl restart mysqld.service 
[root@node5 ~]# systemctl stop mysqld.service 
[root@node5 ~]# rm -rf /var/lib/mysql/*

[root@node5 ~]# mysqlbackup --backup-dir=/data/backup \   #备份过程中产生的日志文件
 apply-log

[root@node5 ~]# mysqlbackup --datadir=/var/lib/mysql/ --backup-dir=/data/backup \  #将备份的文件拷贝到datadir下
 copy-back-and-apply-log

[root@node5 ~]# chown -R mysql.mysql /var/lib/mysql/*
[root@node5 ~]# systemctl start mysqld.service 
[root@node5 ~]# mysql -e 'select * from school.stu'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
+-----+---------+--------+------+------------------+

增量备份：

1、第一次增量备份
mysql> INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (5, 'Emma', 'Female', 20, 'Mathematics'),(6, 'Tom', 'Male', 22, 'Computer Science');

[root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock \
 --compress \

 --incremental \
 --incremental-base=dir:/data/backup \
 --incremental-backup-dir=/data/backup/incr1 \
 backup

[root@node5 ~]# ll /data/backup/incr1/
total 36
-rw-r--r--. 1 root root   255 Feb 24 21:55 backup-my.cnf
drwxr-x---. 6 root root  4096 Feb 24 21:55 datadir
drwxr-x---. 2 root root  4096 Feb 24 21:55 meta
-rw-r-----. 1 root root 19936 Feb 24 21:55 server-all.cnf
-rw-r-----. 1 root root   632 Feb 24 21:55 server-my.cnf

2、第二次增量备份
mysql> INSERT INTO stu (Sno, Sname, Ssex, Sage, Sdept) VALUES (7, 'Lily', 'Female', 21, 'Engineering'),(8, 'Jack', 'Male', 19, 'Literature');

[root@node5 ~]# mysqlbackup --user=mysqlbackup --password=MySQL@123 --socket=/var/lib/mysql/mysql.sock \
 --compress \

 --incremental \
 --incremental-base=history:last_backup \
 --incremental-backup-dir=/data/backup/incr2 \
 backup

[root@node5 ~]# ll /data/backup/incr2/
total 36
-rw-r--r--. 1 root root   255 Feb 24 21:56 backup-my.cnf
drwxr-x---. 6 root root  4096 Feb 24 21:56 datadir
drwxr-x---. 2 root root  4096 Feb 24 21:56 meta
-rw-r-----. 1 root root 19936 Feb 24 21:56 server-all.cnf
-rw-r-----. 1 root root   632 Feb 24 21:56 server-my.cnf

增量备份还原:

[root@node5 ~]# mysql -e 'drop database school'  #删库
[root@node5 ~]# mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@node5 ~]# systemctl stop mysqld.service  #关闭mysqld服务
[root@node5 ~]# rm -rf /var/lib/mysql/*   #清空mysql的datadir目录

[root@node5 ~]# mysqlbackup --backup-dir=/data/backup \  
 apply-log  ##备份过程中产生的日志文件

#增备apply-incremental-backup
[root@node5 ~]# mysqlbackup --incremental-backup-dir=/data/backup/incr1  \
 --backup-dir=/data/backup/ \
 apply-incremental-backup

[root@node5 ~]# mysqlbackup --incremental-backup-dir=/data/backup/incr2  \
 --backup-dir=/data/backup/ \
 apply-incremental-backup

[root@node5 ~]# mysqlbackup --datadir=/var/lib/mysql --backup-dir=/data/backup/ \ 
 copy-back-and-apply-log  #将几次备份搞得文件拷到datadir

[root@node5 ~]# chown -R mysql.mysql /var/lib/mysql/*
[root@node5 ~]# systemctl start mysqld.service 
[root@node5 ~]# mysql -e 'select * from school.stu'
+-----+---------+--------+------+------------------+
| Sno | Sname   | Ssex   | Sage | Sdept            |
+-----+---------+--------+------+------------------+
|   1 | Alice   | Female |   20 | Mathematics      |
|   2 | Bob     | Male   |   22 | Computer Science |
|   3 | Charlie | Male   |   21 | Engineering      |
|   4 | David   | Female |   19 | Literature       |
|   5 | Emma    | Female |   20 | Mathematics      |
|   6 | Tom     | Male   |   22 | Computer Science |
|   7 | Lily    | Female |   21 | Engineering      |
|   8 | Jack    | Male   |   19 | Literature       |
+-----+---------+--------+------+------------------+