第一部分 迁移原因
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
关闭前台显示用户列表
false
注释测试数据
SampleData
localhost
Hypersonic
9001
NATIVE
pentaho_user
password
20
5
1000
select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES
(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 配置
system/hibernate/mysql5.hibernate.cfg.xml
同时检查对应 system/hibernate/mysql5.hiberante.cfg.xml 中 配置信息
com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/hibernate
org.hibernate.dialect.MySQL5InnoDBDialect
hibuser
password
10
false
true
(5)quartz/quartz.properties
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
(6)jackrabbit/repository.xml
注释内容:
取消注释:
注释内容:
取消注释:
注释内容:
取消注释:
注释内容:
取消注释:
注释内容:
取消注释:
注释内容:
取消注释:
(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
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" />
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
注释内容:
hsqldb-databases
sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz
注释内容:
org.pentaho.platform.web.http.context.HsqldbStartupListener
(3)清理 Tomcat 临时文件
rm –rf conf/Catalina/
rm -rf work/Catalina/
4 启动服务进行测试
进入 /opt/ptools/biserver-ce 文件夹
./start-pentaho.sh & >> 1.log &