MariaDB: テーブル単位のバックアップとリストア

## 前提条件
ユーザー　scott
パスワード　tiger123
データベース　city
とします。

作成方法

```text
$ sudo mysql -uroot
MariaDB [(none)]> create user scott@localhost identified by 'tiger123';
Query OK, 0 rows affected (0.044 sec)

MariaDB [(none)]> create database city;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> grant all on city.* to scott@localhost;
Query OK, 0 rows affected (0.004 sec)
```

テーブルが次のようになっているとします。

```text
$ mysql -uscott -ptiger123 city
MariaDB [city]> show tables;
+----------------+
| Tables_in_city |
+----------------+
| aichi          |
| okayama        |
| tochigi        |
+----------------+
```

## バックアップ

```bash:go_backup.sh
mysqldump -uscott -ptiger123 city okayama > okayama.sql
mysqldump -uscott -ptiger123 city tochigi > tochigi.sql
mysqldump -uscott -ptiger123 city aichi > aichi.sql
```

## テーブルの削除

```text
MariaDB [city]> drop table aichi;
Query OK, 0 rows affected (0.020 sec)

MariaDB [city]> drop table okayama;
Query OK, 0 rows affected (0.060 sec)

MariaDB [city]> drop table tochigi;
Query OK, 0 rows affected (0.019 sec)

MariaDB [city]> show tables;
Empty set (0.001 sec)
```

## テーブルのリストア

```bash:go_restore.sh
mysql -uscott -ptiger123 city < okayama.sql
mysql -uscott -ptiger123 city < tochigi.sql
mysql -uscott -ptiger123 city < aichi.sql
```

## データの確認

```text
MariaDB [city]> show tables;
+----------------+
| Tables_in_city |
+----------------+
| aichi          |
| okayama        |
| tochigi        |
+----------------+
3 rows in set (0.001 sec)
```

## バックアップデータ

```text:aichi.sql
-- MariaDB dump 10.19  Distrib 10.11.2-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: city
-- ------------------------------------------------------
-- Server version	10.11.2-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 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 */;

--
-- Table structure for table `aichi`
--

DROP TABLE IF EXISTS `aichi`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `aichi` (
  `id` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `date_mod` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `aichi`
--

LOCK TABLES `aichi` WRITE;
/*!40000 ALTER TABLE `aichi` DISABLE KEYS */;
INSERT INTO `aichi` VALUES
('t2381','名古屋',52761,'2003-04-30'),
('t2382','豊橋',47195,'2003-05-10'),
('t2383','岡崎',21674,'2003-06-14'),
('t2384','一宮',83912,'2003-09-09'),
('t2385','蒲郡',42791,'2003-08-04'),
('t2386','常滑',35287,'2003-01-21'),
('t2387','大府',81246,'2003-07-23'),
('t2388','瀬戸',25791,'2003-10-26'),
('t2389','犬山',54139,'2003-12-15');
/*!40000 ALTER TABLE `aichi` 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 2023-03-21 15:47:17
```

```text:okayama.sql
-- MariaDB dump 10.19  Distrib 10.11.2-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: city
-- ------------------------------------------------------
-- Server version	10.11.2-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 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 */;

--
-- Table structure for table `okayama`
--

DROP TABLE IF EXISTS `okayama`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `okayama` (
  `id` varchar(10) NOT NULL,
  `name` text DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `date_mod` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `okayama`
--

LOCK TABLES `okayama` WRITE;
/*!40000 ALTER TABLE `okayama` DISABLE KEYS */;
INSERT INTO `okayama` VALUES
('t3321','岡山',725139,'2002-3-9'),
('t3322','倉敷',417628,'2002-5-12'),
('t3323','津山',891654,'2002-7-21'),
('t3324','玉野',265981,'2002-11-12'),
('t3325','笠岡',284597,'2002-4-24'),
('t3326','井原',671942,'2002-2-8'),
('t3327','総社',265481,'2002-8-3'),
('t3328','高梁',792356,'2002-3-14'),
('t3329','新見',315892,'2002-1-17');
/*!40000 ALTER TABLE `okayama` 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 2023-03-21 15:47:17
```

```text:tochigi.sql
-- MariaDB dump 10.19  Distrib 10.11.2-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: city
-- ------------------------------------------------------
-- Server version	10.11.2-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 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 */;

--
-- Table structure for table `tochigi`
--

DROP TABLE IF EXISTS `tochigi`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tochigi` (
  `id` varchar(10) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `population` int(11) DEFAULT NULL,
  `date_mod` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `tochigi`
--

LOCK TABLES `tochigi` WRITE;
/*!40000 ALTER TABLE `tochigi` DISABLE KEYS */;
INSERT INTO `tochigi` VALUES
('t0921','宇都宮',41295,'2003-08-12'),
('t0922','小山',38756,'2003-05-15'),
('t0923','佐野',71294,'2003-06-08'),
('t0924','足利',27138,'2003-07-21'),
('t0925','日光',74682,'2003-04-19'),
('t0926','下野',82951,'2003-10-14'),
('t0927','さくら',96823,'2003-05-24'),
('t0928','矢板',57926,'2003-02-12'),
('t0929','真岡',64187,'2003-11-14'),
('t0930','栃木',82354,'2003-07-04'),
('t0931','大田原',72681,'2003-09-17'),
('t0932','鹿沼',23749,'2003-07-20'),
('t0933','那須塩原',12759,'2003-03-12'),
('t0934','那須烏山',62531,'2003-08-17');
/*!40000 ALTER TABLE `tochigi` 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 2023-03-21 15:47:17
```

## テーブルを削除しないでデータを削除して確認する方法

```text
MariaDB [city]> delete from aichi;
Query OK, 9 rows affected (0.005 sec)

MariaDB [city]> delete from okayama;
Query OK, 9 rows affected (0.005 sec)

MariaDB [city]> delete from tochigi;
Query OK, 14 rows affected (0.003 sec)

MariaDB [city]> select count(*) from aichi;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.000 sec)

MariaDB [city]> select count(*) from okayama;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.000 sec)

MariaDB [city]> select count(*) from tochigi;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.000 sec)
```

リストア後

```text
MariaDB [city]> select count(*) from aichi;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.001 sec)

MariaDB [city]> select count(*) from okayama;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.001 sec)

MariaDB [city]> select count(*) from tochigi;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.001 sec)
```