MySQL Upgrade 55 to 56: Unterschied zwischen den Versionen

Aus crazylinux.de
Zur Navigation springen Zur Suche springen
K (a)
K (aa)
Zeile 18: Zeile 18:
#remove !!
#remove !!
table_cache
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>
</syntaxhighlight>


Zeile 23: Zeile 60:
= Links =  
= Links =  
*https://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
*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:Datenbank]]

Version vom 23. April 2018, 23:28 Uhr

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