Migrating from HSQL

From AlfrescoWiki

Back to Server Administration Guide

This page contains instructions for migrating data from the HSQL database to another database.

NOTE: The instructions below may not work for UNIX users. For another method please see this (Note: this method is not sanctioned or supported by Alfresco):

 http://forums.alfresco.com/viewtopic.php?p=33334#33334

[edit] Migrating to MySQL

MySQL provides a database migration tool called 'MySQL Migration Toolkit' that can be downloaded from here:

 http://dev.mysql.com/downloads/gui-tools/5.0.html

This tool can read data from another database (such as HSQL) and import the data into a MySQL database.

The steps to using the tool are:

1. Download and extract the tool to somewhere (e.g. C:\mysql-gui-tools-noinstall-5.0-r12-win32)

2. Copy the 'hsqldb.jar' file from the Alfresco WAR (alfresco\WEB-INF\lib\hsqldb.jar) to

• C:\mysql-gui-tools-noinstall-5.0-r12-win32\MySQL GUI Tools 5.0\java\lib\hsqldb.jar

3. Create a tablespace/schema in MySQL called 'public' and create a user who has permissions on this tablespace/schema (e.g alfrescohsql). Note: the tablespace/schema must be called 'public' to match the tablespace/schema used in the HSQL database.

4. Run the 'MySQLMigrationTool.exe'

5. Follow the wizard steps

• In the section Source Database use the information below:

Database System:   Generic Jdbc
Class Name:        org.hsqldb.jdbcDriver
Connection String: jdbc:hsqldb:file:<path_to_alfresco>/alf_data/hsql_data/alfresco
Username:          alfresco
Password:          alfresco

• In the section Target Database use the information below:

Hostname: <name_of_host> - e.g. localhost
Username: <username created in step above> - e.g. alfrescohsql
Password: <password for user above>

• If any of the information above is entered incorrectly the Connecting to Servers screen will report a failure.

• In the Source Schemata Selection screen, select the PUBLIC database.

• In the Object Mapping screen, choose 'Set Parameter' for Migration of type Schema and choose 'Multilanguage'. Next, choose 'Set Parameter' for Migration of type Table and choose 'Data consistency/multilanguage'.

• Progress through the wizard until the end, and all the data should be successfully migrated (check the contents of the 'public' tablespace/schema within MySQL to see the results.

• Once the data has been imported into the 'PUBLIC' tablespace/schema in MySQL you've done the hard part.

• Using MySQL Administrator you can simply (2 mouse clicks and a bit of typing in MySQL Administrator) create a new tablespace/schema called 'alfresco'. Then create an 'alfresco' user, set the password to 'alfresco', give that user permissions to the 'alfresco' tablespace/schema. (Note, the SQL for this step can be found in 'extras\databases\mysql' in the Alfresco packages if you want to use the command line tools)

• Using MySQL Administrator again perform a 'Backup' of the 'PUBLIC' tablespace/schema and then perform an 'Import' of the data into the 'alfresco' tablespace/schema and you are done (if you want to use the command line tools for this please read the MySQL documentation).

[edit] Migrating to Another Database

Follow the steps above to migrate into MySQL. Once the data is in MySQL, standard SQL tools can be used to dump the data from MySQL for loading into another Database.