SQLのdumpについてまとめています。
SQLのdumpコマンドはデータバックアップ用のコマンド
いくつかのデータベースでは、dumpコマンドがデータベースや指定したテーブルのファイルバックアップ用コマンドになっています。たとえば、MySQLでは、データバックアップ用のmysqldumpコマンドがlinuxのコマンドとして用意されています。
以下のコマンドは、ユーザ名user、パスワードpasswordでデータベースに接続し、データベースdb1のダンプデータをbackup.sqlというファイルに出力します。
$ mysqldump -u user -p password db1 > backup.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.
上記のようにコマンドラインに直接パスワードを指定することが可能ですが、セキュリティ的に問題のある旨が警告として表示されます。
backup.sqlの内容はこのようになっています。（MySQL8.0の場合）
-- MySQL dump 10.13 Distrib 8.0.19, for Linux (x86_64) -- -- Host: localhost Database: employees -- ------------------------------------------------------ -- Server version 8.0.19 /*!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 */; -- -- Temporary view structure for view `current_dept_emp` -- DROP TABLE IF EXISTS `current_dept_emp`; /*!50001 DROP VIEW IF EXISTS `current_dept_emp`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `current_dept_emp` AS SELECT 1 AS `emp_no`, 1 AS `dept_no`,
backup.sqlをmysqlコマンドにリダイレクトすることで、データベース構造とデータをリストアすることが可能です。
mysql -u root -pmy-secret-pw employees < backup.sql mysql: [Warning] Using a password on the command line interface can be insecure. $ mysql -u root -pmy-secret-pw mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | employees | | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 6 rows in set (0.06 sec)
なお、create databaseで別のデータベースを作成しておき、新規に作成したデータベース名をmysqlコマンドで指定することでデータベースの複製を作ることも可能です。
下記は、データベースemployees2を作成し、backup.sqlの内容をemployees2にリダイレクトした例です。
$ mysql -u root -pmy-secret-pw mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database employees2; Query OK, 1 row affected (0.21 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | employees | | employees2 | | information_schema | | mysql | | performance_schema | | sys | | testdb | +--------------------+ 7 rows in set (0.06 sec) $ mysql -u root -pmy-secret-pw employees2 < backup.sql mysql: [Warning] Using a password on the command line interface can be insecure.
select文で、リストアしたemployees2を確認してみると、バックアップ元のデータベースemployeesのデータが複製されていることがわかります。
mysql> select * from employees2.employees limit 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec)
【関連記事】
▶MySQLのデータバックアップなら専用コマンドのmysqldumpを
PostgreSQLは、pg_dumpコマンドでデータをバックアップ
PostgreSQLでは、pg_dumpというバックアップ用のコマンドが用意されています。dump機能は標準SQLで定義されていないため、データベースによってコマンド名や機能が大きくことなります。
以下のコマンドは、ユーザ名postgres、パスワード省略でデータベースサーバに接続し、データベースdvdrentalの内容をファイルdump.sqlに出力します。
$ pg_dump -U postgres dvdrental > dump.sql
出力されたdump.sqlの内容はこのようになっています。
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.6.17
-- Dumped by pg_dump version 9.6.17
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
：
：
バックアップしたファイルのリストア専用コマンドは用意されておらず、ダンプファイルを以下のようにpsqlコマンドにリダイレクトすることでデータベースやテーブルが復元できます。
psql -U postgres < dump.sql
まとめ
ポテパンダの一言メモ
- SQLのdumpは、いくつかのデータベースソフトで、データバックアップ用のコマンドとして用意されている
- 標準SQLには定義されていないため、コマンド名や機能は統一されてない
- バックアップしたデータを使って、データベースを複製することが可能
