为 RDI 准备 MySQL/MariaDB

准备 MySQL 和 MariaDB 数据库以使用 RDI

请按照以下部分中的步骤设置 MySQL 或 MariaDB 使用 Debezium 的 CDC 数据库。

1. 创建 CDC 用户

Debezium 连接器需要一个用户帐户才能连接到 MySQL/MariaDB。这 user 必须对需要 Debezium 的所有数据库具有适当的权限 以捕获更改。

运行 MySQL CLI 客户端，然后运行以下命令：

  1. 创建 CDC 用户：

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

  2. Grant the required permissions to the user:

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

# MySQL v8.0 and above
mysql> GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'localhost';

  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.

Note:
If you are using Amazon RDS for MySQL then you must enable automated backups for your database before it can use binary logging. If you don't enable automated backups first then the settings above will have no effect.

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.

RATE THIS PAGE
Back to top ↑