Mysqldump仅具有某些前缀的表/Mysqldump通配符? [英] Mysqldump only tables with certain prefix / Mysqldump wildcards?

问题描述

我正在清理这个庞大而混乱的数据库.它可以容纳500多个表，这是将Magento Enterprise和Joomla合并到一个数据库中的结果.

I have this huge, messy database I am cleaning up. It houses 500+ tables, which is the result of combining Magento Enterprise with Joomla in one single DB.

更糟糕的是，有70多个Joomla表完全没有使用.这些都以bak_为前缀.

To make things worse, there is a set of 70+ Joomla tables that are not in use at all. These are all prefixed with bak_.

只需删除这些bak_表将很容易，但是我想先将其"bak"起来(看我在那里做了什么?).在我的脑海中，我可以想象这样的命令:

Just deleting these bak_ tables will be easy, but I want to 'bak' them up first (see what I did there?). In my mind I can picture a command like this:

mysqldump -u username -p mydatabase bak_*

但这不起作用.最好的方法是什么?谢谢！

But this doesn't work. What would be the best way to do this? Thanks!

是的，我可以明确列出要包括的70个表，或要排除的〜430个表，但是我正在寻找一种更好的方法来做到这一点.

Yes, I could explicitly list the 70 tables to include, or the ~430 tables to exclude, but I am looking for a better way to do this, if possible.

推荐答案

您可以在命令行上一个接一个地指定表名，但不能使用通配符. mysqldump databasename table1 table2 table3

You can specify table names on the command line one after the other, but without wildcards. mysqldump databasename table1 table2 table3

如果更短，也可以使用--ignore-table.

You can also use --ignore-table if that would be shorter.

另一个想法是使用类似的东西将表放入文件中

Another idea is to get the tables into a file with something like

mysql -N information_schema -e "select table_name from tables where table_schema = 'databasename' and table_name like 'bak_%'" > tables.txt 

编辑文件，并将所有数据库放在一行上.然后做

Edit the file and get all the databases onto one line. Then do

mysqldump dbname `cat tables.txt` > dump_file.sql

要将表格放到一行中(不建议使用)，您可以执行以下操作

To drop tables in one line (not recommended) you can do the following

mysql -NB  information_schema -e "select table_name from tables where table_name like 'bak_%'" | xargs -I"{}" mysql dbname -e "DROP TABLE {}"

这篇关于Mysqldump仅具有某些前缀的表/Mysqldump通配符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！