How to edit MySQL my.cnf file on cPanel

When you have your first dedicated server, cloud or vps box, sometimes you need to tweak the server performance to gain speed for your web applications. One of the most common areas you’ll be forced to tweak is MYSQL Configuration.  Today you’ll learn how to edit MySQL my.cnf file on cPanel.

How can I edit MySQL my.cnf file on cPanel servers?

On cPanel, the my.cnf file can be found at:

/etc/my.cnf

How can I view my.cnf file settings?

Easy, just run cat command against that file:

cat /etc/my.cnf

Another way to view MYSQL variables configuration is using this command:

mysqladmin variables

Output may look similar to this:

[[email protected]:~]cat /etc/my.cnf
[mysqld]
socket="/var/lib/mysql/mysql.sock"
local-infile=0

query_cache_limit=2M
query_cache_size=96M
query_cache_type=1
tmp_table_size=96M
max_heap_table_size=96M

wait_timeout=40
connect_timeout=60
interactive_timeout=20
max_connections=550

max_connect_errors=10
thread_concurrency=8
read_buffer_size=1M
max_allowed_packet=268435456
sort_buffer_size=1M
read_rnd_buffer_size=1M
datadir="/var/lib/mysql"
myisam_sort_buffer_size=64M
thread_cache_size=128
join_buffer_size=24M
innodb_buffer_pool_size=134217728
skip-external-locking
key_buffer_size=200M
default-storage-engine=MyISAM
innodb_file_per_table=1
table_open_cache=19536
open_files_limit=50000

[mysql.server]
user=mysql

[mysqld_safe]
err-log="/var/log/mysqld.log"
pid-file="/var/run/mysqld/mysqld.pid"

Edit MySQL my.cnf file

nano -w /etc/my.cnf

There you’ll see many variable, be careful if you don’t know what to edit. There are many variables that if set incorrectly, may result in MySQL erros from all your databases.

Once done, save the file and restart MySQL to apply changes:

service mysql restart

Further reading about MYSQL and edit MySQL my.cnf file:

About the Author: Martin Keler

Leave a Reply

Your email address will not be published. Required fields are marked *