Amazon Aurora MySQL

Share on facebook
Share on twitter
Share on linkedin

Snowflake Cost Saving

We Automate SnowflakeDB Data Cloud Cost Saving. Sign Our Free 7 Days No Risk Trail Now

Data Connector Description:

Data Connector Type: Database

Data Connector Documentation:

[Aurora RDS MySQL Setup Guide, Follow these instructions to replicate your Aurora RDS MySQL 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. , Note: We do not support serverless Aurora., 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., Choose your connection option, “First, youll 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.”, 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, please follow , these instructions, before proceeding to the next step., Allow access to master, “You must allow Fivetrans data processing server access to your master/writer server. You cannot connect Fivetran to an Aurora MySQL reader because AWS does not make binary logs available on reader nodes. We need binary logs to perform incremental updates.”, In your RDS Dashboard, select the Aurora instance you would like to use. Make sure its replication role is “writer”, then click on the instance to view its details., Scroll down to the , Connect, panel and make sure that the , Publicly Accessible, setting is , Yes, if you choose to connect directly. You do , not, have to make your database publicly accessible if you choose to connect using SSH., Enter the , Endpoint, host address and port number into the form fields on the left., Configure security group, “You must configure your Aurora clusters VPC security group to allow Fivetran to access the cluster. “, “Click the link to the master nodes “, Security Group which will open a new tab., In the security group panel, select the , Inbound, tab., Click , Edit, ., Click , Add Rule, ., A new Custom TCP rule is created at the bottom of the list with a blank space for a , Port Range, and a , Source, IP address., In the , Port Range, ” field, enter your master nodes port number that you copied into the form on the left. “, What you enter in the , Source, ” Custom IP field depends on whether youre connecting directly or via an SSH tunnel. For a direct connection, enter “, “Fivetrans IP”, . For a connection via an SSH tunnel, enter , {your-ssh-tunnel-server-ip-address}/32, ., Click , Save, ., Configure Network ACLs, Return to the master node and scroll to the , Details, panel. Click the link to the , VPC which will open a new tab., Select the VPC., In the , Summary, tab, click the , Network ACL, link. This will open a new tab., Select the , Network ACL, ., You will see tabs for , Inbound Rules, and , Outbound Rules, . You need to edit both. First, select , Inbound Rules, ., If you have a default VPC that was automatically created by AWS, 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 dont include an “, ALL – 0.0.0.0/0 – ALLOW, entry, edit the rules to allow either , “Fivetrans IP”, (for direct connection) or , {your-ssh-tunnel-server-ip-address}/32, (for connection via SSH) to access the port number of your master node. For help on ACL configuration, see , “AWS Network ACL documentation”, ., 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”, or your SSH server., Create user, Next, you must create a Fivetran user in your Aurora database. Make sure to do this on the master node, because replicas are read-only., 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 BY password;nGRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO fivetran@%;n”, Configure replication, To enable incremental updates, you must configure the master node in your Aurora cluster to output binary logs. First, note the name of your existing DB Cluster Parameter Group. If its name begins with , default. it is a default parameter group. Otherwise, it is a custom group. , Navigate to , Parameter Groups, ., , If you have a default parameter group, you must create a new parameter group. Alternatively, you can copy your existing cluster parameter group., , If you are creating a new group, make sure the , Parameter Group Family, value matches the one in your existing default parameter group name (from above). Make sure the , Type, is “DB Cluster Parameter Group”. Give the new group a name and description, then click , Create, ., , Click on the link to the new group (whether you created a new group or copied an existing group). Make sure the , Type, is “DB Cluster Parameter Group”. If not, revisit previous steps., Find the , binlog_format, parameter. Click , Edit parameters, ., , Change the , binlog_format, value to “ROW”. Click , Save, ., , Navigate back to , Instances, ., Select your Aurora master node (the one with the “writer” replication role)., , Click , Instance Actions, -> , Modify, ., In the Modify DB Instance screen, scroll down to find the , Database Option, section. Change the , DB cluster parameter group, to the new group you created., , Select , Continue, at the bottom of the page., Select , Apply Immediately, . Read the warning and confirm the changes by clicking , Modify DB Instance, ., , To make the changes to take effect, you need to reboot the DB instance. With the master node selected, click , Instance Actions, -> , Reboot, ., , Click , Reboot, to confirm that you want to reboot the instance., “Wait for the master node to reboot. The instances “, Status, will change from “rebooting” to “available” when it is done., , The configuration change will be complete when the , DB Cluster Parameter Group, status is “in-sync”., , Set Binary Log Retention Period, We require a minimum of 24 hours of binary logs to be retained on the source MySQL Database to allow for replication. By default, Aurora is set to cull binary log files as quickly as possible. View your current settings with the following function:, CALL mysql.rds_show_configuration;n, If the result of this query is , NULL your database has the default behavior. When your database culls log files as quickly as possible, it can disrupt our incremental update process. Fivetran may have to re-sync your database in full, which can cause significant downtime. Update your configuration to the Fivetran minimum required value of 24 hours:, “CALL mysql.rds_set_configuration(binlog retention hours, 24);n”, We recommend that you set a binary log retention period of 7 days, but know that increasing the binary log retention period requires additional disk space on your source MySQL database to store the log files:, “CALL mysql.rds_set_configuration(binlog retention hours, 168);n”, Choose schema prefix, Each schema from your source database will be mapped to a schema in the destination by adding a prefix to the original schema name. For example, if your original database contains schemas “foo” and “bar” and if you choose the prefix “pre”, then you will get schemas “pre_foo” and “pre_bar” in the output., Related articles, description, Connector Overview, , account_tree, Schema Information, , assignment, Release Notes, , settings, API Connector Configuration, , home, Documentation Home]

Share on facebook
Share on twitter
Share on linkedin

Join The Club

Every week, we'll be sending you curated materials handpicked by professionals. Plus, you'll be the first to know about our latest data!