MySQL Upgrade 55 to 56
Zur Navigation springen
Zur Suche springen
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