Reset All Tables Except Migration Table on MySQL

Overview

When developing an application there is case when you need to clear all the data in your database e.g: testing the seeding or such. But you want to exclude some tables let say migration table which used by the application framework to migrate the schema.

Problem

You want to clean up data on all the tables, except the migration table because you don't want to re-run the schema migration.

Solution

1. Get list of tables
2. Exclude the migration table
3. Append the prefix 'DELETE FROM ' to each line
4. Append the suffix ';' to each line
5. Pipe the result to MySQL

$ echo "SHOW TABLES;" | mysql -N DB_NAME | grep -v orb_migration | sed -e 's/^/DELETE FROM /' -e 's/$/;/' | mysql DB_NAME

In case above the table which excluded is orb_migration.

Share on Facebook Twitter