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
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
