Pentaho Kettle 8.1运行环境切换使用MySQL作为存储数据库

原文地址：https://help.pentaho.com/Documentation/8.1/Setup/Installation/Archive/MySQL_Repository

        参考了很多网上的切换配置，相互说明有矛盾，也没说明改配置的意义是啥，因此去官网帮助文档查了一下，说明的比较详细，就翻译出来存档一下。如果您是大神就可以关闭不看了，本文是准备给我像我一样的新手的。

 

Use MySQL as Your Repository Database (Archive Installation)

使用MySQL作为您的存储数据库（压缩包方式安装）

Last updated  May 22, 2018

Prerequisite: Before you prepare your Pentaho Repository, you must prepare either a Windows or Linux environment.

先决条件:在准备Pentaho存储库之前，必须准备Windows或Linux环境。

The Pentaho Repository resides on the database that you installed during the Windows or Linux environment preparation step, and consists of the following four components:

Pentaho存储库驻留在Windows或Linux环境准备步骤中安装的数据库上，由以下四个组件组成:

• Jackrabbit contains the solution repository, examples, security data, and content data from reports that you use Pentaho software to create.
• Jackrabbit包含您使用Pentaho软件创建的解决方案存储库、示例、安全数据和来自报表的内容数据。
• Quartz holds data that is related to scheduling reports and jobs.
• Quartz保存与调度报表和作业相关的数据。
• Hibernate holds data that is related to audit logging.
• Hibernate保存与审计日志记录相关的数据。
• The optional Pentaho Operations Mart reports on system usage and performance.
• 可选的Pentaho Operations Mart报告系统使用情况和性能。

Initialize MySQL Pentaho Repository Database

初始化MySQL Pentaho存储库数据库

To initialize MySQL so that it serves as the Pentaho Repository, you will need to run a few SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart databases.

为了初始化MySQL，使其作为Pentaho存储库，您需要运行一些SQL脚本来创建Hibernate、Quartz、Jackrabbit (JCR)和Pentaho Operations Mart数据库。

Use the ASCII character set when you run these scripts. Do not use UTF-8 because there are text string length limitations that might cause the scripts to fail.

运行这些脚本时使用ASCII字符集。不要使用UTF-8，因为存在可能导致脚本失败的文本字符串长度限制。

The next few sections take you through the steps to initialize the MySQL Pentaho Repository database.

接下来的几节将介绍初始化MySQL Pentaho存储库数据库的步骤。

Step 1: Change Default Passwords

步骤1:更改默认密码

For your production server, we highly recommend that you change the default passwords in the following SQL script files to make the databases more secure.

做为您的生产服务器，我们强烈建议您更改以下SQL脚本文件中的默认密码，以使数据库更加安全。

If you are evaluating Pentaho, you might want to skip this step.

如果您正在评估Pentaho，您可以跳过这一步。

To change the passwords, go to the pentaho/server/pentaho-server/data/mysql5 directory and use any text editor to change the passwords in these SQL scripts:

要更改密码，请转到pentaho/server/pentaho-server/data/mysql5目录，并使用任何文本编辑器更改这些SQL脚本中的密码:

• create_jcr_mysql.sql
• create_quartz_mysql.sql
• create_repository_mysql.sql
• pentaho_mart_mysql.sql

Step 2: Run SQL Scripts

步骤2:运行SQL脚本

You will need to run these SQL scripts in the table below.

您需要运行下表中的这些SQL脚本。

These scripts require administrator permissions on the server in order to run them.

这些脚本需要服务器上的管理员权限才能运行。

If you have a different port or different password, make sure that you change the passwords and port numbers in these examples to match those in your configuration.

如果您有不同的端口或密码，请确保您更改了这些示例中的密码和端口号，以匹配配置中的密码和端口号。

 

Run these scripts from the MySQL Command Prompt window or from MySQL Workbench.

从MySQL命令行或MySQL管理工具运行这些脚本。

Action	SQL Script
Create Quartz	> source <your filepath>/create_quartz_mysql.sql
Create Hibernate repository	> source <your filepath>/create_repository_mysql.sql
Create Jackrabbit	> source <your filepath>/create_jcr_mysql.sql
Create Pentaho Operations mart	> source <your filepath>/pentaho_mart_mysql.sql

Step 3: Verify MySQL Initialization

步骤3:验证MySQL初始化

After you run the scripts, perform the following steps to verify that databases and user roles have been created:

运行脚本后，执行以下步骤来验证数据库和用户角色是否已经创建:

1. Open the MySQL Workbench tool. MySQL Workbench is freely available at the MySQL development site.打开MySQL管理工具。MySQL管理工具可以在MySQL开发站点上免费获得。
2. Log in as hibuser.以hibuser的身份登录。
3. Make sure that the Quartz, Jackrabbit (JCR), Hibernate, and Pentaho Operations Mart databases are present.请确保Quartz、Jackrabbit (JCR)、Hibernate和Pentaho Operations Mart数据库已经存在。
4. Exit from the MySQL Workbench.退出MySQL工作台。

Configure MySQL Pentaho Repository Database

配置MySQL Pentaho存储数据库

