LogoLogo
Daton
Daton
  • Daton Defined: From Web App Essentials to Advanced Integrations
  • Platform
    • Essentials
      • Billing
      • Integrate Facebook Ads on Daton
      • Integrate Shopify on Daton
      • Integrate Google Analytics on Daton
      • Access permissions for Daton
      • How to get started with Daton setup
      • Terminology in Daton
        • Source and Source_Table statuses
      • Various pages in Daton
    • Features
      • Navigating the pages in Daton
        • Notifications
        • Logs
        • Dashboard
        • My Account
        • Connectors
        • My Integrations
        • Integration Details
      • Templates
      • Account Management
        • My Account Page
      • Notifications
      • Monitoring
        • Logging and Log Retention
      • Manage Integrations
        • Handling Changes to Schema
        • Modifying an integration
      • Data
        • Handling Nested Data
        • Daton Metadata
        • Inserts, Updates, and Upserts
        • Data Type Mapping
        • Data Replication in Daton
          • Replication Fields
          • Replication Methods
            • Key-based Incremental Replication
      • Knowledge Base
        • What is ELT and how is it different from traditional ETL tools
          • Billing
      • Pipelines
        • Pipeline Jobs
        • Loading Behavior
      • Security
      • Feature Updates
        • Amazon Vendor Central
  • Connectors
    • Data Sources
      • Advertising
        • Adjust v1
        • Adjust v2
          • About Adjust v2 Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Adjust v2 with Daton
          • Schema Information
        • Amazon Ads
          • Release Notes
        • Amazon Attribution
          • Release Notes
        • Amazon Brand Metrics
          • About Amazon Brand Metrics
          • Essential Attributes and Supported Tables
          • How to Integrate Amazon Brand Metrics with Daton
          • Schema Infomation
        • Amazon DSP V1
          • Release Notes
        • Amazon DSP V2
        • Amazon Marketing Cloud (AMC)
        • Amazon Marketing Streams
          • About Amazon Marketing Streams Connector
          • Essential Attributes
          • How to Integrate Amazon Marketing Streams with Daton
          • Release Notes
        • Amazon Sponsored Brand Ads
          • Release Notes
        • Amazon Sponsored Display Ads
          • Release Notes
        • Amazon Sponsored Product Ads
          • Release Notes
        • Amazon Sponsored TV Ads
        • Bing Ads
          • Release Notes
        • Bol Ads
          • Release Notes
        • Facebook Ads
          • Release Notes
        • Google Ads
          • Release Notes
        • Google My Business
        • Google My Business v2
          • About Google My Business v2 Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Google Business Profile Account with Daton
          • Schema Information
        • Gorgias
        • Gorgias V2
        • HelpScout
        • HelpScout v2
          • About HelpScout Platform
          • Essential Attributes and Supported Tables
          • How to Integrate HelpScout with Daton
          • Schema Information
          • Release Notes
        • Outbrain
          • Release Notes
        • Outbrain v2
          • About Outbrain Platform
          • Essential Attributes & Supported Tables
          • How to Integrate Outbrain v2 with Daton
          • Schema Information
        • Pinterest v2
          • About Pinterest v2 Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Pinterest v2 with Daton
          • Schema Information
        • Rakuten
          • About Rakuten Connector
          • Essential Attributes
          • How to Integrate Rakuten with Daton
        • StackAdapt
          • About StackAdapt Platform
          • Essential Attributes and Supported Tables
          • How to Integrate StackAdapt with Daton
          • Schema Information
        • Typeform
          • About Typeform Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Typeform with Daton
          • Schema Information
        • Upscribe
          • Release Notes
        • Upscribe V2
          • About Upscribe Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Upscribe v2 with Daton
          • Schema Information
      • Analytics Platforms
        • Appsflyer
        • GA4
          • About Google Analytics Platform
          • Essential Attributes and Table Selection
          • How to Integrate GA4 with Daton
          • Schema Information
        • Google Analytics
          • Release Notes
        • Jungle Scout
        • Northbeam
          • About Northbeam Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Northbeam with Daton
          • Schema Information
        • Prodco Analytics
          • About Prodco Analytics Platform
          • Essential Attributes and Table Selection
          • How to Integrate Prodco Analytics Account with Daton
          • Schema Information
        • Rainforest
          • About Rainforest Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Rainforest Account with Daton
          • Schema Information
        • Sprout Social
          • About Sprout Social Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Sprout Social with Daton
          • Schema Information
        • Taboola
        • Taboola v2
          • About Taboola v2
          • Essential Attributes and Supported Tables
          • How to Integrate Taboola v2 with Daton
          • Schema Information
        • Edge by Ascential
      • Business
        • ClickUp
          • About ClickUp Connector
          • Essential Attributes and Supported Tables
          • How to Integrate ClickUp with Daton
          • Schema Information
        • Bubble
        • Pipedrive
          • About Pipedrive Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Pipedrive with Daton
          • Schema Information
        • Teamwork
          • About Teamwork Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Teamwork with Daton
          • Schema Information
        • Teamwork v2
          • About Teamwork Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Teamwork v2 with Daton
          • Schema Information
        • TikTok Business
          • About TikTok Business Connector
          • Essential Attributes and Supported Tables
          • How to Integrate TikTok Business with Daton
          • Schema Information
        • Unbounce v2
          • About Unbounce v2 Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Unbounce v2 with Daton
          • Schema Information
      • Call Center
        • Aircall
        • Callpage
        • JustCall
          • About JustCall Connector
          • Essential Attributes and Supported Tables
          • How to Integrate JustCall with Daton
          • Schema Information
      • Databases
        • Amazon Aurora MySQL
          • About Amazon Aurora Platform
          • Essential Attributes
          • How to Integrate Amazon Aurora with Daton
        • Amazon Redshift
          • About Amazon Redshift Platform
          • Essential Attributes
          • How to Integrate Amazon Redshift with Daton
        • GCP MySQL
          • About GCP MySQL Platform
          • Essential Attributes
          • How to Integrate GCP MySQL with Daton
        • GCP PostgreSQL
          • Setup Manual
        • Magento (DB)
          • About Magento Connector
          • Essential Attributes
          • How to Integrate Magento with Daton
        • MySQL
          • Release Notes
        • NetSuite
        • PostgreSQL
          • Release Notes
        • SQLServer
        • WooCommerce SQL
          • About WooCommerce SQL Platform
          • Essential Attributes
          • How to Integrate WooCommerce SQL with Daton
      • Daton Connector
        • Release Notes
      • Ecommerce Platforms
        • Amazon MWS
          • Release Notes
        • Amazon Selling Partner
          • All Report Types and Tables
          • Release Notes
        • Amazon Vendor Central
          • All Report Types and Tables
          • Real Time Reports
          • Daily Reports
          • Weekly Reports
          • Snapshot Reports
          • Monthly Reports
          • Raw Tables
          • Release Notes
        • Bigcommerce
        • BigCommerce v2
          • About BigCommerce Platform
          • Essential Attributes and Supported Tables
          • How to Integrate BigCommerce Account with Daton
          • Schema Information
          • Release Notes
        • Bol Retail v2
          • Release Notes
        • Capillary Martjack
          • Release Notes
        • Kaufland
          • About Kaufland Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Kaufland with Daton
          • Schema Information
        • Keepa
          • About Keepa Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Keepa with Daton
          • Schema Information
        • Lazada
        • Loopreturns
          • Release Notes
        • Magento
          • Release Notes
        • Magento 2 (APIs)
          • Release Notes
        • Mercado Libre
          • About Mercado Libre Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Mercado Libre with Daton
          • Schema Information
        • Shopify
          • Access tokens for Shopify
          • Release Notes
        • Shopify V2
        • Walmart
          • Release Notes
        • Walmart_v2
          • Release Notes
        • WooCommerce
      • Files
        • Amazon S3
          • Release Notes
        • Dropbox
          • Release Notes
        • Email Attachments
          • About Email Attachments Connector
          • Essential Attributes and Connector Guidelines
          • How to Integrate Email Attachments with Daton
        • Google Cloud Storage
          • Release Notes
        • Google Drive
        • Google Sheets
          • Release Notes
        • SFTP (SSH File Transfer Protocol)
      • OMS/WMS
        • Cin7
        • Goflow
          • About Goflow Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Goflow with Daton
          • Schema Information
        • Rippling
          • About Rippling Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Rippling with Daton
          • Schema Information
      • Payments
        • PayPal
          • About PayPal Connector
          • Essential Attributes and Supported Tables
          • How to Integrate PayPal with Daton
          • Schema Information
        • Razorpay
          • About Razorpay Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Razorpay with Daton
          • Schema Information
        • Razorpay v1
        • Stripe
      • Productivity Trackers
        • GitLab
          • Release Notes
        • Time Doctor
          • Release Notes
      • SaaS Integrations
        • Aftership
          • Release Notes
        • Aftership V2
        • Anvyl
        • Ascend
        • Awtomic
          • Release Notes
        • Braintree
          • About Braintree Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Braintree Account with Daton
          • Schema Information Braintree
        • Calendly
          • Release Notes
        • Calendly V2
        • Campaign Monitor
        • Capsule CRM
        • Chargebee
        • Freshworks CRM
        • Loop Subscription
        • Commission Junction
        • Constant Contact
        • Copper CRM
          • About Copper
          • Essential Attributes and Supported Tables
          • How to Integrate Copper Account with Daton
          • Schema Information
          • Release Notes
        • Criteo
        • Criteo Retail Media
        • Customer.io
        • DotDigital
        • dotdigital V2
        • Easyecom
        • EasyEcom V2
          • Release Notes
        • ePallet ERP
        • Etsy
        • Exchange Rates
          • About Exchange Rate Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Exchange Rates with Daton
          • Schema Information
          • Release Notes
        • Fairing (previously EnquireLabs)
          • Release Notes
        • FreshBooks
          • Release Notes
        • Freshdesk
          • Release Notes
        • Freshsales
          • Release Notes
        • Fulfil
        • Gladly
          • Release Notes
        • Google Search Console
          • Release Notes
        • HubSpot
          • Release Notes
        • HubSpot v2
          • About HubSpot v2 Connector
          • Essential Attributes and Supported Tables
          • How to Integrate HubSpot v2 with Daton
          • Schema Information
        • Impact
          • Release Notes
        • Infusionsoft (now Keap Max Classic)
          • About Infusionsoft Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Infusionsoft with Daton
          • Schema Information
        • Insightly
        • Intercom
          • Release Notes
        • Intercom V2
          • Release Notes
        • Outbrain V2 (Old)
          • Release Notes
        • Inventory Planner
        • Jira
        • JudgeMe
          • Release Notes
        • Kibo Commerce
        • Klaviyo
          • Release Notes
        • Klaviyo V2
          • Release Notes
        • Kustomer
        • LeadSquared CRM
        • LeadSquared V2
        • LinkedIn Ads
        • LiveChat
        • LoyaltyLion
        • MailChimp
          • Release Notes
        • MNTN
        • Monday
        • NewRelic
        • Okendo Reviews
          • About Okendo Reviews Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Okendo Reviews with Daton
          • Schema Information
        • Optimove
          • Release Notes
        • Pingdom
        • Pinterest
        • Pipedrive
        • PushEngage
        • Quickbase
        • QuickBooks
        • Quickbooks v2
          • About Quickbooks Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Quickbooks v2 with Daton
          • Schema Information
        • Re:amaze
        • ReCharge Payments
          • Release Notes
        • ReCharge Payments v1
          • Setup Manual
          • Schema Information
        • Recharge Payments V2
          • Setup Manual
          • Schema Information
        • Reviewmonitoring
          • Release Notes
        • Salsify
          • Release Notes
        • SendGrid
          • Release Notes
        • Shiphero
        • Shippo
        • Shiprocket
          • Release Notes
        • Shiprocket v2
          • About Shiprocket Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Shiprocket with Daton
          • Schema Information
        • Shipstation
          • Release Notes
        • Skubana
        • SkuVault
          • About SkuVault Connector
          • Essential Attributes and Supported Tables
          • How to Integrate SkuVault with Daton
          • Schema Information
        • Smartrr
        • Smartrr v2
          • About Smartrr Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Smartrr v2 with Daton
          • Schema Information
        • Stamped.io
          • About Stamped.io Platform
          • Essential Attributes and Supported Tables
          • How to Integrate Stamped.io with Daton
          • Schema Information
        • Stamped.io v1
        • Stay AI
        • Sticky.io
        • SugarCRM
          • Release Notes
        • Survey Monkey
        • Tune
        • Unbounce
        • Unicommerce
        • Unicommerce
          • About Unicommerce Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Unicommerce with Daton
          • Schema Information
          • Release Notes
        • When I Work
        • Xero
        • Xero V2
          • About Xero Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Xero V2 with Daton
          • Schema Information
        • Yahoo Gemini
        • Yotpo
        • Yotpo v2
          • About Yotpo Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Yotpo v2 with Daton
          • Schema Information
        • Zendesk Chat
        • Zendesk ETL
          • Release Notes
        • Zoho CRM
          • Release Notes
        • ZohoDesk
          • Release Notes
      • Surveys
        • Alchemer
          • About Alchemer Platform
          • Essential Attributes and Supported Tables Alchemer
          • How to Integrate Alchemer Account with Daton
          • Schema Information Alchemer
        • Fairing
          • About Fairing Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Fairing with Daton
          • Schema Information
        • Survicate
          • About Survicate Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Survicate with Daton
          • Schema Information
      • Shipping Platforms
        • 3PL Central
        • Byrd
        • Cin7
        • ClickPost
        • Dear
        • Easypost
        • Ohi
          • About Ohi Connector
          • Essential Attributes and Supported Tables
          • How to Integrate Ohi with Daton
          • Schema Information
        • ShipBob
          • About ShipBob Connector
          • Essential Attributes and Supported Tables
          • How to Integrate ShipBob with Daton
          • Schema Information
      • Webhooks
    • Destinations
      • Amazon RDS PostgreSQL
        • Amazon RDS for PostgreSQL Integration with Daton
        • How to Set up RDS PostgreSQL Destination in Daton
      • Amazon RDS MySQL
        • About Integrating Amazon RDS for MySQL database with Daton
        • How to Set up RDS MySQL Destination in Daton
      • Amazon Redshift
        • Querying Daton created tables on RedShift
        • Release Notes
      • Amazon S3
        • Release Notes
      • Azure One Lake
        • About Azure One Lake as a connector in Daton
        • How to Set up an Azure One Lake Database for Integrating with Daton
        • How to Integrate your Azure One Lake Database as a Destination in Daton
      • Azure Data Lake Storage
        • About Azure Data Lake Storage as a connector in Daton
        • How to Set up an Azure Data Lake Storage for Integrating with Daton
        • How to Integrate Your Azure Data Lake Storage Database as a Destination in Daton
      • GCP PostgreSQL
        • About Google Cloud SQL for PostgreSQL Database with Daton
        • How to Set up GCP PostgreSQL Destination in Daton
      • Google BigQuery
        • Release Notes
      • GCS
      • MySQL
        • About Integrating MySQL with Daton
        • How to Set up a MySQL Database for Integrating with Daton
        • How to Set up Your MySQL Database as a Destination in Daton
      • Oracle Autonomous Data Warehouse
      • Snowflake
        • Release Notes
  • FAQs and Troubleshooting
    • Connector permissions and API Keys
      • Access tokens for Shopify
      • Amazon Ads Permissions
      • Where to find my Amazon MWS Merchant Token and Auth Token
      • How to create BigCommerce API Account and generate Client Id, Client Secret and Access tokens
      • How to create Client credentials for Bol Retail
      • How to create Developer application for Mercado Libre
      • How to create API Keys for SendGrid
      • Where to find API Keys for Freshsales
      • Where to find API Keys for Freshdesk
      • How to create REST API Keys for NewRelic
      • How to create API Keys for Chargebee
      • How to generate Access Token for Calendly
      • How to obtain Klaviyo API Key
      • Where to find API Keys for RazorPay
      • How to create API Keys for PushEngage
      • How to create Criteo API User and generate Client Id and Client Secret
      • How to find site-id/domain and create WooCommerce Consumer Key and Secret
    • Authentication Failure while connecting Database using SSH tunnel
    • Common Errors and Resolution
    • FAQs
      • How to get access to FB Ads Business Manager?
      • How to get access to Google Analytics?
      • Querying Daton created child tables for nested data
      • Google Analytics - FAQs
      • How does Lite Plan Work?
      • How do I upgrade my subscription?
      • How does loading behaviour impact billing?
      • What are the tables highlighted with the letter "F"?
      • Amazon Advertising FAQs
      • Amazon Business and Inventory Reports FAQs
      • Why don't I see my data?
      • How exactly do Business Reports work in Daton?
      • Steps to upload your own ASIN and SKU file in Amazon SP connectors
      • What are 'FATAL' and 'CANCELLED' report statuses in Amazon Selling Partner?
      • How to setup Google BigQuery Project
      • Amazon Selling Partner, Vendor Central - Access to resource denied error.
      • VendorSalesReportByManufacturing showing a FATAL report status?
      • How to Enable SSH Tunneling during a Database Source Authentication?
  • New Subscription Plans and Billing
    • Subscription Plans Overview
    • Find Your Perfect Data Integration Fit - Growth and Enterprise Plans Explored
    • Daton's Subscriptions Dashboard: Harness the Power of Your Data
    • How to Select and Subscribe to a Daton Subscription Plan?
    • Subscription Plan Upgrade and Downgrade
    • Changing Billing Cycle
  • Product Insights: Report, Roadmap, and Release Notes
    • Introduction to Product Insights
    • 2023 Recap and 2024 Roadmap
    • Release Notes
