MySQL default collation when an account is created in whm

icandoit

Well-Known Member
Dec 21, 2010
79
1
58
When an account is created the MySQL default collation is not utf8
What to do to make the default mysql collation utf8
Will the change effect any present database?
Will the change be overwrittedn as soon as cpanel is updated?
 

kdean

Well-Known Member
Oct 19, 2012
312
32
28
Orlando, FL
cPanel Access Level
Root Administrator
You can edit your /etc/my.cnf and add:

character-set-server=utf8
collation-server=utf8_general_ci


Then Restart MySQL.

This will not affect any existing databases and won't be affected by cPanel updates.

Newly created databases will have that collation unless the code creating the database / tables explicitly sets it otherwise.
 

kdean

Well-Known Member
Oct 19, 2012
312
32
28
Orlando, FL
cPanel Access Level
Root Administrator
I believe you can only edit it via root SSH or SFTP, not directly in WHM.

It sounds like you may have never edited it before, so I'd really recommend reading up on MySQL optimizations since generally the MySQL variable defaults need adjusting for most servers.
 

icandoit

Well-Known Member
Dec 21, 2010
79
1
58
Thanks a lot in my cn file I have

PHP:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Do I add this at the end of the file?
 

icandoit

Well-Known Member
Dec 21, 2010
79
1
58
Thank you very much for your help. If you have any special link for MySQL optimizations I will be very grateful I will google it also but just in case you know a site
 

kdean

Well-Known Member
Oct 19, 2012
312
32
28
Orlando, FL
cPanel Access Level
Root Administrator
I've basically acquired my information via Google and here over the years so I don't have any one place.

You can start by running:

/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

from your root shell and see what recommendations it has. Then you can google those settings for more information. Settings and syntax are dependent on your version of MySQL, and the current specs for your server (such as available memory), so don't just copy paste everything you find people discussing. Be sure to read up on the setting in the mysql.com manual and other places that discuss it. Note that if something should go awry, you can also remove the setting and restart MySQL again from WHM.