# 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:

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