Configure an Amazon RDS MySQL database for CDC

This page describes how to configure change data capture (CDC) to stream data from an Amazon RDS for MySQL database to a supported destination , such as BigQuery or Cloud Storage.

Create a parameter group

1. Launch your Amazon RDS Dashboard.

2. In the Navigation Drawer, click Parameter Groups.

3. Click Create Parameter Group. The Create Parameter Grouppage appears.

4. Select the database family that matches your database, provide a name and description for the parameter group.

5. Click Create.

6. Select the checkbox to the left of your newly created parameter group.

7. Under Parameter group actions, click Edit.

8. Use the following table to set the parameters for your group.

   • For binlog-basedreplication:

     Parameter	Value
     binlog_format	ROW
     log_bin_use_v1_row_events	1
     read_only	0
     net_read_timeout	3600
     net_write_timeout	3600
     wait_timeout	86400

   • For GTID-basedreplication:

     Parameter

     Value

     binlog_format

     ROW

     log_bin_use_v1_row_events

     1

     read_only

     0

     net_read_timeout

     3600

     net_write_timeout

     3600

     wait_timeout

     86400

     expire-logs-days

     7

     binlog_row_image

     FULL

     max_allowed_packet

     1G (recommended value)

     log-replica-updates

     1

     enforce-gtid-consistency

     ON

     gtid_mode

     ON

     You can only set this parameter to ON after you set enforce-gtid-consistency to ON . In general, gtid_mode can have the following values:

     • OFF
     • OFF_PERMISSIVE
     • ON_PERMISSIVE
     • ON

     You can only change the values of the gtid_mode parameter one step at a time. For example, if the current mode is set to OFF , do the following:

     1. Change the setting to OFF_PERMISSIVE .
     2. Change the setting to ON_PERMISSIVE .
     3. Change the setting to ON .

     For more information, see MySQL documentation .

9. Click Save Changes.

Create a read replica

Although this is officially an optional step, we highly recommend using a read replica because it will lessen the load of replication on your MySQL database significantly.

If you choose to replicate directly from the main server, then follow the instructions in Configure the read replica (or source database) .

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 Actionsbutton, 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. Go to the Additional configurationsection.

5. Select the parameter group that you created .

6. Set the Backup retention periodto 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 Configurationstab.

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 Actionsmenu, 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. Create a Datastream user:

    CREATE 
     
    USER 
     
    'datastream' 
    @ 
    '%' 
     
    IDENTIFIED 
     
    BY 
     
    ' YOUR_PASSWORD 
   ' 
    ; 
   

2. Grant the following privileges to them:

    GRANT 
     
    REPLICATION 
     
    SLAVE 
    , 
     
    SELECT 
    , 
     
    REPLICATION 
     
    CLIENT 
     
    ON 
     
    * 
    . 
    * 
     
    TO 
     
    'datastream' 
    @ 
    '%' 
    ; 
    GRANT 
     
    EXECUTE 
     
    ON 
     
    PROCEDURE 
     
    mysql 
    . 
    rds_show_configuration 
     
    TO 
     
    'datastream' 
    @ 
    '%' 
    ; 
    FLUSH 
     
    PRIVILEGES 
    ; 
   

What's next

• Learn more about how Datastream works with MySQL sources .