MariaDB / SkySQL

Striim supports:

MariaDB and MariaDB Galera Cluster versions compatible with MySQL 5.5 and later (using MariaDB Reader)
MariaDB Xpand and SkySQL versions 5.3.x and 6.0.x (using MariaDB Xpand Reader)

MariaDB setup

To use MariaDBReader, an administrator with the necessary privileges must create a user for use by the adapter and assign it the necessary privileges:

CREATE USER 'striim' IDENTIFIED BY '******';
GRANT REPLICATION SLAVE ON *.* TO 'striim'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'striim'@'%';
GRANT SELECT ON *.* TO 'striim'@'%';

The caching_sha2_password authentication plugin is not supported in this release. The mysql_native_password plugin is required.
The REPLICATION privileges must be granted on *.*. This is a limitation of MariaDB.
You may use any other valid name in place of striim. Note that by default MariaDB does not allow remote logins by root.
Replace ****** with a secure password.
You may narrow the SELECT statement to allow access only to those tables needed by your application. In that case, if other tables are specified in the source properties for the initial load application, Striim will return an error that they do not exist.

On-premise MariaDB setup

See Activating the Binary Log.

MariaDB Galera Cluster setup

The following properties must be set on each server in the cluster:

binlog_format=ROW
log_bin=ON
log_slave_updates=ON
Server_id: see server_id
wsrep_gtid_mode=ON

Amazon RDS for MariaDB setup

Create a new parameter group for the database (see Creating a DB Parameter Group).
Edit the parameter group, change binlog_format to row and binlog_row_image to full, and save the parameter group (see Modifying Parameters in a DB Parameter Group).
Reboot the database instance (see Rebooting a DB Instance).
In a database client, enter the following command to set the binlog retention period to one week:
```
call mysql.rds_set_configuration('binlog retention hours', 168);
```

SkySQL setup in Striim Cloud

Set binlog_format to row.
Download the SSL root certificate .pem file for your SkySQL instance (see Connection Parameters in the SkySQL documentation).
Create a truststore using a Java keytool (see keytool), using the path and filename of your downloaded certificate:
keytool -importcert -alias MySQLCACert -keystore truststore -file /<path>/<filename>.pem -storepass striim
Upload the generated truststore to Striim Cloud as described in Manage Striim - Files.

The connection URL in MariaDB Xpand Reader will look something like this (but all on one line without spaces):

jdbc:mariadb://<host_name>:<port_no>/<database_name>/?
  useSSL=true&
  requireSSL=true&
  sslMode=VERIFY_CA&
  verifyServerCertificate=true&
  trustCertificateKeyStoreUrl=file:///opt/striim/UploadedFiles/truststore&
  trustCertificateKeyStorePassword=<password>&
  trustCertificateKeyStoreType=jks

MariaDB Reader and MariaDB Xpand Reader properties

These two readers are identical except as noted below.

When one of these readers is deployed to a Forwarding Agent, you must install the appropriate JDBC driver as described in Installing third-party drivers in the Forwarding Agent.

Striim provides templates for creating applications that read from MariaDB and write to various targets. See Creating an application using a template for details.

The adapter properties are:

