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.
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.
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.