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 ingestSHOW DATABASES
privilege to list available databasesSHOW TABLES
privilege to list available tablesCONNECT
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 | INT8 | BIT | |
BOOL | BOOLEAN | BINARY | |
DATE | BLOB | ||
DECIMAL | DEC, NUMERIC | CHAR | CHARACTER |
DOUBLE | DOUBLE PRECISION | DATETIME | |
FLOAT | ENUM | ||
INT | INTEGER | GEOMETRY | |
STRING | JSON | ||
TIME | LINESTRING | ||
TIMESTAMP | LONGBLOB | ||
VARCHAR | LONGTEXT | ||
MEDIUMBLOB | |||
MEDIUMINT | |||
MEDIUMTEXT | |||
POINT | |||
POLYGON | |||
SET | |||
SMALLINT | |||
TEXT | |||
TINYBLOB | |||
TINYINT | |||
TINYTEXT | |||
VARBINARY | |||
YEAR |
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