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.