Powered by GitBook
On this page
  • Task 1: Set up Your MySQL Database
  • Set up a MySQL Database on Your Machine
  • Set up a MySQL Database Using an AWS EC2 Instance
  • Task 2: Configure Your MySQL Database for Daton

Was this helpful?

  1. Connectors
  2. Destinations
  3. MySQL

How to Set up a MySQL Database for Integrating with Daton

This subtopic instructions and essential notes on how to set up a MySQL database and configure it for integration with Daton.

PreviousAbout Integrating MySQL with DatonNextHow to Set up Your MySQL Database as a Destination in Daton

Last updated 1 year ago

Was this helpful?

Task list:

Task 1: Set up Your MySQL Database

MySQL is a free and open-source relational database management system (RDBMS) that can be installed on a variety of platforms, including Linux, Microsoft Windows, and macOS.

You can either set up a MySQL database on your machine or use an AWS EC2 instance to set up a MySQL database that can integrate with Daton.

Note: The steps in the above MySQL database setup guides are just for reference. You can use other methods to set up your MySQL database or consult the for more detailed instructions.

Once you have completed setting up a MySQL database using either of the above methods or any other method, you need to perform the following steps to integrate your MySQL database with Daton:

  1. Create a dedicated user in the MySQL database.

  2. Allow remote connections to the MySQL database.

  3. Perform the required configuration in your MySQL Database to integrate it with Daton. See .

