# MySQL Connection Strings in API Maker

> Learn how to format MySQL URI strings in API Maker—username, password, host, port, database name, and advanced options. Includes SSL, URL encoding tips, and examples for cloud-hosted services.

Source: https://docs.apimaker.dev/v1/docs/Database-connection-string/mysql-connection-strings.html

## Introduction

A **MySQL connection string** defines how API Maker connects to your MySQL database.  
It contains the necessary details such as **username, password, host, port, and database name**, along with optional parameters for **SSL, pooling, and timeouts**.

This connection string is the starting point for establishing a secure and reliable connection to MySQL.

---

## MySQL Connection String Formats

Below is a **visual breakdown** of the MySQL connection string.

```text
mysql://username:password@host:port/DB_Name?options

 └───┬──┘ └───────┬───────┘ └───┬────┘ └──┬──┘ └──┬──┘
   Scheme      Credentials   Host + Port Database Options
```

---

## MySQL Connection String Examples

### 🧩 Connection String Parts Explained

- **Protocol (scheme):**

    - `mysql://` → Standard MySQL connection.

    - `mariadb://` → Also accepted for MariaDB compatibility.

- **Credentials (optional):**

    - Format: `username:password@`.

    - Use **URL-encoding** for special characters (`pa@ss` → `pa%40ss`).

- **Host + Port:**

    - Default host: `localhost`

    - Default port: `3306`

- **Database (optional):**

    - After `/`, e.g., `/mydb`.

    - If omitted, connection starts without selecting a default database.

- **Options (query params):**

    - Added after `?`, key-value pairs separated by `&`.

    - Examples:

        - `?ssl-mode=REQUIRED` → Enforce SSL/TLS.

        - `?connectTimeout=5000` → Timeout in ms.

        - `?allowPublicKeyRetrieval=true` → Needed for some cloud connections.

---

### Basic Localhost Connection

```text
mysql://localhost:3306
```

### Localhost with Database Name

```text
mysql://localhost:3306/mydb
```

### Localhost with Username & Password

```text
mysql://user:password@localhost:3306/mydb
```

### Localhost with SSL Enabled

```text
mysql://user:password@localhost:3306/mydb?ssl=true
```

### Localhost with Connection Timeout

```text
mysql://user:password@localhost:3306/mydb?connectTimeout=10000
```

### Localhost with Charset Setting

```text
mysql://user:password@localhost:3306/mydb?charset=utf8mb4
```

### Localhost with Multiple Options

```text
mysql://user:password@localhost:3306/mydb?ssl=true&charset=utf8mb4&connectTimeout=10000
```

### Multi-Host / Cluster Connection

```text
mysql://user:password@host1:3306,host2:3306,host3:3306/mydb
```

### Replication Connection

```text
mysql://replica_user:password@replica_host:3306/mydb?replication=true
```

### Cloud Provider Connection (AWS RDS Example)

```text
mysql://user:password@mydb.xxxxxx.us-east-1.rds.amazonaws.com:3306/mydb
```

### Cloud with SSL Required (Azure Database for MySQL)

```text
mysql://user:password@myserver.mysql.database.azure.com:3306/mydb?ssl=true
```

### Cloud with Multi-Host Failover (Google Cloud SQL HA Setup)

```text
mysql://user:password@host1:3306,host2:3306/mydb?ssl=true
```

---

## MySQL Connection String Parts Breakdown

|Part|Description|Example|
|---|---|---|
|**Scheme**|Protocol prefix for MySQL.|`mysql://`|
|**Username** (optional)|Database username for authentication.|`user`|
|**Password** (optional)|Password for authentication (URL-encoded if special chars).|`p%40ss` for `p@ss`|
|**@**|Separator between credentials and host(s).|`user:password@`|
|**Host(s)**|One or more MySQL server addresses.|`localhost`, `mydb.xxxxxx.us-east-1.rds.amazonaws.com`|
|**Port** (optional)|Port number (default: `3306`).|`:3306`|
|**Comma-separated Hosts**|Multiple hosts for HA / load balancing.|`host1:3306,host2:3306`|
|**/** (slash after hosts)|Separator between hosts and default database.|`/`|
|**Default Database**|Database name to connect to if none specified.|`mydb`|
|**?options** (query params)|Connection options in `key=value` format (joined with `&`).|`?ssl-mode=REQUIRED&connect_timeout=10`|

---

## Connection String Options Breakdown and Explanation

|Parameter|Description|Example Value|
|---|---|---|
|**ssl_mode**|Controls SSL/TLS usage (`DISABLED`, `PREFERRED`, `REQUIRED`, `VERIFY_CA`, `VERIFY_IDENTITY`).|`ssl_mode=REQUIRED`|
|**ssl_ca**|Path to the CA certificate for SSL validation.|`ssl_ca=/etc/ssl/ca.pem`|
|**ssl_cert**|Path to client SSL certificate.|`ssl_cert=/etc/ssl/client-cert.pem`|
|**ssl_key**|Path to client SSL key.|`ssl_key=/etc/ssl/client-key.pem`|
|**connect_timeout**|Maximum wait time (in seconds) for a new connection.|`connect_timeout=10`|
|**database**|Default database to connect to.|`database=mydb`|
|**charset**|Sets the character set for the connection.|`charset=utf8mb4`|
|**autocommit**|Controls autocommit mode (`0` = off, `1` = on).|`autocommit=1`|
|**allow_multi_statements**|Allow executing multiple SQL statements in one query.|`allow_multi_statements=true`|
|**read_timeout**|Seconds to wait for a read operation before timing out.|`read_timeout=30`|
|**write_timeout**|Seconds to wait for a write operation before timing out.|`write_timeout=30`|
|**max_allowed_packet**|Maximum packet size (bytes) for sending/receiving data.|`max_allowed_packet=67108864`|
|**allowPublicKeyRetrieval**|Allows retrieval of RSA public key for secure password exchange.|`allowPublicKeyRetrieval=true`|
|**useSSL**|Deprecated SSL flag; prefer `ssl_mode`.|`useSSL=true`|
|**serverTimezone**|Sets the timezone for the connection.|`serverTimezone=UTC`|
|**replication**|For replication connections (`true` or `database`).|`replication=true`|

---

## Secure Connection Best Practices

- Always enable **SSL/TLS (`sslmode=require`)** in production.

- Use **environment variables** instead of hardcoding credentials.

- Leverage **API Maker Secrets Management** to store and rotate sensitive keys.

- Restrict MySQL users to **least privilege access**.

- Use **firewall rules** or `bind-address` to allow only trusted IP addresses.

- Enable **connection timeouts** to prevent hanging connections.

---

## Connecting MySQL in API Maker

1. Open **API Maker Dashboard** → **Secret Management** → **Default**.

2. Select **MySQL** as the database type.

3. Paste your MySQL connection string (with DB and SSL parameters).

4. Click **Test Connection** to verify connectivity.

5. Save the configuration.

Once connected, you can:

- Define **schemas** for your MySQL tables.

- Query data using `/api/schema/...` endpoints.

- Perform **cross-database joins** with PostgreSQL, MongoDB, or SQL Server.

---

## Troubleshooting

When connecting to MySQL (local, self-hosted, or managed services like RDS/Azure/CloudSQL), you may encounter several common errors.  
Below is a categorized list with explanations:

---

### 🔑 Authentication & Authorization Errors

| Error Code | Name                  | Description                                        | Common Fix                                             |  
|------------|-----------------------|----------------------------------------------------|--------------------------------------------------------|  
| 1045       | AccessDenied          | Wrong username or password.                        | Verify username/password in connection string.         |  
| 1130       | HostNotAllowed        | User cannot connect from this host.                | Grant privileges using `GRANT` and whitelist IP.       |  
| 1044       | InsufficientPrivilege | User does not have privileges on the database.    | Grant required privileges using `GRANT`.               |  
| 1203       | TooManyConnections    | Connection limit exceeded.                         | Increase `max_connections` or close idle sessions.     |  

---

### 🌐 Network & Connectivity Errors

| Error Code | Name                       | Description                                  | Common Fix                                               |  
|------------|----------------------------|----------------------------------------------|----------------------------------------------------------|  
| 2002       | ConnectionRefused          | Client could not connect to server.          | Verify hostname, port, and firewall rules.               |  
| 2003       | ConnectionFailure          | Connection unexpectedly terminated.           | Check server logs, network stability.                    |  
| 2013       | LostConnection             | Connection was closed unexpectedly.           | Reconnect before executing queries.                      |  
| 1047       | ServerShutdown             | Server shutting down or not responding.       | Wait for restart and reconnect.                          |  

---

### 🗂️ Duplicate & Constraint Errors

| Error Code | Name                    | Description                                | Common Fix                                             |  
|------------|-------------------------|--------------------------------------------|--------------------------------------------------------|  
| 1062       | DuplicateEntry           | Duplicate value violates unique constraint.| Ensure unique values or handle conflict with `ON DUPLICATE KEY UPDATE`. |  
| 1452       | ForeignKeyViolation      | Insert/update violates foreign key.        | Ensure referenced key exists before inserting/updating. |  
| 3819       | CheckConstraintViolation | Value violates a `CHECK` constraint.       | Insert valid values matching constraint rules.          |  
| 1215       | ForeignKeyConstraintFail | Foreign key constraint creation failed.    | Verify referenced keys and types match.               |  

---

### ⚡ Write & Transaction Errors

| Error Code | Name                  | Description                                    | Common Fix                                              |  
|------------|----------------------|------------------------------------------------|---------------------------------------------------------|  
| 1213       | DeadlockFound         | Deadlock occurred between transactions.       | Redesign queries/locking, or retry after backoff.       |  
| 1205       | LockWaitTimeout       | Transaction timed out waiting for a lock.    | Retry the transaction or increase lock wait timeout.    |  
| 1048       | NotNullViolation      | Tried to insert NULL into NOT NULL column.   | Provide a value or alter column to allow NULL.          |  
| 1021       | DiskFull              | Server ran out of disk space.                 | Free disk space or increase storage.                    |  

---

### 🗄️ Query & Syntax Errors

| Error Code | Name                   | Description                              | Common Fix                                              |  
|------------|------------------------|------------------------------------------|---------------------------------------------------------|  
| 1064       | SyntaxError            | Invalid SQL syntax.                      | Fix query syntax.                                       |  
| 1054       | UnknownColumn          | Column does not exist.                   | Check column name spelling or schema.                   |  
| 1146       | TableDoesNotExist       | Table does not exist.                     | Create table or use correct table name.                 |  
| 1305       | UnknownFunction         | Function/operator not defined.            | Define function or cast arguments properly.             |  
| 1065       | EmptyQuery              | Query is empty or invalid.                | Provide a valid SQL statement.                          |  

---

### 🔒 TLS/SSL & Security Errors

| Error Code | Name                 | Description                                    | Common Fix                                                  |  
|------------|----------------------|------------------------------------------------|-------------------------------------------------------------|  
| 2026       | SSLConnectionError    | TLS/SSL negotiation failed.                    | Ensure certificates, SSL mode (`require`, `verify-ca`).     |  
| 1045       | AccessDeniedSSL       | User not allowed for SSL/host restrictions.   | Grant access or adjust SSL-related configs.                |  
| 1040       | TooManyConnectionsSSL | Too many SSL connections.                      | Increase `max_connections` or close idle sessions.         |  

---

### 🛠️ Miscellaneous Errors

| Error Code | Name                   | Description                                  | Common Fix                                |  
|------------|------------------------|----------------------------------------------|-------------------------------------------|  
| 1221       | IncorrectKeyFile       | Query exceeded internal limit or config issue.| Check table indexes or optimize query.     |  
| 1114       | TableIsFull            | Table storage limit reached.                 | Free disk space or increase table size.    |  
| 1406       | DataTooLong             | Value too long for column type.              | Increase column size or truncate data.    |  
| 1194       | InternalError           | Unexpected internal error.                   | Check server logs and MySQL version.      |  

---

### 🧩 Error Types Summary

| Category                           | Typical Causes                                   | Example Error Codes           |  
| ---------------------------------- | ------------------------------------------------ | ----------------------------- |  
| **Authentication & Authorization** | Invalid login, role issues, too many connections | 1045, 1130, 1044, 1203       |  
| **Network & Connectivity**         | Host unreachable, server shutdown/recovery       | 2002, 2003, 2013, 1047       |  
| **Constraints & Duplicates**       | Unique, foreign key, check constraint violations | 1062, 1452, 3819, 1215       |  
| **Write & Transaction**            | Deadlocks, lock timeouts, null inserts           | 1213, 1205, 1048, 1021       |  
| **Query & Syntax**                 | Invalid SQL syntax, undefined columns/functions  | 1064, 1054, 1146, 1305       |  
| **TLS/SSL & Security**             | SSL handshake errors, host/user restrictions     | 2026, 1045, 1040              |  
| **Miscellaneous**                  | Data truncation, table full, internal errors     | 1221, 1114, 1406, 1194        |  

- **Authentication** covers errors like `1045 (AccessDenied)` and `1130 (HostNotAllowed)`.
- **Network** includes connection shutdowns (`2002`, `2003`) and lost connections (`2013`).
- **Transactions** cover deadlocks and lock timeouts (`1213`, `1205`).
- **Query** includes syntax issues and unknown columns/functions (`1064`, `1054`).
- **Constraints** cover duplicate entries and foreign key violations (`1062`, `1452`).
- **Miscellaneous** includes disk/table space issues (`1114`, `1406`) and internal errors (`1194`).

---

## FAQ

**Q1:** _Is `ssl-mode=REQUIRED` mandatory?_  
Not strictly, but it’s strongly recommended for production to secure data in transit.

**Q2:** _Can I use MySQL cloud services like AWS RDS, Azure Database, or GCP Cloud SQL?_  
Yes. Just provide the full connection string from your provider and whitelist API Maker’s IP.

**Q3:** _How do I connect to a MySQL instance running in Docker?_  
Use the container’s IP or host machine IP, e.g.,  
`mysql://user:pass@172.17.0.2:3306/mydb`.

---

## MySQL Cloud Providers

- **API Maker Cloud**
    - Provides a fully managed MySQL instance with instant APIs and schema management.
    - Ideal for API-first development where you want to skip manual server setup.
    - Offers seamless integration with API Maker features such as auto-increment, joins, and advanced querying.
    - Perfect for small to medium projects needing fast prototyping.
    - [apimaker.dev](https://apimaker.dev)

- **Amazon RDS**
    - Fully managed MySQL database with automated backups, patching, and failover support.
    - Suitable for production workloads running on AWS services like EC2, Lambda, or S3.
    - High availability, monitoring, and security are built-in with minimal operational overhead.
    - Offers flexibility to scale up or out depending on workload demands.
    - [aws.amazon.com/rds/mysql](https://aws.amazon.com/rds/mysql/)

- **Amazon Aurora**
    - MySQL-compatible relational database optimized for high performance and low latency.
    - Supports read replicas and multi-AZ deployment for high availability.
    - Fully managed by AWS, reducing operational complexity while enabling auto-scaling.
    - Ideal for applications requiring enterprise-grade reliability and performance.
    - [aws.amazon.com/rds/aurora](https://aws.amazon.com/rds/aurora/mysql-features/)

- **Google Cloud SQL**
    - Fully managed MySQL with automated failover, backups, and maintenance.
    - Integrates seamlessly with other Google Cloud services like BigQuery, GKE, and Cloud Storage.
    - Provides easy scaling and built-in monitoring to handle variable workloads.
    - Excellent for cloud-native applications running on Google Cloud infrastructure.
    - [cloud.google.com/sql/mysql](https://cloud.google.com/sql/mysql)

- **Azure Database**
    - Managed MySQL service with enterprise-grade security and compliance features.
    - Offers automatic scaling, high availability, and point-in-time backups.
    - Perfect for workloads within the Azure ecosystem or hybrid cloud setups.
    - Provides integration with other Azure services such as App Services and Functions.
    - [azure.microsoft.com/mysql](https://azure.microsoft.com/en-us/products/mysql)

- **Aiven**
    - Managed MySQL service across multiple cloud providers including AWS, GCP, and Azure.
    - Offers automated backups, monitoring, and high availability for production-ready workloads.
    - Supports multi-cloud deployments with easy migrations and flexible configurations.
    - Designed for teams wanting full cloud flexibility with minimal operational effort.
    - [aiven.io/mysql](https://aiven.io/mysql)

- **ScaleGrid**
    - Managed MySQL with full root access for advanced configuration and automation.
    - Balances automation with full customization for production-grade databases.
    - Supports deployment across multiple cloud providers with easy scaling and high availability.
    - Excellent for enterprises and developers requiring control without managing servers manually.
    - [scalegrid.io/mysql-hosting](https://scalegrid.io/mysql-hosting/)

- **PlanetScale**
    - Serverless MySQL platform built for massive scale and performance.
    - Offers zero-downtime schema changes and branch-based development workflow.
    - Globally distributed with high availability and automatic failover.
    - Ideal for modern cloud-native applications needing horizontal scaling.
    - [planetscale.com](https://planetscale.com)

- **ClearDB**
    - Specialized MySQL cloud service focused on web apps and SaaS deployments.
    - Provides automated scaling, backups, and high availability with minimal management overhead.
    - Supports multi-cloud deployments across AWS and other providers.
    - Perfect for applications hosted on PaaS platforms like Heroku.
    - [cleardb.com](https://www.cleardb.com)

- **Clever Cloud MySQL**
    - Fully managed MySQL with automatic updates, backups, and replication.
    - Offers high availability and horizontal scaling for enterprise applications.
    - Supports secure connections and monitoring dashboards for better observability.
    - Designed specifically for developers seeking hands-off database management.
    - [clever-cloud.com/mysql](https://www.clever-cloud.com/mysql)

---

## Related Links

- [MySQL Connection String Documentation](https://dev.mysql.com/doc/dev/connector-nodejs/latest/)
- [API Maker Schemas Overview](https://docs.apimaker.dev/v1/docs/schema/schema.html)
- [API Maker Secrets Management](https://docs.apimaker.dev/v1/docs/secrets/secrets.html)

---