Ronan McGlue <ronan.mcg...@oracle.com> writes:
> Hi Olivier,
>
> On 28/11/2018 8:00 pm, Olivier wrote:
>> Hello,
>>
>> Is there a way that gives an estimate of the size of a mysqldump such a
>> way that it would always be larger than the real size?
>>
>> So far, I have found:
>>
>> mysql -s -u root -e "SELECT SUM(data_length) Data_BB FROM
>> information_schema.tables WHERE table_schema NOT IN
>> ('information_schema','performance_schema','mysql');
>>
>> but the result may be smaller than the real size.
>
> In the above example, you also need to account for index_length, eg
But I thought I had read that indexes are not saved by a myslqdump, but
recreated on a restore?
Thanks in advance,
Olivier
>
> mysql> select round(SUM(data_length+index_length)/POWER(1024,2),1)
> Total_MB,round(SUM(data_length)/POWER(1024,2),1)
> data_MB,round(SUM(index_length)/POWER(1024,2),1) index_MB FROM
> information_schema.tables where TABLE_SCHEMA not in (
> "information_schema", "performance_schema", "mysql") ;
> +----------+---------+----------+
> | Total_MB | data_MB | index_MB |
> +----------+---------+----------+
> | 4546.0 | 4093.7 | 452.2 |
> +----------+---------+----------+
> 1 row in set (0.00 sec)
>
> However, this doesn't 100% map to OS file size ( if using innodb file
> per table ) and will likely never be 100% accurate to what the OS
> reports, due to fragmentation etc.
>
>>
>> I am writting a program that takes the result of mysqldump and pipe it
>> in a tar file.
>
> A typical global mysqldump ( ie taken with -A ) will be a single file.
> Why are you then wanting to pipe this to a tar archive?
>
> Its also common for mysqldump to be compressed via a pipe due to the
> nature of the output file created ( eg text files compress *very* well )
> , to then be sent across the network , eg via ssh
>
> mysqldump -u.. -p -A | gzip > schema.sql.gz
>
>
> Aside from your stated goal of piping to tar, if we can step back a
> level briefly - what are you trying to achieve here?
>
>> Tar file format has the size in the header, before the
>> data and if the size of the dump is bigger than the size declared in the
>> header, tar does not like that (if the size of the dump is smaller than
>> the actual size, it can be padded with spaces).
>>
>> So, the estimate must be larger than the actual dump, how to acheive
>> that?
>
> It wont be anything other than an estimate , however it should still be
> reasonably close if you arent doing a *lot* of dml on it.
>
> You could artificially inflate the expected size by ,eg multiplying by
> 1.1x or 1.2x , however there will always be an edge case table which
> will be greater still..
>
>
> Regards
>
> Ronan McGlue
>
> MySQL Support
>
>
>
>>
>> Thanks in advance,
>>
>> Olivier
>>
>>
>
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
