Backup Database

数年前に比べると、クラウド環境で適切にハードウエアの冗長化が組まれているので、
オンプレミス環境での運用が減りハードウエア障害等が原因でデータベースのデータが破損する事は殆ど経験する事が無くなりましたが、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
--

pg_dump実行中のDDLに関しての留意事項

１：以下の様にpg_dumpを実行

pg_dump -h 127.0.0.1 -p 5432 --compress=9 -U postgres -v POC -f dump.20220309

２：1を実行中にTruncateを実行5秒かかる。（以下の様にLOCKを取得待ちである事を確認）

POC=# begin transaction;
BEGIN
Time: 1.167 ms
POC=*# truncate table truncate1,truncate2 RESTART IDENTITY;
TRUNCATE TABLE
Time: 5339.652 ms (00:05.340)

参考：pg_dump実行中以外のTruncate処理時間 (18 ms)

POC=# begin transaction;
BEGIN
Time: 0.601 ms
POC=*# truncate table truncate1,truncate2 RESTART IDENTITY;
TRUNCATE TABLE
Time: 18.145 ms

pg_dump中にDDLを実行すると、DDLが待たされてそれに続く処理がDDLに待たされるので注意。

When we do a pg_dump and right afterwards truncate a table which is in the dump, what happens?

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と命名されて長い間使われてきましたが、命名も変更される過程にあるのでオプションの変更にも留意しておく必要有りますね。

参照：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 の使用

MySQLではバックアップを取得する為に、MySQL5.7以降から並列スレッドでバックアップ取得可能なmysqlpumpというツールを用意しています。バックアップをより高速に取得したい場合等に検討してみると良いでしょう。

[root@misc01 admin]# mysqlpump --single-transaction --default-parallelism=10 -u root -p > pump_test_with_transaction.sql
Enter password: 
Dump progress: 0/2 tables, 3/4 rows
Dump progress: 16/21 tables, 24973/1000780 rows
Dump progress: 16/21 tables, 56723/1000780 rows
Dump progress: 17/22 tables, 86474/1000782 rows
Dump progress: 38/44 tables, 113280/1000833 rows
Dump progress: 41/46 tables, 142536/1000838 rows
Dump progress: 41/46 tables, 166786/1000838 rows
Dump progress: 41/46 tables, 190286/1000838 rows
<snip>
Dump progress: 100/101 tables, 1124886/1099650 rows
Dump progress: 100/101 tables, 1126886/1099650 rows
Dump progress: 100/101 tables, 1128136/1099650 rows
Dump progress: 100/101 tables, 1128386/1099650 rows
Dump progress: 100/101 tables, 1128636/1099650 rows
Dump progress: 100/101 tables, 1129886/1099650 rows
Dump progress: 100/101 tables, 1130196/1099650 rows
Dump completed in 318316 milliseconds
[root@misc01 admin]# 

参照：4.5.6 mysqlpump — データベースバックアッププログラム

留意事項

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

--flush-logsや--master-dataと共に--single-transactionオプションを利用すると、バイナリログ情報をテーブルのデータと同期するため、バックアップ開始時に一瞬だけグローバルリードロックを取る必要があります。ところが、グローバルリードロックは実行中のすべてのクエリによりブロックされ、また、後続のクエリをすべてブロックしてしまいます。そのため、通常は一瞬で終わる処理なのですが、長時間実行中のクエリがあると、後続の他のクエリが止まってしまうという問題が生じてしまうことになります。

もうひとつの注意点としては、--flush-logsや--master-dataと共に--single-transactionオプションを利用すると、トランザクションを開始する前にFLUSH TABLES WITH READ LOCKが実行されます。FLUSH TABLES WITH READ LOCKは、現在実行中のすべてのクエリが完了するまでブロックされます。ブロックされている間、FLUSH TABLES WITH READ LOCKより後に実行されたクエリをブロックします。そのため、バッチ処理などにより長時間実行されているクエリがある時間帯に、mysqldumpを--single-transactionと前述したオプションの組み合わせ(いずれかひとつ、あるいは両方)で実行すると、長時間他のクエリも止まってしまうことになりますので注意して下さい。
※ FLUSH TABLES WITH READ LOCKによって取られるロックは,Global Read Lockと呼ばれるもので、サーバー全体を読み取り専用にします。

参照：13.7.8.3 FLUSH ステートメント

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

• 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

mysqldump実行中のDDLに関しての留意事項

MySQLの場合もInnoDBにてトランザクション利用してバックアップを取得する時は以下のDDLはNGという事で。留意しておく必要があります。これはPostgreSQLでpg_dumpを利用する場合も同じです。

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

4.5.4 mysqldump — A Database Backup Program

リストア編

Backup Database

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

MySQLとPostgreSQLの基本的なバックアップ比較 from Shinya Sugiyama