第一部分  迁移原因

Pentaho BI 社区版服务的很多元数据信息存储在数据库汇总，其默认使用HSQLDB 数据库，即借助它存储自身的资料库，比如 Quartz 调度信息、业务资料库连接信息（数据源）等。HSQLDB是不能够支撑真实的企业应用的。生产环境必须替换它，因此我们需要将HSQLDB迁移至MySQL

 

第二部分 迁移步骤

1 创建MySQL 数据库

数据库脚本存放在 ${biserver-ce}/data/mysql5下

服务器本地登录MySQL(脚本内IP使用 localhost)

执行以下脚本

source /opt/ptools/biserver-ce/data/mysql5/create_jcr_mysql.sql

创建：

source /opt/ptools/biserver-ce/data/mysql5/create_quartz_mysql.sql

source /opt/ptools/biserver-ce/data/mysql5/create_repository_mysql.sql

2  给 pentaho 添加 MySQL JDBC 文件

mysql-connector-java-5.1.32-bin.jar

放在 /opt/ptools/biserver-ce/tomcat/lib/ 目录下

3 配置

3 –1 /opt/ptools/biserver-ce/pentaho-solutions/system 目录下

（1） pentaho.xml 

关闭前台显示用户列表

<login-show-users-list>false</login-show-users-list>

注释测试数据

<sampledata-datasource>

     <name>SampleData</name>

     <host>localhost</host>

     <type>Hypersonic</type>

     <port>9001</port>

     <access>NATIVE</access>

     <username>pentaho_user</username>

     <password>password</password>

     <max-active>20</max-active>

     <max-idle>5</max-idle>

     <max-wait>1000</max-wait>

     <query>select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES</query>

</sampledata-datasource>

（2）applicationContext-spring-security-jdbc.properties

注释HSQLDB 配置 ，新增MySQL 配置

datasource.driver.classname=com.mysql.jdbc.Driver

datasource.url=jdbc:mysql://localhost:3306/hibernate

datasource.username=hibuser

datasource.password=password

datasource.validation.query=SELECT 1

（3）applicationContext-spring-security-hibernate.properties

注释HSQLDB 配置 ，新增MySQL 配置

jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/hibernate

jdbc.username=hibuser

jdbc.password=password

hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

（4）hibernate/hibernate-settings.xml

修改配置文件为 MySQL 配置

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

 

同时检查对应 system/hibernate/mysql5.hiberante.cfg.xml 中 配置信息

<!-- MySQL Configuration -->

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>

<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>

<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

<property name="connection.username">hibuser</property>

<property name="connection.password">password</property>

<property name="connection.pool_size">10</property>

<property name="show_sql">false</property>

<property name="hibernate.jdbc.use_streams_for_binary">true</property>

 

（5）quartz/quartz.properties 

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

（6）jackrabbit/repository.xml

注释内容：

<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">

    <param name="path" value="${rep.home}/repository"/>

</FileSystem>

取消注释:

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">

    <param name="driver" value="com.mysql.jdbc.Driver"/>

    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

    <param name="user" value="jcr_user"/>

    <param name="password" value="password"/>

    <param name="schema" value="mysql"/>

    <param name="schemaObjectPrefix" value="fs_repos_"/>

</FileSystem>

注释内容：

<DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>

取消注释:

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">

    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

    <param name="user" value="jcr_user"/>

    <param name="password" value="password"/>

    <param name="databaseType" value="mysql"/>

    <param name="driver" value="com.mysql.jdbc.Driver"/>

    <param name="minRecordLength" value="1024"/>

    <param name="maxConnections" value="3"/>

    <param name="copyWhenReading" value="true"/>

    <param name="tablePrefix" value=""/>

    <param name="schemaObjectPrefix" value="ds_repos_"/>

</DataStore>

      

注释内容：

<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">

    <param name="path" value="${wsp.home}"/>

</FileSystem>

取消注释:

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">

    <param name="driver" value="com.mysql.jdbc.Driver"/>

    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

    <param name="user" value="jcr_user"/>

    <param name="password" value="password"/>

    <param name="schema" value="mysql"/>

    <param name="schemaObjectPrefix" value="fs_ws_"/>

</FileSystem>

注释内容：

<PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager">

    <param name="url" value="jdbc:h2:${wsp.home}/db"/>

    <param name="schemaObjectPrefix" value="${wsp.name}_"/>

</PersistenceManager>

取消注释:

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">

    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

    <param name="user" value="jcr_user" />

    <param name="password" value="password" />

    <param name="schema" value="mysql"/>

    <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>

</PersistenceManager>

注释内容：

<FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">

    <param name="path" value="${rep.home}/version" />

</FileSystem>

