Deploying a MySQL Database

Introduction

MySQL is the world’s most popular open-source relational database management system (RDBMS), powering millions of websites and applications globally. Developed by Oracle Corporation, MySQL has been the backbone of the web since the mid-1990s, trusted by tech giants like Facebook, Twitter, YouTube, and Netflix.

MySQL is renowned for:

Proven Reliability: Decades of production use in mission-critical applications
ACID Compliance: Full support for atomicity, consistency, isolation, and durability
High Performance: Optimized for speed with efficient query processing and indexing
Scalability: From small applications to massive data warehouses handling petabytes of data
Ease of Use: Straightforward setup with extensive documentation and community support
Cross-Platform: Runs on Linux, Windows, macOS, and more
Replication: Built-in master-slave and master-master replication for high availability
Storage Engines: Multiple storage engines including InnoDB (default) and MyISAM for different use cases

Common use cases include web applications, e-commerce platforms, content management systems, data warehousing, and logging systems.

This comprehensive guide walks you through deploying MySQL on Klutch.sh using Docker, including detailed installation steps, sample configurations, and production-ready best practices for persistent storage and security.

Prerequisites

Before you begin, ensure you have the following:

A Klutch.sh account
A GitHub account with a repository for your MySQL project
Docker installed locally for testing (optional but recommended)
Basic understanding of Docker and relational databases

Installation and Setup

Step 1: Create Your Project Directory

First, create a new directory for your MySQL deployment project:

mkdir mysql-klutch
cd mysql-klutch
git init

Step 2: Create the Dockerfile

Create a Dockerfile in your project root directory. This will define your MySQL container configuration:

FROM mysql:8.4

# Set default environment variables
# These can be overridden in the Klutch.sh dashboard
ENV MYSQL_DATABASE=mydb
ENV MYSQL_USER=myuser
ENV MYSQL_PASSWORD=mypassword
ENV MYSQL_ROOT_PASSWORD=rootpassword

# Expose the default MySQL port
EXPOSE 3306

# Optional: Copy custom MySQL configuration
# COPY ./my.cnf /etc/mysql/conf.d/custom.cnf

# Optional: Copy initialization scripts
# SQL and shell scripts in /docker-entrypoint-initdb.d/
# will be executed automatically on first startup
# COPY ./init.sql /docker-entrypoint-initdb.d/
# COPY ./init.sh /docker-entrypoint-initdb.d/

Note: MySQL 8.4 is the latest stable version with improved performance and security features.

Step 3: (Optional) Create Initialization Scripts

You can create SQL scripts that will run automatically when the database is first initialized. Create a file named init.sql:

-- init.sql - Database initialization script
-- This script runs automatically on first startup

-- Create additional databases if needed
CREATE DATABASE IF NOT EXISTS app_production;
CREATE DATABASE IF NOT EXISTS app_test;

-- Switch to the main database
USE mydb;

-- Create a sample table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create another table with foreign key relationship
CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data (optional)
INSERT INTO users (username, email) VALUES
    ('admin', 'admin@example.com'),
    ('testuser', 'test@example.com')
ON DUPLICATE KEY UPDATE username=username;

Step 4: (Optional) Create Custom MySQL Configuration

For production deployments, you may want to create a custom my.cnf file with performance tuning settings. Create a file named my.cnf:

# my.cnf - Custom MySQL Configuration

[mysqld]
# Performance tuning
max_connections = 200
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Character set configuration
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Security settings
# Disable local file loading for security
local_infile = 0

# Logging
# log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Binary logging for replication (optional)
# server-id = 1
# log_bin = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds = 604800

If you create custom configuration or initialization files, update your Dockerfile:

FROM mysql:8.4

# Copy custom configuration and initialization scripts
COPY ./my.cnf /etc/mysql/conf.d/custom.cnf
COPY ./init.sql /docker-entrypoint-initdb.d/

ENV MYSQL_DATABASE=mydb
ENV MYSQL_USER=myuser
ENV MYSQL_PASSWORD=mypassword
ENV MYSQL_ROOT_PASSWORD=rootpassword

EXPOSE 3306

Step 5: Test Locally (Optional)

Before deploying to Klutch.sh, you can test your MySQL setup locally:

# Build the Docker image
docker build -t my-mysql .

# Run the container
docker run -d \
  --name mysql-test \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=supersecret \
  my-mysql

# Wait a moment for MySQL to start, then connect
docker exec -it mysql-test mysql -uroot -psupersecret

# Inside MySQL shell:
# SHOW DATABASES;
# USE mydb;
# SHOW TABLES;
# SELECT * FROM users;
# EXIT;

# Stop and remove the test container when done
docker stop mysql-test
docker rm mysql-test

Step 6: Push to GitHub

Commit your Dockerfile and any configuration files to your GitHub repository:

git add Dockerfile init.sql my.cnf
git commit -m "Add MySQL Dockerfile and configuration"
git remote add origin https://github.com/yourusername/mysql-klutch.git
git push -u origin main

Connecting to MySQL

Once deployed, you can connect to your MySQL database from any application using a connection string. Since Klutch.sh routes TCP traffic through port 8000, use the following format:

Connection String

mysql://myuser:mypassword@example-app.klutch.sh:8000/mydb

Replace:

myuser with your database username
mypassword with your database password
example-app.klutch.sh with your actual Klutch.sh app URL
mydb with your database name

Example Connection Code

Node.js (using mysql2):

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

async function connectMySQL() {
  const connection = await mysql.createConnection({
    host: 'example-app.klutch.sh',
    port: 8000,
    user: 'myuser',
    password: 'mypassword',
    database: 'mydb'
  });

  const [rows] = await connection.execute('SELECT * FROM users');
  console.log('Users:', rows);

  await connection.end();
}

connectMySQL();

Python (using mysql-connector-python):

import mysql.connector

# Connect to MySQL
conn = mysql.connector.connect(
    host="example-app.klutch.sh",
    port=8000,
    user="myuser",
    password="mypassword",
    database="mydb"
)

cursor = conn.cursor()

# Execute a query
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

for row in rows:
    print(row)

cursor.close()
conn.close()

Python (using SQLAlchemy):

from sqlalchemy import create_engine, text

# Create connection string
DATABASE_URL = "mysql+mysqlconnector://myuser:mypassword@example-app.klutch.sh:8000/mydb"
engine = create_engine(DATABASE_URL)

# Execute a query
with engine.connect() as connection:
    result = connection.execute(text("SELECT * FROM users"))
    for row in result:
        print(row)

PHP (using PDO):

