LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@ekzemplaro(内田 雅智)

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

Last updated at Posted at 2023-03-21

前提条件

ユーザー　scott
パスワード　tiger123
データベース　city
とします。

作成方法

$ 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)

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

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

バックアップ

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

テーブルの削除

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)

テーブルのリストア

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

データの確認

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

バックアップデータ

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
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
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

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

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)

リストア後

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)
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?