Re: Estimate mysqldump size

Ronan McGlue <[email protected]> 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>=C2=A0 select round(SUM(data_length+index_length)/POWER(1024,2),1)=
=20
> Total_MB,round(SUM(data_length)/POWER(1024,2),1)=20
> data_MB,round(SUM(index_length)/POWER(1024,2),1) index_MB=C2=A0 FROM=20
> information_schema.tables where TABLE_SCHEMA not in (=20
> "information_schema", "performance_schema", "mysql") ;
> +----------+---------+----------+
> | Total_MB | data_MB | index_MB |
> +----------+---------+----------+
> |=C2=A0=C2=A0 4546.0 |=C2=A0 4093.7 |=C2=A0=C2=A0=C2=A0 452.2 |
> +----------+---------+----------+
> 1 row in set (0.00 sec)
>
> However, this doesn't 100% map to OS file size ( if using innodb file=20
> per table ) and will likely never be 100% accurate to what the OS=20
> 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.=C2=
=A0=20
> 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=20
> nature of the output file created ( eg text files compress *very* well )=
=20
> , 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=20
> 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=20
> reasonably close if you arent doing a *lot* of dml on it.
>
> You could artificially inflate the expected size by ,eg multiplying by=20
> 1.1x or 1.2x , however there will always be an edge case table which=20
> will be greater still..
>
>
> Regards
>
> Ronan McGlue
>
> MySQL Support
>
>
>
>>
>> Thanks in advance,
>>
>> Olivier
>>
>>
>

--=20

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql