I have a transaction table in MySQL which get updated almost every second. The table has about 20 million records and counting. I have been running queries directly on this table in my app development which has been having serious impact on performance especially when I have to call some queries using aggregate functions.
I want to connect the MYSQL to Amazon Redshift.
The problem is I want my connection to Redshift to be live. ie. Get Redshift updated with every insert in my tales in mysql.
- How do I create the live connection to Amazon Redshift?
- Is there any other way I can manage data without using Amazon Redshift?
1 Answer 1
In order to create a live connection from MySQL to Redshift you need to choose an appropriate database replication method. Among all existing methods, the Change Data Capture (CDC) is the most suitable for your case.
In this method, after a dump of the initial state of the database, the log of each database operation is continuously streamed and loaded into the data warehouse, and consolidated on a schedule (configurable) and the biggest advantage is that it enables near real-time replication.
A tool that I had a good experience with is Alooma, it can support near real time connection between Amazon Redshift and MySQL data base.