Now that you have initialized your repository database, you will need to configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for a MySQL database.

现在你已经初始化了存储数据库，接下来需要切换Quartz、Hibernate、Jackrabbit和Pentaho Operations Mart 为MySQL数据库。

By default, the examples in this section are for a MySQL database that runs on port 3306. The default password is also in these examples.

默认情况下，本节中的示例用于在端口3306上运行的MySQL数据库。默认密码也在这些示例中。

If you have a different port or different password, make sure that you change the password and port number in these examples to match those in your configuration.

如果您有不同的端口或密码，请确保您更改了这些示例中的密码和端口号，以匹配配置中的密码和端口号。

Step 1: Set Up Quartz on MySQL Pentaho Repository Database

步骤1:切换基于MySQL的Quartz库为的Pentaho存储数据库

Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must indicate where the JobStore is located, by modifying the quartz.properties file using the following steps.

事件信息(例如预定的报告)存储在Quartz JobStore中。在安装过程中，你必须通过按下述步骤修改位于quartz.properties文件中JobStore配置。

1. Open the pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties file in any text editor.使用任何文本编辑器打开pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties文件。
2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown here.找到 #_replace_jobstore_properties部分设置org.quartz.jobStore.driverDelegateClass参数，如下所示。（我配置时本行是注释掉的，去掉注释就行）

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

3. Locate the # Configure Datasources section and set the org.quartz.dataSource.myDS.jndiURL equal to Quartz, like this.找到# Configure Datasources 部分设置org.quartz.dataSource.myDS.jndiURL等于Quartz，如下所示。（我配置时，本行是生效的）

   1	org.quartz.dataSource.myDS.jndiURL = Quartz

4. Save the file and close the text editor.存储修改并退出文本编辑器。

Step 2: Set Hibernate Settings for MySQL

步骤2：切换基于MySQL的Hibernate设置

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate configuration file.

修改Hibernate设置文件，指定Pentaho服务应该在哪里找到Pentaho Hibernate库的配置文件。

The Hibernate configuration file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.

Hibernate配置文件指定驱动程序和连接信息、数据库方言以及如何处理连接、关闭和超时。

The files in this section are located in the pentaho/server/pentaho-server/pentaho-solutions/system/hibernate directory.

本节中的文件位于 pentaho/server/pentaho-server/pentaho-solutions/system/hibernate目录中。

Perform the following steps to specify where Pentaho can find the Hibernate configuration file.

执行以下步骤来指定Pentaho可以在哪里找到Hibernate配置文件。

1. Open the hibernate-settings.xml file in a text editor. Find the <config-file> tags and change postgresql.hibernate.cfg.xml to mysql5.hibernate.cfg.xml as shown.使用文本编辑器打开hibernate-settings.xml 文件。找到<config-file>标记，并将postgresql.hibernate.cfg.xml更改为mysql5.hibernat .cfg.xml，如下所示。
   From:

   1	<config-file>system/hibernate/postgresql.hibernate.cfg.xml</config-file>

   To:

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

2. Save and close the file.保存并关闭文件。
3. Open the mysql5.hibernate.cfg.xml file in a text editor.使用文本编辑器打开mysql5.hibernate.cfg.xml文件。
4. Make sure that the password and port number match the ones you specified in your configuration. Make changes if necessary, then save and close the file.确保密码和端口号与您在配置中指定的密码和端口号匹配。如果需要，进行更改，然后保存并关闭文件。

Step 3: Replace Default Version of Audit Log File with MySQL Version

步骤3:用MySQL替换默认的审计日志文件

Since you are using MySQL to host the Pentaho Repository, you need to replace the audit_sql.xml file with one that is configured for MySQL.

由于使用MySQL来托管Pentaho存储库，因此需要修改audit_sql.xml文件将其更改为MySQL配置。

1. Locate the pentaho-solutions/system/dialects/mysql5/audit_sql.xml file.找到pentaho-solutions/system/dialects/mysql5/audit_sql.xml 文件。
2. Copy it into the pentaho-solutions/system directory.复制到pentaho-solutions/system目录。

Step 4: Modify Jackrabbit Repository Information for MySQL

步骤4:按MySQL配置修改Jackrabbit存储库信息

Change the default jackrabbit repository to MySQL using the following steps.

使用以下步骤将默认的jackrabbit存储库更改为MySQL。

1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.使用文本编辑器打开位于pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit的repository.xml文件。
2. Following the table below, locate and change the code so that the MySQL lines are not commented out, but the PostgreSQL, MS SQL Server, and Oracle lines are commented out.根据下表，定位并更改代码，使MySQL行不被注释掉，而PostgreSQL、MS SQL Server和Oracle行被注释掉。

If you have a different port or different password, make sure that you change the password and port number in these examples to match those in your configuration.

如果您有不同的端口或密码，请确保您更改了这些示例中的密码和端口号，以匹配配置中的密码和端口号。

