Step-by-Step Upgrading MySQL 5.5 to 5.6 for Linux Ubuntu 14

I’m writing this guide to help those out there that just want to “get it done” rather than learn every detailed nuance of upgrading between every release of MySQL. If you are not a Database Administrator, a Systems Admin or DevOps Guru, you probably won’t need to perform this operation more than once (as long as you get it right the first try). So this guide is intended to be able to provide you with mostly just the step-by-step, copy + paste commands and steps.

 

Many of us have been supporting applications and server configurations for use with Ubuntu 14 for at least a year or so by now (12/2015 at the time of this post). Linux Ubuntu and the APT repository (e.g. apt-get install mysql-*) installed with:

Should result in something like:

Your version may vary slightly, but according to the MySQL 5.6 Reference Manual regarding upgrading from MySQL v5.5 to 5.6:

 

Upgrading one release level is supported. For example, upgrading from 5.5 to 5.6 is supported. Upgrading to the latest release series version is recommended before upgrading to the next release level. For example, upgrade to the latest 5.5 release before upgrading to 5.6.

Upgrading more than one release level is supported, but only if you upgrade one release level at a time. For example, upgrade from 5.1 to 5.5, and then to 5.6. Follow the upgrade instructions for each release, in succession.

Direct upgrades that skip a release level (for example, upgrading directly from MySQL 5.1 to 5.6) are not recommended or supported.

 

source: http://dev.mysql.com/doc/refman/5.6/en/upgrading.html

It feels like a lot of “blah blah blah” could be laid out better, doesn’t it? Perhaps some kind of an “Upgrade Matrix Table Graphic” or something? Maybe it’s just me.

Anyway…

For the purposes of this guide, I will assume that you already have a MySQL Database that you are upgrading from. Or, more concisely, you already have data in a database that you don’t want to lose. Again referencing the MySQL upgrade manual, it states the following:

Supported Upgrade Methods

In-place Upgrade: Involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.

Logical Upgrade: Involves exporting existing data from the old MySQL version using mysqldump, installing the new MySQL version, loading the dump file into the new MySQL version, and running mysql_upgrade.

You Should check out the spiffy table from the documentation for the new default settings of MySQL 5.6. If you are into “tuning your MySQL settings” like I am, this might be of interest.

Parameter	Old Default	New Default
back_log	50	Autosized using max_connections
binlog_checksum	NONE	CRC32
–binlog-row-event-max-size	1024	8192
flush_time	1800 (on Windows)	0
innodb_autoextend_increment	8	64
innodb_buffer_pool_instances	1	8 (platform dependent)
innodb_checksum_algorithm	INNODB	CRC32 (changed back to INNODB in MySQL 5.6.7)
innodb_concurrency_tickets	500	5000
innodb_file_per_table	0	1
innodb_old_blocks_time	0	1000
innodb_open_files	300	Autosized using innodb_file_per_table, table_open_cache
innodb_stats_on_metadata	ON	OFF
join_buffer_size	128KB	256KB
max_allowed_packet	1MB	4MB
max_connect_errors	10	100
sync_master_info	0	10000
sync_relay_log	0	10000
sync_relay_log_info	0	10000

Source: http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

Get it? Got it? Good.

Moving on with the update now…

 

Following the documentation, You will want to first get a complete database backup in the form of a .sql dump file.

STOP RIGHT NOW, AND ACTUALLY MAKE THE BACKUP FOR YOUR ENTIRE DATABASE. DO NOT SKIP THIS STEP.

I MEAN IT.

The documentation mentions a pretty slick way of doing this. If you know how to do a FULL MySQL database backup (including backing up Users and Permissions!) with some other tool, such as with GUIs like MySQL Workbench, SQLYog or (my fav) HeidiSQL, knock yourself out. You know what to do. Once you get done with the upgrade process, you will just run the database restore SQL and you are back in business.

But check this out, it’s a quick and clean way to make it all happen at once from the command line (all in one line):

(the one-liner above assumes you are using the MySQL “root” user, and you will get prompted for the root password)

Once you perform this from the command line in Linux Ubuntu, you can FTP or SCP into the directory you fired the command from on the command line and download the complete_backup.sql file (or open it in place if its not ginormous) and save it for when you are ready to perform the upgrade.

(again, the above assumes you are using the MySQL “root” user, and you will get prompted for the root password)

Now would probably be a good time to install MySQL 5.6, right?

From: http://dev.mysql.com/doc/refman/5.6/en/binary-installation.html
and
http://askubuntu.com/questions/203330/how-to-install-mysql-5-6

http://unix.stackexchange.com/questions/158052/how-to-configure-the-mysql-apt-repo-on-ubuntu-on-a-non-interactive-shell