MySQL Upgrade 55 to 56

to do

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

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

#/etc/mysql/conf.d/settings.cnf
[mysqld] 
 innodb_file_per_table=0 
 innodb_checksum_algorithm=INNODB
 binlog_checksum=NONE

#remove !!
table_cache

As the schema of the grant table has changed, the server must be started without reading them:

$  /usr/sbin/mysqld --skip-grant-tables --user=mysql &

and use mysql_upgrade to migrate to the new grant tables, it will rebuild the indexes needed and do the modifications

$ mysql_upgrade
...
OK

Once this is done, just restart the server as usual

Upgrading

Date/Time

# Improved version of http://mysqlserverteam.com/upgrading-old-mysql-5-5-format-temporals-to-mysql-5-6-format-2/#comment-746
SELECT t.table_schema,t.table_name,c.column_name,c.column_type, iss.num_rows
FROM information_schema.tables t
INNER JOIN information_schema.columns c ON c.table_Schema = t.table_schema AND c.table_name = t.table_name
INNER JOIN information_schema.innodb_sys_tables ist ON ist.name = concat(t.table_schema,"/",t.table_name)
INNER JOIN information_schema.innodb_sys_columns isc ON isc.table_id = ist.table_id AND isc.name = c.column_name
INNER JOIN information_schema.innodb_sys_tablestats iss ON iss.name = concat(t.table_schema,"/",t.table_name)
WHERE t.engine = "innodb"
AND c.column_type IN ("time","timestamp","datetime")
AND isc.mtype = 6
ORDER BY t.table_schema,t.table_name,c.column_name;

How to Shrink MySQL ibdata1 Size using innodb_file_per_table

https://www.thegeekstuff.com/2016/02/mysql-innodb-file-per-table/

or optimze ALL tables

mysqlcheck -o --all-databases -p

Links

• https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
• http://mysqlserverteam.com/upgrading-old-mysql-5-5-format-temporals-to-mysql-5-6-format-2/#comment-746