Item:	Code Section:
Repository	<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"/> … </FileSystem>
DataStore	<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore"> <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/> … </DataStore>
Workspaces	<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"/> … </FileSystem>
PersistenceManager (1st part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager"> <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/> … </PersistenceManager>
Versioning	<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"/> … </FileSystem>
PersistenceManager (2nd part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager"> <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/> … </PersistenceManager>
DatabaseJournal	<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal"> <param name="revision" value="${rep.home}/revision.log"/> <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="J_C_"/> <param name="janitorEnabled" value="true"/> <param name="janitorSleep" value="86400"/> <param name="janitorFirstRunHourOfDay" value="3"/> </Journal>

Perform Tomcat-Specific Connection Tasks

完成Tomcat-Specific连接任务

After you configure your repository, you must configure the web application servers to connect to the Pentaho Repository. In this section, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz databases.

在配置存储库之后，必须配置web应用服务器以连接到Pentaho存储库。在本节中，您将建立到Hibernate、Jackrabbit和Quartz数据库的JDBC和JNDI连接。

By default, the Pentaho Server software is configured to be deployed and run on the Tomcat server. As such, connections have already been specified and only the Tomcat context.xml file must be modified.

默认情况下，Pentaho服务器软件被配置为部署并运行在Tomcat服务器上。因此，必须修改已经指定且唯一指定了连接配置的Tomcat context.xml文件。

The next couple of sections guide you through the process of working with the JDBC drivers and connection information for Tomcat.

接下来的几个部分将指导您处理Tomcat的JDBC驱动程序和连接信息。

Step 1: Download Drivers and Apply to the Pentaho Server

步骤1:下载驱动程序并应用到Pentaho服务器

To connect to a database, including the Pentaho Repository database, you will need to download and copy a JDBC driver to the appropriate places for Pentaho Server as well as on the web application server.

要连接到数据库，包括Pentaho存储数据库，您需要下载JDBC驱动程序并将其复制到适合Pentaho服务器和web应用程序服务器的位置。

Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You will have to download and install the file yourself.

由于许可限制，Pentaho不能重新分发一些第三方数据库驱动程序。您必须自己下载并安装该文件。

1. Download a JDBC driver JAR from your database vendor or a third-party driver developer.从数据库供应商或第三方驱动程序开发人员下载JDBC驱动程序JAR。
2. Copy the JDBC driver JAR you just downloaded to the pentaho/server/pentaho-server/tomcat/lib folder.将刚刚下载的JDBC驱动程序JAR复制到pentaho/server/pentaho-server/tomcat/lib文件夹。
3. Copy the hsqldb-2.3.2.jar file to pentaho-server/tomcat/lib if you want to retain the sample provided by Pentaho.复制hsqldb-2.3.2。如果您想保留Pentaho提供的示例，可以将jar文件保存到Pentaho -server/tomcat/lib。

Step 2: Modify JDBC Connection Information in the Tomcat context.xml File

步骤2:在Tomcat context.xml文件中修改JDBC连接信息

Database connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your Pentaho Repository database are stored in the context.xml file. Modify this file to reflect the database connection and network information to reflect your operating environment. If you have chosen to use a Pentaho Repository database other than MySQL, modify the values for the validationQuery parameters in this file.

Pentaho存储数据库的数据库连接和网络信息，例如用户名、密码、驱动程序类信息、IP地址或域名，以及端口号等都存储在context.xml文件中。此文件修改验证查询参数的值。

If you have a different port, password, user, driver class information, or IP address, make sure that you change the password and port number in these examples to match those in your configuration environment.

如果您有不同的端口、密码、用户、驱动程序类信息或IP地址，请确保您更改了这些示例中的密码和端口号，以匹配配置环境中的密码和端口号。

1. Consult your database documentation to determine the JDBC class name and connection string for your Pentaho Repository database.请参阅你的数据库文档，以确定Pentaho存储数据库的JDBC类名和连接字符串
2. Go to the pentaho-server/tomcat/webapps/pentaho/META-INF directory and open the context.xml file with any file editor.使用任意文本编辑器打开位于 pentaho-server/tomcat/webapps/pentaho/META-INF目录的context.xml文件。
3. Comment out the resource references that refer to databases other than MySQL, such as PostgreSQL, MS SQL Server, and Oracle. Then, add the following code to the file if it does not already exist. Be sure to adjust the port numbers and passwords to reflect your environment, if necessary.注释掉引用MySQL以外的数据库的资源引用，例如PostgreSQL、MS SQL Server和Oracle。然后，如果文件不存在，则将以下代码添加到文件中。如果需要，请确保调整端口号和密码以反映您的环境。
4. Make sure that the validationQuery variable for your database is set as follows: validationQuery="select 1"  确保你的数据库validationQuery变量设置如下:validationQuery="select 1"
5. Save the context.xml file, then close it.存储对context.xml文件的变更并关闭它。

<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>

<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>

<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/quartz" driverClassName="com.mysql.jdbc.Driver" password="password" username="pentaho_user" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" maxTotal="20" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>

<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>

<Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart" driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000" maxTotal="20" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>

Start Your Server

启动服务器

Now that you have completed the initial installation steps, you are ready to start the Pentaho Server.

现在您已经完成了初始安装步骤，可以启动Pentaho服务器了。