<?php
try {
    $pdo = new PDO(
        'mysql:host=example-app.klutch.sh;port=8000;dbname=mydb',
        'myuser',
        'mypassword',
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );

    $stmt = $pdo->query('SELECT * FROM users');
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($users as $user) {
        echo $user['username'] . "\n";
    }
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
?>

Java (using JDBC):

import java.sql.*;

public class MySQLConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://example-app.klutch.sh:8000/mydb";
        String user = "myuser";
        String password = "mypassword";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users");

            while (rs.next()) {
                System.out.println("Username: " + rs.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Deploying to Klutch.sh

Now that your MySQL project is ready and pushed to GitHub, follow these steps to deploy it on Klutch.sh with persistent storage.

Deployment Steps

Log in to Klutch.sh

Navigate to klutch.sh/app and sign in to your account.
Create a New Project

Go to Create Project and give your project a meaningful name (e.g., “MySQL Database”).
Create a New App

Navigate to Create App and configure the following settings:
Select Your Repository
- Choose GitHub as your Git source
- Select the repository containing your Dockerfile
- Choose the branch you want to deploy (usually main or master)
Configure Traffic Type
- Traffic Type: Select TCP (MySQL requires TCP traffic for database connections)
- Internal Port: Set to 3306 (the default MySQL port that your container listens on)
Set Environment Variables

Add the following environment variables for your MySQL configuration:
- MYSQL_ROOT_PASSWORD: A strong password for the root user (required - use a secure password generator)
- MYSQL_DATABASE: The name of your default database (e.g., mydb)
- MYSQL_USER: A non-root database username (e.g., myuser)
- MYSQL_PASSWORD: A strong password for your database user
Security Note: Always use strong, unique passwords for production databases. Never use default passwords or commit passwords to your repository.
Attach a Persistent Volume

This is critical for ensuring your database data persists across deployments and restarts:
- In the Volumes section, click “Add Volume”
- Mount Path: Enter /var/lib/mysql (this is where MySQL stores all database files)
- Size: Choose an appropriate size based on your expected data volume (e.g., 10GB, 20GB, 50GB)
Important: MySQL requires persistent storage to maintain your data between container restarts. Without a volume, all data will be lost when the container restarts.
Configure Additional Settings
- Region: Select the region closest to your users for optimal latency
- Compute Resources: Choose CPU and memory based on your workload (minimum 512MB RAM recommended, 1GB+ for production workloads)
- Instances: Start with 1 instance (MySQL single instances don’t benefit from horizontal scaling without replication setup)
Deploy Your Database

Click “Create” to start the deployment. Klutch.sh will:
- Automatically detect your Dockerfile in the repository root
- Build the Docker image
- Attach the persistent volume
- Start your MySQL container
- Execute any initialization scripts in /docker-entrypoint-initdb.d/
- Assign a URL for external connections
Access Your Database

Once deployment is complete, you’ll receive a URL like example-app.klutch.sh. You can connect to your MySQL database using this URL on port 8000:
```
mysql://myuser:mypassword@example-app.klutch.sh:8000/mydb
```

Production Best Practices

Security Recommendations

Strong Passwords: Use complex, randomly generated passwords for both root and application users
Least Privilege: Create database users with only the permissions they need
Environment Variables: Store all credentials as environment variables in Klutch.sh, never in code or Dockerfiles
Regular Updates: Keep your MySQL version up to date with security patches
Disable Remote Root: In production, consider disabling remote root access
SSL/TLS: For sensitive data, configure SSL/TLS encryption for database connections

Performance Optimization

InnoDB Buffer Pool: Allocate 70-80% of available RAM to innodb_buffer_pool_size for best performance
Query Optimization: Use EXPLAIN to analyze and optimize slow queries
Proper Indexing: Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses
Connection Pooling: Use connection pooling in your application to manage database connections efficiently
Regular Maintenance: Run OPTIMIZE TABLE periodically to defragment tables and reclaim space

Backup Strategy

Implement a comprehensive backup strategy:

# Example backup script (run from application container or separate backup job)
mysqldump -h example-app.klutch.sh -P 8000 -u myuser -p mydb > backup.sql

# Restore from backup
mysql -h example-app.klutch.sh -P 8000 -u myuser -p mydb < backup.sql

Consider:

Daily automated backups
Multiple backup retention periods (daily, weekly, monthly)
Offsite backup storage
Regular restore testing

Monitoring

Monitor your MySQL database for:

Query performance and slow queries
Connection count and connection errors
Disk usage and I/O patterns
CPU and memory utilization
Replication lag (if using replication)
Table sizes and growth rates
InnoDB buffer pool hit rate

Troubleshooting

Cannot Connect to Database

Verify that you’re using the correct connection string with port 8000
Ensure your environment variables (username, password) are set correctly in Klutch.sh
Check that the internal port is set to 3306 in your app configuration
Verify that TCP traffic is selected

Database Not Persisting Data

Verify that the persistent volume is correctly attached at /var/lib/mysql
Check that the volume has sufficient space allocated
Ensure initialization scripts ran successfully on first startup

Performance Issues

Review slow query log to identify problematic queries
Check if innodb_buffer_pool_size is properly configured
Ensure proper indexes exist on frequently queried columns
Consider increasing compute resources (CPU/memory)
Monitor disk I/O performance

Out of Disk Space

Check volume usage and increase size if needed
Clean up old binary logs if binary logging is enabled
Remove unnecessary databases or tables
Run OPTIMIZE TABLE to reclaim space from deleted rows

Authentication Errors

Verify that MYSQL_ROOT_PASSWORD is set in environment variables
Ensure username and password match what’s configured
Check that the user has been created (initialization scripts ran successfully)
Try connecting with root user first to verify basic connectivity

Advanced Configuration

Setting Up Replication (Master-Slave)

For high availability and read scaling, you can set up MySQL replication. Add to your my.cnf:

# Master configuration
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydb

Enabling SSL/TLS

For encrypted connections, generate SSL certificates and add to your configuration:

[mysqld]
require_secure_transport = ON
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Additional Resources

Conclusion

Deploying MySQL to Klutch.sh with Docker provides a robust, reliable relational database solution with persistent storage and production-ready configuration. By following this guide, you’ve set up a MySQL database that’s secure, performant, and ready to power your applications with proven relational data management capabilities.