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