Configure a source MySQL database

Overview

If you're using MySQL as your source database, then you must set up change data capture (CDC) for your database.

To replicate your hosted MySQL tables, Datastream needs row-based replication and retention to be set up to be able to read your MySQL events.

Datastream works with the following types of MySQL databases:

Information about how to configure each of these MySQL database types is covered in the sections that follow.

Configure an Amazon RDS for MySQL database

The following sections cover how to configure CDC for an Amazon RDS for MySQL database.

Create a parameter group

  1. Launch your Amazon RDS Dashboard.
  2. In the Navigation Drawer, click Parameter Groups, and then click Create Parameter Group. The Create Parameter Group page appears.
  3. Select the database family that matches your database, provide a name and description for the parameter group, and then click Create.
  4. Select the check box to the left of your newly created parameter group, and then, under Parameter group actions, click Edit.
  5. Use the following table to set the parameters for your group.
    ParameterValue
    binlog_formatROW
    log_bin_use_v1_row_events1
    read_only0
    net_read_timeout3600
    net_write_timeout3600
    wait_timeout86400
  6. Click Save Changes.

Create a read replica

  1. Launch your Amazon RDS Dashboard.
  2. In the Navigation Drawer, click Databases.
  3. Select the main database instance to which you want Datastream to connect.
  4. Expand the Actions button, and then click Create read replica.
  5. Configure the read replica, as necessary.
  6. Click Create read replica.

Configure the read replica (or source database)

  1. Launch your Amazon RDS Dashboard.
  2. In the Navigation Drawer, click Databases.
  3. Select the read replica that you created, and then click Modify.
  4. Scroll down to the Additional configuration section.
  5. Select the parameter group that you created.
  6. Set the Backup retention period to 7 days.
  7. Click Continue.
  8. Under Scheduling of modifications, select Apply immediately.

Verify that the parameter group is assigned to the database instance

  1. Launch your Amazon RDS Dashboard.

  2. In the Navigation Drawer, click Databases, and then select your database instance.

  3. Click the Configurations tab.

  4. Verify that you see the parameter group that you created, and that its status is pending-reboot.

  5. Reboot your database instance to complete the configuration. To reboot the instance:

    1. In the Navigation Drawer, click Instances.
    2. Select your database instance.
    3. From the Instance Actions menu, select Reboot.

Set the binary log retention period

  1. Enter the following MySQL command:

    call mysql.rds_set_configuration('binlog retention hours', 168);
  2. Restart your MySQL server so that the changes you made can take effect.

Create a Datastream user

  1. To create a Datastream user, enter the following MySQL commands:

    CREATE USER 'datastream'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
    GRANT REPLICATION SLAVE, SELECT, REPLICATION CLIENT ON *.* TO 'datastream'@'%';
    FLUSH PRIVILEGES;
    

Configure an Amazon Aurora MySQL database

The following sections cover how to configure CDC for an Amazon Aurora MySQL database.

Create a parameter group

  1. Launch your Amazon RDS Dashboard.
  2. In the Navigation Drawer, click Parameter Groups, and then click Create Parameter Group. The Create Parameter Group page appears.
  3. Use the following table to populate the fields of this page, and then click Create:
    FieldDescription
    Parameter group familySelect the family that matches your database.
    TypeSelect DB Cluster Parameter Group.
    Group nameProvide a name for the parameter group.
    DescriptionProvide a description for the parameter group.
  4. Select the checkbox to the left of your newly created parameter group, and then, under Parameter group actions, click Edit.
  5. Change the value of the binlog_format parameter to ROW.
  6. Click Save Changes.

Assign the parameter group to the database instance

  1. Launch your Amazon RDS Dashboard.
  2. In the Navigation Drawer, click Databases, and then select your database instance.
  3. From the Instance Actions menu, select Modify. The Modify DB Instance dialog box appears.
  4. In the Additional configuration section, select the database cluster parameter group that you created.
  5. Set the Backup retention period to 7 days.
  6. Click Continue.
  7. In the Scheduling of Modifications pane, select the Apply immediately option.

Set the binary log retention period

  1. Enter the following MySQL command:

    call mysql.rds_set_configuration('binlog retention hours', 168);
  2. Restart your MySQL server so that the changes you made can take effect.

Create a Datastream user

  1. To create a Datastream user, enter the following MySQL commands:

    CREATE USER 'datastream'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
    GRANT REPLICATION SLAVE, SELECT, REPLICATION CLIENT ON *.* TO 'datastream'@'%';
    FLUSH PRIVILEGES;
    

Configure a Cloud SQL for MySQL database

Enable binary logging

  1. To enable binary logging for Cloud SQL for MySQL, see Enabling point-in-time recovery.

Create a Datastream user

  1. To create a Datastream user, enter the following MySQL commands:

    CREATE USER 'datastream'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
    GRANT REPLICATION SLAVE, SELECT, REPLICATION CLIENT ON *.* TO 'datastream'@'%';
    FLUSH PRIVILEGES;
    

Configure a self-managed MySQL database

The following sections cover how to configure CDC for a self-managed MySQL database. These instructions also work for databases such as MariaDB, Percona Server for MySQL or Alibaba Cloud PolarDB.

Verify the configuration and retention period of the binary log

  1. Confirm that the binary log is configured correctly by entering the following MySQL command:

    SHOW GLOBAL VARIABLES LIKE '%binlog_format%';
  2. Verify that the value for the binlog_format variable is set to ROW.

  3. Confirm that the row format for the binary log is set to FULL by entering the following MySQL command:

    SHOW GLOBAL VARIABLES LIKE 'binlog_row_image';
  4. Verify that the slave updates option for the binary log is set to ON by entering the following MySQL command:

    SHOW GLOBAL VARIABLES LIKE 'log_slave_updates';
  5. Verify that the retention period of the binary log is set to 7 days by entering the following MySQL command:

    SHOW GLOBAL VARIABLES LIKE 'expire_logs_days';

Create a Datastream user

  1. To create a Datastream user, enter the following MySQL commands:

    CREATE USER 'datastream'@'%' IDENTIFIED BY 'YOUR_PASSWORD';
    GRANT REPLICATION SLAVE, SELECT, REPLICATION CLIENT ON *.* TO 'datastream'@'%';
    FLUSH PRIVILEGES;