Linux VPS cPanel MySQL Configuration Tutorial: Complete Performance and Security Setup Guide for 2026

Prerequisites and Initial Setup

You'll need root access and SSH connectivity to configure MySQL on your cPanel VPS. This guide covers Rocky Linux 9, AlmaLinux 9, and Ubuntu 22.04/24.04 systems running cPanel/WHM version 118 or later.

Check your current MySQL version and status:

mysql --version
sudo systemctl status mysql

Most cPanel installations run MySQL 8.0 or MariaDB 10.6+. Configuration principles stay consistent across versions.

Syntax might differ slightly between versions.

WHM Database Configuration Interface

Log into WHM as root and go to "SQL Services" > "MySQL Configuration". This interface lets you modify settings safely without risking manual file edits.

Key settings to adjust:

• max_connections - 150 for small sites, 300+ for high traffic
• innodb_buffer_pool_size - 60-70% of available RAM
• query_cache_size - 128MB for most setups (MySQL 5.7 only)
• tmp_table_size - Match max_heap_table_size, typically 64MB

Click "Save" after each change. WHM restarts MySQL services automatically and validates your syntax.

HostMyCode managed VPS hosting customers get these optimizations handled during initial server setup by our support team.

Memory and Performance Optimization

Calculate your InnoDB buffer pool size based on available RAM. A 4GB VPS should allocate roughly 2.5GB to MySQL:

# Check available memory
free -h

# Set in WHM or /etc/my.cnf
innodb_buffer_pool_size = 2560M
innodb_buffer_pool_instances = 4

Configure connection handling for concurrent users:

max_connections = 200
max_connect_errors = 999999
connect_timeout = 60
wait_timeout = 300

These settings prevent connection exhaustion while maintaining reasonable timeouts. Monitor actual connection usage through cPanel's "Resource Usage" interface.

Set temporary table limits to avoid disk-based operations:

tmp_table_size = 128M
max_heap_table_size = 128M

Security Hardening Configuration

Enable binary logging for point-in-time recovery and replication readiness:

log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

Configure SSL connections for encrypted database traffic:

ssl-ca = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/server-cert.pem
ssl-key = /var/lib/mysql/server-key.pem

Disable dangerous functions and limit resource usage:

local_infile = OFF
skip_show_database
max_user_connections = 50

Set strict SQL mode for data integrity:

sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Database User Management Through cPanel

Create application-specific database users with minimal required privileges. Go to "MySQL Databases" in cPanel and resist granting ALL PRIVILEGES unnecessarily.

WordPress sites need a dedicated user with these permissions:

• SELECT, INSERT, UPDATE, DELETE - Basic CRUD operations
• CREATE, DROP, ALTER - Schema modifications during updates
• INDEX - Performance optimization
• CREATE TEMPORARY TABLES - WordPress caching

Never use the root MySQL user for web applications. Each website gets its own database and user combination.

Set password policies in WHM under "SQL Services" > "MySQL Root Password":

validate_password.policy = MEDIUM
validate_password.length = 12
validate_password.mixed_case_count = 1
validate_password.number_count = 2

Backup and Recovery Configuration

Enable automatic backups through WHM's "Backup Configuration". Daily database dumps with 7-day retention work for most scenarios.

Configure point-in-time recovery with binary logs:

log-bin = /var/lib/mysql/mysql-bin
binlog_do_db = database_name
max_binlog_size = 100M

Test your backup restoration monthly. Create a test database and restore from backups to verify integrity.

Critical applications should consider setting up a secondary server with HostMyCode VPS hosting for MySQL replication.

Monitoring and Maintenance Scripts

Set up automated monitoring with MySQL's built-in performance schema:

performance_schema = ON
performance_schema_consumer_events_statements_current = ON
performance_schema_consumer_events_statements_history = ON

Create a monitoring script to check key metrics:

#!/bin/bash
# Save as /root/mysql-monitor.sh

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';"
mysql -e "SHOW PROCESSLIST;" | wc -l

# Check for slow queries
mysql -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';"

Run this script via cron every 15 minutes. Log output for trend analysis.

Enable the slow query log to identify performance bottlenecks:

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON

Linux VPS cPanel MySQL Configuration Integration

Configure cPanel's database features for better user experience. Enable phpMyAdmin access through "Software" > "phpMyAdmin" in individual cPanel accounts.

Set reasonable limits in WHM's "Resource Limits":

• Max databases per account: 10-50 depending on hosting plan
• Max database size: 1-5GB for shared environments
• Connection limits per user: 25-50 concurrent connections

Configure MySQL hostname resolution to prevent DNS delays:

skip_name_resolve = ON

This forces MySQL to use IP addresses instead of DNS lookups. It reduces connection establishment time.

Troubleshooting Common Issues

Database connection errors often stem from exceeded connection limits. Check current connections:

mysql -e "SHOW PROCESSLIST;" | grep -c "Query\|Sleep"

If connections consistently hit your max_connections limit, either increase the limit or investigate slow queries. Slow queries cause connection pools to fill.

Memory-related crashes typically indicate insufficient innodb_buffer_pool_size. Monitor memory usage:

mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "BUFFER POOL"

High "Pages dirty" values suggest insufficient memory allocation or disk I/O bottlenecks.

For disk space issues, check binary log accumulation:

mysql -e "SHOW BINARY LOGS;"

Purge old logs manually if automatic cleanup fails:

mysql -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"

Frequently Asked Questions

How much RAM should I allocate to MySQL on a cPanel VPS?

Allocate 60-70% of total RAM to innodb_buffer_pool_size. A 4GB VPS needs 2.5-2.8GB.

An 8GB system should get 5-5.5GB. Reserve memory for the operating system, web server, and other processes.

Should I use MySQL or MariaDB with cPanel in 2026?

Both work excellently with cPanel. MySQL 8.0 offers better JSON support and performance improvements.

MariaDB provides more storage engines and is often preferred for compatibility. Choose based on your specific application requirements.

How do I enable MySQL remote access through cPanel?

Add remote hosts in cPanel's "Remote MySQL" interface. Specify exact IP addresses or use wildcards carefully.

Always use strong passwords and consider SSL connections for remote database access.

What's the recommended backup frequency for production databases?

Daily full backups work for most applications, with hourly binary log backups for critical systems. Test restoration procedures monthly and store backups offsite.

Enable point-in-time recovery for mission-critical databases.

How can I monitor MySQL performance in cPanel?

Use cPanel's Resource Usage interface for basic metrics. Enable the slow query log and performance schema for detailed analysis.

Consider tools like MySQLTuner for configuration recommendations and regular health checks.