windows 我可以使用 mysqldump 获取所有数据库的转储 *除了一个 * 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2917485/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I get a dump of all my databases *except one* using mysqldump?
提问by Daniel Magliola
I'm currently using mySQLdump to backup my dev machine and servers.
我目前正在使用 mySQLdump 来备份我的开发机器和服务器。
There is one project I just started, however, that has a HUUUUUGE database that I don't really need backed up, and i'll be a big problem to add it to the rest of the backup cycle.
然而，我刚开始的一个项目有一个我并不真正需要备份的 HUUUUUGE 数据库，我将它添加到备份周期的其余部分将是一个大问题。
I'm currently doing this:
我目前正在这样做：
"c:\Program Files\mysql\MySQL Server 5.1\bin\mysqldump" -u root -pxxxxxx --all-databases > g:\backups\MySQL\mysqlbackup.sql
Is it possible to somehow specify "except this database(s)"?
是否可以以某种方式指定“除了这个数据库”？
I wouldn't like to have to specify the list of DBs manually, since that would mean that I'd have to remember updating my backup batch file every time I create a new DB, and I know that's not gonna happen.
我不想手动指定数据库列表，因为这意味着每次创建新数据库时我都必须记住更新我的备份批处理文件，我知道这不会发生。
EDIT: As you probably guessed from my command line above, i'm doing this on Windows, so I can't do any kind of fancy bash stuff, only wimpy .bat things.
编辑：正如您可能从上面的命令行中猜到的那样，我在 Windows 上执行此操作，所以我不能做任何花哨的 bash 东西，只能做 .bat 的东西。
Alternatively, if you have other ideas to solve this same issue, they are more than welcome, of course!
或者，如果您有其他想法来解决同样的问题，当然，我们非常欢迎！
回答by Ignacio Vazquez-Abrams
回答by agus nurhadi
echo 'show databases;' | mysql -uroot -proot | grep -v ^Database$ | grep -v ^information_schema$ | grep -v ^mysql$ | grep -v -F db1 | xargs mysqldump -uroot -proot --databases > all.sql
dumps all databases except: mysql, information_schema, mysqland db1.
转储所有数据库，除了：mysql，information_schema，mysql和db1。
Or if you'd like to review the list before dumping:
或者，如果您想在倾销前查看列表：
echo 'show databases;' | mysql -uroot -proot > databases.txt- edit
databases.txtand remove any you don't want to dump cat databases.txt | xargs mysqldump -uroot -proot --databases > all.sql
echo 'show databases;' | mysql -uroot -proot > databases.txt- 编辑
databases.txt并删除您不想转储的任何内容 cat databases.txt | xargs mysqldump -uroot -proot --databases > all.sql
回答by pupeno
What about
关于什么
--ignore-table=db_name.tbl_name
Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times.
--ignore-table=db_name.tbl_name
不要转储给定的表，必须同时使用数据库和表名来指定。要忽略多个表，请多次使用此选项。
Maybe you'll need to specify a few to completely ignore the big database.
也许您需要指定一些来完全忽略大数据库。
回答by Gerhard
I created the following one line solution avoiding multiple grep commands.
我创建了以下一行解决方案，避免了多个 grep 命令。
mysql -e "show databases;" | grep -Ev "Database|DatabaseToExclude1|DatabaseToExclude2" | xargs mysqldump --databases >mysql_dump_filename.sql
The -E in grep enables extended regex support which allowed to provide different matches separated by the pipe symbol "|". More options can be added to the mysqldump command. But only before the "--databases" parameter.
grep 中的 -E 启用扩展的正则表达式支持，允许提供由管道符号“|”分隔的不同匹配项。可以向 mysqldump 命令添加更多选项。但仅在“--databases”参数之前。
Little side note, i like to define the filename for the dump like this ...
小提示，我喜欢像这样定义转储的文件名......
... >mysql_dump_$(hostname)_$(date +%Y-%m-%d_%H-%M).sql
This will automatically ad the host name, date and time to the filename. :)
这将自动将主机名、日期和时间添加到文件名中。:)
回答by AeroX
Seeing as your using Windows you should have PowerShell available to use.
Here is a short PowerShell script to get a list of all Databases, remove unwanted ones from the list & then use mysqldump to backup the others.
在您使用 Windows 时，您应该可以使用 PowerShell。
这是一个简短的 PowerShell 脚本，用于获取所有数据库的列表，从列表中删除不需要的数据库，然后使用 mysqldump 备份其他数据库。
$MySQLPath = "."
$Hostname = "localhost"
$Username = "root"
$Password = ""
# Get list of Databases
$Databases = [System.Collections.Generic.List[String]] (
& $MySQLPath\mysql.exe -h"$Hostname" -u"$Username" -p"$Password" -B -N -e"show databases;"
)
# Remove databases from list we don't want
[void]$Databases.Remove("information_schema")
[void]$Databases.Remove("mysql")
# Dump database to .SQL file
& $MySQLPath\mysqldump.exe -h"$HostName" -u"$Username" -p"$Password" -B $($Databases) | Out-File "DBBackup.sql"
回答by Craig
Create a backup user and only grant that user access to the databases that you want to backup.
创建备份用户并仅授予该用户访问要备份的数据库的权限。
You still need to remember to explicitly grant the privileges but that can be done in the database and doesn't require a file to be edited.
您仍然需要记住明确授予权限，但这可以在数据库中完成并且不需要编辑文件。
回答by Adam Cain
It took me a lot of finagling to come up with this but I've used it for a few years now and it works well...
我花了很多时间才想出这个，但我已经用了几年了，而且效果很好......
mysql -hServerName -uUserName -pPassword -e "SELECT CONCAT('\nmysqldump -hServerName -uUserName -pPassword --set-gtid-purged=OFF --max_allowed_packet=2048M --single-transaction --add-drop-database --opt --routines --databases ',DBList,' | mysql -hServerName2 -uUserName2 -pPAssword2 ' ) AS Cmd FROM (SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') AS DBList FROM information_schema.SCHEMATA WHERE LEFT(schema_name, 8) <> 'cclegacy' AND schema_name NOT IN ('mysql','information_schema','performance_schema','test','external','othertoskip')) a \G" | cmd
Instead of the pipe over to mysql where I'm moving from serverName to Servername2 you could redirect to a file but this allows me to tailor what I move. Sometimes i even OR the list so I can say LIKE 'Prefix%' etc.
您可以重定向到一个文件，而不是连接到 mysql 的管道，在那里我从 serverName 移动到 Servername2，但这允许我定制我移动的内容。有时我什至 OR 列表，所以我可以说 LIKE 'Prefix%' 等。
