« Back to Indexer Documentation
$Id: configureMySQL.html 15596 2012-07-06 17:31:03Z tene $
$URL: https://ghttrac.cr.usgs.gov/websvn/ProductDistribution/trunk/etc/documentation/userguide/configureMySQL.html $
This document describes the steps necessary to configure the Product Index to store product summaries in a MySQL database, instead of the default SQLite database. These instructions can be adapted to allow the Product Index to use another database, provided that a proper JDBC driver is installed.
Note: for this step, you must have privileges to add new MySQL users and create new databases on your server. If you do not have the required privileges, ask your database administrator to perform this step for you.
We recommend that you create a new database exclusively for the Product Index:
CREATE DATABASE product_index;
Next, create two users on the database. The first is an admin user and the second only has read/write permissions.
GRANT ALL PRIVILEGES ON `product_index`.* TO 'pi_admin'@'localhost' IDENTIFIED BY 'mySecretPassword'; GRANT SELECT, INSERT, UPDATE, DELETE ON `product_index`.* TO 'pi_write'@'localhost' IDENTIFIED BY 'myOtherSecretPassword';
Next, you must create four tables in your newly created database. The Product Index
needs tables to store events, products summaries, product summary links, and product
summary. The tables must be created with the SQL commands in the following linked sql file, which can be
executed as the
pi_admin user we just created:
MYSQL Product Index Schema
The commands also create foreign key constraints and indexes. The Product Index will still function fully without either of these, but they are included as enhancements. The foreign key constraints ensure that the integrity of the data is maintained. The indexes can greatly improve performance retrieving products from the database.
Beyond the creation of these four tables, the Indexer is dependent on two additional database components: the feplus system and OnEventUpdate stored procedures:
Your MySQL database is now set up properly, so it's time to configure the Product Index.
As described on the configuration page, the Product
Index can accept 3 configuration parameters:
indexfile only applies to SQLite databases; you should not set
this parameter for MySQL databases. Modify your global configuration file (config.ini) to include
... listeners = listener_indexer [listener_indexer] type = gov.usgs.earthquake.indexer.Indexer ... index = indexer_index [indexer_index] type = gov.usgs.earthquake.indexer.JDBCProductIndex driver = com.mysql.jdbc.Driver url = jdbc:mysql://127.0.0.1/product_index?user=pi_write&password=myOtherSecretPassword
In this example, the
[indexer_index] section sets the parameters for
the Product Index. The
driver parameter must be set to "com.mysql.jdbc.Driver"
to use MySQL. Only the MySQL Java driver is provided by default, so if to use another
database (PostgreSQL, Oracle, etc) you need to install the relevent JDBC driver.
url parameter should be set to a valid JDBC URL. For MySQL,
the format is