Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

MySQL default collation when an account is created in whm

Discussion in 'Database Discussion' started by icandoit, Mar 25, 2013.

  1. icandoit

    icandoit Well-Known Member

    Joined:
    Dec 21, 2010
    Messages:
    75
    Likes Received:
    0
    Trophy Points:
    56
    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?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #1 icandoit, Mar 25, 2013
  2. kdean

    kdean Well-Known Member

    Joined:
    Oct 19, 2012
    Messages:
    290
    Likes Received:
    21
    Trophy Points:
    18
    Location:
    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.
     
    #2 kdean, Mar 25, 2013
  3. icandoit

    icandoit Well-Known Member

    Joined:
    Dec 21, 2010
    Messages:
    75
    Likes Received:
    0
    Trophy Points:
    56
    Thanks. Can't be done in WHM?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #3 icandoit, Mar 25, 2013
  4. kdean

    kdean Well-Known Member

    Joined:
    Oct 19, 2012
    Messages:
    290
    Likes Received:
    21
    Trophy Points:
    18
    Location:
    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.
     
    #4 kdean, Mar 25, 2013
  5. icandoit

    icandoit Well-Known Member

    Joined:
    Dec 21, 2010
    Messages:
    75
    Likes Received:
    0
    Trophy Points:
    56
    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?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #5 icandoit, Mar 26, 2013
  6. kdean

    kdean Well-Known Member

    Joined:
    Oct 19, 2012
    Messages:
    290
    Likes Received:
    21
    Trophy Points:
    18
    Location:
    Orlando, FL
    cPanel Access Level:
    Root Administrator
    Add it under the [mysqld] but above the [mysqld_safe] section.
     
    #6 kdean, Mar 26, 2013
  7. icandoit

    icandoit Well-Known Member

    Joined:
    Dec 21, 2010
    Messages:
    75
    Likes Received:
    0
    Trophy Points:
    56
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #7 icandoit, Mar 26, 2013
  8. kdean

    kdean Well-Known Member

    Joined:
    Oct 19, 2012
    Messages:
    290
    Likes Received:
    21
    Trophy Points:
    18
    Location:
    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.
     
    #8 kdean, Mar 26, 2013
  9. kdean

    kdean Well-Known Member

    Joined:
    Oct 19, 2012
    Messages:
    290
    Likes Received:
    21
    Trophy Points:
    18
    Location:
    Orlando, FL
    cPanel Access Level:
    Root Administrator
    #9 kdean, Mar 26, 2013
(You must log in or sign up to post here.)
Loading...
Similar Threads - MySQL default collation
  1. PatrickVeenstra

    SOLVED MySQL error when using non default PHP version

    PatrickVeenstra, Sep 26, 2018, in forum: Database Discussion
    Replies:
    5
    Views:
    203
    cPanelMichael
    Sep 30, 2018
  2. mahdy_sharifi

    change mysql default charset

    mahdy_sharifi, Jan 31, 2017, in forum: Database Discussion
    Replies:
    3
    Views:
    2,000
    cPanelMichael
    Feb 8, 2017
  3. greektranslator

    Upgrade MariaDB to 10.3: backup without mysqldump?

    greektranslator, Jan 16, 2019 at 1:57 AM, in forum: Database Discussion
    Replies:
    1
    Views:
    57
    cPanelMichael
    Jan 17, 2019 at 3:51 PM
  4. jimtrouble

    MySQL 200% CPU Load

    jimtrouble, Jan 6, 2019, in forum: Workarounds and Optimization
    Replies:
    4
    Views:
    150
    cPanelMichael
    Jan 9, 2019
  5. Kervol

    Strick Mode Work Around on MYSQL 5.7+

    Kervol, Jan 5, 2019, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    114
    cPanelLauren
    Jan 8, 2019

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice