Skip to content

MySQL Connection Strings in API Maker

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.

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

mysql://localhost:3306

Localhost with Database Name

mysql://localhost:3306/mydb

Localhost with Username & Password

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

Localhost with SSL Enabled

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

Localhost with Connection Timeout

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

Localhost with Charset Setting

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

Localhost with Multiple Options

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

Multi-Host / Cluster Connection

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

Replication Connection

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

Cloud Provider Connection (AWS RDS Example)

mysql://user:[email protected]:3306/mydb

Cloud with SSL Required (Azure Database for MySQL)

mysql://user:[email protected]:3306/mydb?ssl=true

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

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:[email protected]: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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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