Removing Large Table Data from MySQL/MariaDB dumps

Apr 15, 2023

As a developer, you're undoubtedly aware that when dealing with extensive MySQL/MariaDB database dumps, reloading for debugging or testing can be a meticulous task. More often than not, the dump contains tables laden with substantial data that you might find irrelevant to your testing environment.

Moreover, when you're operating in a resource-restricted environment, reloading an enormous dump can be a time-consuming endeavor, significantly hampering your productivity. In such situations, streamlining the process becomes paramount, and this entails removing data from those large, non-essential tables.

The 'sed' command-line tool comes in exceptionally handy for this operation.

sed -i '/^INSERT INTO `(table1|table2|tableN)` VALUES/d' path/to/dump.sql

This will remove the data statements for table1, table2 and tableN. In a future post we'll look at how to ignore dumping those tables to speed up the dumping process.