cPanel MySQL Tuning
The default configuration for MySQL provided by cPanel is pretty barren. /etc/my.cnf
[mysqld] skip-innodbThats it. One line. Its rubbish.
Here's mine to help you along the way. Its got some decent logging set, some decent cache and memory options for a shared server set and some helpful security settings enabled.
[mysqld]
max_connections = 200
bind-address = 127.0.0.1
safe-show-database
skip-locking
skip-innodb
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
interactive_timeout=100
# Reduced wait_timeout to prevent idle clients holding connections.
wait_timeout=15
connect_timeout=10
# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512
# Reduced it to 32 to prevent memory hogging.
thread_cache=32
# Reduced it by checking current size of *.MYI files.
key_buffer=128M
thread_concurrency=1
log_error = /var/log/mysql/error.log
# log slow queries is a must.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
[mysqlhotcopy]
interactive-timeoutAdvanced Policy Firewall (for cPanel)
More retro software that has been with us for years - APF - Advanced Policy Firewall. A brilliantly robust and fool proof Linux script that acts as a wrapper for iptables.
Here's how I configure it for all the WHM/cPanel boxes I commission, although you can configure it for non-cPanel servers, just adjust your ports accordingly.
Installing mytop on cpanel
MyTop is one of the best MySQL monitoring tools available, written by a MySQL guru, Jeremy Zawodny.
Its vital for monitoring your MySQL health, especially in a shared hosting environment.
mod_geoip Revisted including stopping spam in phpBB 2
I've recently moved a client's forum onto a new cPanel server, previously it was on a home-brew Ubuntu server.
So I had to install mod_geoip onto cPanel in CentOS. It wasn't as bad as I was expecting.
First off, we need to download the GeoIP libraries.