backup and restore MonetDB

备份需要注意的是一致性问题. 前面我们测试了, monetdb从现象上来看是repeatable read隔离级别的, 所以备份如果在一个事务中进行的话不存在不一致的问题.

例如以下截取, 可以看出备份确实是一个事务中进行的.

postgres@150-> msqldump test
-- msqldump dump database Thu Aug 14 15:35:29 2014
-- MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://150.sky-mobi.com:50000/test'
START TRANSACTION;
CREATE SCHEMA "datacell" AUTHORIZATION "monetdb";
CREATE SCHEMA "rdf" AUTHORIZATION "monetdb";
CREATE SEQUENCE "sys"."seq_6544" AS INTEGER;
SET SCHEMA "rdf";
CREATE TABLE "rdf"."graph" (
        "gname" CHARACTER LARGE OBJECT,
        "gid"   INTEGER
);
SET SCHEMA "sys";
CREATE TABLE "sys"."a" (
        "id"   INTEGER       NOT NULL DEFAULT next value for "sys"."seq_6544",
        "info" VARCHAR(32),
        CONSTRAINT "a_id_pkey" PRIMARY KEY ("id")
);
COPY 536870912 RECORDS INTO "sys"."a" FROM stdin USING DELIMITERS '\t','\n','"';
....

但是, 如果要防止备份期间, 数据库被变更(例如迁移数据库, 需要一致性数据), 那么可以锁库, 然后备份再发布.

Since MonetDB does not provide global locking schemes, a time-consuming dump operation may become invalidated by a concurrent update query.

所以如果需要全库一致性备份, 需要将数据库转入维护模式. 备份完成再发布. 例如 : 

锁: monetdb lock 库名

发布: monetdb release 库名

monetdb提供的备份命令行攻击msqldump.

[root@150 ~]# msqldump --help
Usage: msqldump [ options ] [ dbname ]

Options are:
 -h hostname | --host=hostname    host to connect to
 -p portnr   | --port=portnr      port to connect to
 -u user     | --user=user        user id
 -d database | --database=database  database to connect to
 -f          | --functions        dump functions
 -t table    | --table=table      dump a database table
 -D          | --describe         describe database
 -N          | --inserts          use INSERT INTO statements
 -q          | --quiet            don't print welcome message
 -X          | --Xdebug           trace mapi network interaction
 -?          | --help             show this usage message
--functions and --table are mutually exclusive

一般可以把用户密码配置在~/.monetdb文件中, 然后使用msqldump就不需要输入用户密码了.

[root@150 ~]# vi ~/.monetdb
user=monetdb
password=monetdb
[root@150 ~]# chmod 400 ~/.monetdb 

例如

一般备份重定向到压缩命令或文件即可.

备份整个数据库的DDL

[root@150 ~]# msqldump -D 库名

备份表的DDL

[root@150 ~]# msqldump -t c -D 库名
-- msqldump describe table c Thu Aug 14 14:55:41 2014
-- MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://150.sky-mobi.com:50000/test'
CREATE TABLE "sys"."c" (
        "id"   INTEGER,
        "info" VARCHAR(64),
        "c1"   VARCHAR(64)
);

备份整个数据库, 包括数据

msqldump 库名

备份函数定义

msqldump -f 库名

备份数据, 默认使用COPY方式输出

[root@150 ~]# msqldump -f test
-- msqldump dump functions Thu Aug 14 14:58:24 2014
-- MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://150.sky-mobi.com:50000/test'
[root@150 ~]# msqldump -t c test
-- msqldump dump table c Thu Aug 14 14:59:00 2014
-- MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://150.sky-mobi.com:50000/test'
CREATE TABLE "sys"."c" (
        "id"   INTEGER,
        "info" VARCHAR(64),
        "c1"   VARCHAR(64)
);
COPY 6 RECORDS INTO "sys"."c" FROM stdin USING DELIMITERS '\t','\n','"';
1       "test"  "test"
1       "test"  "test"
1       "test"  "test"
1       "test"  "test"
1       "test"  "test"
100     "test"  "test"

使用INSERT方式输出

[root@150 ~]# msqldump -t c -N test
-- msqldump dump table c Thu Aug 14 14:59:07 2014
-- MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://150.sky-mobi.com:50000/test'
CREATE TABLE "sys"."c" (
        "id"   INTEGER,
        "info" VARCHAR(64),
        "c1"   VARCHAR(64)
);
INSERT INTO "sys"."c" VALUES (1, 'test', 'test');
INSERT INTO "sys"."c" VALUES (1, 'test', 'test');
INSERT INTO "sys"."c" VALUES (1, 'test', 'test');
INSERT INTO "sys"."c" VALUES (1, 'test', 'test');
INSERT INTO "sys"."c" VALUES (1, 'test', 'test');
INSERT INTO "sys"."c" VALUES (100, 'test', 'test');

还原则在mclient下直接运行备份文件即可.

例如

mclient 库名

\< 文件名

[参考]
1. man msqldump

2. man mclient

3. https://www.monetdb.org/Documentation/UserGuide/DumpRestore