Documentation

Setting up CDC for MySQL on RDS

To replicate your MySQL on RDS tables Alooma needs a read-replica, user, and password to be set up.

Note

Please consult this article for a list of supported DDL changes.

While this is officially an optional step, we highly recommend it as it will significantly lessen the load of replication on your MySQL.

You can set up CDC for MySQL on RDS in 4 steps:

Step 1- Create a Read Replica
  1. Go to your Amazon RDS Dashboard. Select Instance Actions and then Create Read Replica.

  2. In the sidebar, click on Parameter Groups, then click the blue Create DB Parameter Group button. You will see a screen with 3 boxes. Enter the following settings:

    1_mysqlRDS_Create_Read_Replica.png
    Copy
    DB Parameter Group Family: mysql5.6
    DB Parameter Group Name: mysql56sync
    DB Parameter Group Description: MySQL5.6 for Sync
  3. Once completed, click Create.

  4. Next, select the icon to the left of the newly created parameter group, mysql56sync and click Edit Parameters. You will need to set a few of the parameters as follows:

    2_mysqlRDS_Create_Param_Group.png
    Copy
    binlog_format: ROW
    log_bin_use_v1_row_events: 1 
    read_only: 0 
    net_read_timeout: 3600 
    net_write_timeout: 3600 
    wait_timeout: 86400
  5. Once complete, click Save Changes to save your new settings.

Step 2 - Configure the Read Replica
  1. Click on Instance Actions drop-down and choose Modify:

    3_mysqlRDS_Press_Modify.png
  2. The Modify DB instance dialog will appear:

    4_mysqlRDS_Modify_Contents.png

    Set the following values (the other values can be the default value or be left empty):

    Copy
    DB Engine Version: MySQL 5.6.xx (default) 
    New Master Password: <YOUR_PASSWORD>
    Parameter Group: mysql56sync 
    Apply Immediately: Check 
    

    Note

    You will be required to enter a New Master Password. However, the password can be anything you choose (including your current password).

  3. Once completed, select Apply Immediately and click Continue. When asked for confirmation, click Modify DB Instance:

    5_mysqlRDS_Press_Modify_DB.png

You will now need to wait for the replica to become available before you can proceed... so take a break, have a coffee coffee.png.

Once the instance is ready, select Instance Actions and then Modify. Set the following parameters:

Copy
Backup Retention Period: 7days 
Apply Immediately: Check 

Now that you’ve modified the replica, once again you will need to wait until it becomes available before proceeding coffee.png.

Step 3 - Create a User

Almost done! It's time to create a user for your new replica.

  1. Log into the master server from a MySQL client (e.g., MySQL Workbench) with the master account.

  2. Create a user to be used only by our sync process. You can do this in MySQL like so:

    Copy
    mysql> CREATE USER 'alooma'@'%' IDENTIFIED BY '<YOUR_PASSWORD>'; 
    mysql> GRANT REPLICATION SLAVE, SELECT, RELOAD, REPLICATION CLIENT, 
           LOCK TABLES, EXECUTE ON *.* TO 'alooma'@'%'; 
    mysql> FLUSH PRIVILEGES;
  3. For <YOUR_PASSWORD>, enter the password you want to use for this new user.

Step 4 - Binary log retention

To ensure proper replication, configure your system to retain binary logs for a minimum of 7 days (168 hours). To do this, enter the following MySQL command:

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

That's it, you're ready to continue to connecting to MySQL.

Search results

    No results found