Skip to content

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:

FieldExample Value
NameProduction MySQL Database
Hostmysql-server.company.com
Port3306
Databaseanalytics_db
Usernamevendia_user
Password****
Use SSLYes
Connection Stringmysql://vendia_user:****@mysql-server.company.com:3306/analytics_db?ssl=true

Vendia Supported and Unsupported MySQL Data Types

Vendia Supported MySQL Data TypesSupported AliasesVendia Unsupported MySQL Data TypesUnsupported Aliases
BIGINTINT8BIT
BOOLBOOLEANBINARY
DATEBLOB
DECIMALDEC, NUMERICCHARCHARACTER
DOUBLEDOUBLE PRECISIONDATETIME
FLOATENUM
INTINTEGERGEOMETRY
STRINGJSON
TIMELINESTRING
TIMESTAMPLONGBLOB
VARCHARLONGTEXT
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:

  1. Connection Refused: Verify the host address and port number are correct
  2. Authentication Failed: Check username and password credentials
  3. Database Not Found: Confirm the database name exists and is accessible
  4. Permission Denied: Verify the user has the required SELECT and SHOW privileges
  5. SSL Errors: Check SSL/TLS configuration and certificate validity
  6. Network Issues: Ensure firewall rules allow connections on the specified port
  7. 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