Setting up Amazon S3 MySQL Integration: 2 Easy Methods

Amazon S3 houses a wide variety of features that make it the preferred Cloud-based storage solution for most businesses. Organizations leverage it to handle a large number of use cases directly. Some of the use cases for which businesses rely on Amazon S3 include creating Backups, Analytical purposes, Data Archiving, and enhancing Security.

Hence, a lot of businesses have started moving data from their databases to Amazon S3 for creating backup and security reasons. This article will provide you with an in-depth understanding of how you can set up Amazon S3 MySQL Integration.

Introduction to Amazon S3

Amazon S3, also known as Amazon Simple Storage Service, is Amazon’s Cloud-based data storage platform. Amazon S3 holds up the entirety of Amazon’s massive Cloud Computing Network along with a significant amount of the modern web including Amazon’s website, Netflix, Facebook, and more.

Since its introduction in 2006, it is seen as the new standard for storing data. Amazon S3 does not store data like the file system of a computer that uses a system of data blocks. Instead, it stores data as independent objects along with complete metadata and a unique object identifier. Its object storage system can work with almost all platforms that make it incredibly flexible and hence, suitable for a wide variety of businesses. Data can be stored across various locations and retrieved much more quickly and seamlessly than any traditional file storage system. Amazon guarantees a 99.99% durability rate. This means that all data stored on Amazon S3 is guaranteed to remain intact and available to the user whenever it’s needed.

Amazon S3 has now been adopted by a wide variety of companies along with some of the world’s largest Enterprises. Two of the most well-known Social Media Platforms, i.e., Facebook and Twitter, rely on Amazon S3 to securely store user data and keep it accessible for all network analyses. Healthcare Enterprises like Illumina, Bristol-Myers Squibb, and Celgene relies on Amazon S3 to keep their patient data secure, enabling them to analyze patients’ health data as per requirements.

Understanding the Key Features of Amazon S3

The key features of Amazon S3 are as follows:

• Storage Management: With S3 bucket names, object tags, prefixes, and S3 Inventory, users have access to a wide range of functionalities such as S3 Batch Operations, S3 Replication, etc., that help them categorize and report their data.
• Storage Monitoring: Amazon S3 houses various functionalities such as AWS Cost Allocation Reports, Amazon CloudWatch, AWS CloudTrail, S3 Event Notifications that enable users to monitor and control how their Amazon S3 resources are being utilized.
• Storage Analytics: Amazon S3 houses two services called S3 Storage Lens and S3 Storage Class Analysis that can provide users with insights on data being stored. S3 Storage Lens delivers organization-wide visibility into object storage usage, activity trends and makes actionable recommendations to improve cost-efficiency and implement the best practices for data protection. Amazon S3 Storage Class Analysis analyzes storage access patterns to help users decide when they should implement transitions for the data into the right storage class.
• Security: Amazon S3 offers various flexible security features to ensure that only authorized users have access to the data. Amazon S3 provides support for both Client-side and Server-side encryption for data uploads.

Introduction to MySQL

MySQL is considered to be one of the most popular Open-Source Relational Database Management Systems (RDBMS). MySQL implements a simple Client-Server Model that helps its users manage Relational Databases i.e. data stored in the form of rows and columns across tables. It uses the well-known query language, Structured Query Language (SQL) that allows users to perform all required CRUD (Create, Read, Update, Delete) operations.

MySQL was first developed by a Swedish company called MySQL AB in 1994. The company was then taken over by Sun Microsystems in 2008 which was finally taken over by the US tech giant, Oracle. Oracle is now responsible for the development and growth of MySQL. Even though MySQL is Open-Source and available free of cost for everyone, it houses some premium features that are offered by Oracle only to those customers who are willing to pay for its usage.

Even though there is very high competition in the database market today, MySQL is considered to be the preferred database for more than 5000 companies including Uber, Netflix, Pinterest, Amazon, Airbnb, Twitter, etc. Amazon RDS makes it easy for users to set up, operate, and scale MySQL deployments in the cloud. With

With Amazon RDS, users can seamlessly deploy scalable MySQL servers within minutes with cost-efficient and resizable hardware capacity. Amazon RDS MySQL enables users to solely focus on the application development by directly handling time-consuming database administration tasks such as scaling, replication, software patching, backups, and monitoring.

Understanding the Key Features of MySQL

Some of the key features of MySQL are as follows:

