Mysql Setup Guide

Prerequisites

• MySQL version 8.0 or above

• (Optional) A dedicated MySQL database user with replication permissions. Creating a dedicated user is recommended for better permission control and auditing:

  CREATE USER <matia_user>@'%' IDENTIFIED WITH mysql_password BY 'password'

• Grant replication privileges to your database user. The user must also be granted SELECT privileges for all columns you wish to sync:

  GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <matia_user>@'%'

• For AWS RDS:

  • Grant read access to the mysql.rds_heartbeat2 system table:

    GRANT SELECT ON mysql.rds_heartbeat2 TO <username>@'%';

  • Grant read access to the mysql.rds_configuration system table:

    GRANT SELECT ON mysql.rds_configuration TO <username>@'%';

  • Grant execute permission on the mysql.rds_kill stored procedure:

    GRANT EXECUTE ON PROCEDURE mysql.rds_kill TO '<username>'@'%';

• Configure your access control systems to allow inbound traffic from the following IPs: 23.21.86.124/32, 52.20.96.22/32 and 44.219.180.239/32

• Make sure that your database is configured as follows:

  [mysqld]
binlog-format=ROW
log-bin=mysql-binlog
server-id=123456789
expire-logs-days=7
log-slave-updates=1

  • Enables ROW format binary log replication, which is required to perform incremental updates.
  • Name the binary log (for example, mysql-binlog).
  • If your configuration already has a log-bin entry, you don't need to change it.
  • If your configuration already has a server-id entry, you don't need to change it. Otherwise, choose any number between 1 and 4294967295 as the server-id.
  • Set the log expiration to a minimum of one day. We recommend seven days.
  • Restart your MySQL server to effect these changes.

For AWS RDS:

• Set the binlog_format to ROW in your DB parameter group.
• Enable automatic backups for your DB instance.
• If you connected Matia to a read replica, check the value of the slave_parallel_workers parameter in your DB parameter group. If it's set to 0, the following changes are not required. Otherwise, update the DB parameter group with:
  • slave_preserve_commit_order = 1
  • slave_parallel_type = LOGICAL_CLOCK
  • binlog_order_commits = 1
• If you modified any of the settings above, reboot the instance to apply the changes.
• We recommend setting your binlog retention period to seven days (168 hours):

  CALL mysql.rds_set_configuration('binlog retention hours', 168);

• To allow Matia to validate the configuration, grant it access to the mysql.rds_configuration table:

  GRANT SELECT ON mysql.rds_configuration TO <username>@'%';

Setup Guide

1. Enter the Username and Password for your MySQL user.
2. Enter the Hostname for your database.
3. Enter the Port for your database.
4. Enter the Database for which you need to replicate data.
5. (Optional) If you are connecting through an SSH tunnel, enter your SSH Hostname, Port and User. Make sure to add the Public Key we've generated for your organization to your server's authorized keys.
6. Enter a Name for the connector.
7. (Optional) Enter a Description for the connector.
8. Select the Owner of the connector.
9. (Optional) Verify that your MongoDB is successfully connected by clicking on Test Connection.
10. Click Connect.

Notes

• Incremental cursors may expire or become corrupted. Cursor expiration can occur due a low sync frequency or the size limit of the binlog. An invalid cursor requires a Full Refresh of the affected stream.
• We support Full Refresh for views and tables without primary keys.
• We do not support the following system schemas:
  • performance_schema
  • information_schema
  • mysql
  • innodb
  • looker_scratch
  • Percona
  • tmp

Schema

• As we process your data, we transform MySQL data types into formats supported by Matia. The following table outlines how we convert MySQL data types:

MySQL Type	Matia Type
BINARY	BINARY
BIGINT	NUMBER
BIT(1)	BOOLEAN
BIT	BINARY
BLOB	BINARY
CHAR	STRING
DATE	TIMESTAMP_TZ
DATETIME	TIMESTAMP_TZ
DECIMAL	FLOAT
DOUBLE	FLOAT
ENUM	STRING
FLOAT	FLOAT
GEOMETRY	VARIANT
GEOMETRYCOLLECTION	VARIANT
JSON	VARIANT
INT	NUMBER
LINESTRING	VARIANT
LONGBLOB	BINARY
LONGTEXT	STRING
MEDIUMBLOB	BINARY
MEDIUMINT	NUMBER
MEDIUMTEXT	STRING
MULTILINESTRING	VARIANT
MULTIPOINT	VARIANT
MULTIPOLYGON	VARIANT
POINT	VARIANT
POLYGON	VARIANT
SET	STRING
SMALLINT	NUMBER
TEXT	STRING
TIME	STRING
TIMESTAMP	TIMESTAMP_TZ
TINYBLOB	BINARY
TINYINT(1)	BOOLEAN
TINYINT	NUMBER
TINYTEXT	STRING
VARCHAR	STRING
VARBINARY	BINARY
YEAR	NUMBER

Supported Streams

This connector outputs a dynamic list of streams, derived from the tables in your database.

Supported Sync Modes

• Full Refresh
• Incremental