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:
|
1 |
mysql –version |
Should result in something like:
|
1 |
mysql Ver 14.14 Distrib 5.5.46, for debian-linux-gnu (x86_64) using readline 6.3 |
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.
NoteMySQL recommends a mysqldump upgrade when upgrading from a previous release. For example, use this method when upgrading from 5.5 to 5.6.
Could that be more cryptic? Why not state this instead:
“A Logic Upgrade is the preferred method of upgrading from v5.5 to v5.6 via the use of mysqldump.”
I digress.
For the sake of simplicity and compatibility, we will follow the “preferred method” for upgrading in this guide. In case its 3am, and you missed it just now, the preferred method of upgrading MySQL 5.5 to 5.6 is a Logical 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):
|
1 |
mysqldump -u root -p –add-drop-table –routines –events –add-drop-table –all-databases –force > complete_backup.sql |
(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.
|
1 |
mysqladmin -u root -p shutdown |
(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
|
1 2 3 |
sudo apt-get purge mysql-server-5.5 mysql-client-5.5 sudo apt-get autoremove sudo apt-get install mysql-server-5.6 mysql-client-5.6 |
|
1 2 3 4 |
export DEBIAN_FRONTEND=noninteractive echo mysql-apt-config mysql-apt-config/enable-repo select mysql-5.6 | sudo debconf-set-selections wget http://dev.mysql.com/get/mysql-apt-config_0.5.3-1_all.deb dpkg –install mysql-apt-config_0.5.3-1_all.deb |
|
1 2 |
mysql -u root -p –execute="source complete_backup.sql" –force mysql_upgrade -u root -p |
