Reduce the size of a large MySQL dump file

Introduction

I already mentioned in a previous post that sometimes at work we have to deal with very large mysqldump backups of our production MySQL databases. A couple of days ago I had to restore locally a database from a 2.6 GB dump file gzipped, which means that the full database was almost 27 GB large.

Running the import command

$ mysql -u homestead -psecret mydatabase < large-dump.sql

completely killed my MySQL development instance (I’m using Laravel Homestead), forcing me to recreate the virtual machine entirely from scratch.

Unfortunately the mysql command line tool doesn’t allow to ignore specific tables. This process should be done from the mysqldump command instead

$ mysqldump --ignore-table=database.table_you_dont_want --ignore-table=database.another_table_you_dont_want

however those dumps are full backups created automatically on AWS.

Solution

I asked myself if it was possible to limit the size of the import perhaps by removing useless tables with a lot of data. For example I knew that in that dump there was also a huge table that stores incoming webhooks, which was quite useless in my local environment.

Limiting the size of the dump would have reduced not only the likelihood of errors, but also the time spent on importing it.

The following command lists the tables in the dump by searching for the CREATE TABLE statement:

$ more large-dump.sql | grep 'CREATE TABLE'

After identifying those huge and useless tables I used again the Unix sed tool to remove completely all the INSERT statements on those tables from the dump:

$ sed '/INSERT INTO `cloudinary_notification_webhooks`/d' large-dump.sql | \
sed '/INSERT INTO `failed_jobs`/d' | \
sed '/INSERT INTO `stripe_webhooks`/d' | \
sed '/INSERT INTO `syncid_cached_claims`/d' | \
sed '/INSERT INTO `download_history`/d' > light-dump.sql

I was able to cut the size and the import time in half.