MonetDB用户指南4——(资源)Dump and restore

An SQL dump of a database is a common method to safely store away a snapshot of the database for archival purposes or to migrate data between database instances, e.g. between two major system releases. The content of a SQL dump is a large collection of SQL commands in ASCII. Running the script will recreate the database in the same state as it was when the dump was created. Since MonetDB does not provide global locking schemes, a time-consuming dump operation may become invalidated by a concurrent update query.

The primary tool to consider for making an ASCII dump is msqldump, which includes a wide variety of options. A simplified version is available as part of the mclient program.

SQL转储是对数据库镜像或者迁移的常用的方法。如在两个版本之间。SQLdump是ASCII编码的SQL的集合。执行这些脚本，可以重新创建一样的数据库。MonetDB不提供全局锁，并发情况下这个转储可能无效。

Linux platforms

Linux平台

Consider you have already installed the SQL tutorial database voc and wish to transport it to another machine. Then the following client command line option generates the dump file.

缺人安装了示例VOC库，并且希望迁移到另外一台机器。下面的客户端命令产生一个dump文件。

shell> mclient -lsql --database=voc --dump >/tmp/voc.sql

You can inspect the file /tmp/voc.sql to confirm that indeed a readable database dump is available. If storage space is a problem, you can readily pipe the output of this command to a compression tool or sent it directly to another machine in your environment using conventional Linux tools. As a baseline, move the file over to the new machine. The monetdb tool can be used to create the database on that machine. Once done, it suffices to feed the dump file tomclient to populate the database.

可以检察/tmp/voc.sql，确认它是可读的。如果空间是问题，可以用管道把输出给一个压缩工具，或者使用linux工具直接发送到另外一台机器，移动数据文件到新机器。MonetDB可以用于创建数据库。

Creation of the dump respects your credentials, which means you only can dump the tables you have access to.

shell> mclient -lsql --database=voc /tmp/voc.sql

Migration of a database from other system follows the same route, but be aware that SQL dialects often differ. A manual or scripted patch of a foreign SQL dump is often needed.

在不同的操作系统，dump方法类似，只是SQL方言经常不一样。

Fast backups on Linux

Aside from the ASCII dumps, one can also take the database server out of production using commands

shell> monetdb stop demo
shell> monetdb lock demo

After the database has been securely stopped, we can create a copy of the database directory in the dbfarm and put it aside in a safe place. Alternatively, incremental file-system dumps can be used to reduce the time and storage space for a recovery point. Finally, the database is released for production again . Beware that taking a system out of production should be followed by ensuring that all running queries have finished. This can be inspected using thesystem monitor.

shell> monetdb release demo

Windows platforms

It is recommended to always dump the old database into ASCII before installing a new MonetDB release. Subsequently remove the dbfarm. After installation, the dump can be restored.

To dump the SQL database, start the MonetDB SQL Client program and type the command

\>...\databasedump.sql
\D
\>

The path after \> should be an absolute path name (i.e. start with a drive letter) and be in a save location. By default the database is located in %APPDATA%\MonetDB5. After having made a database dump it can be removed. This folder is located inside the dbfarm\demofolder.

Restoring the SQL database can be done using the MonetDB SQL Client program with the following command

\<...\databasedump.sql