SQL Dump and Restore

An SQL dump of a database is a common method to safely store away a copy of the database for archival purposes or to migrate data between database instances, e.g. between two major system releases. The content of an SQL dump is a large collection of SQL statements as text. Running the SQL statements 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 database dump operation may become inconsistent by concurrent update statements.

The primary tool to consider for making a database SQL dump is msqldump, which includes a wide variety of options:

$ 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
 -o filename | --output=filename  write dump to filename
 -O dir      | --outputdir=dir    write multi-file dump to dir
 -x ext      | --compression=ext  compression method ext for multi-file dump
 -f          | --functions        dump functions
 -t table    | --table=table      dump a database table
 -D          | --describe         describe database
 -N          | --inserts          use INSERT INTO statements
 -e          | --noescape         use NO ESCAPE
 -q          | --quiet            don't print welcome message
 -X          | --Xdebug           trace mapi network interaction
 -?          | --help             show this usage message
--functions and --table are mutually exclusive
--output and --outputdir are mutually exclusive
--inserts and --outputdir are mutually exclusive
--compression only has effect with --outputdir

Besides dumping a whole database, you can also dump only the database structure/DDL (option --describe) or a specific table (option --table=schema1.mytable) or a set of tables matching a pattern with % wildcard such as --table=schema%.%abc%.

The msqldump program acts as a normal MonetDB client application, i.e. it runs concurrently with all other user sessions, and dumps the generated SQL statements onto standard output. Save this output to a file (by using redirection of stdout) for a later restore session by calling mclient with the saved file as input argument. Details on the various arguments can be found in the manual page for mclient.

For example, consider you have already installed the SQL tutorial database voc on a Linux platform and wishes to transport it to another machine. Then the following command line option generates the dump file.

shell> msqldump --database=voc > /tmp/voc_db.sql

You can inspect the file /tmp/voc_db.sql to confirm that indeed a readable SQL database dump is available. If storage space is a problem, you can readily pipe the output of this command to a compression tool, e.g. gzip, or send it directly to another machine in your environment using conventional Linux tools, e.g. scpy. As a baseline for recovery, move the file over to the new machine. Then the monetdb tool can be used to create the database on the recipient machine. Once done, it suffices to feed the dump file to mclient to populate the database.

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

Migration of a database from other database systems follow the same route, but be aware that SQL dialects often differ. A manual or scripted patch of a foreign SQL dump is often needed. An overview of the various SQL dialects can be found in the SQL dialects book and general background on database compatibility on Wikipedia.

shell> monetdb release demo

Windows platforms

On Windows platforms we advise to always dump the old database into a mydbdump.sql file before upgrading to a new MonetDB software release. Subsequently stop the MonetDB server and rename/remove the dbfarm/demo folder. After upgrade of MonetDB software and restart of MonetDB server, the dump can be restored.

To dump the SQL database, start the MonetDB SQL Client program (mclient.bat) and type the command:

\>C:\...\mydbdump.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 or archived. This folder is located inside the dbfarm\demo folder.

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

\<C:\...\mydbdump.sql

Caveats

  • When the --echo or --interactive option is used, mclient sends its input file(s) line-by-line and waits for confirmation before sending the next line. Therefore, when using mclient to restore large SQL dump files, these options should not be used since they can slow down the process 10s or even 100s times depending on the file size.

  • By default (i.e. without --echo and --interactive), mclient sends an input file in large chunks of 10240 bytes to the MonetDB server. On rare occasions, a block ends exactly at the semicolon (;) which closes the COPY INTO statement. This causes the MonetDB server to complain about invalid input data for this COPY INTO:

    Failed to import table '<table name>', line 1: column 1: Column value missing
    

    even though valid data records will be sent in the next block. This is because the MonetDB server regards the semicolon as the marker for the end-of-input and continues to process the query without asking for more input, but it finds no value for this COPY INTO. A good workaround for this problem is to add a space before the semicolon. For large files on Linux, one can use this command: sed -i 's/INTO <table name>/ &/' $file.