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 currently 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
- Launch your Amazon RDS Dashboard.
- In the Navigation Drawer, click Parameter Groups, and then click Create Parameter Group. The Create Parameter Group page appears.
- Select the database family that matches your database, provide a name and description for the parameter group, and then click Create.
- Select the check box to the left of your newly created parameter group, and then, under Parameter group actions, click Edit.
- Use the following table to set the parameters for your group.
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
- Click Save Changes.
Create a read replica
- Launch your Amazon RDS Dashboard.
- In the Navigation Drawer, click Databases.
- Select the main database instance to which you want Datastream to connect.
- Expand the Actions button, and then click Create read replica.
- Configure the read replica, as necessary.
- Click Create read replica.
Configure the read replica (or source database)
- Launch your Amazon RDS Dashboard.
- In the Navigation Drawer, click Databases.
- Select the read replica that you created, and then click Modify.
- Scroll down to the Additional configuration section.
- Select the parameter group that you created.
- Set the Backup retention period to 7 days.
- Click Continue.
- Under Scheduling of modifications, select Apply immediately.
Verify that the parameter group is assigned to the database instance
Launch your Amazon RDS Dashboard.
In the Navigation Drawer, click Databases, and then select your database instance.
Click the Configurations tab.
Verify that you see the parameter group that you created, and that its status is pending-reboot.
Reboot your database instance to complete the configuration. To reboot the instance:
- In the Navigation Drawer, click Instances.
- Select your database instance.
- From the Instance Actions menu, select Reboot.
Set the binary log retention period
Enter the following MySQL command:
call mysql.rds_set_configuration('binlog retention hours', 168);
Restart your MySQL server so that the changes you made can take effect.
Create a Datastream user
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
- Launch your Amazon RDS Dashboard.
- In the Navigation Drawer, click Parameter Groups, and then click Create Parameter Group. The Create Parameter Group page appears.
- Use the following table to populate the fields of this page, and then click Create:
Field Description Parameter group family Select the family that matches your database. Type Select DB Cluster Parameter Group. Group name Provide a name for the parameter group. Description Provide a description for the parameter group. - Select the checkbox to the left of your newly created parameter group, and then, under Parameter group actions, click Edit.
- Change the value of the
binlog_format
parameter to ROW. - Click Save Changes.
Assign the parameter group to the database instance
- Launch your Amazon RDS Dashboard.
- In the Navigation Drawer, click Databases, and then select your database instance.
- From the Instance Actions menu, select Modify. The Modify DB Instance dialog box appears.
- In the Additional configuration section, select the database cluster parameter group that you created.
- Set the Backup retention period to 7 days.
- Click Continue.
- In the Scheduling of Modifications pane, select the Apply immediately option.
Set the binary log retention period
Enter the following MySQL command:
call mysql.rds_set_configuration('binlog retention hours', 168);
Restart your MySQL server so that the changes you made can take effect.
Create a Datastream user
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
- To enable binary logging for Cloud SQL for MySQL, see Enabling point-in-time recovery.
Create a Datastream user
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-hosted MySQL database
The following sections cover how to configure CDC for a self-hosted MySQL database.
Verify the configuration and retention period of the binary log
Confirm that the binary log is configured correctly by entering the following MySQL command:
SHOW GLOBAL VARIABLES LIKE '%binlog_format%';
Verify that the value for the
binlog_format
variable is set toROW
.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';
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';
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
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;