Configuring MySQL Database

Overview

The following database setup steps are necessary to use the MySQL connector. These steps are based on advice that can be found in the MySQL documentation and we recommend reading that documentation thoroughly.

Step 1: Configure MySQL Binary Logs for Replication

A binary log is a set of log files that keep track of data and data object changes performed on a MySQL server instance. Binary logs are commonly used for data replication and recovery.

Matillion Data Loader supports binary log data ingestion for replication from MySQL servers (BinLog). Binary logging must be enabled on your MySQL server for this to work. This can be done using the MySQL server configuration file or the MySQL server starting options.

Follow these steps to set up BinLog replication:

1. Check whether the log-bin option is already on:
2. Access the MySQL database that you want to check for BinLog activity:

mysql -h hostname -u user -p database

3. Open a secure shell and enter the command:

// for MySql 5.x
mysql> SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"
FROM information_schema.global_variables WHERE variable_name='log_bin';

// for MySql 8.x
mysql> SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"
FROM performance_schema.global_variables WHERE variable_name='log_bin';

If this statement returns a value of 1, BinLog is active. If value returned is 0, this means that BinLog is disabled. To enable it, follow the steps below.

4. Enable BinLog replication. If it is OFF, configure your MySQL server configuration file with the following:
5. Log in to your MySQL server instance.
6. Check your MySQL Server configuration:

sudo nano /etc/mysql/my.cnf

5. In the configuration file, ensure the following configurations are specified. If they aren't specified, add them now:

[mysqld]
binlog_format=ROW
binlog_row_image=FULL
expire_logs_days=10  -- The retention period (`expire_log_days`) can also be set in seconds by using the command: `binlog_expire_logs_seconds=259200`
log_bin=mysql-binlog  -- For ubuntu, use: `/var/log/mysql/mysql-bin.log` 
server-id=1 -- (only in the case of ubuntu) 

6. Restart the MySQL server using the command service mysql restart on your instance.
7. After restart, log in to the MySQL server to check BinLog again:

// for MySql 5.x
mysql> SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"
FROM information_schema.global_variables WHERE variable_name='log_bin';

// for MySql 8.x
mysql> SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"
FROM performance_schema.global_variables WHERE variable_name='log_bin';

The value returned is now 1. This indicates that BinLog is active.

Descriptions of MySQL BinLog configuration properties

Step 2: Grant privileges to the user

The database user specified in the CDC Pipeline must have the following global privileges:

• Select
• SUPER or (REPLICATION_CLIENT and REPLICATION_SLAVE)

To set up these privileges:

• Go to your MySQL server. For example: mysql -u root -p.
• Grant SELECT privileges to the user using following command:

GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON \*.* TO 'cdcuser'@'%';

Step 3: Specify MySQL Connection Settings

In the Configure your MySQL Source page in Matillion Data Loader, specify the following: