原始网页:https://www.cockroachlabs.com/docs/stable/sql-dump.html


cockroach dump命令可导出重新创建表、视图和序列所需的SQL语句。该命令可用于集群数据的备份和导出,且在经过简单的调整以后可以导入到其他关系型数据库。

TIPS: CockroachDB用户也可以使用企业版的BACKUP功能来备份数据。

用户须知

cockroach dump执行以后:

概要

# Dump the schemas and data of specific tables to stdout:
cockroach dump <database> <table> <table...> <flags>

# Dump just the data of specific tables to stdout:
cockroach dump <database> <table> <table...> --dump-mode=data <other flags>

# Dump just the schemas of specific tables to stdout:
cockroach dump <database> <table> <table...> --dump-mode=schema <other flags>

# Dump the schemas and data of all tables in a database to stdout:
cockroach dump <database> <flags>

# Dump just the schemas of all tables in a database to stdout:
cockroach dump <database> --dump-mode=schema <other flags>

# Dump just the data of all tables in a database to stdout:
cockroach dump <database> --dump-mode=data <other flags>

# Dump to a file:
cockroach dump <database> <table> <flags> > dump-file.sql

# View help:
cockroach dump --help

Flags

通用

Flag 简介
--as-of 导出指定时间戳的表schema或是数据。

历史数据仅在垃圾收集窗口中可见,该窗口由表的ttlseconds复制配置控制(默认情况下为25小时)。如果Flag指定的时间戳早于该时间窗口,则导出失败。

Default: 当前时间
--dump-mode 指定导出的是表和视图的schema,或是表数据,或是两者。

若只导出表和视图schema,则配置值为schema;若只导出表数据,则配置值为data;若导出两者,则配置值为both

表和视图的schema将按顺序导出以保证SQL能够执行成功。例如,若表2在表1上存在外键约束,则表1的schema语句在表2的schema语句之前。

Default: both
--echo-sql (New in v1.1)显示命令后工具成功发送的SQL语句。

客户端连接

Flag 简介
--host 指定服务器ip地址,可以是集群的任意节点。

环境变量COCKROACH_HOST
默认localhost
--port
-p
指定服务器端口

环境变量COCKROACH_PORT
默认26257
--user
-u
指定SQL用户

环境变量COCKROACH_USER
默认root
--insecure 使用非安全模式的连接

环境变量COCKROACH_INSECURE
默认false
--certs-dir 指定存放着CA证书和客户端证书密钥的目录

环境变量COCKROACH_CERTS_DIR
默认${HOME}/.cockroach-certs/
--url CockroachDB的connection URL地址。

环境变量COCKROACH_URL
默认:no URL

更多细节可以查看:客户端连接参数

NOTE: --user指定的用户需要具备备份数据相关的表的SELECT语句权限。

日志

cockroach dump默认输出日志错误到stderr,更丰富的日志功能可查看:日志行为

示例

NOTE: 以下示例使用cockroach gen命令生成了测试集群的库startrek,同时测试用户maxroach拥有所有表的SELECT权限。

导出表的schema和数据

cockroach dump startrek episodes --insecure --user=maxroach > backup.sql
cat backup.sql

CREATE TABLE episodes (
    id INT NOT NULL,
    season INT NULL,
    num INT NULL,
    title STRING NULL,
    stardate DECIMAL NULL,
    CONSTRAINT "primary" PRIMARY KEY (id),
    FAMILY "primary" (id, season, num),
    FAMILY fam_1_title (title),
    FAMILY fam_2_stardate (stardate)
);

INSERT INTO episodes (id, season, num, title, stardate) VALUES
    (1, 1, 1, 'The Man Trap', 1531.1),
    (2, 1, 2, 'Charlie X', 1533.6),
    (3, 1, 3, 'Where No Man Has Gone Before', 1312.4),
    (4, 1, 4, 'The Naked Time', 1704.2),
    (5, 1, 5, 'The Enemy Within', 1672.1),
    (6, 1, 6, e'Mudd\'s Women', 1329.8),
    (7, 1, 7, 'What Are Little Girls Made Of?', 2712.4),
    (8, 1, 8, 'Miri', 2713.5),
    (9, 1, 9, 'Dagger of the Mind', 2715.1),
    (10, 1, 10, 'The Corbomite Maneuver', 1512.2),
    ...

只导出特定表的schema

cockroach dump startrek episodes --insecure --user=maxroach --dump-mode=schema > backup.sql
cat backup.sql

CREATE TABLE episodes (
    id INT NOT NULL,
    season INT NULL,
    num INT NULL,
    title STRING NULL,
    stardate DECIMAL NULL,
    CONSTRAINT "primary" PRIMARY KEY (id),
    FAMILY "primary" (id, season, num),
    FAMILY fam_1_title (title),
    FAMILY fam_2_stardate (stardate)
);

只导出特定表的数据

cockroach dump startrek episodes --insecure --user=maxroach --dump-mode=data > backup.sql
cat backup.sql

INSERT INTO episodes (id, season, num, title, stardate) VALUES
    (1, 1, 1, 'The Man Trap', 1531.1),
    (2, 1, 2, 'Charlie X', 1533.6),
    (3, 1, 3, 'Where No Man Has Gone Before', 1312.4),
    (4, 1, 4, 'The Naked Time', 1704.2),
    (5, 1, 5, 'The Enemy Within', 1672.1),
    (6, 1, 6, e'Mudd\'s Women', 1329.8),
    (7, 1, 7, 'What Are Little Girls Made Of?', 2712.4),
    (8, 1, 8, 'Miri', 2713.5),
    (9, 1, 9, 'Dagger of the Mind', 2715.1),
    (10, 1, 10, 'The Corbomite Maneuver', 1512.2),
    ...