Additional Notes:

  • You can use a MySQL GUI tool, such as MySQL Workbench, to simplify the process of setting up and managing your MySQL database.

  • If you are using an AWS EC2 instance to set up your MySQL database, you can create a security group to control access to the database.

  • It would help to regularly back up your MySQL database to protect your data from loss or corruption.

Set up a MySQL Database on Your Machine

Perform the following steps to set up a MySQL database on your machine:

  1. Download and install the MySQL server on your machine.

    • On Linux, use the package manager such as apt and yum

      • For more detailed information, refer to the following MySQL documentation:

    • On macOS, use Homebrew or the DMG installer

    • On Linux/macOS, use

    sudo systemctl start mysql

    • On Windows, start the service from the services panel

    • On macOS, use

    mysql.server start

  2. Run security script to set root password and remove anonymous users

    mysql_secure_installation

  3. Create a new MySQL database

    CREATE DATABASE mydatabase;

  4. Verify the database is created

    SHOW DATABASES;

Steps to Create a Dedicated User in the MySQL Database

It's best practice to create a separate user instead of using the root user. Perform the following steps:

  1. Log in to MySQL console as root

    sudo mysql -u root -p

  2. Create a new user

    CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';

    Note: Replace myuser with the name of the user you want to create localhost with the hostname of the server where the database is located, and password with the password for the user.

  3. Grant privileges to the user

    GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';

  4. Flush privileges

    FLUSH PRIVILEGES;