取消注释:

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">

    <param name="driver" value="com.mysql.jdbc.Driver"/>

    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

    <param name="user" value="jcr_user"/>

    <param name="password" value="password"/>

    <param name="schema" value="mysql"/>

    <param name="schemaObjectPrefix" value="fs_ver_"/>

</FileSystem>

注释内容：

<PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.H2PersistenceManager">

    <param name="url" value="jdbc:h2:${rep.home}/version/db"/>

    <param name="schemaObjectPrefix" value="version_"/>

</PersistenceManager>

取消注释:

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">

    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>

    <param name="user" value="jcr_user" />

    <param name="password" value="password" />

    <param name="schema" value="mysql"/>

    <param name="schemaObjectPrefix" value="pm_ver_"/>

</PersistenceManager>

（7）simple-jndi/jdbc.properties 

注释HSQL 配置：

#SampleData/type=javax.sql.DataSource

#SampleData/driver=org.hsqldb.jdbcDriver

#SampleData/url=jdbc:hsqldb:hsql://localhost/sampledata

#SampleData/user=pentaho_user

#SampleData/password=password

#Hibernate/type=javax.sql.DataSource

#Hibernate/driver=org.hsqldb.jdbcDriver

#Hibernate/url=jdbc:hsqldb:hsql://localhost/hibernate

#Hibernate/user=hibuser

#Hibernate/password=password

#Quartz/type=javax.sql.DataSource

#Quartz/driver=org.hsqldb.jdbcDriver

#Quartz/url=jdbc:hsqldb:hsql://localhost/quartz

#Quartz/user=pentaho_user

#Quartz/password=password

#Shark/type=javax.sql.DataSource

#Shark/driver=org.hsqldb.jdbcDriver

#Shark/url=jdbc:hsqldb:hsql://localhost/shark

#Shark/user=sa

#Shark/password=

#SampleDataAdmin/type=javax.sql.DataSource

#SampleDataAdmin/driver=org.hsqldb.jdbcDriver

#SampleDataAdmin/url=jdbc:hsqldb:hsql://localhost/sampledata

新增 MySQL配置：

SampleData/type=javax.sql.DataSource

SampleData/driver=com.mysql.jdbc.Driver

SampleData/url=jdbc:mysql://localhost:3306/hibernate

SampleData/user=hibuser

SampleData/password=password

Hibernate/type=javax.sql.DataSource

Hibernate/driver=com.mysql.jdbc.Driver

Hibernate/url=jdbc:mysql://localhost:3306/hibernate

Hibernate/user=hibuser

Hibernate/password=password

Quartz/type=javax.sql.DataSource

Quartz/driver=com.mysql.jdbc.Driver

Quartz/url=jdbc:mysql://localhost:3306/quartz

Quartz/user=pentaho_user

Quartz/password=password

Shark/type=javax.sql.DataSource

Shark/driver=com.mysql.jdbc.Driver

Shark/url=jdbc:mysql://localhost:3306/hbibernate

Shark/user=hibuser

Shark/password=password

SampleDataAdmin/type=javax.sql.DataSource

SampleDataAdmin/driver=com.mysql.jdbc.Driver

SampleDataAdmin/url=jdbc:mysql://localhost:3306/hibernate

SampleDataAdmin/user=hibuser

SampleDataAdmin/password=password

（8）删除测试数据

rm default-content/*.zip*

rm –rf jackrabbit/repository/

 

3-2 /opt/ptools/biserver-ce/tomcat/ 目录下

（1）webapps/pentaho/META-INF/context.xml

将HSQLDB 的配置 更改为 MySQL

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"

               factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxTotal="20" maxIdle="5"

               maxWaitMillis="10000" username="hibuser" password="password"

               driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhosti:3306/hibernate"

               validationQuery="select 1" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"

               factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxTotal="20" maxIdle="5"

               maxWaitMillis="10000" username="pentaho_user" password="password"

               driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"

               validationQuery="select 1"/>

（2）webapps/pentaho/WEB-INF/web.xml

注释内容：

<!-- [BEGIN HSQLDB DATABASES] -->

<context-param>

    <param-name>hsqldb-databases</param-name>

    <param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>

</context-param>

<!-- [END HSQLDB DATABASES] -->

 

注释内容：

<!-- [BEGIN HSQLDB STARTER] -->

<listener>

    <listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>

</listener>

<!-- [END HSQLDB STARTER] -->

（3）清理 Tomcat 临时文件

rm –rf conf/Catalina/

rm -rf work/Catalina/

4 启动服务进行测试

进入 /opt/ptools/biserver-ce 文件夹

./start-pentaho.sh & >> 1.log &

 

首页：