Mysqldump from mysql to mariadb - database size diffrence

Question

i wanted to switch from mysql to mariadb, to do so, i exported the old databeses and import them to a new mariadb server, now i have the problem, that the new imported database is smaller than the original.

I did the following:

Created a backup with mysqldump from mysql (mysqldump --all-databases --user=root --password --master-data > backupdatabase.sql)
Imported it to a new maridb-server (mysql -u root -p < backupdatabase.sql)

If in want to see, how big the databases are, i see the following:

On the original mysql-server:

mysql> SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;

| database | size in MB |

| DB-1 | 0.40625000

| DB-2 | 4.09375000 |

| DB-3 | 506.60937500 |

6 rows in set (0.90 sec)

If i do now the same on the maraidb host:

MariaDB [(none)]> SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;

| database | size in MB |

| DB-1 | 0.39062500 |

| DB-2 | 3.03125000 |

| DB-3 | 416.39062500 |

6 rows in set (0.09 sec)

Where can the diffrence come from? Is this my failure?

Jimmy · Accepted Answer · 2015-10-26 19:51:47Z

0

MySQL and MariaDB storages for InnoDB and MySIAM (one of the engines you are probably using) is identical. Furthermore, you can install MariaDB by stomping it on top of MySQL and it will work with the same file databases.

Your issue is most likely coming from the fact that when you create a table from scratch and insert all the rows there is no fragmentation or space wasted or anything. When you have a live server, as you delete rows the data becomes somewhat fragmented, and sometimes it even stores "empty" space and doesn't give it back to the OS (InnoDB).

Try this, create a new Database in MySQL as DB1-test and import the dump into it, then compare the DB1-test size vs the MariaDB-DB1 and it should coincide.

answered Oct 26, 2015 at 19:51

Jimmy

1,1841 gold badge10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bernd Buffen · Accepted Answer · 2015-10-26 19:36:52Z

0

Yor Table are not optimized on the MySQL Server. So MySQL free NEVER unused Disc Space. The only thing to free it is to Drop and create a Table. This is what optimize done. A other Thing is the Option "One File per Table". If it set MySQL use on file per Table and its possible to free disc space with Optimize else you have only one big file.

answered Oct 26, 2015 at 19:36

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Collectives™ on Stack Overflow

Mysqldump from mysql to mariadb - database size diffrence

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related