A dedicated user has now been created.

Steps to Allow Remote Connections to the MySQL Database

By default, MySQL only allows local connections. To allow remote connections to a MySQL database, you need to edit the mysqld.cnf file.

  1. Edit /etc/mysql/mysql.conf.d/mysqld.cnf.

    • The mysqld.cnf file is located in the etc/mysql directory on most Linux systems. To edit the mysqld.cnf file, open it in a text editor.

  2. Navigate to the line that begins with the bind-address directive and add the following line. This line will tell MySQL to listen for connections on all interfaces.

    bind-address = 0.0.0.0

  3. Restart the MySQL server

    sudo systemctl restart mysql

  4. Change user plugin: This step is used to change the authentication plugin for a MySQL user account. The authentication plugin is used to authenticate the user when they connect to the MySQL server.

    • Check the user plugin with

      SELECT user, plugin, host FROM mysql.user;

    • If necessary, change the user plugin

      RENAME USER 'username'@'localhost' TO 'username'@'%';

  5. Flush privileges

    FLUSH PRIVILEGES;

Open port 3306: Assuming you’ve configured a firewall on your database server, you must also open port 3306 to allow traffic to MySQL.

Set up a MySQL Database Using an AWS EC2 Instance

When you set up a MySQL database on your machine, you are responsible for all aspects of the database's installation, configuration, and maintenance. This includes installing the MySQL software, creating the database and database users, granting privileges to users, and backing up the database.

