Prepare MySQL/MariaDB for RDI

Prepare MySQL and MariaDB databases to work with RDI

Follow the steps in the sections below to set up a MySQL or MariaDB database for CDC with Debezium.

1. Create a CDC user

The Debezium connector needs a user account to connect to MySQL/MariaDB. This user must have appropriate permissions on all databases where you want Debezium to capture changes.

Run the MySQL CLI client and then run the following commands:

1. Create the CDC user:

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

2. Grant the required permissions to the user:

   mysql> GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user' IDENTIFIED BY 'password';
   

3. Finalize the user's permissions:

   mysql> FLUSH PRIVILEGES;
   

2. Enable the binlog

You must enable binary logging for MySQL replication. The binary logs record transaction updates so that replication tools can propagate changes. You will need administrator privileges to do this.

First, you should check whether the log-bin option is already set to ON, using the following query:

// 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 log-bin is OFF then add the following properties to your server configuration file:

server-id         = 223344 # Querying variable is called server_id, e.g. SELECT variable_value FROM information_schema.global_variables WHERE variable_name='server_id';
log_bin                     = mysql-bin
binlog_format               = ROW
binlog_row_image            = FULL
binlog_expire_logs_seconds  = 864000

You can run the query above again to check that log-bin is now ON.

3. Enable GTIDs

Global transaction identifiers (GTIDs) uniquely identify the transactions that occur on a server within a cluster. You don't strictly need to use them with a Debezium MySQL connector, but you might find it helpful to enable them. Use GTIDs to simplify replication and to confirm that the primary and replica servers are consistent.

GTIDs are available in MySQL 5.6.5 and later. See the MySQL documentation about GTIDs for more information.

Follow the steps below to enable GTIDs. You will need access to the MySQL configuration file to do this.

1. Enable gtid_mode:

   mysql> gtid_mode=ON
   

2. Enable enforce_gtid_consistency:

   mysql> enforce_gtid_consistency=ON
   

3. Confirm the changes:

   mysql> show global variables like '%GTID%';
   
   >>> Result:
   
   +--------------------------+-------+
   | Variable_name            | Value |
   +--------------------------+-------+
   | enforce_gtid_consistency | ON    |
   | gtid_mode                | ON    |
   +--------------------------+-------+
   

4. Configure session timeouts

RDI captures an initial snapshot of the source database when it begins the CDC process (see the architecture overview for more information). If your database is large then the connection could time out while RDI is reading the data for the snapshot. You can prevent this using the interactive_timeout and wait_timeout settings in your MySQL configuration file:

mysql> interactive_timeout=<duration-in-seconds>
mysql> wait_timeout=<duration-in-seconds>

5. Enable query log events

If you want to see the original SQL statement for each binlog event then you should enable binlog_rows_query_log_events (MySQL configuration) or binlog_annotate_row_events (MariaDB configuration):

mysql> binlog_rows_query_log_events=ON

mariadb> binlog_annotate_row_events=ON

This option is available in MySQL 5.6 and later.

6. Check binlog_row_value_options

You should check the value of the binlog_row_value_options variable to ensure it is not set to PARTIAL_JSON. If it is set to PARTIAL_JSON then Debezium might not be able to see UPDATE events.

Check the current value of the variable with the following command:

mysql> show global variables where variable_name = 'binlog_row_value_options';

>>> Result:

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| binlog_row_value_options |       |
+--------------------------+-------+

If the value is PARTIAL_JSON then you should unset the variable:

mysql> set @@global.binlog_row_value_options="" ;

7. Configuration is complete

After following the steps above, your MySQL/MariaDB database is ready for Debezium to use.