📘Install MySQL Tuner & Optimise MariaDB on cPanel + CloudLinux (2025)

By /

MySQL Tuner Optimization Guide specifically for cPanel, CloudLinux, and MariaDB (2025):

This guide is intended for MariaDB (used by default in cPanel), running on CloudLinux 8/9 with cPanel 126.x+.
It helps you tune /etc/my.cnf, optimise all databases, and use MySQLTuner effectively.

✅ 1. Backup All Databases

Before making any changes:

rsync -vrplogDtH /var/lib/mysql /home/MYSQL-BACKUP/

Or use:

/scripts/pkgacct username

To create a full cPanel account backup (replace username with the actual cPanel user).

✅ 2. Optimise and Repair All Tables

Run any of the following commands via SSH as root:

mysqlcheck --auto-repair --check --optimize --all-databases

Or run step-by-step:

mysqlcheck -r -A     # Repair
mysqlcheck -c -A     # Check
mysqlcheck -o -A     # Optimise

✅ 3. Enable Slow Query Log (Troubleshooting Bad Queries)

Edit /etc/my.cnf:

nano /etc/my.cnf

Add or update:

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2

Make sure /var/lib/mysql/ is writable by mysql:mysql.

Then restart MariaDB:

/scripts/restartsrv_mysql

✅ 4. Install & Run MySQLTuner

MariaDB in cPanel is fully compatible with MySQLTuner.

cd /root
wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

Let it run for at least 24–48 hours uptime for best analysis.

✅ 5. Tune /etc/my.cnf for Performance

Edit:

nano /etc/my.cnf

Suggested base settings for MariaDB on CloudLinux (2–8GB RAM):

[mysqld]
max_connections         = 200
wait_timeout            = 30
interactive_timeout     = 30

# Query Cache (MariaDB specific)
query_cache_type        = 1
query_cache_limit       = 2M
query_cache_size        = 128M

# Table Cache
table_open_cache        = 2048
table_definition_cache  = 2048

# Buffers
join_buffer_size        = 1M
sort_buffer_size        = 2M
read_buffer_size        = 2M

# InnoDB (important!)
innodb_buffer_pool_size = 512M
innodb_log_file_size    = 128M
innodb_file_per_table   = 1

# Logging
slow_query_log          = 1
slow_query_log_file     = /var/lib/mysql/slow.log

🚨 Do not blindly copy this if your server has <2GB RAM. For low-RAM VPS, reduce values accordingly.

Then restart MySQL again:

/scripts/restartsrv_mysql

✅ 6. Ensure Queries Use Indexes (Manual)

This is application-specific. Make sure:

  • JOINs use indexed columns.
  • Use EXPLAIN SELECT ... in phpMyAdmin or CLI.
  • Avoid SELECT * unnecessarily.

✅ 7. Monitor Performance in cPanel/WHM

WHM → SQL Services → Show MySQL Processes

Or CLI:

mysqladmin proc stat
mysql -e "SHOW FULL PROCESSLIST;"

You can also view the MariaDB error log:

tail -n 50 /var/lib/mysql/$(hostname).err

✅ 8. Optional: CloudLinux LVE Limits (Important for MySQL Stability)

Ensure each user has reasonable resource limits in:

WHM → CloudLinux LVE Manager

Recommended starting point for heavy database users:

  • CPU: 100% (1 core)
  • RAM: 512–1024MB
  • IO: 4–8MB/s
  • nPROC: 50
  • EP: 20+

✅ 9. Regular Maintenance (Suggested Cron)

Add a cron job for automatic regular checks:

@weekly /usr/bin/mysqlcheck -Ao --auto-repair > /dev/null 2>&1

Add this via:

crontab -e

✅ You’re Done!

Your cPanel server on CloudLinux running MariaDB is now better optimised.
Let me know if you’d like advanced tuning with Percona Toolkit, sysbench, or using Imunify360 + MySQL Governor.

Here’s an advanced version of the guide tailored for cPanel + CloudLinux + MariaDB setups using Imunify360 and MySQL Governor:

✅ Advanced MariaDB Optimization on cPanel + CloudLinux (2025)

With Imunify360 & MySQL Governor Integration

This guide builds on basic MySQL/MariaDB tuning and incorporates CloudLinux MySQL Governor for per-user resource control and Imunify360 for security.

🔒 1. Use Imunify360 to Secure MySQL Access

Imunify360 helps protect against database abuse via:

Enable MySQL abuse protection:

Imunify360 monitors and limits MySQL attacks automatically.

Verify it’s active via:

imunify360-agent config show | grep -i mysql

Ensure these are enabled:

imunify360-agent config update '{"MYSQL_ABUSE_PROTECTION": {"enabled": true}}'

Optional: Enable CAPTCHA for suspicious MySQL brute-force attempts:

imunify360-agent config update '{"CAPTCHA_MYSQL": {"enabled": true}}'

Restart Imunify360:

imunify360-agent restart

📊 2. Enable and Configure MySQL Governor (DB Resource Limits per User)

MySQL Governor controls and throttles MySQL usage per user in real time, perfect for shared hosting.

Install MySQL Governor (if not already)

yum groupinstall governor-mysql

Enable via LVE Manager:

  • WHM → LVE Manager → MySQL Governor
  • Select mode: DB, SOFT, or HARD (most shared hosts use SOFT)
  • Click Apply to all users

Or enable manually:

dbgovernor install
db-select-mysql --mysql-version=mariadb104  # or mariadb106/mariadb10X depending on version

Rebuild and apply rules:

dbgovernor-syscall-check
dbgovernor-util --install

Track abusive users in real time:

dbtop

⚙️ 3. Configure /etc/my.cnf for CloudLinux + MariaDB + MySQL Governor

[mysqld]
max_connections         = 300
wait_timeout            = 30
interactive_timeout     = 30

# Query Cache
query_cache_type        = 1
query_cache_size        = 128M

# Cache & Buffer tuning
table_open_cache        = 4096
table_definition_cache  = 4096
join_buffer_size        = 1M
sort_buffer_size        = 2M
read_buffer_size        = 2M

# InnoDB tuning
innodb_buffer_pool_size = 512M
innodb_log_file_size    = 128M
innodb_file_per_table   = 1

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

# For MySQL Governor
userstat = 1

Then restart:

/scripts/restartsrv_mysql

📈 4. Run MySQLTuner Regularly

cd /root
wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

Helps identify if settings like query_cache_size, tmp_table_size, or innodb_buffer_pool_size need adjustment.

🧠 5. Suggested Cron Jobs

Weekly repair & optimize:

@weekly /usr/bin/mysqlcheck -Ao --auto-repair > /dev/null 2>&1

Weekly MySQLTuner report emailed to root:

@weekly /root/mysqltuner.pl | mail -s "MySQL Tuner Report" root

📋 6. Optional: Monitor Logs for Abuse

MariaDB slow queries:

tail -n 100 /var/lib/mysql/slow.log

Governor logs:

tail -n 100 /var/log/dbgovernor/mysql.log

Imunify360 MySQL abuse log:

cat /var/log/imunify360/mysql-abuse.log

✅ Summary

FeatureTool Used
Per-user MySQL throttlingCloudLinux MySQL Governor
MySQL abuse protectionImunify360
Query tuningMySQLTuner
Logging & Analysisslow.log, dbtop, Imunify logs
Weekly repair/optimizemysqlcheck + Cron
Resource isolationLVE + dbgovernor
Scroll to Top