Quelltext der Seite 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

<syntaxhighlight lang="bash">
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
</syntaxhighlight>


<syntaxhighlight lang="ini">
#/etc/mysql/conf.d/settings.cnf
[mysqld] 
 innodb_file_per_table=0 
 innodb_checksum_algorithm=INNODB
 binlog_checksum=NONE

#remove !!
table_cache
</syntaxhighlight>


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

<syntaxhighlight lang="bash">$  /usr/sbin/mysqld --skip-grant-tables --user=mysql &</syntaxhighlight>

and use mysql_upgrade to migrate to the new grant tables, it will rebuild the indexes needed and do the modifications 
<syntaxhighlight lang="bash">$ mysql_upgrade
...
OK</syntaxhighlight>

Once this is done, just restart the server as usual

= Upgrading =
== Date/Time ==
<syntaxhighlight lang="sql">
# 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;
</syntaxhighlight>

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

<syntaxhighlight lang="bash">
mysqlcheck -o --all-databases -p
</syntaxhighlight>


= 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

[[Kategorie:Datenbank]]
[[Kategorie:MySQL]]
[[Kategorie:Linux]]
[[Kategorie:Ubuntu]]