SQL dump and restoreSQL dump and restore mk Sun, 03/28/2010 - 13:56
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 an SQL dump is a large collection of SQL statements as text. 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 a database SQL dump is msqldump, which includes a wide variety of options:.
Usage: msqldump [ options ] [ dbname ]
-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
msqldump program acts as a normal MonetDB client application, e.g. it runs concurrently with all other user sessions, and dumps the generated SQL statements onto standard output. Safe this 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.sql
You can inspect the file /tmp/voc.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 sent 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
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. 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:
\>...\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. This folder is located inside the dbfarm\demo folder.
Restoring an SQL database can be done using the MonetDB SQL Client program (mclient.bat) with the following command
File system level backupFile system level backup mk Mon, 03/24/2014 - 22:14
Aside from the ASCII dumps, one can also take the database server out of production using the following commands. Be careful in using shutdown(), because there may be users running transactions. This can be seen using the sys.sessions table or the system monitor.
shell> mclient -d demo
select * from sys.sessions;
shell> monetdb lock demo
shell> monetdb stop demo
The "lock" step is necessary to prevent monetdbd from automatically restarting the database "demo" upon receiving another client connection request.
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 using:
shell>monetdb release <dbname>