AWS handles many tasks when you use an AWS EC2 instance to set up a MySQL database. You need to launch an EC2 instance and select the MySQL AMI. AWS will install the MySQL software and configure the database for you. You will still need to create the database and database users and grant privileges to users. However, AWS will automatically back up the database for you.

There are many advantages to using an AWS EC2 instance to set up a MySQL database, including:

  • Control and flexibility: You have complete control over all aspects of the database, including the hardware, software, and configuration. This gives you the flexibility to tailor the database to your specific needs.

  • Scalability: You can easily scale your database up or down as needed by launching or terminating EC2 instances. This is especially useful for applications with variable workloads.

  • Reliability: AWS EC2 instances are highly reliable and offer various features to improve your database's reliability, such as high availability zones and elastic block store (EBS) volumes.

  • Security: AWS EC2 instances offer a variety of security features to protect your database, such as firewalls, intrusion detection systems, and access control lists.

  • Cost-effectiveness: AWS EC2 instances can be very cost-effective, mainly if you use spot instances or reserved instances

In addition to these general advantages, there are also some specific advantages to using AWS EC2 instances to set up a MySQL database, such as:

  • Easy setup and management: AWS provides a variety of tools and services to make it easy to set up and manage a MySQL database on an EC2 instance. For example, you can use the AWS Launch Wizard to launch an EC2 instance with a MySQL database pre-installed.

  • Integration with other AWS services: AWS offers a variety of other services that can be integrated with MySQL, such as Amazon Relational Database Service (RDS), Amazon Aurora, and Amazon Redshift. This allows you to build complex database solutions that meet your specific needs.