• Robust Transactional Support: Implementation of ACID (Atomicity, Consistency, Isolation, Durability) properties that ensures no data loss or inconsistency.
• Ease of Use: Considering that it makes use of SQL for querying data, anyone with basic knowledge of SQL can perform the required tasks with ease.
• Security: It implements a complex data security layer that ensures that only authorized users can access sensitive data.
• Scalable: MySQL is considered to be highly scalable due to support for multi-threading. 
• Roll-back Support: MySQL supports roll-backs, commits, and crash recovery for all transactions.
• High Performance: MySQL houses various fast load utilities along with Table Index Partitioning and Distinct Memory Caches that can ensure high performance.

Methods to Set up Amazon S3 MySQL Integration

The two methods to set up Amazon S3 MySQL Integration:

• Method 1: Amazon S3 MySQL Integration Using AWS Data Pipeline
• Method 2: Using Hevo Data to Set up Amazon S3 MySQL Integration

Method 1: Amazon S3 MySQL Integration Using AWS Data Pipeline

Users can set up Amazon S3 MySQL Integration by implementing the following steps:

S3 MySQL Integration Step 1: Creating IAM Roles for AWS Data Pipeline

Every AWS Data Pipeline should have IAM Roles assigned to them that determine its permissions to perform the necessary actions and control its access to AWS resources. The AWS Data Pipeline IAM Roles define the permissions that the AWS Data Pipeline should have. A Resource Role determines the permissions that various applications running on pipeline resources, such as EC2 instances, have. You have to specify these roles when you create an AWS Data Pipeline. You can also choose to use the default roles, i.e., DataPipelineDefaultRole and DataPipelineDefaultResourceRole. 

In case default roles are chosen, you must first create the roles and attach permission policies accordingly.

S3 MySQL Integration Step 2: Allowing IAM Principals to Perform the Necessary Actions

In order to set up the AWS Data Pipeline, an IAM Principal in your AWS account must have the necessary permission to perform all the actions that an AWS Data Pipeline might be performing.

The AWSDataPipeline_FullAccess policy can easily be attached to the required IAM Principals. This policy gives access to perform all actions to the IAM Principal, and the iam:PassRole action is set to perform the default roles used within AWS Data Pipeline when any custom roles are not specified. Also, read AWS MySQL article here.

The following example shows the policy statement attached to an IAM Principal that is using an AWS Data Pipeline:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": "iam:PassRole",
            "Effect": "Allow",
            "Resource": [
                "arn:aws:iam::*:role/MyPipelineRole",
                "arn:aws:iam::*:role/MyResourceRole"
            ]
        }
    ]
}

The following steps can be implemented to create a user group and attach the AWSDataPipeline_FullAccess policy to it:

• Open the IAM console.
• From the left navigation pane, click on Groups, and select Create New Group.
• Enter a Group Name of your choice. For example, DataPipelineDevelopers, and then click Next Step.
• From the Filter dropdown, select AWSDataPipeline_FullAccess.
• Click on Next Step and then select Create Group.

Users can be added to the group by implementing the following steps:

• Select the group you created from the list of groups.
• Click on Group Actions, and select Add Users to Group.
• Select the users you wish to add to the group and then click on Add Users to Group.

S3 MySQL Integration Step 3: Creating AWS Data Pipeline

The AWS Data Pipeline can be created by implementing the following steps:

• Open the AWS Data Pipeline Console.
• From the left navigation bar, select a region of your choice. Since the AWS Data Pipeline allows users to use resources in a different region from the pipeline, you can choose to select any available region irrespective of your current location.
• The first screen that will show up will depend on whether there is an existing AWS Data Pipeline associated with your account in the selected region or not. If you haven’t created an AWS Data Pipeline in this region, the console will display an introduction screen. Click on Get Started Now. On the other hand, if you have already created an AWS Data Pipeline in the selected region, the console will display a list of all your AWS Data Pipelines in the region. Click on Create New Pipeline.
• Enter a suitable name for your AWS Data Pipeline along with an optional description.
• Under the Source section, select Build Using a Template, and finally select Full copy of RDS MySQL table to S3 as the template.
• Under the Parameters section, implement the following steps:
  • Enter the name of the Aurora DB Instance that you wish to copy data from as the DBInstance ID.
  • The endpoint details of your DB Instance can be located by referring to the Amazon RDS User Guide.
  • Enter the user name that was used while creating the MySQL database instance as the RDS MySQL Username.
  • Enter the password that was used while creating the DB instance as the RDS MySQL Password.
  • Enter the instance type of your EC2 instance as the EC2 Instance Type.
  • Click on the folder icon next to the output S3 folder, choose one of your folders or buckets, and then click on Select.
