MySQL

MySQL can be very effective as a data warehouse for smaller volumes. For enterprise-class data warehouses, MySQL is not typically used as a data warehouse because it is a row-based database better suited for transactional needs.

Prerequisites

To connect your MySQL as a destination database in ETLrobot, you need:

• MySQL version 5.7.8 or above.
• Your database's IP address (e.g., 1.2.3.4) or domain (your.server.com).
• Your database's port (usually 3306).
• Database privileges that allow you to create users and grant privileges. This is required to create a database user and role for ETLrobot.
• A database that uses the InnoDB storage engine.
• It is recommended to set the database timezone to UTC.

---

Common supported implementations:

• Generic MySQL
• Amazon RDS MySQL
• Amazon Aurora MySQL
• Google Cloud MySQL
• Azure MySQL

---

Setup instructions

1. Database setup

   1. Connect to your MySQL instance as an admin user using your SQL client.

   2. Create a database to use for this data warehouse if you don't have one already.
      Possible name: dw (for data warehouse)

      create database if not exists <database_name> CHARACTER SET=utf8mb4;
      

   3. After connecting, execute the following command to create a user for ETLrobot. Choose a memorable username (for example, etlrobot). Replace <password> with a strong password of your choice.

      CREATE USER 'etlrobot'@'%' IDENTIFIED BY '<password>';
      

   4. Execute the following commands to grant the etlrobot user the following privileges. The database you specify here must be a pre-existing database.

      SyntaxInfo

      grant create, alter, index, create view, select, insert, update, delete, drop on <database>.* to 'etlrobot'@'%';
      flush privileges;
      

      • grant: These are the privileges ETLrobot needs to interact wit the database. Create enables database and table creation.
      • flush privileges: Reloads the grant tables in the mysql database enabling the changes to take effect without restarting mysql service.

2. Choose your connection method

   Decide whether to connect to your PostgreSQL database directly or via an SSH tunnel.

   Connect directly

   If your database is publicly accessible you may connect directly. You must whitelist ETLrobot's IPs by creating a rule in a security group that allows ETLrobot access to your database port and instance.

   Configure your firewall and/or other access control systems to allow:

   1. incoming connections to your host and port (usually 3306) from ETLrobot's IPs for your database's region
   2. outgoing connections from all ports (1024 to 65535) to ETLrobot's IPs
   Connect via an SSH tunnel

   If your database is not publicly accessible you will need to connect using an SSH tunnel.

   SSH requires a publicly accessible SSH server to act as an intermediary between ETLrobot and your private database. The SSH server will forward traffic from ETLrobot through a secured encrypted tunnel to the private database.

   To connect using SSH, do the following:

   1. Select the SSH option.
   2. Copy ETLrobot's public SSH key.
   3. Add the public key to the authorized_keys (usually $user/.ssh/authorized_keys) file of your SSH server. The key must be all on one line, so make sure that you don't introduce any line breaks when cutting and pasting.

   Click here detailed instructions on connecting via an SSH tunnel.

3. Complete the destination setup

   Be sure to fill out all the required fields.

   ETLrobot tests and validates your MySQL destination connection and database setup. If successful, the sync to your database will automatically start.