Backup Database

LOAD DATA

数年前に比べると、クラウド環境で適切にハードウエアの冗長化が組まれているので、
オンプレミス環境での運用が減りハードウエア障害等が原因でデータベースのデータが破損する事は殆ど経験する事が無くなりましたが、RDS, BigQuery等で運用していても、人的障害、ウイルス感染 、不正アクセス等からデータを守る事が出来ません。また、自然災害等の大規模災害等が発生するケースも予期せずあるでしょう。勿論、マルチリージョンで構成を組む事で、東京で災害が発生しても、大阪でサービスを継続させる事も可能でしょう。ただ、常時運用しておくとコストも高くつくので、東京で50%、大阪で50%運用しておくか。データだけリモートに同期しておいて、インフラをコード化しておいて、障害発生時にリモートで迅速に環境を構築する等の方法で機会損失を最小限に抑える事が出来るでしょう。勿論、想定損失、コスト、工数、属人性等色々なバランスを鑑みて検討する必要がある事に変わりありませんが。そんな中、バックアップは基本的な運用の一つですし、バックアップがあれば、運用中に失敗してもリカバリー出来る安心感があるので高可用性構成を検討する前に適切なバックアップ方法を選択して検証してリカバリーまで検証しておくと良いでしょう。

Basic Backup on PostgreSQL

データベース全体

root@ubuntu:~/tmp/dump# pg_dump -h 127.0.0.1 POC > dump_all.sql
Password:
root@ubuntu:~/tmp/dump# head -n 60 dump_all.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.5 (Debian 13.5-1.pgdg110+1)
-- Dumped by pg_dump version 14.1 (Ubuntu 14.1-1.pgdg18.04+1)

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: postgis; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;


--
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION postgis IS 'PostGIS geometry and geography spatial types and functions';


SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: p12a-14_01; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public."p12a-14_01" (
    gid integer NOT NULL,
    p12_001 integer,
    p12_002 character varying(100),
    p12_003 character varying(2),
    p12_004 character varying(120),
    p12_005 character varying(100),
    p12_006 character varying(254),
    p12_007 integer,
    geom public.geometry(Point,4326)
);


ALTER TABLE public."p12a-14_01" OWNER TO postgres;

--
-- Name: p12a-14_01_gid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public."p12a-14_01_gid_seq"

特定テーブルのダンプ

root@ubuntu:~/tmp/dump# pg_dump -h 127.0.0.1 -t trains POC > trains_dump.sql
Password:
root@ubuntu:~/tmp/dump# head -n 60 trains_dump.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.5 (Debian 13.5-1.pgdg110+1)
-- Dumped by pg_dump version 14.1 (Ubuntu 14.1-1.pgdg18.04+1)

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;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: trains; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.trains (
    id integer NOT NULL,
    route_name character varying NOT NULL
);


ALTER TABLE public.trains OWNER TO postgres;

--
-- Data for Name: trains; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.trains (id, route_name) FROM stdin;
1       test1
2       test2
3       test3
\.


--
-- PostgreSQL database dump complete
--

Schemaのみダンプ

root@ubuntu:~/tmp/dump# pg_dump -h 127.0.0.1 -s -t trains POC > trains_dump_schema_only.sql
Password:
root@ubuntu:~/tmp/dump# head -n 60 trains_dump_schema_only.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.5 (Debian 13.5-1.pgdg110+1)
-- Dumped by pg_dump version 14.1 (Ubuntu 14.1-1.pgdg18.04+1)

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;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: trains; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.trains (
    id integer NOT NULL,
    route_name character varying NOT NULL
);


ALTER TABLE public.trains OWNER TO postgres;

--
-- PostgreSQL database dump complete
--

データのみダンプ

root@ubuntu:~/tmp/dump# pg_dump -h 127.0.0.1 -a -t trains POC > trains_dump_data_only.sql
Password:
root@ubuntu:~/tmp/dump# head -n 60 trains_dump_data_only.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.5 (Debian 13.5-1.pgdg110+1)
-- Dumped by pg_dump version 14.1 (Ubuntu 14.1-1.pgdg18.04+1)

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;

--
-- Data for Name: trains; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.trains (id, route_name) FROM stdin;
1       test1
2       test2
3       test3
\.


--
-- PostgreSQL database dump complete
--

Basic Backup on MySQL

データベース全体 (全てのデータベース)

  • 特定データベースのみ取得する場合は、–databases <データベース名>で指定してください。
  • 全てのテーブルをロックしてデータベース全体の バックアップを取得

mysqldump –host=127.0.0.1 –user=root –password=password –source-data=2 \
–hex-blob –default-character-set=utf8mb4 –all-databases \
–lock-all-tables > mysql_backup_with_no_transaction_dump.sql

~/backup [ 8:58:20]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --hex-blob --default-character-set=utf8mb4 --all-databases \
> --lock-all-tables > mysql_backup_with_no_transaction_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
~/backup [ 8:58:40]> head -n 60 mysql_backup_with_no_transaction_dump.sql
-- MySQL dump 10.13  Distrib 8.0.27, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database:
-- ------------------------------------------------------
-- Server version       8.0.27

/*!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' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!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 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=1232;

--
-- Current Database: `mysql`
--

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

USE `mysql`;

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `columns_priv` (
  `Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `columns_priv`
--

LOCK TABLES `columns_priv` WRITE;
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */;
UNLOCK TABLES;
  • InnoDBのトランザクションを使用してデータベース全体の バックアップを取得

mysqldump –host=127.0.0.1 –user=root –password=password –source-data=2 \
–hex-blob –default-character-set=utf8mb4 –all-databases \
–single-transaction > mysql_backup_with_transaction_dump.sql

