Upgrading MySQL 5.5 to MySQL 5.6 on Ubuntu 14.04 LTS

Solution 1:

How can I securely safely install

It will always be tricky.

Please read the 5.5/5.6 compatibility page before doing anything. Mind 1 big difference between the 2:

Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. The motivation for these changes is to provide better out-of-box performance and to reduce the need for the database administrator to change settings manually. These changes are subject to possible revision in future releases as we gain feedback.

1 of the big changes is that 5.5 uses 1 big file as a transactions log and 5.6 uses several:

Therefore, if you are upgrading an existing MySQL installation, have not already changed the values of these parameters from their previous defaults, and backward compatibility is a concern, you may want to explicitly set these parameters to their previous defaults. For example, put these lines in the server option file:

 [mysqld] 
 innodb_file_per_table=0 
 innodb_checksum_algorithm=INNODB
 binlog_checksum=NONE

If you use replication pay attention to this part of the link:

To upgrade servers used for replication, upgrade the slaves first, then the master. Replication between the master and its slaves should work provided that all use the same value of explicit_defaults_for_timestamp:

Bring down the slaves, upgrade them, configure them with the desired value of explicit_defaults_for_timestamp, and bring them back up.

The slaves will recognize from the format of the binary logs received from the master that the master is older (predates the introduction of explicit_defaults_for_timestamp) and that operations on TIMESTAMP columns coming from the master use the old TIMESTAMP behavior.

Bring down the master, upgrade it, and configure it with the same explicit_defaults_for_timestamp value used on the slaves, and bring it back up.

If this is a production server I would advice to first attempt this on a testing machine. We have had a rather difficult transition from 5.5 to 5.6 and opted to install another machine that was installed with 5.6 and use mysldump to create backups and load them into our databases on that machine. Mind though that this will take a long time if you have a large database (due to the recreation of the innodb transactions files).

A general method:

• use mysqldump to create backups of your database (users, trable structure and table data) and of your config file (probably /etc/mysql/my.cnf)
• Remove 5.5. After removing 5.5 check that the innodb transactions files are gone (ibdata1, ib_logfile0 and ib_logfile1). As 5.6 uses the better version of transactioning I assume you also would to use this...
• Install 5.6
• Change the new config file and add in what you changed for 5.5 (mind though the link above and check if any of the changes have become invalid).
• Upload your backup into 5.6 (users first). Mind that this can take a bit of time since it will recreate new transaction files.

In commands (after making the backup):

sudo apt-get remove mysql-server
sudo apt-get autoremove
sudo apt-get install mysql-client-5.6 mysql-client-core-5.6
sudo apt-get install mysql-server-5.6