Prerequisites

To set up a MySQL database using an AWS EC2 instance, you need an AWS account with access to launch EC2 instances.

AWS Documentation Reference Links:

Steps to Set up a MySQL Database on AWS EC2

Perform the following high-level steps to launch an EC2 instance and set up a MySQL database:

  1. Launch an EC2 instance using a MySQL-compatible AMI: You can launch an EC2 instance using the AWS Management Console or the AWS CLI.

  2. Install MySQL Server on the EC2 instance: You can install MySQL Server on the EC2 instance using the Amazon Systems Manager (SSM) or the AWS CLI.

  3. Create a MySQL database: Once MySQL Server is installed on the EC2 instance, you can create a MySQL database using the CREATE DATABASE statement.

  4. Create a MySQL user: You must create a MySQL user to access the database. You can create a MySQL user using the CREATE USER statement.

  5. Grant privileges to the MySQL user: You will need to grant the MySQL user the necessary privileges to access and modify the database. You can grant privileges using the GRANT statement.

  6. Flush the privileges: You must flush the privileges table to ensure that the changes you made to the privileges take effect. You can flush the privileges using the FLUSH PRIVILEGES statement.

  7. Allow remote connections: MySQL only listens for connections on the local loopback interface by default. To allow remote connections to a MySQL database, you need to edit the mysqld.cnf file. The mysqld.cnf file is located in the etc/mysql directory on most Linux systems. After you have edited the mysqld.cnf file, save it, and restart the MySQL server.

  8. Test the connection: You can test the connection to your MySQL database using the MySQL command-line client.

