Search Posts

include or exclude tables with a specific prefix in mysqldump or mysql

I crawled the database, and there were enough tables to die.

For example, there are about 200 tables, and about 60 of them have a prefix like piyo_, and there is a desire that I want to execute mysqldump excluding it.

For a few tables excluded,--ignore-tableYou can use Because there are multiple--ignore-table="piyo%"I thought I shouldn’t do it, but I’m sorry.

As a result, I was able to manage somehow with the following approach.

First of all, from a simple pattern that is not excluded.

How to dump only tables with a specific prefix

For example, if you have 20 tables with hoge_ prefix and you want to dump them, you can do the following: (Because I’m sorry, I write it with -u root.)


Now the information you want in dump.sql is written out.

(To be exact"hoge\_%"When_I think that it is good because it is possible to specify the prefix strictly if you write it escaped, but it’s hard to see, so all is omitted)


-N … Do not display column names.

-e … execute sql

Extract the hoge_ prefix table in the first half and use it as an argument of the second half (mysqldump) in xargs. (So, multiple table information is passed)

Main subject

First, the sql execution part

It is ok with customization to feel like that? I thought …

This can not be used.

If you’re looking in various ways, write as follows! I found that …

This is no good. (It used to be ok, but it’s a new version, something like that is written.mysql: What is the right syntax for NOT LIKE?) I tried actually, but something wrong.

But found!

Reference source:Why doesn’t this work?-SHOW TABLES NOT LIKE ‘history%’

Well done ….

What this!

Suddenly the complexity came out. I don’t know table_schema or anything at all. . I will study here later.

But now I know how to show by excluding certain tables, so I can combine this with the dump.

Finished form


It’s long.

Honestly, I felt quite uneasy about whether such a command is normal or not, but if I go wrong,

It was possible! ! \ (̊ ̊) /

You can now dump tables other than the piyo_ prefix table.

Thank you very much.

■ Web page I gave you reference: Many thanks

Leave a Reply