MySQL RDS Setup Guide
Follow these instructions to replicate your MySQL RDS database to your destination via Fivetran.
Prerequisites
To connect your MySQL database to Fivetran, you need:
- MySQL version 5.1.5 or above for non-RDS databases (5.5.40 is the earliest version tested). MySQL version 5.6.13 or above for RDS databases.
- IP (for example, 1.2.3.4) or host (your.server.com)
- Port (usually 3306)
For the prerequisites connecting via an SSH tunnel, see Fivetran's Connection Options page.
Replica ID
The replica ID is a unique ID within the MySQL replica set. It must be an integer different from all other master and replica server IDs within the same group. By default, the replica ID is a random integer greater than 1000.
Connection options
First, you'll need to decide whether to connect your MySQL database directly or via an SSH tunnel. Security group configuration in subsequent steps will differ depending on this decision.
If you connect directly, you will need to create a rule in a security group that allows us access to your database instance.
If you connect via SSH, Fivetran will connect to a separate server in your network which provides an SSH tunnel to your database. This method is necessary if your database is in an inaccessible subnet. You will then configure your tunnel server's security group to allow us access and then configure the database instance's security to allow access from the tunnel.
If you have an SSH connection, please follow these instructions before proceeding to the next step.
Allow access to read replica
If you already have a read replica, you can skip ahead in the instructions to Enable Access.
If not, it's easy to create one. A read replica is necessary because RDS does not allow binary logging to be activated on the master alone (binary logging is required for incremental updates). It is also a good idea because it allows Fivetran to integrate your data without putting unnecessary load on or interrupting the queries running on your Master server.
To create a read replica, navigate to your RDS Dashboard and select the MySQL master instance:
Click "Instance Actions" and select "Create Read Replica":
Make sure the replica is accessible from outside your VPC:
Specify the instance type for the replica. It need not be as large your master instance:
Set an instance ID:
Then "Create Read Replica":
Your RDS dashboard should now show the status of the replica as "creating":
It will take a few minutes for the read replica to finish being created. The status will show "available" when it is done:
Enable Access
The Fivetran data processing servers will need access to your read replica. Assuming your instance is in a VPC, access is controlled by two mechanisms: VPC Security Groups and Network ACLs (Access Control Lists). If your instance is not in a VPC, then you only need to configure Security Groups.
Configure security group
These instructions assume that your read replica is in a VPC (if not, you can still use these instructions as a guide because configuring a non-VPC security group is almost identical).
Click on your read replica to view details:
A panel of details for your read replica will appear. Scroll to the "Connect" panel and write down the read replica's port number (you will need this later). Then click the link to its "Security Group" and a new tab should open up:
In the security group panel, select the "Inbound" tab:
Click "Edit":
Click "Add Rule":
A new Custom TCP Rule gets created at the bottom of the list with a blank space for a "Port Range" and a "Source IP address".
For the Port, enter your read replica's port number that you wrote down earlier (this will be 3306 for direct connections, unless you changed the default).
What you enter for the "Custom IP" depends on whether you're connecting directly or via an SSH tunnel: for a direct connection, enter Fivetran's IP. For a connection via an SSH tunnel enter {your-ssh-tunnel-server-ip-address}/32:
Click "Save":
Configure Network ACLs
Return to the RDS Dashboard and click on the read replica:
Scroll to the "Details" section and click the link to the read replica's "VPC" :
Select the VPC:
In the "Summary" tab, click the "Network ACL" link:
You will see tabs for "Inbound Rules" and "Outbound Rules". We will need to edit both. First, select "Inbound Rules":
If you have a default VPC that was automatically created by AWS, then the settings already allow all incoming traffic as indicated by the Source value 0.0.0.0/0 and the fact that the ALLOW entry is listed above the DENY entry:
If your inbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow "Source" Fivetran's IP for direct connection, or {your-ssh-tunnel-server-ip-address}/32 for connection via SSH to access the port number of your read replica (3306 for direct connection, unless you changed the default). Help on ACL configuration can be found here.
Select "Outbound Rules":
If your outbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow outbound traffic to all ports 1024-65535 for destination Fivetran's IP. For connections via SSH or {your-ssh-tunnel-server-ip-address}/32 for connection via SSH.
Create Fivetran user in master database
Next, we need to create a Fivetran user in your MySQL master database. This will not work on the read replica you created in the previous step, because the replica is read-only. When you create the user on the master, it will automatically be replicated to the replica.
WARNING: This user must be reserved for Fivetran use only and must be unique to your connector. For more information, see our MySQL documentation.
Using your favorite SQL tool (for example MySQL Workbench or the "mysql" command in your operating system's terminal window), create the "fivetran" user and grant replication permissions by running the following SQL commands. The password should of course be replaced with one of your choice:
CREATE USER fivetran@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO fivetran@'%';
Configure Replica
Two changes need to be made to the default RDS replica configuration: 1) Binary logging format needs to be changed to "ROW", and 2) Automated backups need to be turned on to enable binary logging
Start by creating a new "Parameter Group" in your RDS Dashboard:
Choose a "Parameter Group Family" that reflects the correct major version for your database, e.g. if your database version is 5.6.21, then the "Parameter Group Family" is "mysql5.6":
Enter a "Group Name" and a "Description" and click Create:
Select the new parameter group and then click "Edit Parameters":
Find the "binlog_format" parameter, change "binlog_format" to "ROW" and click Save Changes:
Now that we have created a custom parameter group, it needs to be applied to the replica along with enabling automated backups. Return to "Instances" in your RDS Dashboard:
Select your "MySQL replica", click "Instance Actions" -> "Modify":
In the "Modify DB Instance" screen, scroll down to find "Database Options" -> "DB Parameter Group". Change the parameter group setting to the new parameter group you created:
Now scroll down to find "Backup" -> "Backup Retention Period":
Change the "Backup Retention Period" to 1 day and click "Continue":
Select to "Apply Immediately", read the warning, and confirm that you wish to "Modify DB Instance":
The "Parameter Group" setting in the instance details should now show the name of the new parameter group. The status of the "Parameter Group" will say "applying" at first. Wait until it changes to "pending-reboot":
Then, for the changes to take effect, reboot your instance by selecting "Instance Actions" -> "Reboot":
Confirm that you want to "Reboot":
Rebooting will take a few minutes. Wait for the status of the Parameter Group to change to "in-sync" and for DB Instance Status to change to "available". Then the configuration change is complete!
Set your binlog retention period
To set a longer binlog retention period, run the following command on the connected database:
CALL mysql.rds_set_configuration('binlog retention hours', 168);
Grant Fivetran permission to check binlog retention period
During the connector setup process, Fivetran can check your binlog retention period and alert you if you need to set a longer retention period. If you want Fivetran to check your binlog retention period, you must grant Fivetran permission to access the mysql.rds_configuration table.
To grant access, run the following command on your RDS master:
GRANT SELECT ON mysql.rds_configuration to fivetran@'%'