1
  • I have a MariaDB server - 5.5.60
  • I have a database xyz that has both MyISAM and InnoDB tables
  • The majority of the tables are MyISAM but in this database in particular, the data is mainly in InnoDB tables.

If I run du -sh /var/lib/mysql/xyz I get 11M

If I run mysqldump -u root -pXXX "xyz" > xyz.sql the resulting file is 371M

I can use the xyz.sql file to restore the database just fine. With other databases I have had all MyISAM tables and never noticed much of a difference in size.

Why is the .sql file so much larger than the actual database directory? Is there something I should do differently when using mysqldump with a database with InnoDB tables?

Jason
  • 115
  • 4

1 Answers1

1

this is normal.

with a version 5.5.60 default settings for innodb innodb_file_per_table = 0 it means - data stored not in files inside the database folder, but in global innodb file.

You can check it in /var/lib/mysql file - ibdata1

after version 5.6 default value for innodb_file_per_table settings is 1

ibdata1 is still extremely important, but data stored in separate files similar for myisam.

a_vlad
  • 3,705
  • 2
  • 13
  • 17