Sync RDS MySQL/Aurora to Redshift
When you login with your FlyData account the first time, you will be taken to the setup wizard to walk you through the steps to configure your MySQL instance (data source) and Amazon Redshift cluster (data destination) for FlyData Sync.
STEP 1: Data Source
Choose “MySQL” for your data source:
If your data source instance is located on AWS and you are using RDS, click “RDS MySQL” or “Amazon Aurora”.
STEP 2: Setting up your Data Destination
The next step is setting up your data destination in Amazon Redshift. With FlyData Sync, we provide 2 methods of transferring your data into Redshift.
FlyData Direct (recommended) – This is a full end-to-end SaaS service where we will pull data from your database and sync directly to Redshift. We use SSL encryption between all endpoints so your data is secure. For customers who are not able to modify their Security Groups/Firewall for the Data Source, we also offer a Secure Tunnel option to open a VPN tunnel between the FlyData Cloud and your environment.
FlyData Agent – If FlyData Direct is not an option, you can choose to install FlyData Agent on an instance located within your environment and will push the data to the FlyData Cloud.
Please see the details about both options here: https://www.flydata.com/blog/flydata-direct-vs-flydata-agent/
Select FlyData Sync Direct and click Next to continue
To set up your Redshift cluster for FlyData Sync, please input the following information:
You can retrieve this information from the AWS Console in the Redshift section:
Once you have filled out all the information and set appropriate permissions, press next and we will test your connection to ensure it is accessible from the FlyData Cloud.
STEP 3: Setting up your Data Source
For the Data Source setup, you have a few options. You can either use a RDS read replica (creating a new or use an existing instance), or connect to a master DB. Depending on your use of MySQL, using a read replica is the recommended option due to possible performance and maintenance that is required (ie. reboots needed, etc) related to the use of FlyData Sync.
Although Aurora is based upon MySQL, it’s architecture is different and normally using the master database is recommended unless there are any special concerns (ie. already using Aurora and cannot apply additional DB parameters, etc). If so, please contact us and we can provide alternate options for your environment.
Whichever option you choose, whether it be using a read replica or a master instance, you will need to apply the configured DB parameters on the instance. See more details on how to create a read-replica and apply the DB parameters.
You will also need to setup security settings for the MySQL instance. Depending on if your instance is within VPC or not, this document will explain the setup details for you.
FlyData also requires a database user so we can access the proper database resources to sync your data. You can create the user by running the following commands. Please ensure your MySQL instance has enough disk space as some of the updated DB parameters may increase the binary log size.
Run the following commands to add the Sync User to your read replica and to set your bin-log retention period.
mysql> CREATE USER ‘sync’@’%’ IDENTIFIED BY ‘your\_password”;
mysql> GRANT REPLICATION SLAVE, SELECT, RELOAD, REPLICATION CLIENT, LOCK TABLES, EXECUTE ON \*.\* TO ‘sync’@’%’;
mysql> FLUSH PRIVILEGES;
mysql> call mysql.rds\_set\_configuration(‘binlog retention hours’, 96);
Lastly, input the MySQL information on the next screen. Click ‘Next’ and we will test connectivity to your MySQL instance. If it passes, the setup is complete!
Your setup is now complete. Sit back and relax and the sync from MySQL to Redshift will start shortly!