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 currently works with the following types of MySQL databases:

• Amazon RDS for MySQL
• Amazon Aurora MySQL
• Cloud SQL for MySQL
• Self-hosted MySQL

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.

   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

6. 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 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.

Because you modified your read replica, you must wait until the changes to your parameter group are applied before proceeding.

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.
   After you reboot your database instance, its status will change to in-sync.

Set the binary log retention period

1. Enter the following MySQL command:

   call mysql.rds_set_configuration('binlog retention hours', 168);

   You're entering this command because you want to ensure proper replication by configuring your system to retain binary logs for a minimum of 7 days (or 168 hours).

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;
   

   Under certain conditions, Datastream might also require the LOCK TABLE permission. For more information, see MySQL change data capture.

Configure an Amazon Aurora MySQL database

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

Before configuring CDC for your Amazon Aurora MySQL database, make sure that you have an existing Aurora cluster and that you have connected to the Writer endpoint of your master database server.

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:

   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.

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);

   You're entering this command because you want to ensure proper replication by configuring your system to retain binary logs for a minimum of 7 days (or 168 hours).

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;
   

   Under certain conditions, Datastream might also require the LOCK TABLE permission. For more information, see MySQL change data capture.

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;
   

   Under certain conditions, Datastream might also require the LOCK TABLE permission. For more information, see MySQL change data capture.

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

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';

   If your MySQL database isn't set to full row-based replication and a 7-day retention period, then you'll need to change the configuration of the binary log, as follows:

   1. Navigate to the /etc/mysql/ directory.
   2. Using an editor, open the my.cnf file.
   3. Add the following lines to the file:

          [mysqld] 
          log-bin=mysql-bin 
          server-id=1 
          binlog_format=ROW 
          log-slave-updates=true
          expire_logs_days=7
          

      You're entering the expire_logs_days=7 line of code because you want to ensure proper replication by configuring your system to retain binary logs for a minimum of 7 days.
   4. Save your changes to the my.cnf file, and then close the file.
   5. 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;
   

   Under certain conditions, Datastream might also require the LOCK TABLE permission. For more information, see MySQL change data capture.