|Caching Data with GemFire XD / Using DBSynchronizer to Apply DML to an RDBMS|
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.
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.
The following procedure describes how to configure GemFire XD with DBSynchronizer to apply table DML operations to a third-party database.
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.
$ sudo mysqld_safe
$ 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.
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.
$ export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
Start a GemFire XD data store in a custom 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.
$ gfxd encrypt-password external -mcast-port=10334 Enter User Name: gfxduser Enter password: gfxdpassword Re-enter password: gfxdpassword Connecting to distributed system: mcast=/18.104.22.168:10334 Encrypted to 25325ffc3345be8888eda8156bd1c313
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.
Create the AsyncEventListener configuration
Driver=com.mysql.jdbc.Driver URL=jdbc:mysql://localhost:3306/gfxddb User=gfxduser secret=25325ffc3345be8888eda8156bd1c313
$ 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);
The LISTENERCLASS argument must specify the built-in GemFire XD DBSynchronizer implementation.
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);
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.
Start the AsyncEventListener
gfxd> call sys.start_async_event_listener('TESTLISTENER');
Create identical schema and tables in GemFire XD
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.
Execute DML and verify synchronization
gfxd> insert into gfxdtest values (1, '1st Entry');
mysql> select * from gfxddb.gfxdtest;
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.
Change DBSynchronizer Connection Information as Necessary
gfxd> call sys.stop_async_event_listener('TESTLISTENER'); gfxd> call sys.start_async_event_listener('TESTLISTENER');