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 Port: 3306
• Cluster Support: No (Single node only)
• Use Cases: Relational data, web applications, OLTP workloads
• Features: Replication, backups, InnoDB engine

Key Features

• High Performance: Optimized for speed and efficiency
• ACID Compliant: Full transaction support with InnoDB engine
• Replication: Master-slave replication for read scaling
• 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

Resource Tiers

Tier	CPU	Memory	Disk	Replicas	Best For
Small	0.5	1GB	10GB	1	Development, testing
Medium	1	2GB	25GB	1	Small production apps
Large	2	4GB	50GB	2	Production workloads
XLarge	4	8GB	100GB	2	High-traffic applications
Custom	User-defined	User-defined	User-defined	User-defined	Specific requirements

Creating a MySQL Add-on

1. Navigate to Add-ons -> Create Add-on
2. Select MySQL as the type
3. Choose a version (8.4, 8.0, or 5.7)
4. Configure:
   • Label: Descriptive name (e.g., "Application Database")
   • Description: Purpose and notes
   • Resource Tier: Based on your workload requirements
5. Configure backups:
   • Schedule: Hourly, Daily, Weekly, or Monthly
   • Retention: Number of backups to keep (default: 7)
6. Click Create Add-on

Connection Information

After deployment, connection details are available in the add-on details page and automatically injected into your apps via STRONGLY_SERVICES.

Connection String Format

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

The default database name is defaultdb. Credentials (username and password) 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 alphanumeric string.

Accessing Connection Details

Python

import os
import json
import mysql.connector

# Parse STRONGLY_SERVICES
services = json.loads(os.environ.get('STRONGLY_SERVICES', '{}'))

# Get MySQL add-on connection
mysql_addon = services['addons']['addon-id']

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

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

Node.js

const mysql = require('mysql2/promise');

// Parse STRONGLY_SERVICES
const services = JSON.parse(process.env.STRONGLY_SERVICES || '{}');
const mysqlAddon = services.addons['addon-id'];

// Create connection pool
const pool = mysql.createPool({
  host: mysqlAddon.host,
  port: mysqlAddon.port,
  database: mysqlAddon.database,
  user: mysqlAddon.username,
  password: mysqlAddon.password,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Query example
const [rows] = await pool.query('SELECT * FROM users WHERE active = ?', [true]);

Go

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "os"
    _ "github.com/go-sql-driver/mysql"
)

type Services struct {
    Addons map[string]Addon `json:"addons"`
}

type Addon struct {
    Host     string `json:"host"`
    Port     int    `json:"port"`
    Database string `json:"database"`
    Username string `json:"username"`
    Password string `json:"password"`
}

func main() {
    var services Services
    json.Unmarshal([]byte(os.Getenv("STRONGLY_SERVICES")), &services)

    mysqlAddon := services.Addons["addon-id"]

    // Build DSN
    dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s",
        mysqlAddon.Username,
        mysqlAddon.Password,
        mysqlAddon.Host,
        mysqlAddon.Port,
        mysqlAddon.Database)

    db, err := sql.Open("mysql", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()
}

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: AWS S3 (s3://strongly-backups/backups/<addon-id>/)

Manual Backup

1. Go to add-on details page
2. Click Backup Now
3. Monitor progress in job logs
4. Backup saved as backup-YYYYMMDDHHMMSS.sql

Scheduled Backups

Configure during add-on creation or in settings:

• Hourly: For critical data with frequent changes
• Daily: Recommended for most production workloads
• Weekly/Monthly: For less frequently changing data
• Retention: 7-14 days minimum for production

Data Loss

Backup restoration replaces ALL current data. Create a current backup before restoring if needed.

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

Monitor your MySQL add-on through the Strongly platform:

• CPU Usage: Track CPU utilization
• Memory Usage: Monitor memory consumption
• Disk Space: Watch disk utilization
• Connection Count: Active connections
• Query Performance: Slow query detection

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
10. Resource Planning: Start with appropriate tier, scale up as needed

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 Strongly dashboard
• Review MySQL official documentation
• Contact Strongly support through the platform

Related Documentation

• Managed Add-ons Overview
• PostgreSQL
• MongoDB