[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 for connecting via an SSH tunnel, see Fivetrans “, 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 primary and replica server IDs within the same group. By default, the replica ID is a random integer greater than 1000., Choose your connection option , First, decide whether to connect Fivetran to your MySQL database directly or through an SSH tunnel. How you configure security groups will differ based on your connection method., Connect directly, If you connect directly, you must create a rule in a security group that allows Fivetran access to your database instance. , Connect via SSH, “If you connect via SSH, Fivetran connects to a separate server in your network which provides an SSH tunnel to your database. You must then configure your tunnel servers security group to allow Fivetran access and configure the database instances security to allow access from the tunnel. You must connect through SSH if your database is contained within an inaccessible subnet. “, If you have an SSH connection follow these instructions, before you proceed to the next step. , Create a read replica , If you already have a read replica, skip ahead to , the Enable Access section, ., If you do not have a read replica, follow the instructions below to create one. You must connect Fivetran to a read replica because RDS does not allow binary logging to be activated on the primary database alone; Fivetran requires binary logging to perform incremental updates. Connecting Fivetran to a read replica also allows us to integrate your data without putting unnecessary load on or interrupting the queries running on your primary server., Navigate to your RDS Dashboard and select the MySQL primary instance., Click , Instance Actions then select , Create Read Replica, ., Make sure the replica is accessible from outside your VPC if you choose to connect directly. You do , not, have to make your database publicly accessible if you choose to connect using SSH., Specify the instance type for the replica. It does not need to be as large your primary instance., Set an instance ID., Click , 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 change to “available” when the read replica is created., Enable access to read replica , “Fivetrans data processing servers need access to your read replica. If your instance is in a VPC, two mechanisms control access – VPC Security Groups and Network Access Control Lists (ACLs). If your instance is not in a VPC, you only need to configure Security Groups.”, Configure security group, These instructions assume that your read replica is in a VPC. If your read replica is not in a VPC, 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 appears. Scroll to the , Connect, ” panel and note the read replicas port number (you will need this later). Then click the link to its security group, which makes a new tab open.”, In the security group panel, select the , Inbound, tab., Click , Edit, ., Click , Add Rule, . This creates a new Custom TCP Rule at the bottom of the list with a blank space for a , Port Range, and a , Source IP address, ., Enter your , Port Range, and , Source IP address, values., In the , Port Range, ” field, enter your read replicas port number that you wrote down in Step 2 of this section. The port number will be “, 3306, for direct connections, unless you changed the default., In the , Custom IP, field, enter , “Fivetrans IP”, if you are connecting directly or , {your-ssh-tunnel-server-ip-address}/32, if you are connecting through an SSH tunnel., Click , Save, ., Configure Network ACLs, Return to the RDS Dashboard and click on the read replica., Scroll to the , Details, ” section. Click the link to the read replicas VPC.”, Select the VPC., In the , Summary, tab, click the , Network ACL, link., You will see tabs for , Inbound Rules, and , Outbound Rules, . You must edit both. , Edit inbound rules, Select , Inbound Rules, ., If you have a default VPC that was automatically created by AWS, the settings already allow all incoming traffic. To verify that the settings allow incoming traffic, confirm that the Source value is , 0.0.0.0/0, and that the ALLOW entry is listed above the DENY entry., “If your inbound rules dont include an “, ALL – 0.0.0.0/0 – ALLOW, entry, edit the , Source, field to allow , “Fivetrans 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. The port number will be , 3306, for direct connections, unless you changed the default. For help on ACL configuration, see , “AWS Network ACL documentation”, ., Edit outbound rules, Select , Outbound Rules, ., “If your outbound rules dont include an “, ALL – 0.0.0.0/0 – ALLOW, entry, edit the rules to allow outbound traffic to all ports , 1024-65535, for , Destination, , “Fivetrans IP”, (for direct connection) or , {your-ssh-tunnel-server-ip-address}/32, for connection via SSH. , Create Fivetran user in primary database, Next, you must create a Fivetran user in your MySQL primary 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 in the primary database, 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 systems terminal window), create the , fivetran, user and grant replication permissions by running the following SQL commands. Replace , password, with a password of your choice., ” CREATE USER fivetran@% IDENTIFIED WITH mysql_native_password BY password;n GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO fivetran@%;n”, Configure replica , You must make two changes to the default RDS replica configuration – you must change the binary logging format to ROW and turn on automated backups to enable binary logging., Start by creating a new parameter group in your RDS Dashboard. Go to your RDS dashboard and click , Parameter groups, ., , Click , Create new parameter group, ., Choose a , Parameter Group Family, that reflects the correct major version for your database. For example, if your database version is 5.6.21, then the Parameter Group Family is , mysql5.6, ., Enter a , Group Name, and a , Description then click , Create, ., Select the new parameter group. , Click , Edit Parameters, ., Find the , binlog_format, parameter. Change the binlog format value to , ROW then click , Save Changes, ., Now that you have created a custom parameter group, you must apply it to the replica and enable automated backups. Return to , Instances, in your RDS Dashboard., Select your MySQL replica. Click , Instance Actions then click , Modify, ., In the , Modify DB Instance, screen, scroll down to find the , Database Options, section. Change the , DB Parameter Group, value to the new parameter group you created in Step 4 of this section., Scroll down to , Backup, . Find the , Backup Retention Period, field., Change the , Backup Retention Period, to 1 day, then click , Continue, ., Select , Apply Immediately, . Read the warning, then click , Modify DB Instance, ., The , Parameter Group, setting in the instance details should now show the name of your new parameter group. The Parameter Group status will say “applying” at first. Wait until the status changes to “pending-reboot”., Reboot your instance by selecting , Instance Actions, -> , Reboot”, to make the changes take effect., , Click , Reboot, to confirm that you want to reboot the instance., Rebooting will take a few minutes. The configuration change is complete when the Parameter Group status changes to “in-sync” and the DB Instance Status changes to “available”., 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 primary:, “GRANT SELECT ON mysql.rds_configuration to fivetran@%”, Related articles, description, Connector Overview, , account_tree, Schema Information, , assignment, Release Notes, , settings, API Connector Configuration, , home, Documentation Home]