property	type	default value	notes
Bidirectional Marker Table	String		When performing bidirectional replication, the fully qualified name of the marker table (see Bidirectional replication). This setting is case-sensitive.
CDDL Action	enum	Process	MariaDB Xpand Reader only: see Handling schema evolution.
CDDL Capture	Boolean	False	MariaDB Xpand Reader only: see Handling schema evolution.
Cluster Support	String		MariaDBReader only: set to `Galera` when reading from a MariaDB Galera Cluster.
Compression	Boolean	False	Set to True when the output of this reader is the input of a Cassandra Writer target. When replicating data from one MariaDB instance to another, when a table contains a column of type FLOAT, updates and deletes may fail with messages in the log including "Could not find appropriate handler for SqlType." Setting Compression to True may resolve this issue. If the table's primary key is of type FLOAT, to resolve the issue you may need to change the primary key column type in MySQL.
Connection Retry Policy	String	retryInterval=30, maxRetries=3	With the default setting, if a connection attempt is unsuccessful, the adapter will try again in 30 seconds (`retryInterval`. If the second attempt is unsuccessful, in 30 seconds it will try a third time (`maxRetries`). If that is unsuccessful, the adapter will fail and log an exception. Negative values are not supported.
Connection URL	String		When reading from MariaDB, `jdbc:mariadb://` followed by the MariaDB server's IP address or network name, optionally a colon and the port number (if not specified, port 3306 is used). When reading from a MariaDB Galera Cluster, specify the IP address and port for each server in the cluster, separated by commas: `jdbc:mariadb://<IP address>:<port>,<IP address>:<port>,...`. Appending `?zeroDateTimeBehavior=convertToNull` to the connection URL will convert "zero" DATE and DATETIME values) to nulls (see MySQLReader data type support and correspondence).
Excluded Tables	String		Change data for any tables specified here will not be returned. For example, if `Tables` uses a wildcard, data from any tables specified here will be omitted. Multiple table names and wildcards may be used as for `Tables`.
Filter Transaction Boundaries	Boolean	True	With the default value of True, begin and commit transactions are filtered out. Set to False to include begin and commit transactions.
Password	encrypted password		the password specified for the username (see Encrypted passwords)
Replace Invalid Date	String		MariaDB Xpand Reader only: if the database contains "zero" dates (`0000-00-00 00:00:00` or `0000-00-00`), specify a replacement date in the format `YYYY-MMM-dd HH:mm:ss` (see Joda-Time > Pattern-based formatting).
Send Before Image	Boolean	True	set to False to omit `before` data from output
Start Position	String		With the default value of null, reading starts with transactions that are committed after the Striim application is started. To start from an earlier point, specify a Global Transaction ID (GTID) in the format `GTID: #-#-#`, replacing `#-#-#` with the last GTID before the point where you want to start. Reading will start with the next valid GTID. If your environment has multiple binlog files, instead specify the name of the file and the offset for the start position, for example, `FileName:clustrix-bin.000001;offset:720`. When the application is recovered after a system failure, it will automatically resume from the point where it left off. See also Switching from initial load to continuous replication.
Start Timestamp	String	null	MariaDB Xpand Reader only: With the default value of null, only new (based on current system time) transactions are read. If a timestamp is specified, transactions that began after that time are also read. The format is YYYY-MMM-DD HH:MM:SS. For example, to start at 5:00 pm on February 1, 2020, specify 2020-FEB-01 17:00:00. When the application is recovered after a system failure, it will automatically resume from the point where it left off. See also Switching from initial load to continuous replication.
Tables	String		The table(s) for which to return change data in the format `<database>.<table>`. Names are case-sensitive. You may specify multiple tables as a list separated by semicolons or with the following wildcards in the table name only (not in the database name): `%`: any series of characters `_`: any single character For example, `my.%` would include all tables in the `my` database. The `%` wildcard is allowed only at the end of the string. For example, `mydb.prefix%` is valid, but `mydb.%suffix` is not. If any specified tables are missing Striim will issue a warning. If none of the specified tables exists, start will fail with a "found no tables" error.
Username	String		the login name for the user created as described in MariaDB setup

MariaDB Reader WAEvent fields

MariaDB Reader WAEvent fields are the same as as MySQL Reader WAEvent fields.

MariaDB Reader simple application

The following application will write change data for the specified table to SysOut. Replace wauser and ****** with the user name and password for the MariaDB account you created for use by MariaDB Reader (see MariaDB setup) and mydb and mytable with the names of the database and table(s) to be read.

CREATE APPLICATION MariaDBTest;

CREATE SOURCE MariaDBCDCIn USING MariaDBReader (
  Username:'striim',
  Password:'******',
  ConnectionURL:'jdbc:mariadb://192.168.1.10:3306',
  Database:'mydb',
  Tables:'mytable'
) 
OUTPUT TO MariaDBCDCStream;

CREATE TARGET MariaDBCDCOut
USING SysOut(name:MariaDBCDC)
INPUT FROM MariaDBCDCStream;

END APPLICATION MariaDBTest;

For MariaDB Galera Cluster, the connection URL would specify all nodes in the cluster.

CREATE SOURCE MySQLCDCIn USING MariaDBReader (
  Username:'striim',
  Password:'******',
  ClusterSupport: 'Galera'
  ConnectionURL:'mysql://192.168.1.10:3306,192.168.1.11:3306,192.168.1.12:3306',
  Database:'mydb',
  Tables:'mytable'
) 
OUTPUT TO MySQLCDCStream;

MariaDB Reader example output

Output is identical to that from MySQL Reader (see MySQLReader example output).

MariaDB Reader data type support and correspondence

Data type support and correspondence are identical to those for MySQL Reader (see MySQLReader data type support and correspondence).

Runtime considerations when using MariaDB Xpand Reader

If a Striim application terminates with an "unexpected end of stream" error, you may need to reduce MariaDB's wait_timeout from its default value of 28000 (almost eight hours) to something much shorter, such as 300 (five minutes).

In this section:

Striim Cloud 4.1.2 documentation