Upgrading MySQL 5.5 to 5.6

Question

I've decided to do the update of MySQL from 5.5 to 5.6 on my CentOS server. How can I backup MySQL5.5 in case I want to downgrade, and how can I install MySQL 5.6?

Is there a way to use yum for this? Will upgrading to 5.6 break cPanel?

The steps on the MySQL website is pretty vague, and I am limited experience in installing things.

Solution 1

There is no way to run multiple MySQL installations using yum on a single instance. But, it's very easy to install and uninstall current and future versions via yum or rpm.

It seems you are going about this in a very wrong way. First do this on a staging machine. Document the steps and time taken in detail. Do this multiple times if you have to improving your steps each time.

[1] Backup your MySQL data using mysqldump
[2] Upgrade your MySQL to 5.6
[3] Restore the data 
[4] Verify data integrity and ensure all your applications work 

Then, do this on production server with the same steps as you did in your final staging trial.

Rollback is as simple as installing the current version of MySQL back and restoring your dump.

This way, you verified the upgrade works, you verified your applications work .. cPanel and what not.

Solution 2

cPanel should be fine with MySQL 5.6 but you would need to confirm on your dev/test/staging environment where you're going to trial the upgrade process first =)

cPanel support suggests doing the MySQL upgrade their way: http://forums.cpanel.net/f5/mysql-upgradation-211421.html#post871392
You would need to be using a yum installed version of MySQL under cPanel at the moment to be able to replace the mysql binaries with yum. You will need to cover the MySQL config and data yourself.

From http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html To upgrade to 5.6 from any previous version, MySQL recommends that you dump your tables with mysqldump before upgrading and reload the dump file after upgrading. http://dev.mysql.com/doc/refman/5.6/en/rebuilding-tables.html

If I were doing a MySQL upgrade manually it would involve the following:

run mysqldump --add-drop-table --all-databases > dump.sql
stop mysql
Backup 5.5 config
Backup 5.5 data directory
Remove 5.5 rpms
Install 5.6 rpms
Start mysql
run mysql –default-character-set=utf8 < dump.sql
run mysql_upgrade

doesn't work?
stop mysql
remove 5.6 rpms
install 5.5 rpms
restore 5.5 config
restore 5.5 data directory
start mysql

Comments

[KTK27]

Have you tested this yet with cPanel? I'm in the same situation (I need full-text search on innoDB tables) and am trying to find out if the upgrade causes trouble with cPanel.

[Nyxynyx]

Is there an alternative if I do not have staging server?

[Chida]

Try this on your laptop/desktop with virtualbox or VMware player with same CentOS version and architecture as your production box. You can recover your production backup if it's not too big on the VM to ensure you work with production data and it is as real as it can get.