~/backup [ 9:02:38]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --hex-blob --default-character-set=utf8mb4 --all-databases \
-single-> --single-transaction > mysql_backup_with_transaction_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
~/backup [ 9:02:43]> head -n 60 mysql_backup_with_transaction_dump.sql
-- MySQL dump 10.13  Distrib 8.0.27, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database:
-- ------------------------------------------------------
-- Server version       8.0.27

/*!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' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!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 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=1232;

--
-- Current Database: `mysql`
--

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

USE `mysql`;

--
-- Table structure for table `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `columns_priv` (
  `Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `columns_priv`
--

LOCK TABLES `columns_priv` WRITE;
/*!40000 ALTER TABLE `columns_priv` DISABLE KEYS */;
/*!40000 ALTER TABLE `columns_priv` ENABLE KEYS */;
UNLOCK TABLES;

MySQLのレプリケーションは、Master, Slaveと命名されて長い間使われてきましたが、命名も変更される過程にあるのでオプションの変更にも留意しておく必要有りますね。

master-data
dump-slave

参照:MySQL Terminology Updates

特定テーブルのダンプ

mysqldump –host=127.0.0.1 –user=root –password=password –source-data=2 \
–hex-blob –default-character-set=utf8mb4 \
–single-transaction POC test > mysql_backup_with_transaction_specific_table_dump.sql

~/backup [13:31:12]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --hex-blob --default-character-set=utf8mb4 \
> --single-transaction POC test > mysql_backup_with_transaction_specific_table_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
~/backup [13:31:14]> head -n 60 mysql_backup_with_transaction_specific_table_dump.sql
-- MySQL dump 10.13  Distrib 8.0.27, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database: POC
-- ------------------------------------------------------
-- Server version       8.0.27

/*!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 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=1425;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `note` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1,'test1'),(2,'test2'),(3,'test3');
/*!40000 ALTER TABLE `test` 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 2022-01-14 13:31:14

Schemaのみダンプ

mysqldump –host=127.0.0.1 –user=root –password=password –source-data=2 \
–no-data –hex-blob –default-character-set=utf8mb4 \
–single-transaction POC test > mysql_backup_with_transaction_specific_table_with_nodata_dump.sql



~/backup [13:26:48]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --no-data --hex-blob --default-character-set=utf8mb4 \
> --single-transaction POC test > mysql_backup_with_transaction_specific_table_with_nodata_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
~/backup [13:27:54]> head -n 60 mysql_backup_with_transaction_specific_table_with_nodata_dump.sql
-- MySQL dump 10.13  Distrib 8.0.27, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database: POC
-- ------------------------------------------------------
-- Server version       8.0.27

/*!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 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=1425;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
  `id` int DEFAULT NULL,
  `note` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!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 2022-01-14 13:27:54
~/backup [13:28:04]>

データのみダンプ

mysqldump –host=127.0.0.1 –user=root –password=password –source-data=2 \
–no-create-info –hex-blob –default-character-set=utf8mb4 \
–single-transaction POC test > mysql_backup_with_transaction_specific_table_with_data_only_dump.sql

~/backup [13:31:30]> mysqldump --host=127.0.0.1 --user=root --password=password --source-data=2 \
> --no-create-info --hex-blob --default-character-set=utf8mb4 \
> --single-transaction POC test > mysql_backup_with_transaction_specific_table_with_data_only_dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
~/backup [13:33:41]> head -n 60 mysql_backup_with_transaction_specific_table_with_data_only_dump.sql
-- MySQL dump 10.13  Distrib 8.0.27, for Linux (x86_64)
--
-- Host: 127.0.0.1    Database: POC
-- ------------------------------------------------------
-- Server version       8.0.27

/*!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 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=1425;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1,'test1'),(2,'test2'),(3,'test3');
/*!40000 ALTER TABLE `test` 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 2022-01-14 13:33:41

参照: 7.4 バックアップへの mysqldump の使用

留意事項

  • postgresql.conf, my.cnf 等データベースによって必要な設定ファイルのバックアップも取得しておきましょう。
  • WAL、バイナリーログ等はPTR (Point in Time Recovery)に利用可能です、サービス内容によってリカバリーポリシーを策定し、詳細はマニュアルを読んで把握して検証しておきましょう。
  • バックアップ中にALTER TABLE, CREATE TABLE, DROP TABLE,RENAME TABLE, TRUNCATE TABLE等のDDLを実行した場合に、データの整合性が失われる事があります。留意しておきましょう。

特定テーブルのデータを抽出する場合

  • PostgreSQL

抽出する場合

root@ubuntu:~/backup# psql -h 127.0.0.1 -U postgres POC -c "COPY (select * from trains) \
> to '/tmp/poc_trains.tsv' DELIMITER E'\\t';"
Password for user postgres:
COPY 3

取り込む場合

postgres> \COPY <取り込み用テーブル> FROM '/tmp/poc_trains.tsv' WITH DELIMITER E'\t';

  • MySQL

抽出する場合

~/git/rdbms-docker/mysql [14:13:18]> mysql -h 127.0.0.1 -u root -p -e " \
> SELECT * INTO OUTFILE '/tmp/test.tsv' \
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' \
> LINES TERMINATED BY '\n' \
> FROM POC.test;"
Enter password:
~/git/rdbms-docker/mysql [14:13:41]>

ファイルの書き出しを可能にする為に以下のオプションを設定しています。

mysql> show global variables like 'secure_file%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.00 sec)

取り込む場合

mysql> LOAD DATA INFILE '/tmp/test.tsv' into table test FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
Query OK, 3 rows affected (0.01 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

リストア編

まとめ資料(詳細はマニュアルを参照下さい)

カテゴリー:

最近のコメント

表示できるコメントはありません。