#!/bin/bash

#
# tatsaechlicher Speicherplatzbedarf von diesem DBMS
#

VERSION="v2017033100"
#MYSQL_PROG="mysql --defaults-file=/etc/mysql/debian.cnf -t"
MYSQL_PROG="mysql --defaults-file=/root/.my.cnf -t"


### Übersicht
echo "
SELECT
        table_schema,
        table_name,
        data_length,
        index_length,
        data_length,
        index_length,
        data_free
FROM
        information_schema.tables
WHERE
        data_length IS NOT NULL
        AND data_length<>'information_schema'
        AND data_length<>'performance_schema'
        AND data_length<>'sys'
ORDER BY data_length DESC
;" | ${MYSQL_PROG}


### Summen
echo "
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SELECT 
        table_schema AS 'Datenbankname',
        table_name,
        ROUND( SUM( data_length ) / 1024 / 1024, 3 ) AS 'Daten (MB)',
        ROUND( SUM( index_length ) / 1024 / 1024, 3 ) AS 'Index (MB)',
        ROUND( SUM( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Gesamt (MB)',
        ROUND( SUM( data_free ) / 1024 / 1024, 3 ) AS 'Freier Speicher (MB)'
FROM
        information_schema.tables
GROUP BY table_schema
ORDER BY 5 DESC
;" | ${MYSQL_PROG}