Integrating your MySQL database with Alooma is easy:
How do you want to connect to Alooma? If it's via an SSH server, check out how to connect via SSH. Otherwise, you'll need to whitelist access to Alooma's IP addresses.
Is your MySQL hosted in Amazon? Check out how to whitelist IPs in Amazon.
From the Plumbing screen, click Add new input. Select MySQL from the list of inputs.
Name your MySQL input and enter the following details:
Hostname of the MySQL server (default port is 3306)
Username and Password (if you're using RDS/Aurora you created this when you configured CDC)
If your MySQL server is behind an SSH server you can connect to MySQL via SSH.
Click Connect to Continue.
Select the destination schema. This is the schema where you’d like Alooma to create the tables for this input in your data warehouse.
Choose the replication method you'd like to use for MySQL replication.
Alooma does not support GTID replication.
For CDC replication (recommended) simply select CDC and then specify which tables to replicate. If you want to load a subset of the tables in your MySQL instance, select "Specific tables from specific databases" and then click in the Table Names field and choose tables from the list that appears.
Mark the checkbox if you want to perform an initial snapshot of historical data. If unchecked, we’ll only pull newly changed data and will not replicate any historical data.
You must set up CDC prior to this step. See the corresponding "Setting up CDC" documentation for your environment.
For incremental replication, provide:
The database/schema you want to replicate.
A table and update indicator column pair for each table you want to replicate.
If you're using incremental replication for your database, and not using xmin, we recommend — especially for larger databases — that you create an index on the update indicator columns (
dtLastModifiedin SQL Server/Oracle, for example).
If you do not index the indicator column, your database will:
Create a potentially large temp table, which could end up filling the disk.
Sort the table at load, which is CPU intensive and will add time to the process.
Follow the instructions for creating an index here: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
For full dump/load replication:
Specify the database/schema you want to replicate.
Select the tables you want to replicate from the list.
Specify the frequency at which you'd like to replicate your tables. The more frequent, the more fresh your data will be, but the more load it puts on your MySQL database.
Keep the mapping mode to the default of OneClick if you'd like Alooma to automatically map all MySQL tables exactly to your target data warehouse. Otherwise, they'll have to be mapped manually from the Mapper screen.
Alooma maintains three tables in your target database per each table in MySQL.
That's it! You're done integrating MySQL and Alooma.