Upgrading MySQL 5.5 to MySQL 5.6 on Ubuntu 14.04 LTS

Question

I have installed my LAMP with the following commands:

sudo apt-get update
sudo apt-get install apache2 apache2-suexec mysql-server php5-mysql
sudo mysql_secure_installation
sudo apt-get install php5 libapache2-mod-php5 php5-mcrypt
sudo apt-get install phpmyadmin

It was almost a year ago. Now in PHPMyAdmin, I see the following:

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.43-0ubuntu0.14.04.1 - (Ubuntu)
Protocol version: 10
Web server
Apache/2.4.7 (Ubuntu)
Database client version: libmysql - 5.5.43
PHP extension: mysqli Documentation

MySQL version is 5.5.43-0ubuntu0.14.04.1. How can I safely and securely install and use mysql 5.6.4+ as I need to use FULLTEXT with INNODB. I see the packages in the Ubuntu repository, but I really should avoid any conflict or installation problems.

Thanks for any help.

Solution

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

Comments

[YahyaE]

For your information, I took a backup first then successfully install it with this command: 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

[Rinzwind]

That looks good yes :D Gratz! There I editted it in ;-)

[NaN]

Does that procedure erases the databases in this server?

[Rinzwind]

@EASI No but the question is ... bad. When upgrading mysql you need to create a backup and restore it to have mysql scan for deprecated functions/parameters/options/settings.