Upgrading MySQL Without Breaking PHP
- Written by Vanessa Vasile
- Published in Hacks, Howto, MySQL
- 5 Comments
Update: Please be advised that this post does not apply to you if you’re running EasyApache 4, because EA4 uses PHP RPMs and MySQL client compatibility libraries. Please make sure you are following the instructions below that pertain to your system!
There’s a dilemma among system administrators of high-traffic servers when it comes to doing updates – downtime. Most upgrades on a Linux/cPanel server can be done without interruption, but when it comes to upgrading MySQL, some get quite a surprise.
Now, if you’re using EasyApache 3, which is obsolete as of cPanel 60, upgrading the release within a minor MySQL version (like 5.0.81 to 5.0.85) is pretty seamless. However, going from 5.0 to 5.1, or 5.1 to 5.5 is just as easy, but requires a little extra TLC to prevent PHP-driven websites from going down. Many administrators are unaware that upgrading MySQL on a cPanel server will break PHP. This is because PHP is compiled with the system MySQL libraries, and when MySQL is upgraded to a different minor version, the old libraries for that version are removed and replaced with the new ones. If PHP is compiled with MySQL support, it can’t execute because the libraries it’s looking for are now gone. The only way to properly fix this is to recompile PHP via EasyApache or downgrade back the version of MySQL you were running in the first place.
If you’re on EasyApache 4:
Upgrading is easy as you don’t need to mess with PHP at all. You can either use the MySQL Upgrader in WHM, or follow the below steps to perform the upgrade via CLI:
1. Set the mysql-version variable in /var/cpanel/cpanel.config to the version you are trying to install, ie 5.6, 10.0, or 10.1. Note that 10.0 and 10.1 imply MariaDB.
2. Set the RPM targets to tell cPanel not to install the versions you don’t want. If you have MySQL 5.5 installed on your server already, run this command (note that it’s all one line)
/scripts/update_local_rpm_versions –edit target_settings.MySQL55 uninstalled
3. Run /scripts/check_cpanel_rpms –force, which should install the version of MySQL you specified earlier. If you previously disabled that version, make sure it’s enabled again. IE, for MySQL 5.6:
/scripts/update_local_rpm_versions –edit target_settings.MySQL56 installed
Then run /scripts/check_cpanel_rpms –force again which should remove the currently-installed MySQL version and install the one you specified.
For more instructions, go here (or here if you’re trying to convert to MariaDB)
If you’re on EasyApache 3:
We’ve devised a simple way to run a MySQL upgrade without breaking PHP on your server. You will still need to run EasyApache to fully complete the upgrades, but this process will keep your websites running in the meantime. We’ve formulated the concept into an easy-to-read bash script that will do all the work for you.
You can download the script from our Github repo.
Update 2/14/14: Happy Valentine’s Day! I updated the script to work on cPanel 11.36+ and MySQL 5.6. I also added a –runeasy option to automatically invoke EasyApache after the upgrade.
Update 12/19/16: Support for MariaDB 10+ has been added
This is a nice post.
Works like a charm, it has exactly the effect that you want.. upgraded from MySQL 5.0 to 5.5.. no downtime at all!
Pingback: Upgrading or Downgrading MySQL :: The cPanel Admin
Is upgrading MySQL through WHM doable for noobs? (meaning by that, for ppl without any knowledge of servers and without the need for root access?)
Because I read in WHM “MySQL Upgrade (Step 1 of 7)” but I can’t find anywhere which/what these steps are and what knowledge I need to even respond on those (for me) unknown steps.
If you’re not sure, it’s best to have an experienced administrator do this for you. The script in this post does automate the process but the WHM interface is more intuitive. So if you’re having issues with the interface, you should probably have someone else work on this.
EasyApache knocks out all our sites while it runs.
And our sites don’t run until its rebuilt.
So, we’re essentially dead in the water.
Merry Christmas, I’ve now learned how incredibly dangerous cPanel updates can be.