CloudPloy

MySQL Hosting on CloudPloy

CloudPloy installs MySQL 8.0 (or MariaDB 10.11) directly on your server and manages it alongside your PHP applications. The database runs on the same server as your apps unless you configure a dedicated database server. CloudPloy handles the installation, creates databases and users per application, manages credentials in environment variables, and provides backup configuration. This guide covers how MySQL works in CloudPloy's architecture and how to configure it for production workloads.

How MySQL is Deployed

When you create a new application in CloudPloy and select MySQL as the database, CloudPloy:

  1. Installs MySQL 8.0 on the server if not already present
  2. Creates a dedicated database and user for the application
  3. Generates a strong random password for the database user
  4. Stores the connection string as a DATABASE_URL environment variable
  5. Configures MySQL to listen on 127.0.0.1 (not exposed to the public internet by default)

Your application connects to MySQL using the DATABASE_URL environment variable. The connection string uses the server's internal IP address because apps run inside Docker containers while MySQL runs directly on the host:

# Example DATABASE_URL format
mysql://appuser:generatedpassword@172.17.0.1:3306/appdb?serverVersion=8.0

MySQL vs MariaDB

CloudPloy supports both MySQL 8.0 and MariaDB 10.11. The choice depends on your application's requirements:

Feature MySQL 8.0 MariaDB 10.11
WordPress compatibility Excellent Excellent (drop-in replacement)
Laravel compatibility Excellent Excellent
JSON column support Full JSON type JSON alias for LONGTEXT (10.11)
Window functions Yes Yes
Galera Cluster support Via Group Replication Native Galera support
Default storage engine InnoDB InnoDB (Aria for internal tables)

For most WordPress, Laravel, and Symfony applications, either database is suitable. MySQL 8.0 is the safer default for strict SQL mode compliance and JSON column behavior.

Connection Configuration

Laravel

# .env.example (committed - no secrets)
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=laravel
DB_CHARSET=utf8mb4
DB_COLLATION=utf8mb4_unicode_ci

In Laravel, the actual credentials come from DATABASE_URL set by CloudPloy. Alternatively, set individual DB_HOST, DB_DATABASE, DB_USERNAME, and DB_PASSWORD variables in the CloudPloy dashboard.

Symfony / Doctrine

# DATABASE_URL format for Doctrine
DATABASE_URL="mysql://appuser:password@172.17.0.1:3306/appdb?serverVersion=8.0&charset=utf8mb4"

The serverVersion parameter is important for Doctrine - it affects which SQL features Doctrine will use in queries. Set it to match your actual MySQL version.

WordPress

CloudPloy configures wp-config.php automatically for WordPress installations. The database credentials are injected via environment variables:

define('DB_NAME', getenv('WORDPRESS_DB_NAME'));
define('DB_USER', getenv('WORDPRESS_DB_USER'));
define('DB_PASSWORD', getenv('WORDPRESS_DB_PASSWORD'));
define('DB_HOST', getenv('WORDPRESS_DB_HOST'));
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', 'utf8mb4_unicode_ci');

Performance Configuration

CloudPloy tunes MySQL configuration based on your server's available memory. The key parameters and their recommended values:

Parameter 4GB Server 8GB Server 16GB Server Purpose
innodb_buffer_pool_size 1G 4G 8G Main InnoDB cache (most important)
innodb_buffer_pool_instances 1 4 8 Parallelism for buffer pool
query_cache_size 0 0 0 Disabled (deprecated in MySQL 8)
max_connections 150 300 500 Max simultaneous connections
innodb_log_file_size 256M 512M 1G Redo log size (write performance)

Set innodb_buffer_pool_size to 70-80% of available RAM minus what the OS and other processes need. On a 4GB server with WordPress and Nginx running, 1GB for the buffer pool is a safe starting point.

Slow Query Log

Enable the slow query log to identify queries that need optimization. CloudPloy can enable this from the dashboard, or configure it manually:

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_limit = 100

A threshold of 1 second catches obviously slow queries. Reduce to 0.5 seconds for high-traffic applications. The log_queries_not_using_indexes option logs queries doing full table scans even if they complete quickly - this is the most common source of database performance problems.

Analyzing Slow Queries

# Summary of slow queries sorted by total time
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# Show full slow query log for a specific pattern
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# Use EXPLAIN to analyze a specific query
EXPLAIN SELECT * FROM wp_posts WHERE post_status = 'publish' ORDER BY post_date DESC LIMIT 10;

Automated Backups

CloudPloy provides daily automated backups of all MySQL databases. Backups are stored in compressed format and retained according to your plan's retention policy. To verify backups are running and view backup history, check the Backups section in your server dashboard.

Manual Backup

