Exported MariaDB dump different size

Question

I'm trying to copy a database from a MariaDB server on another. I made a dump of the database on server A with :

sudo mysqldump -u root -p --databases database1 > /var/tmp/database1.sql

I created the new database on server B with :

create database database1;

Then I copied the .sql dump file from server A to server B with scp and imported it on server B with :

sudo mysql -u root -p database1 < database1.sql

However, the two databases seem to not have the same size :

Server A :

MariaDB [(none)]> SELECT table_schema "database1", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"  FROM information_schema.tables  GROUP BY table_schema;
+--------------------+---------------+
| database1          | DB Size in MB |
+--------------------+---------------+
| information_schema |           0.2 |
| mysql              |           2.5 |
| performance_schema |           0.0 |
| database1          |         516.7 |
+--------------------+---------------+

Server B :

MariaDB [(none)]> SELECT table_schema "database1",ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"  FROM information_schema.tables  GROUP BY table_schema;
+--------------------+---------------+
| database1          | DB Size in MB |
+--------------------+---------------+
| information_schema |           0.2 |
| mysql              |           0.7 |
| performance_schema |           0.0 |
| database1          |         456.8 |
+--------------------+---------------+

Also, If I look at the same table on both database, they seem to not have the same number of lines. I've tried to copy the database multiple times but always get a difference. Is there something I'm missing ? I'm not an expert on MariaDB. Both server are under Debian 9.

Rick James · Accepted Answer · 2020-04-14T22:04:17.557

That is normal. Here is what happened:

mysqldump generates INSERT statements from the BTree-structured data.
The reload executes those statements, filling up a BTree with the data.
BTrees are designed for flexibility -- you can add a record in the middle of a table;
A BTree is composed of "blocks" (16KB each in the case of InnoDB)
If an insert goes into a 'full' block, then that block is 'split' -- 16KB suddenly became 32KB. A lot of this can cause a table to grow.
On the other hand, the dump was probably done in "order". In that case the reload might pack the blocks better than would happen from random inserts/deletes.
This reload is likely to be smaller, but not necessarily.

Meanwhile...

SHOW TABLE STATUS gives only an estimate of the number of rows. If you want the exact number of rows, use SELECT COUNT(*) FROM tablename. (one table at a time) Since the STATUS is an "estimate", it may be more or less than the real count; sometimes by a significant amount. Again this is "normal".

Exported MariaDB dump different size

1 Answers1