To replicate your MySQL on RDS tables Alooma needs a read-replica, user, and password to be set up.
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:
Go to your Amazon RDS Dashboard. Select Instance Actions and then Create Read Replica.
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:
DB Parameter Group Family: mysql5.6 DB Parameter Group Name: mysql56sync DB Parameter Group Description: MySQL5.6 for Sync
Once completed, click Create.
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:
binlog_format: ROW log_bin_use_v1_row_events: 1 read_only: 0 net_read_timeout: 3600 net_write_timeout: 3600 wait_timeout: 86400
Once complete, click Save Changes to save your new settings.
Click on Instance Actions drop-down and choose Modify:
The Modify DB instance dialog will appear:
Set the following values (the other values can be the default value or be left empty):
DB Engine Version: MySQL 5.6.xx (default) New Master Password: <YOUR_PASSWORD> Parameter Group: mysql56sync Apply Immediately: Check
You will be required to enter a New Master Password. However, the password can be anything you choose (including your current password).
Once completed, select Apply Immediately and click Continue. When asked for confirmation, click Modify DB Instance:
You will now need to wait for the replica to become available before you can proceed... so take a break, have a coffee .
Once the instance is ready, select Instance Actions and then Modify. Set the following parameters:
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 .
Almost done! It's time to create a user for your new replica.
Log into the master server from a MySQL client (e.g., MySQL Workbench) with the master account.
Create a user to be used only by our sync process. You can do this in MySQL like so:
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;
For YOUR_PASSWORD, enter the password you want to use for this new user.
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.rds_set_configuration('binlog retention hours',
That's it, you're ready to continue to connecting to MySQL.