# Backup a single database
mysqldump -u root -p --single-transaction --routines --triggers dbname | gzip > backup.sql.gz

# Backup all databases
mysqldump -u root -p --all-databases --single-transaction | gzip > all-databases.sql.gz

# Restore from backup
gunzip < backup.sql.gz | mysql -u root -p dbname

The --single-transaction flag creates a consistent snapshot of InnoDB tables without locking them during the backup. This is essential for backing up live production databases without downtime.

Automated Backup Script

#!/bin/bash
# /etc/cron.daily/mysql-backup
BACKUP_DIR="/var/backups/mysql"
RETENTION_DAYS=7
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

# Backup each database separately
mysql -u root -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|sys)" | while read db; do
  mysqldump -u root --single-transaction --routines "$db" | \
    gzip > "$BACKUP_DIR/${db}_${DATE}.sql.gz"
done

# Remove old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

Remote Access

By default, MySQL on CloudPloy servers is not accessible from the public internet. This is the correct security configuration for most applications. If you need to connect from a remote client (your local machine, a remote analytics tool, etc.), use an SSH tunnel instead of opening MySQL to the internet:

# Create an SSH tunnel on local port 3307 to remote MySQL port 3306
ssh -L 3307:127.0.0.1:3306 your-user@your-server-ip -N

# Then connect your local MySQL client to localhost:3307
mysql -h 127.0.0.1 -P 3307 -u appuser -p appdb

To configure your database GUI (TablePlus, DBeaver, Sequel Ace) to use the SSH tunnel, set the SSH host to your server's IP address and the MySQL host to 127.0.0.1.

If you must allow direct remote access (for a managed database client, BI tool, or read replica), restrict access to specific IP addresses and never open port 3306 to 0.0.0.0/0:

# Create a user restricted to a specific IP
CREATE USER 'analytics'@'10.0.1.50' IDENTIFIED BY 'strong_password';
GRANT SELECT ON appdb.* TO 'analytics'@'10.0.1.50';
FLUSH PRIVILEGES;

Index Optimization

Proper indexing is the most impactful database optimization for application performance. Common indexing patterns for PHP applications:

Composite Indexes for WHERE and ORDER BY

-- WordPress: Optimize post listing queries
ALTER TABLE wp_posts ADD INDEX idx_status_date (post_status, post_date);
ALTER TABLE wp_posts ADD INDEX idx_type_status (post_type, post_status);

-- Laravel: Index foreign keys and status columns
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

-- Check existing indexes
SHOW INDEX FROM wp_posts;

Find Missing Indexes

-- Find tables with full table scans
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.TABLE_ROWS
FROM information_schema.TABLES t
LEFT JOIN information_schema.STATISTICS s ON t.TABLE_SCHEMA = s.TABLE_SCHEMA AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND s.TABLE_NAME IS NULL
AND t.TABLE_ROWS > 1000
ORDER BY t.TABLE_ROWS DESC;

Character Set and Collation

Always use utf8mb4 character set with utf8mb4_unicode_ci collation. MySQL's utf8 charset is actually 3-byte UTF-8 and cannot store emoji or certain CJK characters. Set this in your MySQL configuration and create all databases with utf8mb4:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect = 'SET NAMES utf8mb4'

[client]
default-character-set = utf8mb4
-- Create database with correct charset
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Fix existing database
ALTER DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Fix existing tables
ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Read Replicas

For high-traffic applications where read queries dominate, add a MySQL read replica on a second server. The primary server handles writes; the replica handles reads. Set up replication between CloudPloy servers:

# On the primary server - enable binary logging
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

# Create replication user
CREATE USER 'replica'@'replica-server-ip' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'replica-server-ip';
FLUSH PRIVILEGES;
# On the replica server
[mysqld]
server-id = 2
relay-log = relay-log
read_only = ON

# Start replication
CHANGE MASTER TO
  MASTER_HOST='primary-server-ip',
  MASTER_USER='replica',
  MASTER_PASSWORD='replication_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=0;

START SLAVE;
SHOW SLAVE STATUS\G

Configure your application to use the replica for read queries. In Laravel, configure the read and write database connections in config/database.php.

Common Issues

Problem Cause Fix
Too many connections Connection pool too large or leaking connections Reduce pool size; check for unclosed connections
Slow queries on large tables Missing index Run EXPLAIN on slow queries; add appropriate indexes
Emoji not storing correctly Table using 3-byte utf8 charset Convert table to utf8mb4
Out of disk space Binary logs not rotating Set expire_logs_days=7 in my.cnf
Connection refused from container MySQL bound to 127.0.0.1 only Use server's Docker bridge IP (172.17.0.1) as host
InnoDB buffer pool too small Default config not tuned for server size Set innodb_buffer_pool_size to 70% of available RAM