• Select On Pipeline Activation under Schedule.
• Leave logging enabled under Pipeline Configuration. Click on the folder icon next to S3 Location for Logs, select one of your folders of buckets, and then click on Select.
• Leave IAM Roles set to Default under Security/Access.

S3 MySQL Integration Step 4: Saving and Validating AWS Data Pipeline

The AWS Data Pipeline can be saved by clicking on Save Pipeline. AWS Data Pipeline will now validate your pipeline definition and return a warning, error, or success message accordingly. If you get a warning or error message, click on Close, and choose Errors/Warnings from the right navigation pane to view a list of objects that failed validation. In a particular error message, go to the specific object pane where you see the error and make the necessary changes to fix it. After all warnings and errors have been dealt with, click on Save Pipeline again to validate the pipeline and repeat the same process until you get the success message.

S3 MySQL Integration Step 5: Activating AWS Data Pipeline

The AWS Data Pipeline can be activated by clicking on the Activate button and selecting Close on the pop-up dialog box.

Limitations of Using AWS Data Pipeline to Set up Amazon S3 MySQL Integration

The limitations of using the AWS Data Pipeline to set up Amazon S3 MySQL Integration are as follows:

• Setting up an AWS Data Pipeline for Amazon S3 MySQL Integration is a very complex process even for someone with a technical background and might require the support of trained AWS Architects for creating and maintaining the pipeline.
• AWS starts charging you for using the AWS Data Pipeline for Amazon S3 MySQL Integration the moment you activate the pipeline. To stop incurring extra charges, the pipeline has to be deleted. This means that the pipeline has to be created again every time the data transfer needs to be done.
• AWS Data Pipeline is not fully managed which means that you have to monitor the pipeline and fix any errors that might occur.

Method 2: Using Hevo Data to Set up Amazon S3 MySQL Integration

Hevo Data, a No-code Data Pipeline, helps you directly transfer data from S3 to MySQL in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Hevo takes care of all your data preprocessing needs required to set up Amazon S3 MySQL Integrations and lets you focus on key business activities

Hevo also enables you to load data from files in an S3 bucket into your Destination database or Data Warehouse seamlessly. Moreover, S3 stores its files after compressing them into a Gzip format. Hevo’s Data pipeline automatically unzips any Gzipped files on ingestion and also performs file re-ingestion in case there is any data update. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

The following steps can be implemented to set up Amazon S3 MySQL Integration using Hevo:

• Step 1) Configure Source: Connect Hevo Data with Amazon S3 by providing a unique name for your destination along with information about your Amazon S3 bucket, its name, region, your AWS Access Key ID, Secret Access Key, and the file format in which you want to write your data. You can either choose JSON or ORC.

• Step 2) Configure Destination: Load data from Amazon S3 to MySQL by providing your MySQL database credentials such as your authorized username and password, along with information about your host IP and port number value. You will also need to provide a name for your database and a unique name for this destination.

Check out what makes Hevo amazing:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data from S3 buckets and maps it to the destination schema.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use for aggregation.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

With continuous Real-Time data movement, Hevo allows you to combine S3 data along with your other data sources and seamlessly load it to MySQL with a no-code, easy-to-setup interface. Try our 14-day full-feature access free trial!

Get Started with Hevo for Free

Conclusion

This article provided you with a step-by-step guide on how you can set up Amazon S3 MySQL Integration using AWS Data Pipeline or using Hevo. However, there are certain limitations associated with AWS Data Pipeline. You will need to implement it manually, which will consume your time & resources and is error-prone. Moreover, you need full working knowledge of the AWS environment to successfully implement the AWS Data Pipeline. You will also have to regularly map new S3 data to MySQL as the AWS Pipeline is not fully managed.

Hevo Data provides an Automated No-code Data Pipeline that empowers you to overcome the above-mentioned limitations. Hevo caters to 100+ data sources (including 40+ free sources) and can seamlessly transfer your data from S3 to MySQL within minutes. Hevo’s Data Pipeline enriches your data and manages the transfer process in a fully automated and secure manner without having to write any code. It will make your life easier and make data migration hassle-free.

Learn more about Hevo

Want to take Hevo for a spin? Signup for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

Share your understanding of S3 to MySQL in the comments below!