How to upgrade MySQL 5.5 to 5.6 on Ubuntu 14.04 LTS

By Cris, August 26, 2016
Last updated: August 27, 2016
Edit

Does your SQL installation meet minimum requirements?

The standard Ubuntu 14.04 LTS Server installation installs MySQL 5.5 by default using the following command:


sudo apt-get update
sudo apt-get install mysql-server

However, certain new web applications and services require at least MySQL 5.6. This includes the latest version of Magento 2.0.4 Community Edition. After following a few guides and crippling my existing installation I’ve decided to share what worked and what didn’t. (This makes the difference between 5 minutes and 5 hours 🙂 )

Warning: This has been tested with Ubuntu 14.04 LTS and is valid for only this release! Make sure to backup your database as outlined in case things go bad. If they do, read the bottom section ;). And of course, use at your own risk!

Start by making a backup of your database files using the following shell command:


sudo mysqldump --lock-all-tables -u root -p --all-databases > /home/USERNAME/dump.sql

I chose to dump the entire SQL into my /home/USERNAME folder. Feel free to place this file at your convenience.

Next list all packages that are related to your current MySQL 5.5 installation:


[email protected]:~$ sudo dpkg --get-selections | grep mysql

[sudo] password for USERNAME:

libdbd-mysql-perl                               install
libmysqlclient18:amd64                          install
mysql-client-5.5                                install
mysql-client-core-5.5                           install
mysql-common                                    install
mysql-server                                    install
mysql-server-5.5                                install
mysql-server-core-5.5                           install
php5-mysql                                      install

Take note of these packages and remove them all like so:


sudo apt-get remove libdbd-mysql-perl libmysqlclient18:amd64 mysql-client-5.5  mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 php5-mysql

Reading package lists... Done
Building dependency tree
Reading state information... Done

The following packages were automatically installed and are no longer required:
  libaio1 libdbi-perl libterm-readkey-perl

Use 'apt-get autoremove' to remove them.

The following packages will be REMOVED:
  libdbd-mysql-perl libmysqlclient18 mysql-client-5.5 mysql-client-core-5.5
  mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 php5-mysql
0 upgraded, 0 newly installed, 9 to remove and 3 not upgraded.

After this operation, 94.8 MB disk space will be freed.

Do you want to continue? [Y/n] Y

Note the apt-get autoremove at the very end!

Almost there, now it is time to reinstall the newer version:


sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-server-5.6

When prompted, reset or leave your root MySQL password. And you’re done!

How NOT to upgrade MySQL 5.5 to 5.6 on Ubuntu 14.04 LTS

Here is what did not work and resulted in a partial, broken installation that made downgrading and reinstalling challenging:


sudo apt-get update
sudo apt-get upgrade
sudo apt-get install mysql-server-5.6

Follow the proper instructions in the main guide to remove package files in a neat and complete way followed by a complete reinstallation. If not, you will end up with a broken installation of either version. In case you need to fix something that went wrong start here:


[email protected]:~$ sudo dpkg --get-selections | grep mysql

[sudo] password for USERNAME:

libdbd-mysql-perl                               install
libmysqlclient18:amd64                          install
mysql-client-5.6                                install
mysql-client-core-5.5                           deinstall
mysql-common                                    install
mysql-server                                    install
mysql-server-5.5                                deinstall
mysql-server-core-5.6                           install
php5-mysql                                      install

Using the same dpkg | grep command, list all files of all MySQL installation. You will see something like the above (fictitious example but same idea). Make a list of all packages you find here and, like the guide above, use apt-get remove to get rid of them all:


sudo apt-get remove libdbd-mysql-perl libmysqlclient18:amd64 mysql-client-5.5  mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 php5-mysql

Reading package lists... Done
Building dependency tree
Reading state information... Done

The following packages were automatically installed and are no longer required:
  libaio1 libdbi-perl libterm-readkey-perl

Use 'apt-get autoremove' to remove them.

The following packages will be REMOVED:
  libdbd-mysql-perl libmysqlclient18 mysql-client-5.5 mysql-client-core-5.5
  mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 php5-mysql
0 upgraded, 0 newly installed, 9 to remove and 3 not upgraded.

After this operation, 94.8 MB disk space will be freed.

Do you want to continue? [Y/n] Y

If you are desperate to get things back to the way they were before on MySQL 5.5, delete the following “.flag” file that was generated by the 5.6 upgrade attempt


sudo su

cd /var/lib/mysql

rm debian-5.*.flag

And re-install default MySQL 5.5:


sudo apt-get update
sudo apt-get install mysql-server-5.5

If your data is gone, re-import the backup dump you made prior to beginning like so

 	
mysql -u root -p < /path/to/dump.sql

At this point, either leave it as it is or attempt to upgrade as per the main guide at the top of this page.

What do you think?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.