Configuring the MySQL CDC (Binlog)

Overview

Change Data Capture (CDC) enables ROMA Connect to synchronize data with data sources and physically delete data tables in real time.

This section describes how to enable the CDC function in Binlog mode for the MySQL database.

Prerequisites

• Fields of the binary type, such as TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB, cannot be collected.
• The DATETIME(6) field cannot be collected.

Procedure

It is recommended that the database administrator configure the CDC function. The following uses the Linux environment as an example.

1. Enable Binlog.
   1. Use a command line tool to connect to the server where the MySQL database is located and run the following command to log in to the database as user root:

      mysql -uroot -ppassword

      password indicates the password of user root of the database. You can obtain the password from the database administrator.

   2. Run the following command to check whether Binlog is enabled for the MySQL database:

      show variables like 'log_bin';
      • If the value of log_bin is OFF, the Binlog function is disabled. Go to the next step.
      • If the value of log_bin is ON, the Binlog function is enabled. Run the following SQL command to check whether the parameter settings meet the requirements:

        show variables like '%binlog_format%';
        show variables like '%binlog_row_image%';

        The value of binlog_format must be ROW, and the value of binlog_row_image must be FULL. If yes, go to 2. If no, go to the next step.

   3. Run the following command to exit the database:

      exit;
   4. Run the following command to open the configuration file and press i to enter the editing mode:

      vi /etc/my.cnf
   5. Add the following content to the file to enable Binlog:

      server-id = 123
      log_bin = mysql-bin
      binlog_format = row
      binlog_row_image = full
      expire_logs_days = 10
      gtid_mode = on
      enforce_gtid_consistency = on

      Among them:

      • The value of server-id must be an integer greater than 1. Set this parameter based on site requirements. The value of Server Id set during data integration task creation must be different from the value of this parameter.
      • expire_logs_days indicates the retention period of Binlog files. Binlog files that have been retained for more than two days will be automatically deleted.
      • gtid_mode = on and enforce_gtid_consistency = on are added only when the MySQL version is 5.6.5 or later. Otherwise, delete the two rows.
   6. Press Esc to exit the input mode, enter :wq, and press Enter to save the settings and exit.
   7. Run the following command to restart the MySQL database:

      service mysqld restart
   8. Log in to the database as user root and run the following command to check whether the value of the log_bin variable is ON, that is, whether the Binlog function is enabled:

      show variables like 'log_bin';
   9. (Optional) When creating schema mapping for a MySQL CDC task, add binlog_rows_query_log_events = 1 to the MySQL database configuration file in e.
2. Run the following commands in the database to create a user for connecting ROMA Connect to the database and configure permissions for the user:

   CREATE USER 'roma'@'%' IDENTIFIED BY 'password';
   GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'roma'@'%';

   Among them:

   • roma indicates the name of the ROMA Connect connection user. Set it based on site requirements.
   • password indicates the password of the ROMA Connect connection user. Set it based on site requirements.
3. (Optional) If the MySQL database version is 8.0, run the following command to change the password authentication mode of the database connection user:

   ALTER USER roma IDENTIFIED WITH mysql_native_password BY 'password';

   Among them:

   • roma is the database connection username created in 2.
   • password is the password of the database connection user.
4. Run the following command to exit the database:

   exit;