Example: Set up a MySQL database using an AWS EC2 instance using the AWS Management Console.

  1. Go to the AWS Management Console and sign in.

  2. Search for and select EC2.

  3. Click Launch Instance.

  4. Select a MySQL-compatible Amazon Machine Image (AMI) for your EC2 instance. For example, you can select Amazon Linux AMI.

  5. Configure your EC2 instance and click Launch Instances.

  6. Once your EC2 instance is running, connect to it using an SSH client.

    • You can use an SSH client like PuTTY to connect to your EC2 instance.

  7. Update the package manager

    sudo yum update

  8. Install MySQL Server using the yum package manager

    sudo yum install mysql-server

  9. Start MySQL Server

    sudo service mysqld start

  10. Secure MySQL Server: Run the security script to set the root password and remove anonymous users

    sudo mysql_secure_installation

  11. Create a MySQL database

    mysql -u root -p

    CREATE DATABASE my_database;

  12. Create a dedicated MySQL user

    CREATE USER my_user IDENTIFIED BY 'my_password';

  13. Grant privileges to the MySQL user and flush privileges

    GRANT ALL PRIVILEGES ON my_database.* TO my_user;

    FLUSH PRIVILEGES;

  14. Modify MySQL's bind address to allow connections from other hosts

    sudo vi /etc/mysqld.cnf

    # Change bind-address to:

    bind-address = 0.0.0.0

    Note: Contact AWS support for assistance if you cannot modify the MySQL configuration files.

  15. Restart MySQL to apply the changes

    sudo systemctl restart mysqld

  16. Change user plugin: This step is used to change the authentication plugin for a MySQL user account. The authentication plugin is used to authenticate the user when they connect to the MySQL server.

    1. Check the user plugin with

      SELECT user, plugin, host FROM mysql.user;

    2. If necessary, change the user plugin

      RENAME USER 'username'@'localhost' TO 'username'@'%';

  17. Flush privileges

    FLUSH PRIVILEGES;

The MySQL server is now installed and secured on the EC2 instance. Make sure to restrict access to the EC2 instance and MySQL server to trusted sources for security. To do this, you can use a number of methods, such as:

  • SSH keys: SSH keys are a more secure way to connect to your EC2 instances than using passwords. To use SSH keys, you will need to generate an SSH key pair and then add the public key to your EC2 instance's security group.

  • VPN: A VPN can be used to create a secure tunnel between your computer and your AWS VPC. This allows you to access your EC2 instances and MySQL server without exposing them to the public internet.

Configure Security Groups

Perform the following high-level steps to configure security groups to allow MySQL traffic for a MySQL database on AWS EC2.

  1. Create a security group for the MySQL database.

  2. Allow inbound TCP traffic on port 3306 from the IP addresses or security groups of the clients that will be connecting to the database.

  3. Assign the security group to the EC2 instance running the MySQL database.

Example: Steps to create a security group for a MySQL database using the AWS Management Console.

  1. Go to the EC2 console and click Security Groups.

  2. Click Create Security Group.

  3. Enter a name and description for the security group.

  4. Click Add Rule.

  5. Select TCP from the Protocol drop-down list.

  6. Enter 3306 in the Port Range field.

  7. Select Anywhere from the Source drop-down list.

  8. Click Save.

Once you have created the security group, you need to allow inbound TCP traffic on port 3306 from the IP addresses or security groups of the clients that will be connecting to the database. To do this, perform the following steps:

  1. Select the security group that you created in the previous step.

  2. Click Edit.

  3. Click Add Rule.

  4. Select TCP from the Protocol drop-down list.

  5. Enter 3306 in the Port Range field.

  6. Select the IP addresses or security groups of the clients that will be connecting to the database from the Source drop-down list.

  7. Click Save.

