Configuring and Using DBSynchronizer

You install and configure DBSynchronizer similar to the way you would install any other AsyncEventListener implementation, using the CREATE ASYNCEVENTLISTENER statement. However, DBSynchronizer requires specific initialization parameters to establish a connection with a third-party database.

Prerequisites

Preconfigure the third-party database with the schema and table definitions using the same names as those used in the GemFire XD database. When you deploy DBSynchronizer, you specify the name of the JDBC driver for the third-party database (the class that implements java.sql.Driver) as well as the full JDBC connection string URL to use to connect to the database. You can include any required connection parameters, such as user credentials, database names, and so forth, in the URL or as separate initialization parameters. DBSynchronizer uses the driver, URL, and parameters to obtain a java.sql.Connection to the third party RDBMS. DBSynchronizer Initialization Parameters provides a complete reference to all DBSynchronizer initialization parameters.

Procedure

The following procedure describes how to configure GemFire XD with DBSynchronizer to apply table DML operations to a third-party database.

Note: The example commands in this procedure use MySQL Community Server 5.5.18 as the third-party database with the Connector/J 5.1.18 driver. However, the general steps are the same regardless of which database you use. Consult your third-party database and JDBC driver documentation for more information.
Note: If you are synchronizing tables in a MySQL database, you must configure MySQL to use case-insensitive identifiers. See Restrictions and Limitations.
  1. Configure the third-party database

    Before you configure DBSynchronizer in GemFire XD, install and configure the third-party database. Create the database schema and table definitions for all of the tables that you intend to use in GemFire XD.

    To ensure that the tables stay in synch with those in GemFire XD, only create the table definitions in the third-party database. Insert and update rows only in GemFire XD, so that DBSynchronizer can apply your changes to the database.

    For example, begin by starting MySQL if it is not already running:
    $ sudo mysqld_safe
    In a separate terminal prompt, start the mysql client and create a new database and table definition. Log on using an account that has privileges to create a new database. For example:
    $ mysql -u my_username -p
    mysql> create database gfxddb;
    mysql> use gfxddb;
    mysql> create table gfxdtest
        -> (id int not null, name varchar(10));

    You will create a table of the same name, "gfxdtest," in GemFire XD, and associate it with a DBSynchronizer.

  2. Configure JDBC connectivity to the third-party database

    DBSynchronizer requires a JDBC driver and connection URL to connect to the third-party database and apply DML operations. Before you configure DBSynchronizer, use a Java client application such as SQuirreL SQL to verify that you can connect to the database.

    In the MySQL example shown in this procedure, the components necessary to establish a JDBC connection are:
    • Driver JAR file: mysql-connector-java-5.1.18-bin.jar
    • Driver class: com.mysql.jdbc.Driver
    • Connection URL: jdbc:mysql://localhost:3306/gfxddb?user=gfxduser&password=gfxdpassword
      Note: Although you can include the username and password directly in the JDBC connection URL (as shown above), doing so runs the risk of having the plain-text password appear in exception messages. To avoid recording plain-text passwords, this example will use an encrypted secret generated using the encrypt-password command.
    To ensure that GemFire XD can access the JDBC driver class, add the JAR location to your CLASSPATH. For example, open a new command prompt and enter:
    $ export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
  3. Start a GemFire XD data store in a custom server group

    When you create a new AsyncEventListener such as DBSynchronizer, you must assign the listener to an available server group. For example, to start a GemFire XD data store and add it to a named server group:
    $ mkdir gfxddbsync
    $ cd gfxddbsync
    $ export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
    $ gfxd server start -server-groups=dbsync -mcast-port=10334

    This starts a new GemFire XD data store server and assigns it to the "dbsync" server group.

  4. Encrypt the Database Credentials
    Use the gfxd encrypt-password command with the external option to encrypt the database credentials:
    $ gfxd encrypt-password external -mcast-port=10334
    Enter User Name: gfxduser
    Enter password: gfxdpassword
    Re-enter password: gfxdpassword
    Connecting to distributed system: mcast=/239.192.81.1:10334
    Encrypted to 25325ffc3345be8888eda8156bd1c313
    Note: When you execute the gfxd encrypt-password command, specify the same connection properties that GemFire XD members use to connect to the distributed system. For example, specify the same locator or multicast connection properties, as well as any authorization credentials that members require to join the distributed system.

    The encrypted secret that is returned is specific to this particular GemFire XD distributed system, because the system uses a unique private key to generate the secret. (An obfuscated version of the private key is stored in the persistent data dictionary.) You will use the encrypted secret when configuring the DBSynchronizer initialization parameters, so that the plain-text password never appears in exception messages. The above example uses the default AES transformation and 128-bit key size for encryption. See encrypt-password for information about changing those options.

    If you ever need to move the DBSynchronizer configuration to another GemFire XD system, or if the existing data dictionary is ever deleted and recreated, then you must generate and use a new encrypted secret for use with the new distributed system.

  5. Create the AsyncEventListener configuration

    Begin by creating a parameters file to hold the connection information required to access your database. Use a text editor to create the file /usr/local/dbsync-params. Add the following parameters to the file, with one parameter definition on each line:
    Driver=com.mysql.jdbc.Driver
    URL=jdbc:mysql://localhost:3306/gfxddb
    User=gfxduser
    secret=25325ffc3345be8888eda8156bd1c313
    SkipIdentityColumns=true

    See DBSynchronizer Initialization Parameters for a complete reference to all DBSynchronizer initialization parameters.

    The SkipIdentityColumns is an optional parameter, which defaults to "true." Set this parameter to "false" for tables that have auto-generated identity columns if you want to pass the GemFire XD-generated identity value to the underlying database. See Identity Columns.

    Note: As a best practice, place all initialization parameters in a separate file. This enables you to change the parameters (for example, RDBMS credentials) after you configure the DBSynchronizer implementation in GemFire XD.
    Note: To ensure the security of your system, protect both the DBSynchronizer parameter file that contains the encrypted secret, as well as the persistent data dictionary files for the GemFire XD distributed system. Although the data dictionary obfuscates the private key that is used to generate the encrypted secret, you should consider any passwords to be compromised if either the DBSynchronizer configuration file or the data dictionary are compromised.
    After you save the parameter file, connect to GemFire XD and create a new AsyncEventListener using the DBSynchronizer. Ensure that you have added the third-party JDBC driver JAR file to your classpath, then start gfxd and create the listener definition:
    $ export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
    $ gfxd
    gfxd> connect client 'localhost:1527';
    gfxd> create asynceventlistener testlistener
    > (
    > listenerclass 'com.pivotal.gemfirexd.callbacks.DBSynchronizer' 
    > initparams 
    >   'file=/usr/local/dbsync-params' 
    > )
    > server groups (dbsync);
    Note: You can optionally install and start the AsyncEventListener configuration after you associate a table with the listener name. Make sure that you use the same listener name with both the CREATE ASYNCEVENTLISTNER command and the CREATE TABLE command.

    The LISTENERCLASS argument must specify the built-in GemFire XD DBSynchronizer implementation.

    The INITPARAMS argument specifies only the path to the file that contains the DBSynchronizer initialization parameters. Using a parameter file is a best practice because it enables you to change parameters after you have configured the DBSynchronizer implementation. As an alternative, you could specify all of the parameters directly in the INITPARAMS string, as in this example:
    gfxd> create asynceventlistener testlistener
    > (
    > listenerclass 'com.pivotal.gemfirexd.callbacks.DBSynchronizer' 
    > initparams 
    >   'com.mysql.jdbc.Driver,jdbc:mysql://localhost:3306/gfxddb,user=gfxduser,secret=25325ffc3345be8888eda8156bd1c313' 
    > )
    > server groups (dbsync);
    Note: If you specify parameters directly in the INITPARAMS string, you must drop and re-create the DBSynchronizer configuration in order to change or add parameters (for example, to change the third-party database credentials).

    GemFire XD installs a DBSychronizer instance on each data store in the specified SERVER GROUPS. Ensure that the server groups have at least two data stores in order to provide redundancy for DBSynchronizer operations. As a best practice, install no more than two standby DBSynchronizer instances (redundancy of at most two) for performance and memory reasons.

    When multiple data stores host an event listener, there is no direct way to ensure that a specific GemFire XD data store hosts the active instance. If you require a specific member to host the active instance, ensure that only that member is running in the target server groups when you deploy the listener and attach the table. You can then start the remaining data stores in the server group, and they will host redundant, standby instances.

  6. Start the AsyncEventListener

    Use the SYS.START_ASYNC_EVENT_LISTENER procedure to start the new DBSynchronizer implementation:
    gfxd> call sys.start_async_event_listener('TESTLISTENER');
    Note: The AsyncEventListener name is a SQL identifier and should be entered in uppercase letters with this procedure.
  7. Create identical schema and tables in GemFire XD

    After you create the DBSynchronizer listener in GemFire XD, create tables and associate them with the listener to have DBSynchronizer propagate subsequent table DML operations to the third-party database. The tables you create should use the same schema, table name, and table definitions as those you used in the third-party database. For example:
    gfxd> create schema gfxddb;
    gfxd> set schema gfxddb;
    gfxd> create table gfxdtest
    > (id int not null, name varchar(10))
    > asynceventlistener(testlistener);

    Notice that the table definition is the same as that used in MySQL, except that includes the ASYNCEVENTLISTENER clause to associate the table with DBSynchronizer.

    Note: Because the associated listener configuration does not have to be available at the time you create the table, GemFire XD does not display an error message if the specified listener name does not yet exist. Make sure that you use the same listener name with both the CREATE ASYNCEVENTLISTNER command and the CREATE TABLE command.
  8. Execute DML and verify synchronization

    After you associate the table with a DBSynchronizer implementation, GemFire XD queues DML operations that are executed against the table to the third-party database specified in the DBSynchronizer INITPARAMS argument. For example, while still in gfxd, execute:
    gfxd> insert into gfxdtest values (1, '1st Entry');
    Then return to the mysql client and verify that the DML was propagated to your database:
    mysql> select * from gfxddb.gfxdtest;
  9. Exclude DML Operations from DBSynchronizer Processing as Necessary

    The default DBSynchronizer implementation propagates all DML operations to a configured database. If you want to execute certain DML operations only on GemFire XD (without propagating those changes to configured listeners), then set the skip-listeners property to true when you connect to the distributed system. Setting skip-listeners to true prevents all DML statements that are executed on the connection from being queued and delivered to configured listener implementations.

  10. Change DBSynchronizer Connection Information as Necessary

    If you store DBSynchronizer initialization parameters in a separate file (recommended), then you can easily modify those parameters by editing the file and then restarting the DBSynchronizer configuration. For example, if you need to update the password for the user associated with the third-party database, first edit the parameter file to include the correct password. Then stop and restart the DBSynchronizer:
    gfxd> call sys.stop_async_event_listener('TESTLISTENER');
    gfxd> call sys.start_async_event_listener('TESTLISTENER');

    See DBSynchronizer Initialization Parameters for a complete reference to all DBSynchronizer initialization parameters.