Pentaho6.1资源库MySQL迁移

原创
2016/11/10 21:38
阅读数 2K

一、软件环境

  • 操作系统:Windows10 64位
  • Pentaho版本: biserver-ce-6.1.0.1-196
  • MySQL版本:5.5.15 MySQL Community Server (GPL)
  • JDK版本:Java 1.7.0_80

二、执行官方sql脚本文件

    1、 sql脚本目录

    2、执行脚本

    3、 运行结果

三、相关配置项的修改

    1、修改quartz的资源库配置

  • 文件位置:\biserver-ce\pentaho-solutions\system\quartz

  • 修改项:org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

#org.quartz.jobStore.misfireThreshold = 60000
#org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
#org.quartz.jobStore.useProperties = false
#org.quartz.jobStore.dataSource = myDS
#org.quartz.jobStore.tablePrefix = QRTZ5_
#org.quartz.jobStore.isClustered = false

# Job Store
org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX

#_replace_jobstore_properties

org.quartz.jobStore.misfireThreshold = 60000
#原始的配置
#org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
#用户做的修改
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.useProperties = false
org.quartz.jobStore.dataSource = myDS
org.quartz.jobStore.tablePrefix = QRTZ5_
org.quartz.jobStore.isClustered = false

    2、修改hibernate,设置为mysql

  • 修改文件hibernate-settings.xml

               文件目录: \biserver-ce\pentaho-solutions\system\hibernate

                修改内容:

<?xml version='1.0' encoding='utf-8'?>
<settings>

  <!--
  * This setting allows the deployment to specify where to find the 
  * database-specific hibernate configuration. The samples supplied
  * include the following:
  * 
  * system/hibernate/hsql.hibernate.cfg.xml
  * system/hibernate/mysql5.hibernate.cfg.xml
  * system/hibernate/postgresql.hibernate.cfg.xml
  * system/hibernate/oracle10g.hibernate.cfg.xml
  *
  -->
	<!--  原始设置
    <config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>
	-->
	<!-- 修改为mysql的配置 -->
	<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>

  <!--
  *
  * managed should be set to true if running the BI Platform
  * in a managed environment (like JBoss, Orion, etc). In this configuration,
  * you should specify another location for the hibernate.cfg.xml (see below)
  * instead of simply using the default one provided. This setting essentially
  * tells the HibernateUtil class to use JNDI to locate the factory class for
  * getting sessions. This allows the platform to use Hibernate across boundaries
  * in message beans (for example).
  *
    <managed>false</managed>
  -->

    <managed>false</managed>
</settings>
  • 修改文件applicationContext-spring-security-jdbc.xml

                文件目录:\biserver-ce\pentaho-solutions\system

                修改内容:

	<!-- 原始配置 
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="${datasource.driver.classname}" />
		<property name="url" value="${datasource.url}" />
		<property name="username" value="${datasource.username}" />
		<property name="password" value="${datasource.password}" />
		<!-- the following are optional -->
	<!--
		<property name="validationQuery" value="${datasource.validation.query}" />
		<property name="maxWait" value="${datasource.pool.max.wait}" />
		<property name="maxActive" value="${datasource.pool.max.active}" />
		<property name="maxIdle" value="${datasource.max.idle}" />
		<property name="minIdle" value="${datasource.min.idle}" />
	</bean>
	-->
	
	<!-- 用户定义的mysql配置 -->
	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/hibernate" />
		<property name="username" value="hibuser" />
		<property name="password" value="password" />
		
		<!-- 可选项 -->
		<!--
		<property name="validationQuery" value="${datasource.validation.query}" />
		<property name="maxWait" value="${datasource.pool.max.wait}" />
		<property name="maxActive" value="${datasource.pool.max.active}" />
		<property name="maxIdle" value="${datasource.max.idle}" />
		<property name="minIdle" value="${datasource.min.idle}" />
		-->
	</bean>
  • 修改文件applicationContext-spring-security-hibernate.properties

                文件目录:\biserver-ce\pentaho-solutions\system

                修改内容:

#  原始配置
#jdbc.driver=org.hsqldb.jdbcDriver
#jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate
#jdbc.username=hibuser
#jdbc.password=password
#hibernate.dialect=org.hibernate.dialect.HSQLDialect

# 用户定义的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.MySQLDialect

    3、更换Audit log file为mysql配置

