Skip to main content

Create a MySQL source connector

Stream every row change in your MySQL databases to Kafka topics, where sink connectors can deliver it to other systems. The connector reads the MySQL binary log (binlog), and can start with a consistent snapshot of existing data because MySQL purges old binlog files.

Prerequisites​

  • A CDC Service whose status is Healthy. See Create a CDC Service.
  • A supported MySQL topology:
    • Standalone, with the binlog enabled.
    • Primary and replica. The connector reads the binlog of one server and sees only the changes on that server.
    • High availability.
  • The database prepared for CDC as described in Prepare the MySQL database.

Prepare the MySQL database​

On a MySQL database from FPT Cloud, the binlog, GTIDs, and binlog_row_value_options are already set up. Run steps 1 to 3 and skip steps 4 to 8.

  1. Create the user that the connector connects as.

    CREATE USER 'YOUR_USER'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
  2. Grant the user SELECT on the databases to capture, and the global privileges RELOAD, SHOW DATABASES, REPLICATION SLAVE, and REPLICATION CLIENT.

    GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'YOUR_USER'@'%';
    FLUSH PRIVILEGES;

    To limit SELECT to one database, run instead:

    GRANT SHOW DATABASES, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'YOUR_USER'@'%';
    GRANT SELECT ON YOUR_DATABASE.* TO 'YOUR_USER'@'%';
    FLUSH PRIVILEGES;
  3. Grant the user INSERT and CREATE on the captured database if you plan to select Enable incremental snapshot. Creating the connector then creates the signal table cdc_signal in that database, and snapshots run through it.

    GRANT INSERT, CREATE ON YOUR_DATABASE.* TO 'YOUR_USER'@'%';
    FLUSH PRIVILEGES;
  4. Check whether the binlog is on.

    SHOW GLOBAL VARIABLES LIKE 'log_bin';
    -- Or, on MySQL 8.x
    SELECT variable_value FROM performance_schema.global_variables WHERE variable_name = 'log_bin';
    -- Or, on MySQL 5.x
    SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'log_bin';
  5. Add these lines to the MySQL configuration file if log_bin is OFF. Set server-id to the result of SHOW VARIABLES LIKE 'server_id';.

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

    If the binlog is already on and you only need to change its format, you can run these statements on the running server instead. SET cannot turn on log_bin, and the new values last only until the next restart unless you also add them to the configuration file.

    SET @@global.binlog_format = 'ROW';
    SET @@global.binlog_row_image = 'FULL';
    SET @@global.binlog_expire_logs_seconds = 864000;
  6. Turn on GTIDs. First check both variables.

    SHOW GLOBAL VARIABLES LIKE 'gtid_mode';
    SHOW GLOBAL VARIABLES LIKE 'enforce_gtid_consistency';

    If both are OFF, set them in the MySQL configuration file:

    gtid_mode = ON
    enforce_gtid_consistency = ON

    Or set them on a running server. MySQL changes gtid_mode one step at a time.

    SET @@global.enforce_gtid_consistency = ON;
    SET @@global.gtid_mode = OFF_PERMISSIVE;
    SET @@global.gtid_mode = ON_PERMISSIVE;
    SET @@global.gtid_mode = ON;
  7. Restart MySQL so that the changes to the configuration file in steps 5 and 6 take effect. Skip this step if you did not change the configuration file.

    warning

    Restarting MySQL interrupts the applications that use it. Plan the restart for a maintenance window.

  8. Set binlog_row_value_options to an empty value, so that the connector can capture UPDATE events.

    SHOW GLOBAL VARIABLES LIKE 'binlog_row_value_options';
    SET @@global.binlog_row_value_options = '';

Create the connector​

  1. Select Data platform → Workspace management, then select your workspace.

  2. On the Overview tab, under Services, select the CDC card.

  3. Select the Connectors tab, then select Create Connector.

    Connectors tab of a Healthy CDC Service with the No connector yet message and the Create Connector button

  4. On the Connector Information screen, enter a Name, select source in Type, and select MySQL in Database.

    Connector Information screen of the Create Connector form with the Name, Type, and Database fields

  5. Select Next Step. On the Properties screen, enter the connection details, the Database server ID, and the Topic prefix, then select Test connection. Next Step becomes available after the test succeeds with the message "Connect Successfully".

    Database Information section with From FPT Database Engine selected, the Database Name list, the connection fields, and Database server ID

    Database Information section with Manual configuration selected, the Host name, Port, Database, Username, Password, and Database server ID fields, and the Enable incremental snapshot checkbox

    Kafka topic section with the Topic prefix field

  6. Select Next Step. On the Additional properties screen, select the snapshot Mode, the tables and columns to capture, and the Heartbeat interval (ms).

    Snapshot section with the Mode list

    Include section with the orders table selected in Table (Optional) and the Column (Optional) - (orders) list

    Heartbeat section with 3000 entered in Heartbeat interval (ms)

  7. Select Next Step and check the settings on the Review screen.

    Review screen of a MySQL source connector with the Basic Information, Kafka topic, Database Information, Snapshot, Include, and Heartbeat sections

  8. Select Create.

    The portal shows "Create Successfully" and opens the Essential Information tab of the CDC Service. On the Connectors tab, the new connector shows Running, with every Tasks counter at 0 until its task starts, after about 2 minutes. Snapshot Status stays Not Started, because it tracks incremental snapshots only.

Connector settings​

Connector information​

FieldRequiredDescription
NameYesThe connector name, in lowercase letters, digits, and hyphens (-). Uppercase letters, _, ., and spaces give the error "Name has invalid characters".
TypeYesSelect source.
DatabaseYesSelect MySQL.

Properties​

FieldRequiredDescription
From FPT Database Engine / Manual configurationYesSelect a MySQL instance from FPT Database Engine, or enter the connection details yourself. Manual configuration is selected by default. See Connection mode.
Database NameWith From FPT Database EngineThe MySQL instance in FPT Database Engine. The list shows instance names only, and two instances can have the same name. Selecting one fills in Host name and Port (3306), which you can still edit. Database, Username, Password, and Database server ID stay empty.
Host nameYesHost name or IP address of the MySQL server.
PortYesThe MySQL server port, for example 3306. The field has no default in Manual configuration.
DatabaseYesThe database to capture changes from.
UsernameYesThe user you created in step 1.
PasswordYesThe user's password.
Database server IDYesA number that the connector uses as its own ID when it reads the binlog as a replication client. It must be unique among the replicas and other replication clients of the MySQL server, and must differ from the server's own server_id. The field has no default. Enter a number from 1000 to 9999.
Enable incremental snapshotNoLets you run incremental snapshots on this connector. Creating the connector then also creates the signal table cdc_signal in the Database you entered, and Test connection checks the CREATE TABLE and INSERT permissions. Without this option there is no signal table, so Snapshot → Execute cannot run, although the menu still shows it. See Incremental snapshot.
Topic prefixYesPrefix of the Kafka topics the connector writes to. See Topic prefix.

Additional properties​

FieldRequiredDescription
ModeYesWhat the connector does with existing rows. The field has no default, so select one. initial snapshots them, then captures changes. initial_only snapshots them without capturing changes. no_data captures new changes only. See Snapshot mode.
Table (Optional)NoThe tables to capture, from the database on the Properties screen. You can select several.
Column (Optional)NoThe columns to capture from the selected table. The field appears after you select a table, and its label shows the table name, for example Column (Optional) - (orders). Leave it empty to capture all columns.
Heartbeat interval (ms)YesTime between heartbeat messages, from 0 to 30000. Default 0, which sends no heartbeat. See Heartbeat.

Next steps​