Setup MySQL

This page describes how to configure your MySQL database to work with the Drasi MySQL source. This is a summary of the Debezium v2.7 documentation originally published under the Apache 2.0 License. The original documentation can be found at debezium.io. This documentation is licensed under the Apache 2.0 License.

Overview

The MySQL source is built upon the Debezium MySQL connector, which uses MySQL’s binary log (binlog) to capture changes. The binlog records transaction updates in a way that enables replicas to propagate those changes. For the connector to function properly, you need to enable binary logging and configure several other MySQL settings.

Creating a User

A Debezium MySQL connector requires a MySQL user account with appropriate permissions on all databases for which it captures changes.

Prerequisites

• A MySQL server
• Basic knowledge of SQL commands

Procedure

1. Create the MySQL user:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

2. Grant the replication permissions to the user:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'localhost';

3. Grant SELECT permission to user on the databases you want Drasi to access.

GRANT SELECT ON <your_database_name>.* TO 'user'@'localhost';

4. Finalize the user’s permissions:

FLUSH PRIVILEGES;

Required Permissions Explained

Enabling the Binary Log

You must enable binary logging for MySQL replication. The binary logs record transaction updates for replication purposes.

Prerequisites

• A MySQL server
• Appropriate MySQL user privileges

Procedure

1. Check whether the log-bin option is enabled:

For MySQL 5.x:

SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::" 
FROM information_schema.global_variables WHERE variable_name='log_bin';

For MySQL 8.x:

SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::" 
FROM performance_schema.global_variables WHERE variable_name='log_bin';

2. If the binlog is OFF, add the following properties to the configuration file for your MySQL server:

server-id = 223344
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
binlog_expire_logs_seconds = 864000

3. After making these changes, restart your MySQL server and confirm the binlog status again using the same query as in step 1.

Note: If you run MySQL on Amazon RDS, you must enable automated backups for your database instance for binary logging to occur.

Binlog Configuration Properties Explained

Enabling GTIDs (Optional)

Global transaction identifiers (GTIDs) uniquely identify transactions that occur on a server within a cluster. Though not required for a Debezium MySQL connector, using GTIDs simplifies replication and enables you to more easily confirm if primary and replica servers are consistent.

Prerequisites

• A MySQL server (version 5.6.5 or later)
• Basic knowledge of SQL commands
• Access to the MySQL configuration file

Procedure

1. Enable gtid_mode:

SET GLOBAL gtid_mode=ON;

2. Enable enforce_gtid_consistency:

SET GLOBAL enforce_gtid_consistency=ON;

3. Confirm the changes:

SHOW GLOBAL VARIABLES LIKE '%GTID%';

The result should show:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
| gtid_mode                | ON    |
+--------------------------+-------+

Note: For permanent configuration, add these settings to your MySQL configuration file:

gtid_mode=ON
enforce_gtid_consistency=ON