Skip to main content

MySQL

MySQL

MySQL is a popular open-source relational database management system known for its reliability, ease of use, and performance.

Overview​

  • Versions: 8.4, 8.0, 5.7 (default: 8.4)
  • Default Port: 3306
  • Cluster Support: No (Single node only)
  • Use Cases: Relational data, web applications, OLTP workloads
  • Features: Backups, InnoDB engine

Key Features​

  • High Performance: Optimized for speed and efficiency
  • ACID Compliant: Full transaction support with InnoDB engine
  • Storage Engines: InnoDB, MyISAM, and others for different use cases
  • Rich Ecosystem: Extensive tooling and community support
  • Stored Procedures: Support for stored procedures, triggers, and views

Resources​

Choose the add-on's resources on the create form:

SettingOptionsDefault
CPU (vCPU)Free entry (e.g., 0.5, 1, 2)0.5
Memory1GB, 2GB, 4GB, 8GB, 16GB1GB
Disk Space1GB, 5GB, 10GB, 20GB, 50GB, 100GB10GB
GPU Count0-8 (0 for CPU-only)0

Creating a MySQL Add-on​

  1. Navigate to Add-ons and click Create Add-on
  2. On the Create New Add-on page, select MySQL as the type
  3. Choose a version (8.4, 8.0, or 5.7)
  4. Configure:
    • Add-on Label (required): descriptive name (e.g., "app-database")
    • Description (optional): purpose and notes
    • Resources: CPU, memory, and disk for your workload
  5. Optionally enable automatic backups:
    • Schedule: Hourly, Daily, Weekly, or Monthly
    • Retention: number of backups to keep (1-30, default 7)
  6. Click Create Add-on

Connection Information​

Once the add-on is running, the Connection tab of the add-on details page shows the internal host (for apps), port, database, username, password, and a ready-to-use connection string. The same details are exposed to your apps via STRONGLY_SERVICES.

Connection String Format​

mysql://username:password@host:3306/defaultdb

The default database name is defaultdb. Credentials are auto-generated during add-on creation: the username is a randomly generated string (e.g., user_a1b2c3d4) and the password is a 32-character random secret.

Accessing Connection Details​

In STRONGLY_SERVICES, add-ons are grouped by type under services.addons, and each entry is one provisioned instance:

{
  "id": "addon-abc123defg",
  "name": "app-database",
  "type": "mysql",
  "category": "add-on",
  "status": "running",
  "version": "8.4",
  "connection": {
    "connection_string": "mysql://user_a1b2c3d4:<password>@<internal-host>:3306/defaultdb",
    "uri": "mysql://user_a1b2c3d4:<password>@<internal-host>:3306/defaultdb",
    "host": "<internal-host>",
    "port": 3306,
    "database": "defaultdb"
  },
  "auth": {
    "method": "username_password",
    "credentials": { "username": "user_a1b2c3d4", "password": "<password>" }
  },
  "limits": { "max_connections": 100, "storage_gb": 10 },
  "metadata": { "cpu": "0.5", "memory": "1GB", "disk": "10GB", "backup_enabled": false }
}
import os
import json
import mysql.connector

# Parse STRONGLY_SERVICES
services = json.loads(os.environ['STRONGLY_SERVICES'])

# Pick your MySQL add-on by name (the label you gave it)
mysql_addon = next(
    a for a in services['services']['addons']['mysql']
    if a['name'] == 'app-database'
)

# Connect using individual parameters
conn = mysql.connector.connect(
    host=mysql_addon['connection']['host'],
    port=mysql_addon['connection']['port'],
    database=mysql_addon['connection']['database'],
    user=mysql_addon['auth']['credentials']['username'],
    password=mysql_addon['auth']['credentials']['password']
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE active = %s", (True,))
results = cursor.fetchall()

Common Operations​

Creating Tables​

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    metadata JSON
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created ON users(created_at);

JSON Operations (MySQL 5.7+)​

-- Query JSON data
SELECT * FROM users
WHERE JSON_EXTRACT(metadata, '$.premium') = true;

-- Or using -> operator
SELECT * FROM users
WHERE metadata->>'$.premium' = 'true';

-- Update JSON field
UPDATE users
SET metadata = JSON_SET(metadata, '$.last_login', '2025-01-26')
WHERE id = 1;

-- Extract JSON field
SELECT email, metadata->>'$.plan' as plan
FROM users;

Full-Text Search​

-- Create FULLTEXT index
ALTER TABLE articles ADD FULLTEXT INDEX idx_fulltext (title, content);

-- Search using MATCH AGAINST
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('mysql database' IN NATURAL LANGUAGE MODE);

-- Boolean search
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+mysql -postgresql' IN BOOLEAN MODE);

Backup & Restore​

MySQL add-ons use mysqldump for backups, creating complete SQL dumps of your database.

Backup Configuration​

  • Tool: mysqldump
  • Format: .sql
  • Includes: Schema, data, indexes, constraints
  • Storage: the platform's S3 backup storage

Manual Backup​

  1. Go to the add-on details page
  2. Open the Backup tab and click Create Manual Backup (the add-on must be running)
  3. The Backup tab shows the last backup time when complete

Scheduled Backups​

Configure during add-on creation or on the Backup tab:

  • Hourly: For critical data with frequent changes
  • Daily: Recommended for most production workloads
  • Weekly/Monthly: For less frequently changing data
  • Retention: number of backups to keep (3, 7, 14, or 30 on the Backup tab)

Performance Optimization​

Connection Pooling​

Use connection pooling for better performance:

from mysql.connector import pooling

# Create a connection pool
connection_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=10,
    host='host',
    database='database',
    user='user',
    password='password'
)

