mysql dump - exclude some table data

Is it possible, using mysql dump to export the entire database structure, but exclude certain tables data from export.

Say the database has 200 tables, I wish to export the structure of all 200 tables, but i want to ignore the data of 5 specific tables.

If this is possible, how is it done?

Solution 1:

This will produce export.sql with structure from all tables and data from all tables excluding table_name

mysqldump --ignore-table=db_name.table_name db_name > export.sql
mysqldump --no-data db_name table_name >> export.sql

Solution 2:

I think that AmitP's solution is great already - to improve it even further, I think it makes sense to create all tables (structure) first and then fill it with data, except the ones "excluded"

mysqldump --no-data db_name > export.sql
mysqldump --no-create-info --ignore-table=db_name.table_name db_name >> export.sql

if you want to exclude more than 1 table, simply use the --ignore-tabledirective more often (in the 2nc command) - see mysqldump help:

--ignore-table=name   Do not dump the specified table. To specify more than one
                      table to ignore, use the directive multiple times, once
                      for each table.  Each table must be specified with both
                      database and table names, e.g.,
                     --ignore-table=database.table

Solution 3:

I am a new user, and do not have enough reputation to vote or comment on answers, so I am simply sharing this as an answer.

@kantholy clearly has the best answer.

@AmitP's method dumps all structure and data to a file, and then a drop/create table statement at the end. The resulting file will still require you to import all of your unwanted data before simply destroying it.

@kantholy's method dumps all structure first, and then only data for the table you do not ignore. This means your subsequent import will not have to take the time to import all the data you do not want - especially important if you have very large amounts of data you want to ignore to save time.

To recap, the most efficient answer is:

mysqldump --no-data db_name > export.sql
mysqldump --no-create-info --ignore-table=db_name.table_name1 [--ignore-table=db_name.table_name2, ...] db_name >> export.sql

Solution 4:

As per the mysqldump docs:

mysqldump name_of_db --ignore-table=name_of_db.name_of_table

Solution 5:

In mysqldump from MariaDB in version 10.1 or higher you can use --ignore-table-data:

mysqldump --ignore-table-data="db_name.table" db_name > export.sql

For multiple tables repeat the --ignore-table-data option:

mysqldump --ignore-table-data="db_name.table_1" --ignore-table-data="db_name.table_2" db_name > export.sql

From MariaDB mysqldump docs:

--ignore-table-data=name

Do not dump the specified table data (only the structure). To specify more than one table to ignore, use the directive multiple times, once for each table. Each table must be specified with both database and table names. From MariaDB 10.1.46, MariaDB 10.2.33, MariaDB 10.3.24, MariaDB 10.4.14 and MariaDB 10.5.3. See also --no-data.