Skip to main content

MariaDB / SkySQL

Striim supports:

  • MariaDB and MariaDB Galera Cluster versions 10.0.2 and later using MariaDB Reader (for versions 5.5 to 10.0.1, use MySQL 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'@'%';

If you are reading from MariaDB 10.5 or later, also enter:

GRANT BINLOG MONITOR 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.

On-premise MariaDB setup with multi-source replication

If you are using multi-source replication, use MySQL Reader rather than MariaDB Reader. For more information, see Multi-Source Replication, Multi-source replication in MariaDB 10.0, Multisource Replication: How to resolve the schema name conflicts, and High Availability with Multi-Source Replication in MariaDB Server.

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

  1. Create a new parameter group for the database (see Creating a DB Parameter Group).

  2. 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).

  3. Reboot the database instance (see Rebooting a DB Instance).

  4. 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

  1. In Striim 4.2.0.3 or earlier, set binlog_format to row both in the global configuration and for each individual binlog to be read. Starting in Striim 4.2.0.4, the global configuration can be default.

  2. Download the SSL root certificate .pem file for your SkySQL instance (see Connection Parameters in the SkySQL documentation).

  3. 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

  4. 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

Specify 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 connecting through an SSH tunnel (see Using an SSH tunnel to connect to a source or target), specify the IP address of the tunnel.

Only one MariaDB Reader at a time can connect to a MariaDB database instance. Thus a single Striim application may not contain more than one MariaDB Reader. If a second Striim application attempts to connect to a MariaDB database instance that is already connected to a MariaDB Reader, the first application will halt.

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>,....

When reading from SkySQL using MariaDB Xpand reader, specify the following, all on one line:

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

Excluded Tables

String

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 (separated by semicolons) and wildcards may be used exactly 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 source has DATE or DATETIME columns containing"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 the name of the file and the offset for the start position, for example, FileName:clustrix-bin.000001;offset:720.

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 you are using schema evolution (see Handling schema evolution, set a Start Position only if you are sure that there have been no DDL changes after that point.Handling schema evolution

If your environment has multiple binlog files, specify the name of the one to use, for example, FileName:clustrix-bin.000001.

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.

If you are using schema evolution (see Handling schema evolution, set a Start Timestamp only if you are sure that there have been no DDL changes after that point.Handling schema evolution

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.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 MySQL Reader data type support and correspondence).

Runtime considerations when using MariaDB Reader

  • The default value of MariaDB's wait_timeout is 28800 seconds (eight hours). Reducing this to 300 seconds (five minutes) can resolve a variety of errors such as "connect timed out" or "unexpected end of stream." See wait_timeout for more information.

  • Only one MariaDB Reader at a time can connect to a MariaDB database instance. Thus a single Striim application may not contain more than one MariaDB Reader. If a second Striim application attempts to connect to a MariaDB database instance that is already connected to a MariaDB Reader, the first application will halt.

Runtime considerations when using MariaDB Xpand Reader

The default value of MariaDB's wait_timeout is 28800 seconds (eight hours). Reducing this to 300 seconds (five minutes) can resolve a variety of errors such as "connect timed out" or "unexpected end of stream." See wait_timeout for more information.