Fetching Changes from your MySQL - RDS

Direct Model

To fetch changes from your MySQL tables, you can have FlyData directly monitor changes to your database. Alternatively, you can install the FlyData Agent onto a Linux server that has access to your MySQL server. While the first choice requires you to open your MySQL port to FlyData, it simplifies your process and you can save on server costs. Otherwise, please follow the steps below for the second option (installing the Agent).

FlyData Agent Model

The FlyData Agent is what monitors the changes made to your MySQL tables. Specifically, it monitors the MySQL binary log (i.e., the binlog), and sends these changes to the FlyData Cloud, in order to load it into Amazon Redshift.

To install the FlyData Agent, you will need a Linux server, which has access to your MySQL server. If you don’t have a server already, you can launch a new EC2 instance in your AWS account with the following steps. If you already have a server on which you can install FlyData Agent, you may skip to the next section.

Launch an Instance for the FlyData Agent

You will need an encryption key pair to log into the EC2 instance. You may use an existing one for the server or you could create a new key pair. Once you have one, though, you will need to know where it’s located on your server.

Once you have your encryption key ready, launch an EC2 instance that the FlyData Agent will use. To do this, follow these steps:

  • From the AWS Console, select EC2.
  • Click on Launch Instance and then click on Select to choose an Amazon Machine Image (AMI).
  • Choose the Instance Type. This will affect initial-sync processing speed. For reference, below are the processing speeds when using a single instance of the following instance types (Run multiple instances to increase throughput.):
    • m1.small: 3GB/hour
    • c3.large: 10GB/hour (*Please contact us for more information on how to increase throughput.)
  • To finish, click on Review and Launch and then Launch.

You may have to wait a bit for the instance to be available. Once it is available, log into the instance by entering the following from the command-line:

$ ssh ec2-user@your-instance-public-DNS -i your_public_key

You would replace public_key with the path and file name for your public key. It might read something like,~/.ssh/foobar.pem. The instance-public-DNS would be the specific internet address for your instance. It would look something like, ec2-23-20-136-106.compute-1.amazonaws.com.

Install FlyData Agent

The specific install command for the FlyData Agent is available in your FlyData Dashboard. It should look something like this:

$ bash <(curl -L https://console.flydata.com/i/your-unique-identity)  $ source ~/.bashrc

You will need the exact command for your system. So copy the install command from your Dashboard and execute it on your Linux server.

Generate Redshift table definition script and run on Redshift

To start the FlyData Agent synchronizing, you will need to generate a table definition script that will be run on Amazon Redshift. You can do this by entering the following from the command-line on server:

$ flydata sync:generate_table_ddl > create_table.sql  $ cat create_table.sql

This uses the FlyData Agent to provide the needed table definition script. Copy the output and run it fromFlyData’s Amazon Redshift Access Console.

Start Sync

The installation and configuration is finished. You need only to start the FlyData Agent on your server. This agent will extract data from the MySQL binlog on your server, and send it to Amazon Redshift for storing and processing.

To start the FlyData Agent, enter the following on your server:

$ flydata start

At this point, whatever tables you’ve created on Amazon Redshift’s cluster should be populated and kept in sync. If you want to be sure, run some SELECT statements on some of the tables on both systems and compare the results. Add or change data on your server and see if it updates on Amazon Redshift. It may take as much as five minutes to synchronize, but it should work just fine.