MySQL

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

Overview

• Versions: 8.0, 5.7
• 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	Best For
Small	0.5	1GB	10GB	Development, testing
Medium	1	2GB	25GB	Small production apps
Large	2	4GB	50GB	Production workloads
XLarge	4	8GB	100GB	High-traffic applications

Creating a MySQL Add-on

1. Navigate to Add-ons → Create Add-on
2. Select MySQL as the type
3. Choose a version (8.0 or 5.7)
4. Configure:
   • Label: Descriptive name (e.g., "Application Database")
   • Description: Purpose and notes
   • Environment: Development or Production
   • Resource Tier: Based on your workload requirements
5. Configure backups:
   • Schedule: Daily recommended for production
   • Retention: 7+ days for production
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/database

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:

• Daily backups: Recommended for production
• Retention: 7-14 days minimum for production
• Custom cron: For specific schedules (e.g., 0 2 * * * for 2 AM daily)

Restore Process

1. Navigate to Backups tab
2. Select backup from list
3. Click Restore
4. Confirm (add-on will stop temporarily)
5. Data restored and add-on restarts

Data Loss

Restoring from backup replaces ALL current data. Create a current backup first 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. Test Restores: Periodically verify backup restoration
8. Use Prepared Statements: Prevent SQL injection and improve performance
9. Separate Dev/Prod: Never connect production apps to development databases
10. Resource Planning: Start with appropriate tier, scale up as needed
11. UTF8MB4 Charset: Use utf8mb4 for full Unicode support including emojis
12. 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 Strongly dashboard
• Review MySQL official documentation
• Contact Strongly support through the platform

Related Documentation

• Managed Add-ons Overview
• PostgreSQL
• MongoDB