Why Your MySQL Dump Is Smaller Than Your DB Data Directory

Why Your MySQL Dump Is Smaller Than Your DB Data Directory

QuoteBefore every deployment or any database changes, you always take a database backup. Sometimes, when you compare the size of that backup file to the database directory on disk (/var/lib/mysql), you’ll often see a big difference. A smaller dump and a larger data folder are perfectly normal. Your dump command isn’t wrong.

1. Why Dump Files Are Smaller Than Disk Usage

What mysqldump Exports

  • Schemas: only the CREATE TABLE statements

  • Raw row data: only the INSERT statements

  • Nothing else: no indexes, no transaction logs, no engine metadata

What Lives in /var/lib/mysql/dbname/

  • Data files

    • InnoDB tablespaces (*.ibd)

    • MyISAM data files (*.MYD)

  • Indexes

    • B-tree structures for fast lookups (*.MYI for MyISAM; built into .ibd for InnoDB)

  • Logs & Overhead

    • Undo/redo logs, binary logs (if enabled)

    • Storage-engine metadata and page headers

  • Unused fragmented space

    • Gaps left inside data files by deletes or updates, not returned to the OS


Notes

Example : The MySQL dump is only 96MB and the size of the database directory path is 230MB (/var/lib/mysql/prod_orangehrm)


            dump = essentials

            data folder = essentials + indexes + logs + fragmentation.


2. Key Concepts

Compression - InnoDB can compress pages on disk to save space, but dump files remain plain-text SQL.
Fragmentation - Gaps inside data files created by deletes/updates; these gaps bloat the file size until reclaimed

Quote
With this little knowledge, you’ll understand why your backups look smaller and how the MySQL data directory grows.



    • Related Articles

    • OS to Enterprise Data Migration Tool

      Please find the deployment guide from the link OS to Enterprise Migration - Deployment Guide.

    • On-Site Client Automated Backup Script

      Requirement: Onsite clients may request for automated backup scripts to be deployed on their servers for daily backups. Use: Deploy the below script on the clients server along with a specific cron-task setup for backups to be taken on a pre-defined ...

    • Chelton Custom Salary Upload Task

      Chelton Custom Salary Upload Task As the client has some customisation on their Salary screen the usual Bulk Uploader will not work, when the usual Uploader is used all Salary History records are truncated. Hence, a custom script has been created to ...

    • How to change encrypted mysql password in databases.yml

      Updating any encrypted database password of database.yml files can be done following the below steps. Login to Support repo and check out 'db-password-encrypt' task from below path.  ...

    • Opensource Demo Instance Upgrade

      Upgrading Opensource Demo Environment Details DB Name: demo_opensource Host: db Codebase: /var/www/html/OHRMStandalone/DEMO/opensource-demo74.orangehrmlive.com Server: Faith The above config values can be found on <root>/lib/confs/Conf.php file. ...