The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL/MariaDB Upgrade

Discussion in 'Database Discussions' started by rpvw, May 4, 2016.

  1. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    120
    Likes Received:
    34
    Trophy Points:
    28
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    WHM 56.0.13


    I started to look at this knowing that I had all the database users on pre-4.1-style MySQL passwords and wholly prepared to update all the passwords to longer MySQL password hashes.

    I was somewhat disconcerted to be presented with 75 users that were all prefixed cpses_ and which I understand are left-over temporary users.

    I have checked that they do not exist in the /var/cpanel/databases folder but they are present in the 'user' table of the mysql database.

    Is it safe to delete all the cpses_****** users from this table ? and what might need to be done after deleting them ?

    Is there any alternative clean up script that would have the same effect ?

    Thanks in advance
     
  2. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    120
    Likes Received:
    34
    Trophy Points:
    28
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    Well I can't think that this question was too difficult to answer, so perhaps it was too silly a question to deserve wasting time on.
    Nevertheless, for those of you that may find yourself in the same position, here is what I did to resolve the issue.

    Before you start, you need to know that any advice here could easily lead to divorce, drink all your whiskey or empty your bank account, so proceed at your own risk and TAKE A BACKUP (now just for fun, take another backup in a different format and keep it somewhere else!)

    Whilst on the subject of backups, the MySQL/MariaDB Upgrade routine glibly asks you to take backups without ever indicating how. Since this is fundamental to saving our sanity if something goes wrong, here are a couple of suggestions:

    In a root shell, cd into the root folder and run
    Code:
    mysqldump --all-databases > all_databases.sql
    This will backup all the MySQL databases you have on the server into one big .sql file in the root folder. If you want to backup all the databases to gz compressed individual files you can use
    Code:
    for I in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $I | gzip > "$I.sql.gz"; done
    Now that we have our backups – open up your SQL Services > phpMyAdmin in WHM and navigate to the mysql database and to the user table.

    You should see quite clearly in the Password column the entries that have a short hash that need attention, and any redundant users that are prefixed cpses_

    The way I fixed them that worked for me and seemed the safest was to reset to the same password any user that required it – this meant re-entering the users cPanel passwords or database password (available for the most part from the config files of the programs that use them) If you don't have records of password for users, you may have to reset their passwords as necessary.

    I found some users with short hashes from system processes like munin – all I did here was to un-install and re-install munin which reset the password to a long hash and then I un-installed munin again which still leaves the munin user in the users database but at least in the correct hash length.

    Some users like RvsiteBuilder that I had un-installed months ago, I was forced to delete, along with all the users prefixed cpses_

    After doing all the above – I ran the MySQL upgrade script ( Software > MySQL/MariaDB Upgrade) again and got no more critical errors – if you get any left over critical errors – address them before moving onto the next step of actually performing the upgrade and be sure to read and follow any instructions carefully.
     
  3. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    653
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Hello,

    Thank you for taking the time to reply back with the steps you took to address the issue. To answer your original question, yes, those are temporary MySQL users so it's acceptable to remove them. Before upgrading MySQL, you can also determine whether any users have old-style passwords by running the following query from the MySQL command prompt:

    Code:
    mysql> SELECT user, Length(Password) FROM mysql.user;
    Users who return a 16-character length use the pre-4.1-style MySQL password.

    Thank you.
     
Loading...

Share This Page