Upgrading or Downgrading MySQL

Note:  These instructions are intended for versions 11.34 and earlier.
You can easily change the major version of MySQL running on your server, keeping in mind that the actual version will be dependent on what cPanel has released in their repository. You may notice that in WHM > Tweak Settings, where MySQL upgrades and downgrades are usually done, you can no longer downgrade to versions under 5.1, nor (at the time of this writing) can you choose MySQL 5.5. Luckily cPanel gives you a way to get around this.


To change the MySQL version, edit the file /var/cpanel/cpanel.config and look for this line

mysql-version

Then change the version number to the major version that you want to upgrade to. For instance, 5.1, 5.5.

Then save the file. To perform the upgrade, you can run either of the following scripts:

/usr/local/cpanel/whostmgr/bin/whostmgr2 –updatetweaksettings

/usr/local/cpanel/scripts/check_cpanel_rpms –fix

Then confirm the new version with the mysql -V command. In most cases, if you upgrade/downgrade between major versions you’ll ‘break’ PHP if PHP is compiled with MySQL support. You may then have to re-run EasyApache to recompile PHP, which will automatically compile with the new libraries. You’ll know if this is necessary when you run the php -m command and get an error like this:

php: /usr/lib64/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by php)

Word to the wise (and the lazy system admin that likes to gun through updates), you should ALWAYS dump all your databases prior to upgrading/downgrading between major versions of MySQL. Here’s a command I use to dump all the databases on the server into a backup folder:

mkdir /root/dbbackups ; touch /root/dbbackups/list
for db in `mysql -e ‘show databases’ |awk ‘{print $1}’` ; do mysqldump –add-drop-table $db > /root/dbbackups/$db.sql && echo $db >> list ; done

Then to re-import after the upgrade/downgrade:

for db in `cat /root/dbbackups/list` ; do mysqldump $db < /root/dbbackups/$db.sql ; done

Notes:
*If the MySQL upgrade fails, you can try a forced reinstall by doing /scripts/mysqlup –force
*In my experience, downgrades to MySQL 4.0 from other versions isn’t always so smooth, and I’ve had to actually move out the entire /var/lib/mysql folder and do a complete reinstall of MySQL. This obviously will remove all the databases, but you may be able to manually move them back into the mysql folder and reimport from your backups
*MySQL 4.0 is not automatically compatible with PHP 5+