Finally, you must assign the security group to the EC2 instance running the MySQL database. To do this, perform the following steps:

  1. Go to the EC2 console and click Instances.

  2. Select the EC2 instance that is running the MySQL database.

  3. Click Actions > Change Security Groups.

  4. Select the security group (that you created in the previous step) from the Available security groups list.

  5. Click Add Security Group.

  6. Click Apply.

After you have completed these steps, your MySQL database will be accessible to the clients that you specified in the security group rules.

Additional Notes:

  • Give the security group a relevant name related to its purpose, such as sg-mysql-prod-db.

  • Add a description of the security group rules and purpose.

  • Specify TCP port 3306 explicitly in the inbound or outbound rules.

  • For security reasons, it is essential to restrict access to the MySQL database to only the clients that need it. It would help if you also used strong passwords for all MySQL users.

Test the Connection

To test the connection to the MySQL database, run the following command:

mysql -u my_user -p -h <your_ec2_instance_public_ip>
  • -u my_user: Specifies the MySQL user to connect with.

  • -p: Prompts the user for the MySQL user's password.

  • -h <your_ec2_instance_public_ip>: Specifies the public IP address of the EC2 instance where the MySQL database is running.

If you can connect to the MySQL database, then you have successfully set up a MySQL database using an AWS EC2 instance, and you will be prompted for the MySQL user's password. After you enter the password, you will be logged into the MySQL database.

Task 2: Configure Your MySQL Database for Daton

To configure the MySQL database for integrating it as a destination with Daton, perform the following steps:

  1. Edit the mysqld.cnf file

    1. Open the mysqld.cnf file in a text editor.

    2. Add the following parameters to the end of the file

      wait_timeout = 600 
      interactive_timeout = 600 
      connect_timeout = 300 
      bind-address = 0.0.0.0 
      mysqlx-bind-address = 127.0.0.1 
      local-infile=1

      Save and close the mysqld.cnf file. These settings increase timeout values, allow remote connections, and enable the loading of local data files.

  2. Restart the MySQL server for the changes to take effect

    service mysql restart

  3. Enable local_infile

    1. Check the status of local_infile

      SHOW GLOBAL VARIABLES LIKE 'local_infile';

      The output of the command should show that local_infile is set to ON.

    2. Enable local_infile if it is not enabled

      SET GLOBAL local_infile = true;

  4. Flush privileges

    FLUSH PRIVILEGES;

Your MySQL database is now ready for integration with Daton.

The local_infile variable allows you to load data into MySQL tables from local files. This is required for Daton to be able to replicate data from your MySQL database.

For more detailed information on setting up a MySQL database on your machine, refer to the MySQL documentation: .

For Yum-based Linux distributions like Oracle Linux, Red Hat Enterprise Linux, and Fedora, see .

For APT-based distributions like Debian and Ubuntu, see .

On Windows, download the installer from

For more detailed information, refer to the following MySQL documentation: .

For more detailed information, refer to the following MySQL documentation:

Start the MySQL server: For more information, refer to the following MySQL documentation:

Security groups: Security groups are a firewall that controls inbound and outbound traffic to your EC2 instances. You can create a security group that allows access to port 3306 from only specific IP addresses, such as your own IP address or the IP address of your office network. See .

Next, perform the steps in the subtopic.

Installing and Upgrading MySQL
Installing MySQL on Linux
A Quick Guide to Using the MySQL Yum Repository
A Quick Guide to Using the MySQL APT Repository
https://dev.mysql.com/downloads/installer/
Installing MySQL on Microsoft Windows
Installing MySQL on macOS
Starting the Server
Creating and connecting to a MySQL DB instance
Amazon Elastic Compute Cloud Documentation
Controlling access with security groups
Create an Amazon RDS DB instance
Create and Connect to a MySQL Database with Amazon RDS
How to Set up Your MySQL Database as a Destination in Daton
Configure Security Groups

This guide will walk you through the essential steps of installing a MySQL database on your machine.

This guide will walk you through the essential steps of using an AWS EC2 instance to set up a MySQL database.

MySQL documentation
Task 1: Set up Your MySQL Database
Task 2: Configure Your MySQL Database for Daton
Task 2: Configure Your MySQL Database for Daton

Set up a MySQL Database on Your Machine
Set up a MySQL Database Using an AWS EC2 Instance