导出所有表

cockroach dump startrek --insecure --user=maxroach > backup.sql
cat backup.sql

CREATE TABLE episodes (
    id INT NOT NULL,
    season INT NULL,
    num INT NULL,
    title STRING NULL,
    stardate DECIMAL NULL,
    CONSTRAINT "primary" PRIMARY KEY (id),
    FAMILY "primary" (id, season, num),
    FAMILY fam_1_title (title),
    FAMILY fam_2_stardate (stardate)
);

CREATE TABLE quotes (
    quote STRING NULL,
    characters STRING NULL,
    stardate DECIMAL NULL,
    episode INT NULL,
    INDEX quotes_episode_idx (episode),
    FAMILY "primary" (quote, rowid),
    FAMILY fam_1_characters (characters),
    FAMILY fam_2_stardate (stardate),
    FAMILY fam_3_episode (episode)
);

INSERT INTO episodes (id, season, num, title, stardate) VALUES
    (1, 1, 1, 'The Man Trap', 1531.1),
    (2, 1, 2, 'Charlie X', 1533.6),
    (3, 1, 3, 'Where No Man Has Gone Before', 1312.4),
    (4, 1, 4, 'The Naked Time', 1704.2),
    (5, 1, 5, 'The Enemy Within', 1672.1),
    (6, 1, 6, e'Mudd\'s Women', 1329.8),
    (7, 1, 7, 'What Are Little Girls Made Of?', 2712.4),
    (8, 1, 8, 'Miri', 2713.5),
    (9, 1, 9, 'Dagger of the Mind', 2715.1),
    (10, 1, 10, 'The Corbomite Maneuver', 1512.2),
    ...

INSERT INTO quotes (quote, characters, stardate, episode) VALUES
    ('"... freedom ... is a worship word..." "It is our worship word too."', 'Cloud William and Kirk', NULL, 52),
    ('"Beauty is transitory." "Beauty survives."', 'Spock and Kirk', NULL, 72),
    ('"Can you imagine how life could be improved if we could do away with jealousy, greed, hate ..." "It can also be improved by eliminating love, tenderness, sentiment -- the other side of the coin"', 'Dr. Roger Corby and Kirk', 2712.4, 7),
    ...

因权限不足导致导出失败

用户若没有导出数据的表的SELECT权限,则导出操作失败:

cockroach dump startrek episodes --insecure --user=leslieroach > backup.sql

Error: pq: user leslieroach has no privileges on table episodes
Failed running "dump"

从备份文件恢复特定表的数据

启动内置的SQL客户端,查看表数据

cockroach sql --insecure --execute="SELECT * FROM db1.dump_test"

+--------------------+------+
|         id         | name |
+--------------------+------+
| 225594758537183233 | a    |
| 225594758537248769 | b    |
| 225594758537281537 | c    |
| 225594758537314305 | d    |
| 225594758537347073 | e    |
| 225594758537379841 | f    |
| 225594758537412609 | g    |
| 225594758537445377 | h    |
| 225594991654174721 | i    |
| 225594991654240257 | j    |
| 225594991654273025 | k    |
| 225594991654305793 | l    |
| 225594991654338561 | m    |
| 225594991654371329 | n    |
| 225594991654404097 | o    |
| 225594991654436865 | p    |
+--------------------+------+
(16 rows)

指定时间戳2017-03-07 19:55:00,生成查询,查看该时间戳之前的表数据:

cockroach sql --insecure --execute="SELECT * FROM db1.dump_test AS OF SYSTEM TIME '2017-03-07 19:55:00'"

+--------------------+------+
|         id         | name |
+--------------------+------+
| 225594758537183233 | a    |
| 225594758537248769 | b    |
| 225594758537281537 | c    |
| 225594758537314305 | d    |
| 225594758537347073 | e    |
| 225594758537379841 | f    |
| 225594758537412609 | g    |
| 225594758537445377 | h    |
+--------------------+------+
(8 rows)

使用带--as-ofFlag的cockroach dump命令导出2017-03-07 19:55:00之前的表数据:

cockroach dump db1 dump_test --insecure --dump-mode=data --as-of='2017-03-07 19:55:00'
INSERT INTO dump_test (id, name) VALUES
    (225594758537183233, 'a'),
    (225594758537248769, 'b'),
    (225594758537281537, 'c'),
    (225594758537314305, 'd'),
    (225594758537347073, 'e'),
    (225594758537379841, 'f'),
    (225594758537412609, 'g'),
    (225594758537445377, 'h');

对比上述查询结果,可见导出操作正确执行。

已知缺陷

cockroach dump命令为指定表创建一个dump文件,该表允许有对其自身的外键引用,或者是具有循环外键依赖性的一组表(例如,表a依赖于表b,表b依赖于a)。但是需要手动编辑dump文件,删除CREATE TABLE语句中的外键定义,并在INSERT语句后添加ALTER TABLE ... ADD CONSTRAINT语句补充外键约束,该dump文件才能被CockroachDB成功执行。