MySQL Integration
In this article, we’ll integrate a MySQL data source with Y42.
MySQL is an open source relational database typically used to keep in-house custom data. Our integration platform replicates data from your MySQL source database and loads it into your y42 instance.
Overview
Authentication
Your database credentials allow you to log into your database, either locally or remotely. A credential is a record that contains the authentication information. This information is used internally by server hosting the SQL database. The credentials entail the host address, the IP hosting the server/database, the port number, the default port on MySQL is 3306, the user and the password.
You may also use SSH tunneling or use a Y42 IP address [35.198.102.73, 35.234.125.155, 35.198.107.177, 35.242.197.68, 35.241.201.46 ] for whitelisting.
SSH tunneling is a method of transporting arbitrary networking data over an encrypted SSH connection. The SSH connection is used by the application to connect to the application server. With tunneling enabled, the application contacts to a port on the local host that the SSH client listens on. The SSH client then forwards the application over its encrypted tunnel to the server. See the configuration example page for detailed configuration instructions. The SSH command line options and SSH server configuration file pages may also be helpful.
To make the SSH connection work when connecting in Y42, you will need to chain the SSH tunnel configuration and the IP whitelisting as illustrated below:
Meanwhile, whitelisting means taking manual steps to ensure that a certain IP address isn't blocked from accessing your application by some automated security process. Whitelisting IP addresses enables you to control who can (or cannot) access your database. You can add an IP to Whitelist on the security configurations of your database.
Import Settings
None.
Schema
Dynamic Schema is a schema which will be dynamically loaded at run-time.
Updating your data
You have the option of scheduling Full imports or Incremental Imports by the month, weeks, days, and even by the hour. With the incremental import you will only add the new rows to that table, while a full import will actually re-import the entire table completely.
Incremental imports significantly increase the import speed, we import your table incrementally based on the last imported state e.g. id - If the table does not have a state, a full import will be triggered - This is done automatically.
Enable MySQL binlog
You also have the option of performing updates on MySQL tables via binlog. The binary log is a set of log files that contain information about data modifications made to a MySQL server instance. This means that with the log-based incremental replication active, updates will only be done on top of those records that got modified in the MySQL instance - including inserts, updates, and deletes.
To enable Binary Logging for incremental updates please follow the steps:
- Check if your mysql server bin log is already enabled by running the following statement `show global variables like “log_bin”`
Note: Binary Log needs to use ROW-based not MIXED. You can cause the server to use row-based logging by starting it with--binlog-format=ROW. - If enabled, please skip to step 4.
- Otherwise, use the following instructions to enable the bin log: link
- Please make sure that that all permissions for the database are given for the mysql user used. The MySQL user needs SUPER or BINLOG MONITOR permissions. BINLOG MONITOR is called REPLICATION CLIENT in newer MySQL/MariaDB versions.
- When connecting to your database check the box Use binlog as replication method for incremental imports
Using binlog on AWS Aurora MySQL
With Aurora you have the option to set-up multiple read replicas to your write-instance. If you want to point Y42 to use these read-instances, please bear in mind, that using the read-only URL is not compatible with binary log replication.
So if you want to use incremental imports via the binary log with Aurora MySQL, you must use the write-endpoint (URL) and not the read-only-endpoint when setting up the connection in the Y42 integration. You can find more information about this on the Aurora documentation at AWS.
MySQL Setup Guide
- On Integrate, click on "Add..." to search for MySQL and select it.
- Name your integration.
- Authorize access with your database credentials. Alternatively, use SSH tunnel(s) to connect or use a y42 IP for whitelisting.
- After authentication, you are good to go and start importing your tables.
- Select the tables you need and click import. You can start accessing the tables once the status is “Ready”.