在 MariaDB 数据库中，有两种方法可以备份 MariaDB 数据库。

1. 逻辑备份以纯文本文件的形式导出记录中的信息。

2. 物理备份由存储内容的文件和目录的副本组成。

在本文中，我们将进行逻辑备份并使用它来执行恢复。

从逻辑备份中恢复 MariaDB 数据库

要创建恢复方案，我们必须删除数据库 dev 。

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dev                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)
MariaDB [(none)]> drop database dev;
Query OK, 1 row affected (0.07 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]>

我们已经删除了我们的数据库 dev 。
现在，我们将从备份中恢复数据库 ~/dev_backup.dump 。

首先，我们必须创建一个同名的数据库，并使用 source 命令导入备份文件。

MariaDB [(none)]> create database dev;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use dev;
Database changed
MariaDB [dev]> source ~/dev_backup.dump
Query OK, 0 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
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 [dev]> show tables;
+---------------+
| Tables_in_dev |
+---------------+
| items         |
+---------------+
1 row in set (0.00 sec)
MariaDB [dev]> select * from items;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Pencil    |
|    2 | Eraser    |
|    3 | Sharpener |
|    4 | Copy      |
+------+-----------+
4 rows in set (0.00 sec)
MariaDB [dev]>

我们已经成功地从逻辑备份中恢复了我们的 MariaDB 数据库。

对 MariaDB 数据库进行逻辑备份

假设我们有一个包含表名 items 的 MariaDB 数据库 dev 。
我们来看一下。

[root@server1 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dev                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use dev;
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 [dev]> show tables;
+---------------+
| Tables_in_dev |
+---------------+
| items         |
+---------------+
1 row in set (0.00 sec)
MariaDB [dev]> select * from items;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Pencil    |
|    2 | Eraser    |
|    3 | Sharpener |
|    4 | Copy      |
+------+-----------+
4 rows in set (0.00 sec)
MariaDB [dev]>

要备份 MariaDB 数据库，我们有 mysqldump 命令。

[root@server1 ~]# mysqldump -u root -p dev > ~/dev_backup.dump
Enter password:
[root@server1 ~]#

让我们来看看转储文件的内容。

[root@server1 ~]# cat ~/dev_backup.dump
-- MySQL dump 10.14  Distrib 5.5.35-MariaDB, for Linux (x86_64)
-
-- Host: localhost    Database: dev
-- -----------------------------------------------------
-- Server version       5.5.35-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 `items`
-
DROP TABLE IF EXISTS `items`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `items` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
-
-- Dumping data for table `items`
-
LOCK TABLES `items` WRITE;
/*!40000 ALTER TABLE `items` DISABLE KEYS */;
INSERT INTO `items` VALUES (1,'Pencil'),(2,'Eraser'),(3,'Sharpener'),(4,'Copy');
/*!40000 ALTER TABLE `items` 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 2018-07-20 11:10:06
[root@server1 ~]#

我们已经成功地对我们的数据库进行了逻辑备份。