复制文件: biserver-ce /pentaho-solutions/system/dialects/mysql5/audit_sql.xml
覆盖 biserver-ce /pentaho-solutions/system/ audit_sql.xml

    4、更改jackrabbit内容仓库信息配置为mysql

  • 修改文件:\biserver-ce\pentaho-solutions\system\jackrabbit\repository.xml

            a.    Repository标签下的FileSystem,注释或删除掉默认配置项,文件所在行数为80

                 将文档中的此处代码

  <!--  原始的配置 -->
  <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
     <param name="path" value="${rep.home}/repository"/>
  </FileSystem>

                修改为:

  <!-- 用户定义的mysql的配置 -->
  <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>

             b.    DataStore标签注释或删除掉默认配置项,文件所在行数为163

                将文档中的此处代码

  <!-- 原始的配置 -->
  <DataStore class="org.apache.jackrabbit.core.data.FileDataStore"/>

                修改为:

  <!-- 用户定义的mysql的配置 -->
  <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>

 

            c.    Workspaces中的filesystem配置项,注释或删除掉默认配置项,文件所在行数为285

                     将文档中的此处代码

    <!-- 原始的配置 -->
    <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
      <param name="path" value="${wsp.home}"/>
    </FileSystem>

                     修改为:

    <!-- 用户定义的mysql的配置 -->
    <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>

 

            d.    PersistenceManager 修改为 mysql 配置,(文中第一部分 PersistenceManager )
        注释 掉默认配置项标签( 文本内容第
353 行)

                     将文档中此处代码

    <!-- 原始的配置 -->
    <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>

                     修改为:

    <!-- 用户定义的mysql的配置 -->
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <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="${wsp.name}_pm_ws_"/>
    </PersistenceManager>

            e.    Versioning 修改为 mysql 配置项,Versioning filesystem 默认配置项( 文本内容第 457 行)
                    将文档中此处代码

    <!-- 原始的配置 -->
    <FileSystem class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
      <param name="path" value="${rep.home}/version" />
    </FileSystem>

                    修改为:

    <!-- 用户定义的mysql的配置 -->
    <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>

            f.    PersistenceManager,PersistenceManager (文中第二部分 PersistenceManager )
        注释 掉默认配置项标签( 文本内容第
526 行)

                    将文档中此处代码

    <!-- 原始的配置 -->
    <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>

                    修改为:

    <!-- 用户定义的mysql的配置 -->
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <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="pm_ver_"/>
    </PersistenceManager>

    5、修改默认的context

  • 文件目录: biserver-ce/tomcat/webapps/pentaho/META-INF/context.xml
  • 修改的内容:
<?xml version="1.0" encoding="UTF-8"?>
<!-- 原始的配置
<Context path="/pentaho" docbase="webapps/pentaho/">
	<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="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"
		validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />
		
	<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="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"
		validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>

</Context>
-->

<!-- 用户定义的mysql的配置 -->
<Context path="/pentaho" docbase="webapps/pentaho/">
	<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://localhost:3306/hibernate"
		validationQuery="select 1" />
	<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
	  factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
	  maxWait="10000" username="pentaho_user" password="password"
	  driverClassName="com.mysql.jdbc.Driver"	  url="jdbc:mysql://localhost:3306/quartz"
	  validationQuery="select 1 "/>	  
</Context>

    6、停止掉默认的hsqlDB

  •   文件目录:\biserver-ce\tomcat\webapps\pentaho\WEB-INF\web.xml
  • 修改的内容:

    7、检查mysql的jar包的存在

        检查目录 biserver-ce\tomcat\lib 存不存在 mysql 的jar 包, 不存在或者版本太低的 jar 包,
根据自己所需更新, 本文采用的是
mysql-connector-java-5.1.38-bin.jar

    8、Pentaho默认的SampeData.sql数据的下载(可选,非必要)

 

四、迁移成功标志

                未完待续、、、、、

五、迁移官方文档

详情请查看:http://help.pentaho.com/Documentation/6.1/0F0/0P0/030/020

 

展开阅读全文
打赏
0
7 收藏
分享
加载中

引用来自“灯下黑鬼吹灯”的评论

引用来自“包万里”的评论

请问迁移成功了吗?是否用户都在mysql下管理了?

是的 用户全部在mysql里了
你好!我按你的说明的进行操作:环境和你基本一致。除了mysql是5.7版本以外。启动还是报错,如下。可以留个qq帮忙看看吗?或者把你改好的版本放到百度云盘共享出来学习一下,谢谢!
2017-05-07 16:04:04,370 ERROR [org.pentaho.platform.util.logging.Logger] Error: Pentaho
2017-05-07 16:04:04,370 ERROR [org.pentaho.platform.util.logging.Logger] misc-org.pentaho.platform.engine.core.system.PentahoSystem: org.pentaho.platform.api.engine.PentahoSystemException: PentahoSystem.ERROR_0014 - 在试图执行启动sequenceorg.pentaho.platform.engine.services.connection.datasource.dbcp.DynamicallyPooledDatasourceSystemListener发生错误
org.pentaho.platform.api.engine.PentahoSystemException: org.pentaho.platform.api.engine.PentahoSystemException: PentahoSystem.ERROR_0014 - 在试图执行启动sequenceorg.pentaho.platform.engine.services.connection.datasource.dbcp.DynamicallyPooledDatasourceSystemListener发生错误
  at org.pentaho.platform.engine.core.system.PentahoSystem.notifySystemListenersOfStartup(PentahoSystem.java:373)
  at org.pentaho.platform.engine.core.system.PentahoSystem.init(PentahoSystem.java:335)
  at org.pentaho.platform.engine.core.system.PentahoSystem.init(PentahoSystem.java:231)
  at org.pentaho.platform.web.http.context.SolutionContextListener.contextInitialized(SolutionContextListener.java:162)
  at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4811)
  at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5251)
  at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
  at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:725)
  at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:701)
  at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:717)
  at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1092)
  at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1834)
  at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
  at java.util.concurrent.FutureTask.run(FutureTask.java:262)
  at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
  at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
  at java.lang.Thread.run(Thread.java:745)
2017/05/07 16:45
回复
举报

引用来自“包万里”的评论

请问迁移成功了吗?是否用户都在mysql下管理了?

是的 用户全部在mysql里了
2017/05/04 19:44
回复
举报
请问迁移成功了吗?是否用户都在mysql下管理了?
2017/05/04 19:20
回复
举报

引用来自“kkkkkoo”的评论

oooo
😌
2016/11/11 17:41
回复
举报
更多评论
打赏
4 评论
7 收藏
0
分享
返回顶部
顶部