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