Tuesday, September 08, 2015

steps to upgrade mysql 5.5 to 5.6

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)

    /mnt/ssdata/mysql5.5_bak# time mysqldump --lock-all-tables -u root -p --all-databases > mysql5.5_dump.sql
    Enter password:
    -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

    real    1m50.291s
    user    0m52.281s
    sys     0m5.228s


     try to export again

    root@YZ37:/mnt/ssdata/mysql5.5_bak# time mysqldump --events --ignore-table=mysql.events --lock-all-tables -u root -p --all-databases > mysql5.5_dump2.sql
    Enter password:

    real    1m45.663s
    user    1m4.349s
    sys     0m5.483s
  • 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

    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
    
  • 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.


    I choose importing user databases one by one, except below these three system databases.
 information_schema 
 mysql              
 performance_schema 
 

mysql  Ver 14.04.3 LTS Distrib 5.6.19, for debian-linux-gnu (x86_64) using  EditLine wrapper

References:

http://askubuntu.com/questions/614886/upgrading-mysql-5-5-to-mysql-5-6-on-ubuntu-14-04-lts

another good article is http://xmodulo.com/upgrade-mysql-server-debian-ubuntu.html

http://sharadchhetri.com/2014/05/07/install-mysql-server-5-6-ubuntu-14-04-lts-trusty-tahr/

After upgrade, we may see this warning message:

2015-09-07 18:36:32 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
mysql start/running, process 17378
Processing triggers for ureadahead (0.100.0-16) ...


The solution is set to explicit_defaults_for_timestamp=true in my.cnf

Do check mysql manual to understand its behaviour change of explicit_defaults_for_timestamp .