Migrating from HSQL

[resplin]

Obsolete Pages{{Obsolete}}

The official documentation is at: http://docs.alfresco.com

DatabaseHSQLMySQL
Back to Server Administration Guide

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

Note: These instructions may not work for Unix users. For another method please see the following link (Note: this method is not sanctioned or supported by Alfresco):

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

Migrating to MySQL

MySQL provides a database migration tool called MySQL Migration Toolkit that can be downloaded from the MySQL website:

 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. For example, extract the tool to C:\MySQL Tools for 5.0\.
   
2. Copy the <WEB-INF>\lib\hsqldb.jar file to C:\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 (for example, alfrescohsql). Note: the tablespace/schema must be called public to match the tablespace/schema used in the HSQL database.
   
4. Run 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> - for example, 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 screens until the end. The data should then be successfully migrated.
  

• Check the contents of the public tablespace/schema within MySQL to see the results.
  

• Using MySQL Administrator you can create a new tablespace/schema called alfresco. Then create an alfresco user, set the password to alfresco, and 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.
  

Note: If you want to use the command line tools for this task please read the MySQL documentation.

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.