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.

Task list:

1. Task 1: Set up Your MySQL Database

2. Task 2: Configure Your MySQL Database for Daton

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.

Set up a MySQL Database on Your Machine

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

Set up a MySQL Database Using an AWS EC2 Instance

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

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 MySQL documentation 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 Task 2: Configure Your MySQL Database for Daton.

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

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

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:

       • Installing MySQL on Linux

       • For Yum-based Linux distributions like Oracle Linux, Red Hat Enterprise Linux, and Fedora, see A Quick Guide to Using the MySQL Yum Repository.

       • For APT-based distributions like Debian and Ubuntu, see A Quick Guide to Using the MySQL APT Repository.

   • On Windows, download the installer from https://dev.mysql.com/downloads/installer/

     • For more detailed information, refer to the following MySQL documentation: Installing MySQL on Microsoft Windows.

   • On macOS, use Homebrew or the DMG installer

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

2. Start the MySQL server: For more information, refer to the following MySQL documentation: Starting the Server

   • On Linux/macOS, use

   sudo systemctl start mysql

   • On Windows, start the service from the services panel

   • On macOS, use

   mysql.server start

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

   mysql_secure_installation

4. Create a new MySQL database

   CREATE DATABASE mydatabase;

5. 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:

• 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

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:

• 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 Configure Security Groups.

• 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.

Next, perform the steps in the How to Set up Your MySQL Database as a Destination in Daton subtopic.