bash mysqldump 与 db 在一个单独的文件中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10867520/
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
mysqldump with db in a separate file
提问by resting
I'm writing a single line command that backups all databases into their respective names instead using of dumping all in one sql.
我正在编写一个单行命令，将所有数据库备份到它们各自的名称中，而不是使用一个 sql 全部转储。
Eg: db1 get saved to db1.sql and db2 gets saved to db2.sql
例如：db1 被保存到 db1.sql 并且 db2 被保存到 db2.sql
So far, I'd gathered the following commands to retrieve all databases.
到目前为止，我已经收集了以下命令来检索所有数据库。
mysql -uuname -ppwd -e 'show databases' | grep -v 'Database'
I'm planning to pipe it with awk to do something like
我打算用 awk 用管道来做类似的事情
awk '{mysqldump -uuname -ppwd > .sql}'
But that doesn't work.
但这不起作用。
I'm new to bash, so I could be wrong in my thinking.
What should I do to make it export the db in their respective names?
我是 bash 新手，所以我的想法可能是错误的。
我该怎么做才能使其以各自的名称导出数据库？
update:
Ok, have to finally managed to get it working from the hints below.
This is the final script
更新：
好的，必须最终设法从下面的提示中使其工作。
这是最终的脚本
# replace [] with your own config
# replace own dir to save
# echo doesn't work. hmm...
mysql -u[uname] -p'[pwd]' -e "show databases" \
| grep -Ev 'Database|information_schema' \
| while read dbname; \
do \
echo 'Dumping $dbname' \
mysqldump -u[uanme] -p'[pwd]' $dbname > ~/db_backup/$dbname.sql;\
done
The echoing part of doesn't work though.
的呼应部分虽然不起作用。
回答by ziad-saab
mysql -uroot -N -e 'show databases' | while read dbname; do mysqldump -uroot --complete-insert --some-other-options "$dbname" > "$dbname".sql; done
回答by Bob Siefkes
Creating backups per database is indeed much more efficient. Not only easier to restore once needed, but also I experienced that making a backup of the whole database would break in case one table was broken/corrupt. And by creating backups per database it will only break for that database and the rest is still valid.
为每个数据库创建备份确实效率更高。不仅在需要时更容易恢复，而且我还体验到，如果一个表损坏/损坏，备份整个数据库会中断。通过为每个数据库创建备份，它只会破坏该数据库，其余的仍然有效。
The oneliner we created to backup our mysql databases is:
我们创建的用于备份 mysql 数据库的 oneliner 是：
mysql -s -r -u bupuser -pSecret -e 'show databases' | while read db; do mysqldump -u bupuser -pSecret $db -r /var/db-bup/${db}.sql; [[ $? -eq 0 ]] && gzip /var/db-bup/${db}.sql; done
Best to create a new readonly mysql user 'bupuser' with passsword 'Secret' (change!). It will first retrieve the list of databases. Then loop and for each database create a dump.sql file to /var/db-bup (you can change). And only when there are no errors encountered then also gzip the file which will really drastically save storage. When some databases encountered errors then you will see the .sql file and not the .sql.qz file.
最好创建一个新的只读 mysql 用户 'bupuser'，密码为 'Secret'（更改！）。它将首先检索数据库列表。然后循环并为每个数据库创建一个 dump.sql 文件到 /var/db-bup （您可以更改）。并且只有当没有遇到错误时，才会对文件进行 gzip 压缩，这将真正大大节省存储空间。当某些数据库遇到错误时，您将看到 .sql 文件而不是 .sql.qz 文件。
回答by WonderLand
Here an easy script that will:
这是一个简单的脚本，它将：
- dump all DB a compress the output ->
SCHEMA_NAME.sql.gz - use [autocommit/unique_checks/foreign_key_checks] to speed up import
- exclude default schemas
- 转储所有数据库 a 压缩输出 ->
SCHEMA_NAME.sql.gz - 使用 [autocommit/unique_checks/foreign_key_checks] 加速导入
- 排除默认模式
File: Dump_all.sh
文件：Dump_all.sh
How to use:
./Dump_all.sh -> will dump all DB
./Dump_all.sh SCHEMA_NAME -> will dump SCHEMA_NAME DB
如何使用：
./Dump_all.sh -> will dump all DB
./Dump_all.sh SCHEMA_NAME -> will dump SCHEMA_NAME DB
#!/bin/bash
MYSQL_USER="root"
MYSQL_PASS="YOUR_PASS"
echo "-- START --"
echo "SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;" > tmp_sqlhead.sql
echo "SET autocommit=1;SET unique_checks=1;SET foreign_key_checks=1;" > tmp_sqlend.sql
if [ -z "" ]
then
echo "-- Dumping all DB ..."
for I in $(mysql -u $MYSQL_USER --password=$MYSQL_PASS -e 'show databases' -s --skip-column-names);
do
if [ "$I" = information_schema ] || [ "$I" = mysql ] || [ "$I" = phpmyadmin ] || [ "$I" = performance_schema ] # exclude this DB
then
echo "-- Skip $I ..."
continue
fi
echo "-- Dumping $I ..."
# Pipe compress and concat the head/end with the stoutput of mysqlump ( '-' cat argument)
mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $I | cat tmp_sqlhead.sql - tmp_sqlend.sql | gzip -fc > "$I.sql.gz"
done
else
I=;
echo "-- Dumping $I ..."
# Pipe compress and concat the head/end with the stoutput of mysqlump ( '-' cat argument)
mysqldump -u $MYSQL_USER --password=$MYSQL_PASS $I | cat tmp_sqlhead.sql - tmp_sqlend.sql | gzip -fc > "$I.sql.gz"
fi
# remove tmp files
rm tmp_sqlhead.sql
rm tmp_sqlend.sql
echo "-- FINISH --"
回答by Tom Berghuis
Here is what worked for me
这对我有用
mysql -s -r -uroot -e 'show databases' -N | while read dbname; do
mysqldump -uroot --complete-insert --single-transaction "$dbname" > "$dbname".sql;
done
回答by Yasir Elsharif
This is what I am using, it's very simple and works fine for me.
这就是我正在使用的，它非常简单，对我来说效果很好。
mysql --skip-column-names -u root -p -e 'show databases' | while read dbname; do mysqldump --lock-all-tables -u root -p "$dbname"> "$(date +%Y%m%d)-$dbname".sql; done
mysql --skip-column-names -u root -p -e 'show databases' | while read dbname; do mysqldump --lock-all-tables -u root -p "$dbname"> "$(date +%Y%m%d)-$dbname".sql; done
With compression option:
带压缩选项：
mysql --skip-column-names -u root -p -e 'show databases' | while read dbname; do mysqldump --lock-all-tables -u root -p "$dbname" | gzip> /tmp/"$(date +%Y%m%d)-$dbname".sql.gz; done
mysql --skip-column-names -u root -p -e 'show databases' | while read dbname; do mysqldump --lock-all-tables -u root -p "$dbname" | gzip> /tmp/"$(date +%Y%m%d)-$dbname".sql.gz; done
If you didn't add the password in the command, you need to type it one plus the total number of the databases you have.
如果您没有在命令中添加密码，则需要键入密码加上您拥有的数据库总数。
回答by Jeshurun
Not an answer to your question, but take a look at the AutoMySQLBackupproject on Sourceforge, instead of re-inventing the wheel. It does what you want, and offers a ton of additional features on top, including compression, encryption, rotation, and email notifications. I used it a while back and it worked really well.
不是您问题的答案，而是查看Sourceforge上的AutoMySQLBackup项目，而不是重新发明轮子。它可以满足您的需求，并提供大量附加功能，包括压缩、加密、轮换和电子邮件通知。我用了一段时间，效果非常好。
回答by BLaZuRE
It appears fine. The only thing I can find at the moment (without testing) is that you're missing a semicolong after Show Tables.
看起来不错。我目前唯一能找到的（未经测试）是您在Show Tables之后缺少一个分号。
回答by Bram
While looking for available packages for the AutoMySQLBackup project suggested by @Jeshurun I came accross Holland.
在为@Jeshurun 建议的 AutoMySQLBackup 项目寻找可用的软件包时，我遇到了Holland。
Intrigued by the name (I live in Belgium to the South of The Netherlands, sometimes - or better some parts - referred to as "Holland"), I decided to check it out. Perhaps it can help you as well.
对这个名字很感兴趣（我住在荷兰南部的比利时，有时 - 或者更好的某些部分 - 被称为“荷兰”），我决定去看看。也许它也可以帮助你。