# Get connection from pool
conn = connection_pool.get_connection()
# Use connection
# ...
conn.close()  # Returns to pool

Indexing Strategy​

-- Standard B-tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_users_email_active ON users(email, active);

-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Analyze index usage
SHOW INDEX FROM users;

-- Check index cardinality
SELECT table_name, index_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'your_database'
ORDER BY cardinality DESC;

Query Optimization​

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Analyze table for better statistics
ANALYZE TABLE users;

-- Optimize table (defragment and update statistics)
OPTIMIZE TABLE users;

-- Monitor slow queries
SELECT query_time, lock_time, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 10;

Monitoring​

The Metrics tab on the add-on details page shows:

  • CPU Usage: CPU utilization percentage
  • Memory Usage: memory utilization percentage
  • Disk Space: disk utilization percentage
  • Network I/O: current throughput
  • Request Stats: connections per minute and average response time
  • Instance Health: instance count and uptime

Enable Slow Query Log​

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- Log queries taking more than 2 seconds

-- Check slow query log status
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Best Practices​

  1. Use InnoDB Engine: Default engine with ACID compliance and row-level locking
  2. Enable Connection Pooling: Reduce connection overhead
  3. Regular Optimization: Run OPTIMIZE TABLE periodically
  4. Monitor Query Performance: Use EXPLAIN for slow queries
  5. Use Transactions: Wrap related operations in transactions
  6. Backup Regularly: Enable daily backups for production databases
  7. Use Prepared Statements: Prevent SQL injection and improve performance
  8. UTF8MB4 Charset: Use utf8mb4 for full Unicode support including emojis
  9. Avoid MyISAM: Use InnoDB for better reliability and performance

Migration Guide​

From PostgreSQL to MySQL​

Key differences to note:

-- PostgreSQL: SERIAL
-- MySQL: AUTO_INCREMENT
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY
);

-- PostgreSQL: LIMIT count OFFSET offset
-- MySQL: LIMIT offset, count (or LIMIT count OFFSET offset in newer versions)
SELECT * FROM users LIMIT 20, 10;
-- or
SELECT * FROM users LIMIT 10 OFFSET 20;

-- PostgreSQL: || operator
-- MySQL: CONCAT() function
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- PostgreSQL: BOOLEAN type
-- MySQL: TINYINT(1) or BOOLEAN (alias for TINYINT(1))
CREATE TABLE users (
    active BOOLEAN DEFAULT TRUE
);

-- PostgreSQL: NOW()
-- MySQL: NOW() or CURRENT_TIMESTAMP
SELECT NOW();

Troubleshooting​

Connection Issues​

# Test connection
mysql -h host -P 3306 -u username -p database

# Check connection limits
SHOW VARIABLES LIKE 'max_connections';

# View current connections
SHOW PROCESSLIST;

# Count connections by user
SELECT user, COUNT(*) FROM information_schema.processlist GROUP BY user;

Performance Issues​

-- Find long-running queries
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;

-- Kill a long-running query
KILL QUERY process_id;

-- Check table sizes
SELECT table_schema, table_name,
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'your_database'
ORDER BY (data_length + index_length) DESC;

Disk Space Issues​

-- Check database size
SELECT table_schema AS 'Database',
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

-- Check table sizes in current database
SELECT table_name,
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
       ROUND((data_free / 1024 / 1024), 2) AS free_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 10;

Support​

For issues or questions:

  • Check add-on logs in the Logs tab of the add-on details page
  • Review MySQL official documentation
  • Contact Strongly support through the platform