Fli
08-31-2017, 02:55 PM
I faced unpleasant issue where i have been restoring cPanel full backups on the new cPanel server (with MySQL 5.6) and the process was resetting mysql user passwords because the password hash was not of enough good quality.
Background:
The reason MySQL user passwords are being changed is due to the change from MySQL 5.5, which used an older method for storing passwords, using a less secure shorter hash. These MySQL old-style passwords are no longer supported by MySQL, and must be updated to conform to MySQL 5.6's requirements. Do note that since the passwords are encrypted, it's not possible to simply rehash them unfortunately. They need to be reset with a new password.
So the result is that one would have to manually reset mysql password for all mysql users and manually update all .php configuration files where password is defined. That is almost unacceptable when there is too many accounts and would cause alot of downtime untill job is complete.
Workaround?
A) Use older MySQL 5.5 during cPanel installation
To get MySQL 5.5, or any other tweaks you may need to your server, you'd need to set that upon installation of cPanel itself. We have some documentation that covers how to do that - https://documentation.cpanel.net/display/ALD/Installation+Guide+-+Customize+Your+Installation#InstallationGuide-CustomizeYourInstallation-Configurationsettings
While the above is not a comprehensive guide, there is some further infromation at https://documentation.cpanel.net/display/ALD/The+cpanel.config+File
To ensure MySQL 5.5 is installed upon installation of cPanel, simply add:
mysql-version=5.5
to /root/cpanel_profile/cpanel.config
I imagine this is not ideal either, but that is the method of getting MySQL 5.5 (or any other custom tweaks).
B) before backing up/transfering cpanel accounts backups to a server with new MySQL version, change all weak/legacy passwords
an idea how to simplify the process is in this topic (https://internetlifeforum.com/control-panels/10116-how-speed-up-manual-process-resetting-all-mysql-passwords-whm-cpanel-server/).
C) i read there is some workaround with skip-secure-auth, i do not know if works, likely not
You'll want to add:
skip-secure-auth
to the [mysqld] section of the /etc/my.cnf file, followed by a restart of MySQL using the script /scripts/restartsrv_mysql
In each of the cPanel user backups, there should be a mysql.sql file. These files are what you'll want to import into MySQL.
passwords would need to be updated to the new format, likely best done via the cPanel interface:
https://documentation.cpanel.net/display/ALD/Change+MySQL+User+Password
---
Background:
The reason MySQL user passwords are being changed is due to the change from MySQL 5.5, which used an older method for storing passwords, using a less secure shorter hash. These MySQL old-style passwords are no longer supported by MySQL, and must be updated to conform to MySQL 5.6's requirements. Do note that since the passwords are encrypted, it's not possible to simply rehash them unfortunately. They need to be reset with a new password.
So the result is that one would have to manually reset mysql password for all mysql users and manually update all .php configuration files where password is defined. That is almost unacceptable when there is too many accounts and would cause alot of downtime untill job is complete.
Workaround?
A) Use older MySQL 5.5 during cPanel installation
To get MySQL 5.5, or any other tweaks you may need to your server, you'd need to set that upon installation of cPanel itself. We have some documentation that covers how to do that - https://documentation.cpanel.net/display/ALD/Installation+Guide+-+Customize+Your+Installation#InstallationGuide-CustomizeYourInstallation-Configurationsettings
While the above is not a comprehensive guide, there is some further infromation at https://documentation.cpanel.net/display/ALD/The+cpanel.config+File
To ensure MySQL 5.5 is installed upon installation of cPanel, simply add:
mysql-version=5.5
to /root/cpanel_profile/cpanel.config
I imagine this is not ideal either, but that is the method of getting MySQL 5.5 (or any other custom tweaks).
B) before backing up/transfering cpanel accounts backups to a server with new MySQL version, change all weak/legacy passwords
an idea how to simplify the process is in this topic (https://internetlifeforum.com/control-panels/10116-how-speed-up-manual-process-resetting-all-mysql-passwords-whm-cpanel-server/).
C) i read there is some workaround with skip-secure-auth, i do not know if works, likely not
You'll want to add:
skip-secure-auth
to the [mysqld] section of the /etc/my.cnf file, followed by a restart of MySQL using the script /scripts/restartsrv_mysql
In each of the cPanel user backups, there should be a mysql.sql file. These files are what you'll want to import into MySQL.
passwords would need to be updated to the new format, likely best done via the cPanel interface:
https://documentation.cpanel.net/display/ALD/Change+MySQL+User+Password
---