Pentaho6.1资源库MySQL迁移

小鱼儿 2022-05-16 05:54 144阅读 0赞

# Pentaho6.1资源库MySQL迁移 #

[Pentaho BI][][Pentaho][][MySQL][]

# ![214000_Ao7T_2453090.jpg][] #

# 一、软件环境 #

*  操作系统：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脚本目录 ###

![215033_U258_2453090.jpg][]

###     2、执行脚本 ###

![215531_Nm2r_2453090.jpg][]

![215626_MLfD_2453090.jpg][]

![215754_b6K4_2453090.jpg][]

###     3、 运行结果 ###

![220102_L3Ah_2453090.jpg][]

# 三、相关配置项的修改 #

###     1、修改quartz的资源库配置 ###

*  文件位置：\\biserver-ce\\pentaho-solutions\\system\\quartz

![220748_UB30_2453090.jpg][]

*  修改项：org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

![222124_VZqh_2453090.jpg][]

#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

![224412_3zc4_2453090.jpg][]

修改内容：

<?xml version='1.0' encoding='utf-8'?>
    <settings>
    
      
    	
    	
    	<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>
    
      
    
        <managed>false</managed>
    </settings>

*  修改文件**applicationContext-spring-security-jdbc.xml**

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

![225612_eGOn_2453090.jpg][]

修改内容：

![230610_hXCR_2453090.jpg][]

<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" />
    		
    		
    		
    	</bean>

*  修改文件**applicationContext-spring-security-hibernate.properties**

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

![231358_R9Za_2453090.jpg][]

修改内容：

#  原始配置
    #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**

![232911_Womg_2453090.jpg][]

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

将文档中的此处代码

<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>

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

将文档中的此处代码

<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>

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

将文档中的此处代码

<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>

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>

修改为：

<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>

修改为：

<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>

修改为：

###     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="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**
 *  修改的内容：

![003036_m4I5_2453090.jpg][]

![003318_C1GX_2453090.jpg][]

###     7、检查mysql的jar包的存在 ###

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

**![003600_KGnJ_2453090.jpg][]**

###     8、Pentaho默认的SampeData.sql数据的下载（可选，非必要） ###

# 四、迁移成功标志 #

# **            **  #

# 五、迁移官方文档 #

[**详情请查看：****http://help.pentaho.com/Documentation/6.1/0F0/0P0/030/020**][http_help.pentaho.com_Documentation_6.1_0F0_0P0_030_020]

**转载：**[https://my.oschina.net/TaoPengFeiBlog/blog/785892][https_my.oschina.net_TaoPengFeiBlog_blog_785892]

[Pentaho BI]: https://my.oschina.net/TaoPengFeiBlog?q=Pentaho%20BI
[Pentaho]: https://my.oschina.net/TaoPengFeiBlog?q=Pentaho
[MySQL]: https://my.oschina.net/TaoPengFeiBlog?q=MySQL
[214000_Ao7T_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1130/214000_Ao7T_2453090.jpg
[215033_U258_2453090.jpg]: /images/20220516/619c594c7ffd424b938ef1d30c900eec.png
[215531_Nm2r_2453090.jpg]: /images/20220516/f3d4214fedfe47b2a55d444fc4053843.png
[215626_MLfD_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/215626_MLfD_2453090.jpg
[215754_b6K4_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/215754_b6K4_2453090.jpg
[220102_L3Ah_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/220102_L3Ah_2453090.jpg
[220748_UB30_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/220748_UB30_2453090.jpg
[222124_VZqh_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/222124_VZqh_2453090.jpg
[224412_3zc4_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/224412_3zc4_2453090.jpg
[225612_eGOn_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/225612_eGOn_2453090.jpg
[230610_hXCR_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/230610_hXCR_2453090.jpg
[231358_R9Za_2453090.jpg]: /images/20220516/fa373d86f46c41369d03aee5ed073cbf.png
[232911_Womg_2453090.jpg]: https://static.oschina.net/uploads/space/2016/1110/232911_Womg_2453090.jpg
[003036_m4I5_2453090.jpg]: /images/20220516/ddb8dc8a60f1478ab5e2a612fb10ef8a.png
[003318_C1GX_2453090.jpg]: /images/20220516/66d6d27f781b4d1db42f9c5426fd988f.png
[003600_KGnJ_2453090.jpg]: /images/20220516/5a12bec13e44477b830c87eb1720941f.png
[http_help.pentaho.com_Documentation_6.1_0F0_0P0_030_020]: http://help.pentaho.com/Documentation/6.1/0F0/0P0/030/020
[https_my.oschina.net_TaoPengFeiBlog_blog_785892]: https://my.oschina.net/TaoPengFeiBlog/blog/785892