Ingesting Data from MySQL and MariaDB | Vendia

Ingesting Data from MySQL and MariaDB

Connecting to MySQL or MariaDB

To connect to MySQL, MariaDB, or MySQL-compatible databases, you need to provide the following information:

Name: A friendly name for your connection to easily identify and reuse it for ingesting additional tables
Connection String (URI) - Optional: Paste your complete MySQL connection URI. If provided, this will be used instead of the individual fields below. Example: mysql://user:password@host:port/database?ssl=true
- Note: If you use this option, you can skip the individual fields below
Or Enter Details Individually:
- Host: The hostname or IP address of your MySQL/MariaDB server
- Port: The port number on which your MySQL/MariaDB server is listening (default is 3306)
- Database: The name of the database containing the tables you want to ingest
- Username: The username for the MySQL/MariaDB account you want to connect with
- Password: The password for the MySQL/MariaDB account you want to connect with
- Use SSL: Enable SSL/TLS encryption for the connection (recommended for security)

Supported MySQL-Compatible Databases

Vendia supports ingesting data from the following MySQL-compatible databases:

MySQL
MariaDB
Amazon RDS for MySQL
Amazon Aurora MySQL
Google Cloud SQL for MySQL
Azure Database for MySQL
Most other databases that are MySQL-compatible and support standard MySQL connection strings

Connection String Format

The connection string format for MySQL is:

mysql://[username]:[password]@[host]:[port]/[database]

Prerequisites

Before connecting to MySQL or MariaDB, ensure that:

Your database server is accessible from Vendia
You have valid credentials with appropriate permissions
The database service is running and accepting connections
Network connectivity allows access to the specified port (default 3306)
SSL/TLS is configured if required by your security policies

Required Permissions

The user account connecting to MySQL or MariaDB must have the following permissions:

SELECT privilege on the tables you want to ingest
SHOW DATABASES privilege to list available databases
SHOW TABLES privilege to list available tables
CONNECT privilege on the database

Example Configuration

Here’s an example of a typical MySQL/MariaDB connection configuration:

Field	Example Value
Name	Production MySQL Database
Host	mysql-server.company.com
Port	3306
Database	analytics_db
Username	vendia_user
Password	********
Use SSL	Yes
Connection String	`mysql://vendia_user:****@mysql-server.company.com:3306/analytics_db?ssl=true`

Vendia Supported and Unsupported MySQL Data Types

Vendia Supported MySQL Data Types	Supported Aliases	Vendia Unsupported MySQL Data Types	Unsupported Aliases
BIGINT		BINARY
DATE		BIT
DECIMAL	DEC, NUMERIC	BLOB
DOUBLE	DOUBLE PRECISION, REAL	CHAR	CHARACTER
FLOAT		DATETIME
INT	INTEGER	ENUM
MEDIUMINT		GEOMETRY
SMALLINT		JSON
TIME		LINESTRING
TIMESTAMP		LONGBLOB
TINYINT		LONGTEXT
TINYINT(1)	BOOL, BOOLEAN	MEDIUMBLOB
VARCHAR		MEDIUMTEXT
		POINT
		POLYGON
		SET
		TEXT
		TINYBLOB
		TINYTEXT
		VARBINARY
		YEAR

Note: In MySQL, BOOL and BOOLEAN are aliases that specifically map to TINYINT(1). When you declare a column as BOOLEAN or BOOL in MySQL, it is automatically converted to TINYINT(1).

Best Practices

Security: Always use SSL/TLS encryption for database connections in production
Permissions: Apply the principle of least privilege when creating database users
Testing: Test connectivity with a small sample table before ingesting large datasets

Troubleshooting

If you encounter connection issues:

Connection Refused: Verify the host address and port number are correct
Authentication Failed: Check username and password credentials
Database Not Found: Confirm the database name exists and is accessible
Permission Denied: Verify the user has the required SELECT and SHOW privileges
SSL Errors: Check SSL/TLS configuration and certificate validity
Network Issues: Ensure firewall rules allow connections on the specified port
Timeout Errors: Check network latency and database server load

Next Steps

After successfully connecting to your MySQL/MariaDB database, you can:

Select specific tables to ingest
Configure data transformations and mappings
Set up incremental data ingestion jobs
Schedule regular data synchronization tasks