MySQL Upgrade 55 to 56

Wechseln zu: Navigation, Suche

1 to do

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


#remove !!

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

Once this is done, just restart the server as usual

2 Upgrading

2.1 Date/Time

# Improved version of
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 = concat(t.table_schema,"/",t.table_name)
INNER JOIN information_schema.innodb_sys_columns isc ON isc.table_id = ist.table_id AND = c.column_name
INNER JOIN information_schema.innodb_sys_tablestats iss ON = 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;

2.2 How to Shrink MySQL ibdata1 Size using innodb_file_per_table

or optimze ALL tables

mysqlcheck -o --all-databases -p

3 Links