blocks|key|769812|text|MySQL管理员|type|unstyled|depth|inlineStyleRanges|entityRanges|data|769813|使用MySQL管理器，您需要手动将数据库添加到备份中。|769814|mysqldump和--all-database|769815|如果您通过dump-command实现此目的，则可以使用--all-databases选项将所有数据库都包含到转储中。|offset|length|style|CODE|769816|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option%255Fmysqldump|1|http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_all-databases^0|0|0|0|S|F|5|C|0|S|F|1|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Y|8|@$H|Z|I|10|J|K]]|9|@$H|11|I|12|1|13]|$H|14|I|15|1|16]]|A|$]]|$1|L|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]|T|$5|O|P|Q|A|$R|U]]]]

<h3>MySQL Administrator</h3>
With MySQL Administrator you need to manually add databases to the backup.
<h3>mysqldump and --all-databases</h3>
If you approach this through a <a href="http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option%5Fmysqldump" rel="noreferrer">dump-command</a>, you can use the <a href="http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_all-databases" rel="noreferrer"><code>--all-databases</code></a> option to include all databases into the dump.

blocks|key|769821|text|尝试使用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|769822|mysqldump+--all-databases|code-block|syntax|javascript|769823|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Try using

<pre><code>mysqldump --all-databases
</code></pre>

blocks|key|650965|text|mysqldump+-u+<username>+-p<password>+--all-databases+>+database_backup.sql|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|650966|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>mysqldump -u &lt;username&gt; -p&lt;password&gt; --all-databases &gt; database_backup.sql
</code></pre>

blocks|key|1954083|text|到目前为止发布的三个答案的问题是，它们不能让您有选择地恢复数据库。除了灾难，这在所有方面都可能是一个真正的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954084|理想情况下，您应该有一个每日备份，并具有一些历史记录。它应该是防弹的(--force)，它应该被记录(>>+...log)，它应该被压缩(%7C+gzip)，它应该保留每个数据库的单独副本，并且它应该自动拾取任何添加的数据库。|offset|length|style|CODE|1954085|更确切地说，可以考虑使用这样的脚本：|BOLD|1954086|#!/bin/bash

Host=server.domain.com
BDir=/home/backup/backup/mysql

Dump="/usr/bin/mysqldump+--skip-extended-insert+--force"
MySQL=/usr/bin/mysql

Today=$(date+"%2B%25a")

#+Get+a+list+of+all+databases
Databases=$(echo+"SHOW+DATABASES"+%7C+$MySQL+-h+$Host)


for+db+in+$Databases;+do
++++++++date=`date`
++++++++file="$BDir/$Host-$db-$Today.sql.gz"
++++++++echo+"Backing+up+'$db'+from+'$Host'+on+'$date'+to:+"
++++++++echo+"+++$file"
++++++++$Dump+-h+$Host+$db+%7C+gzip+>+$file
done|code-block|syntax|javascript|1954087|假设您有一个文件~/.my.cnf+(Chmod600)，它具有：|1954088|[client]
user+=+"BACKUP"
password+=+"SOMEPASS8342783492"|1954089|确保您用于BACKUP的任何用户都具有以下授权语句：|1954090|GRANT+
++SELECT,+SHOW+VIEW+ON+*.*+
++TO+BACKUP@localhost+
++IDENTIFIED+BY+'SOMEPASS8342783492';|1954091|因此，只需将其添加到每晚的cronjob中，您就有了一个每周7天轮换的每日备份。|1954092|0+3+*+*+*+++backup-mysql+>>+backup-mysql.log+2>>+backup-mysql.log|1954093|1954094|然后，备份目录包含：|1954095|-rw-r--r--+1+backup+backup+2217482184+Sep++3+13:35+base.appcove.net-VOS4_0-20090903.sql.gz
-rw-rw-r--+1+backup+backup+2505876287+Dec+25+00:48+base.appcove.net-VOS4_0-Fri.sql.gz
-rw-r--r--+1+backup+backup+2500384029+Dec+21+00:48+base.appcove.net-VOS4_0-Mon.sql.gz
-rw-r--r--+1+backup+backup+2506849331+Dec+26+00:48+base.appcove.net-VOS4_0-Sat.sql.gz
-rw-r--r--+1+backup+backup+2499859469+Dec+20+00:48+base.appcove.net-VOS4_0-Sun.sql.gz
-rw-rw-r--+1+backup+backup+2505046147+Dec+24+00:48+base.appcove.net-VOS4_0-Thu.sql.gz
-rw-rw-r--+1+backup+backup+2502277743+Dec+22+00:48+base.appcove.net-VOS4_0-Tue.sql.gz
-rw-r--r--+1+backup+backup+2504169910+Dec+23+00:48+base.appcove.net-VOS4_0-Wed.sql.gz
-rw-r--r--+1+backup+backup+++76983829+Dec+25+00:49+base.appcove.net-VOS4_Mail_0-Fri.sql.gz
-rw-r--r--+1+backup+backup+++76983829+Dec+21+00:49+base.appcove.net-VOS4_Mail_0-Mon.sql.gz
-rw-r--r--+1+backup+backup+++76983829+Dec+26+00:49+base.appcove.net-VOS4_Mail_0-Sat.sql.gz
-rw-r--r--+1+backup+backup+++76983829+Dec+20+00:48+base.appcove.net-VOS4_Mail_0-Sun.sql.gz
-rw-rw-r--+1+backup+backup+++76983829+Dec+24+00:49+base.appcove.net-VOS4_Mail_0-Thu.sql.gz
-rw-rw-r--+1+backup+backup+++76983829+Dec+22+00:49+base.appcove.net-VOS4_Mail_0-Tue.sql.gz
-rw-r--r--+1+backup+backup+++76983829+Dec+23+00:49+base.appcove.net-VOS4_Mail_0-Wed.sql.gz
-rw-r--r--+1+backup+backup++304803726+Dec+25+00:49+base.appcove.net-WeSell_0-Fri.sql.gz
-rw-r--r--+1+backup+backup++303480087+Dec+21+00:49+base.appcove.net-WeSell_0-Mon.sql.gz
-rw-r--r--+1+backup+backup++304710121+Dec+26+00:49+base.appcove.net-WeSell_0-Sat.sql.gz
-rw-r--r--+1+backup+backup++303791294+Dec+20+00:49+base.appcove.net-WeSell_0-Sun.sql.gz
-rw-rw-r--+1+backup+backup++305315415+Dec+24+00:49+base.appcove.net-WeSell_0-Thu.sql.gz
-rw-rw-r--+1+backup+backup++302516217+Dec+22+00:49+base.appcove.net-WeSell_0-Tue.sql.gz
-rw-r--r--+1+backup+backup++303314217+Dec+23+00:49+base.appcove.net-WeSell_0-Wed.sql.gz
-rw-r--r--+1+backup+backup+++++135301+Dec+25+00:30+dc40.appcove.net-mysql-Fri.sql.gz
-rw-r--r--+1+backup+backup+++++135301+Dec+21+00:30+dc40.appcove.net-mysql-Mon.sql.gz
-rw-r--r--+1+backup+backup+++++135301+Dec+26+00:30+dc40.appcove.net-mysql-Sat.sql.gz
-rw-r--r--+1+backup+backup+++++135301+Dec+20+00:30+dc40.appcove.net-mysql-Sun.sql.gz
-rw-rw-r--+1+backup+backup+++++135301+Dec+24+00:30+dc40.appcove.net-mysql-Thu.sql.gz
-rw-rw-r--+1+backup+backup+++++135301+Dec+22+00:30+dc40.appcove.net-mysql-Tue.sql.gz
-rw-r--r--+1+backup+backup+++++135301+Dec+23+00:30+dc40.appcove.net-mysql-Wed.sql.gz|1954096|entityMap^0|0|Z|7|1F|9|1X|6|0|F|3|0|0|8|9|0|0|5|6|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@$D|1A|E|1B|F|G]|$D|1C|E|1D|F|G]|$D|1E|E|1F|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|1G|8|@$D|1H|E|1I|F|J]]|9|@]|A|$]]|$1|K|3|L|5|M|7|1J|8|@]|9|@]|A|$N|O]]|$1|P|3|Q|5|6|7|1K|8|@$D|1L|E|1M|F|G]]|9|@]|A|$]]|$1|R|3|S|5|M|7|1N|8|@]|9|@]|A|$N|O]]|$1|T|3|U|5|6|7|1O|8|@$D|1P|E|1Q|F|G]]|9|@]|A|$]]|$1|V|3|W|5|M|7|1R|8|@]|9|@]|A|$N|O]]|$1|X|3|Y|5|6|7|1S|8|@]|9|@]|A|$]]|$1|Z|3|10|5|M|7|1T|8|@]|9|@]|A|$N|O]]|$1|11|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1V|8|@]|9|@]|A|$]]|$1|14|3|15|5|M|7|1W|8|@]|9|@]|A|$N|O]]|$1|16|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]]|17|$]]

The problem with the three answers posted so far is that they do not enable you to selectively restore the databases. This can be a real problem in all but a catastrophe. 

Ideally, you should have a daily backup, with some history. It should be bulletproof (<code>--force</code>), it should be logged (<code>&gt;&gt; ...log</code>), it should be compressed (<code>| gzip</code>), it should keep separate copies of each database, and it should automatically pick up any databases that are added.

Consider, rather, a shell script like this:

<pre><code>#!/bin/bash

Host=server.domain.com
BDir=/home/backup/backup/mysql

Dump="/usr/bin/mysqldump --skip-extended-insert --force"
MySQL=/usr/bin/mysql

Today=$(date "+%a")

# Get a list of all databases
Databases=$(echo "SHOW DATABASES" | $MySQL -h $Host)


for db in $Databases; do
 date=`date`
 file="$BDir/$Host-$db-$Today.sql.gz"
 echo "Backing up '$db' from '$Host' on '$date' to: "
 echo " $file"
 $Dump -h $Host $db | gzip &gt; $file
done
</code></pre>

Which is assuming that you have a file <code>~/.my.cnf</code> (chmod 600), that has:

<pre><code>[client]
user = "BACKUP"
password = "SOMEPASS8342783492"
</code></pre>

Make sure that whatever user you are using for <code>BACKUP</code> has this grant statement:

<pre><code>GRANT 
 SELECT, SHOW VIEW ON *.* 
 TO BACKUP@localhost 
 IDENTIFIED BY 'SOMEPASS8342783492';
</code></pre>

So simply add this to a nightly cronjob, and you have a daily backup that rotates each 7 days week.

<pre><code>0 3 * * * backup-mysql &gt;&gt; backup-mysql.log 2&gt;&gt; backup-mysql.log
</code></pre>

<hr />

The backup directory then contains:

<pre><code>-rw-r--r-- 1 backup backup 2217482184 Sep 3 13:35 base.appcove.net-VOS4_0-20090903.sql.gz
-rw-rw-r-- 1 backup backup 2505876287 Dec 25 00:48 base.appcove.net-VOS4_0-Fri.sql.gz
-rw-r--r-- 1 backup backup 2500384029 Dec 21 00:48 base.appcove.net-VOS4_0-Mon.sql.gz
-rw-r--r-- 1 backup backup 2506849331 Dec 26 00:48 base.appcove.net-VOS4_0-Sat.sql.gz
-rw-r--r-- 1 backup backup 2499859469 Dec 20 00:48 base.appcove.net-VOS4_0-Sun.sql.gz
-rw-rw-r-- 1 backup backup 2505046147 Dec 24 00:48 base.appcove.net-VOS4_0-Thu.sql.gz
-rw-rw-r-- 1 backup backup 2502277743 Dec 22 00:48 base.appcove.net-VOS4_0-Tue.sql.gz
-rw-r--r-- 1 backup backup 2504169910 Dec 23 00:48 base.appcove.net-VOS4_0-Wed.sql.gz
-rw-r--r-- 1 backup backup 76983829 Dec 25 00:49 base.appcove.net-VOS4_Mail_0-Fri.sql.gz
-rw-r--r-- 1 backup backup 76983829 Dec 21 00:49 base.appcove.net-VOS4_Mail_0-Mon.sql.gz
-rw-r--r-- 1 backup backup 76983829 Dec 26 00:49 base.appcove.net-VOS4_Mail_0-Sat.sql.gz
-rw-r--r-- 1 backup backup 76983829 Dec 20 00:48 base.appcove.net-VOS4_Mail_0-Sun.sql.gz
-rw-rw-r-- 1 backup backup 76983829 Dec 24 00:49 base.appcove.net-VOS4_Mail_0-Thu.sql.gz
-rw-rw-r-- 1 backup backup 76983829 Dec 22 00:49 base.appcove.net-VOS4_Mail_0-Tue.sql.gz
-rw-r--r-- 1 backup backup 76983829 Dec 23 00:49 base.appcove.net-VOS4_Mail_0-Wed.sql.gz
-rw-r--r-- 1 backup backup 304803726 Dec 25 00:49 base.appcove.net-WeSell_0-Fri.sql.gz
-rw-r--r-- 1 backup backup 303480087 Dec 21 00:49 base.appcove.net-WeSell_0-Mon.sql.gz
-rw-r--r-- 1 backup backup 304710121 Dec 26 00:49 base.appcove.net-WeSell_0-Sat.sql.gz
-rw-r--r-- 1 backup backup 303791294 Dec 20 00:49 base.appcove.net-WeSell_0-Sun.sql.gz
-rw-rw-r-- 1 backup backup 305315415 Dec 24 00:49 base.appcove.net-WeSell_0-Thu.sql.gz
-rw-rw-r-- 1 backup backup 302516217 Dec 22 00:49 base.appcove.net-WeSell_0-Tue.sql.gz
-rw-r--r-- 1 backup backup 303314217 Dec 23 00:49 base.appcove.net-WeSell_0-Wed.sql.gz
-rw-r--r-- 1 backup backup 135301 Dec 25 00:30 dc40.appcove.net-mysql-Fri.sql.gz
-rw-r--r-- 1 backup backup 135301 Dec 21 00:30 dc40.appcove.net-mysql-Mon.sql.gz
-rw-r--r-- 1 backup backup 135301 Dec 26 00:30 dc40.appcove.net-mysql-Sat.sql.gz
-rw-r--r-- 1 backup backup 135301 Dec 20 00:30 dc40.appcove.net-mysql-Sun.sql.gz
-rw-rw-r-- 1 backup backup 135301 Dec 24 00:30 dc40.appcove.net-mysql-Thu.sql.gz
-rw-rw-r-- 1 backup backup 135301 Dec 22 00:30 dc40.appcove.net-mysql-Tue.sql.gz
-rw-r--r-- 1 backup backup 135301 Dec 23 00:30 dc40.appcove.net-mysql-Wed.sql.gz
</code></pre>

blocks|key|1954111|text|我使用http://sourceforge.net/projects/automysqlbackup/备份我的MySQL数据库已经有几年了，它对我来说运行得很好。下面是sourceforge页面的描述：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1954112|mysqldump使用|1954113|对MySQL数据库进行每日、每周和每月备份的脚本。功能-备份多个数据库-单个备份文件或每个数据库的单独文件-压缩备份文件-备份远程服务器-电子邮件日志-更多..|blockquote|1954114|1954115|1954116|您可以将脚本设置为备份所有数据库，以便在添加新数据库时不必更改脚本。如果您有一个或两个出于某种原因不想备份的数据库，您还可以告诉它要排除哪个数据库。|1954117|它有很好的文档记录，您可以设置许多选项来满足大多数基本的数据库备份需求。因为它是一个单独的bash脚本，所以如果有些东西不是您想要的样子，也可以很容易地修改/调整。|1954118|entityMap|0|LINK|mutability|MUTABLE|url|http://sourceforge.net/projects/automysqlbackup/^0|3|1C|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|12|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|13|8|@]|9|@]|C|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|C|$]]|$1|O|3|-4|5|6|7|16|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

I've been using <a href="http://sourceforge.net/projects/automysqlbackup/" rel="nofollow noreferrer">http://sourceforge.net/projects/automysqlbackup/</a> to backup my MySQL databases for a couple years now and it's worked very well for me. Here's the description from the sourceforge page:

<blockquote>
 A script to take daily, weekly and
 monthly backups of your MySQL
 databases using mysqldump. Features -
 Backup mutiple databases - Single
 backup file or to a seperate file for
 each DB - Compress backup files -
 Backup remote servers - E-mail logs -
 More..
</blockquote>

You can set the script to backup all databases so you don't have to change the script when new dbs are added. You can also tell it which dbs to exclude if you have a database or two that you don't want to backup for some reason. 

It's well documented and there are lots of options that you can set that will cover most basic db backup needs. Since it's a single bash script, it's also easy to modify/tweak if something's not quite how you want it to be.

blocks|key|2178762|text|这只是我的两个观点。从@gahooa升级excelent脚本|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2178763|它增加了授权保存。如果您想保存权限和一些改进和值检查，则非常有用。还包括user和pass的用法(如果提供)。|2178764|希望能有所帮助。|2178765|UDATE:为每个文件添加sha1+sums|2178766|#!/bin/bash
#+
#+Script+to+backup+database+and+grants+from+mysql+
#+Author:+Gonzalo+Aguilar+Delgado+<gaguilar+indomain+level2crm.com>+
#+Usage:
#+++++++backup-mysql.sh+<HOST>+username+password+++<---+Yes+this+is+insecure+as+this+will+get+on+history
#+Based+on+script+http://stackoverflow.com/questions/1963884/how-to-automatically-backup-all-mysql-databases-into-sql-statement
#+from+gahooa+(http://stackoverflow.com/users/64004/gahooa)
#


if+[+"$#"+-lt+1+];+then
++++FILENAME="$(basename+$0)"
++++echo+"Usage+$FILENAME+<HOST>+[user]+[pass]"
++++exit+1
fi

#http://stackoverflow.com/questions/5947742/how-to-change-the-output-color-of-echo-in-linux
COLOR_GREEN='\e[0;32m'
COLOR_RED='\e[0;31m'
COLOR_BLACK='\e[0;30m'
COLOR_DISABLE='\e[0m'

HOST=$1

if+[+!+-z+"$2"+];+then+
++++USER=$2
fi
PASS=$3
BACKUP_DIRECTORY="/home/gaguilar/mysql-backup"

if+[+!+-x+$BACKUP_DIRECTORY+];+then
++++echo+"Cannot+access+to+$BACKUP_DIRECTORY"
++++exit+1
fi

DUMP_COMMAND="/usr/bin/mysqldump+--skip-extended-insert+--force"
MYSQL_COMMAND=/usr/bin/mysql

TODAY_DATE=$(date+"%2B%25F")

COMMAND_ARGS="-h$HOST"

if+[+!+-z+"$USER"+];+then
++++COMMAND_ARGS="$COMMAND_ARGS+-u$USER"+
fi

if+[+!+-z+"$PASS"+];+then
++++COMMAND_ARGS="$COMMAND_ARGS+-p$PASS"+
fi


echo+-e+"Connecting+to+${COLOR_BLACK}$HOST${COLOR_DISABLE}+with+user+${COLOR_BLACK}$USER${COLOR_DISABLE}..."
#+Get+a+list+of+all+databases
DB_LIST=$(echo+"SHOW+DATABASES"+%7C+$MYSQL_COMMAND+$COMMAND_ARGS)

if+[+"$?"+-ne+"0"+];+then
++++echo+"Cannot+connect"
++++exit+1
fi

echo+"Starting+backup"
for+db+in+$DB_LIST;+do
++++if+[+"$db"+!=+"information_schema"+]+&&+[+"$db"+!=+"mysql"+]+&&+[+"$db"+!=+"performance_schema"+];+then
++++++++++++date=`date`
++++++++++++file="$BACKUP_DIRECTORY/$HOST-$db-$TODAY_DATE.sql.bz2"
++++++++++++file_sum=$BACKUP_DIRECTORY/$HOST-$db-$TODAY_DATE.sql.bz2.sha1
++++++++++++echo+"Backing+up+'$db'+from+'$HOST'+on+'$date'+to:+"
++++++++++++echo+"+++[$file]..."
++++++++++++$DUMP_COMMAND+$COMMAND_ARGS+$db+%7C+bzip2+>+$file
++++++++++++sha1sum+$file+>+$file_sum
++++++++++++if+[+"$?"+-ne+"0"+];+then
++++++++++++++++++++echo+"Backup+of+'$db'+from+'$HOST'+${COLOR_RED}failed${COLOR_DISABLE}..."
++++++++++++++++fi
++++else
++++++++++++++++echo+"Skipping+$db+database+because+mysql+internal"
++++fi
done
echo+-e+"${COLOR_GREEN}Done+backup+databases${COLOR_DISABLE}"

echo+"Backing+up+privileges+of+users"
USER_LIST=$(echo+"SELECT+CONCAT('\'',user,'\'@\'',host,'\'')+FROM+mysql.user+WHERE+user+!=+'root'"+%7C+$MYSQL_COMMAND+-BN+$COMMAND_ARGS)
grants_file="$BACKUP_DIRECTORY/$HOST-grants-$TODAY_DATE.sql"

echo+"--+GRANTS+FOR+$HOST+at+$TODAY_DATE"+>+$grants_file
for+username+in+$USER_LIST;+do
++++echo+"Backing+up+$username"
++++GRANT_LIST=$(echo+"show+grants+for+$username"+%7C+$MYSQL_COMMAND+-BN+$COMMAND_ARGS+%7C+sed+'s/$/;/;+s/\\\\/\\/g';)+#+
++++echo+""+>>+$grants_file
++++echo+"--+"+>>+$grants_file
++++echo+"--+BACKUP+OF+USERNAME+[$username]"+>>+$grants_file
++++echo+"--+"+>>+$grants_file
++++echo+"$GRANT_LIST"+>>+$grants_file
done

echo+-e+"${COLOR_GREEN}Done+backup+users${COLOR_DISABLE}"|code-block|syntax|javascript|2178767|授权文件的输出如下所示：|2178768|--+GRANTS+FOR+localhost+at+2014-10-15

--+
--+BACKUP+OF+USERNAME+['java'@'%25']
--+
GRANT+USAGE+ON+*.*+TO+'java'@'%25'+IDENTIFIED+BY+PASSWORD+'XXX';
GRANT+SELECT,+INSERT,+UPDATE,+DELETE,+CREATE,+DROP,+REFERENCES,+INDEX,+ALTER,+CREATE+TEMPORARY+TABLES,+LOCK+TABLES,+EXECUTE,+CREATE+VIEW,+SHOW+VIEW,+CREATE+ROUTINE,+ALTER+ROUTINE,+TRIGGER+ON+`widgetdb`.*+TO+'java'@'%25';

--+
--+BACKUP+OF+USERNAME+['planes2'@'%25']
--+
GRANT+USAGE+ON+*.*+TO+'planes2'@'%25'+IDENTIFIED+BY+PASSWORD+'XXX';
GRANT+ALL+PRIVILEGES+ON+`planes2`.*+TO+'planes2'@'%25';|2178769|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|W|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|Y|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Just my two cents. Upgrading the excelent script from @gahooa

It adds grant save. Usefull if you want to save permissions and a few of improvements and value checking. Also usage of user and pass if provided. 

Hope it helps.

UDATE: Added sha1 sums for each file

<pre><code>#!/bin/bash
# 
# Script to backup database and grants from mysql 
# Author: Gonzalo Aguilar Delgado &lt;gaguilar indomain level2crm.com&gt; 
# Usage:
# backup-mysql.sh &lt;HOST&gt; username password &lt;--- Yes this is insecure as this will get on history
# Based on script http://stackoverflow.com/questions/1963884/how-to-automatically-backup-all-mysql-databases-into-sql-statement
# from gahooa (http://stackoverflow.com/users/64004/gahooa)
#


if [ "$#" -lt 1 ]; then
 FILENAME="$(basename $0)"
 echo "Usage $FILENAME &lt;HOST&gt; [user] [pass]"
 exit 1
fi

#http://stackoverflow.com/questions/5947742/how-to-change-the-output-color-of-echo-in-linux
COLOR_GREEN='\e[0;32m'
COLOR_RED='\e[0;31m'
COLOR_BLACK='\e[0;30m'
COLOR_DISABLE='\e[0m'

HOST=$1

if [ ! -z "$2" ]; then 
 USER=$2
fi
PASS=$3
BACKUP_DIRECTORY="/home/gaguilar/mysql-backup"

if [ ! -x $BACKUP_DIRECTORY ]; then
 echo "Cannot access to $BACKUP_DIRECTORY"
 exit 1
fi

DUMP_COMMAND="/usr/bin/mysqldump --skip-extended-insert --force"
MYSQL_COMMAND=/usr/bin/mysql

TODAY_DATE=$(date "+%F")

COMMAND_ARGS="-h$HOST"

if [ ! -z "$USER" ]; then
 COMMAND_ARGS="$COMMAND_ARGS -u$USER" 
fi

if [ ! -z "$PASS" ]; then
 COMMAND_ARGS="$COMMAND_ARGS -p$PASS" 
fi


echo -e "Connecting to ${COLOR_BLACK}$HOST${COLOR_DISABLE} with user ${COLOR_BLACK}$USER${COLOR_DISABLE}..."
# Get a list of all databases
DB_LIST=$(echo "SHOW DATABASES" | $MYSQL_COMMAND $COMMAND_ARGS)

if [ "$?" -ne "0" ]; then
 echo "Cannot connect"
 exit 1
fi

echo "Starting backup"
for db in $DB_LIST; do
 if [ "$db" != "information_schema" ] &amp;&amp; [ "$db" != "mysql" ] &amp;&amp; [ "$db" != "performance_schema" ]; then
 date=`date`
 file="$BACKUP_DIRECTORY/$HOST-$db-$TODAY_DATE.sql.bz2"
 file_sum=$BACKUP_DIRECTORY/$HOST-$db-$TODAY_DATE.sql.bz2.sha1
 echo "Backing up '$db' from '$HOST' on '$date' to: "
 echo " [$file]..."
 $DUMP_COMMAND $COMMAND_ARGS $db | bzip2 &gt; $file
 sha1sum $file &gt; $file_sum
 if [ "$?" -ne "0" ]; then
 echo "Backup of '$db' from '$HOST' ${COLOR_RED}failed${COLOR_DISABLE}..."
 fi
 else
 echo "Skipping $db database because mysql internal"
 fi
done
echo -e "${COLOR_GREEN}Done backup databases${COLOR_DISABLE}"

echo "Backing up privileges of users"
USER_LIST=$(echo "SELECT CONCAT('\'',user,'\'@\'',host,'\'') FROM mysql.user WHERE user != 'root'" | $MYSQL_COMMAND -BN $COMMAND_ARGS)
grants_file="$BACKUP_DIRECTORY/$HOST-grants-$TODAY_DATE.sql"

echo "-- GRANTS FOR $HOST at $TODAY_DATE" &gt; $grants_file
for username in $USER_LIST; do
 echo "Backing up $username"
 GRANT_LIST=$(echo "show grants for $username" | $MYSQL_COMMAND -BN $COMMAND_ARGS | sed 's/$/;/; s/\\\\/\\/g';) # 
 echo "" &gt;&gt; $grants_file
 echo "-- " &gt;&gt; $grants_file
 echo "-- BACKUP OF USERNAME [$username]" &gt;&gt; $grants_file
 echo "-- " &gt;&gt; $grants_file
 echo "$GRANT_LIST" &gt;&gt; $grants_file
done

echo -e "${COLOR_GREEN}Done backup users${COLOR_DISABLE}"
</code></pre>

The output of the grant file is like this one:

<pre><code>-- GRANTS FOR localhost at 2014-10-15

-- 
-- BACKUP OF USERNAME ['java'@'%']
-- 
GRANT USAGE ON *.* TO 'java'@'%' IDENTIFIED BY PASSWORD 'XXX';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `widgetdb`.* TO 'java'@'%';

-- 
-- BACKUP OF USERNAME ['planes2'@'%']
-- 
GRANT USAGE ON *.* TO 'planes2'@'%' IDENTIFIED BY PASSWORD 'XXX';
GRANT ALL PRIVILEGES ON `planes2`.* TO 'planes2'@'%';
</code></pre>

blocks|key|2178823|text|我做了一个脚本，这将采取除information_schema和performance_schema以外的所有数据库备份。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2178824|将创建date+and+time+wise文件夹，保存数据库的db文件夹名和gunzip。我今天创建了它，并发现工作100%25正常|2178825|USER=root
PASSWORD='password'

#Place+where+you+want+to+preserved+backup.
OUTPUT="."

TIMESTAMP=`date+%2B%25Y-%25m-%25d_%25H-%25M-%25S`;
mkdir+$OUTPUT/$TIMESTAMP;
cd+$OUTPUT/$TIMESTAMP;
echo+"Starting+MySQL+Backup";
echo+'date';
databases=$(mysql+-u+$USER+-p$PASSWORD+-e+"SHOW+DATABASES;"+%7C+tr+-d+"%7C+"+%7C+grep+-Ev+"(Database%7Cinformation_schema%7Cperformance_schema)");

echo+$databases;


for+db+in+$databases;+do
++++++++mysqldump+--force+--opt+-u+$USER+-p$PASSWORD+--databases+$db+>+$OUTPUT/dbbackup-$TIMESTAMP-$db.sql
++++++++gzip+$OUTPUT/dbbackup-$TIMESTAMP-$db.sql
done
echo+"Finished+MySQL+Backup";
echo+'date';|code-block|syntax|javascript|2178826|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I made a script , which will take all database backup expect information_schema and performance_schema.

Will create date and time wise folder , save database its db folder name wise and gunzip . I created it today and found working 100% ok 

<pre><code>USER=root
PASSWORD='password'

#Place where you want to preserved backup.
OUTPUT="."

TIMESTAMP=`date +%Y-%m-%d_%H-%M-%S`;
mkdir $OUTPUT/$TIMESTAMP;
cd $OUTPUT/$TIMESTAMP;
echo "Starting MySQL Backup";
echo 'date';
databases=$(mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -Ev "(Database|information_schema|performance_schema)");

echo $databases;


for db in $databases; do
 mysqldump --force --opt -u $USER -p$PASSWORD --databases $db &gt; $OUTPUT/dbbackup-$TIMESTAMP-$db.sql
 gzip $OUTPUT/dbbackup-$TIMESTAMP-$db.sql
done
echo "Finished MySQL Backup";
echo 'date';
</code></pre>

blocks|key|1954197|text|回答晚了，但就像它得到的一样简单：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954198|1954199|您需要更改第3、4和5行目录以反映您要存储转储的MySQL+用户目录、密码目录和<代码>E210目录。|ordered-list-item|offset|length|style|CODE|BOLD|1954200|每次运行时，它都会删除所有以前的备份(如果您不想这样做，只需注释+#rm+"$OUTPUT/*gz"+>+/dev/null+2>&1)|1954201|1954202|MySqlBackup.sh|1954203|#!/bin/bash
+
USER="your_user"
PASSWORD="your_password"
OUTPUT="/path/to/backup/dir"
+
rm+"$OUTPUT/*gz"+>+/dev/null+2>&1
+
databases=`mysql+--user=$USER+--password=$PASSWORD+-e+"SHOW+DATABASES;"+%7C+tr+-d+"%7C+"+%7C+grep+-v+Database`
+
for+db+in+$databases;+do
++++if+[[+"$db"+!=+"information_schema"+]]+;+then
++++++++echo+"Dumping+database:+$db"
++++++++mysqldump+--force+--opt+--user=$USER+--password=$PASSWORD+--databases+$db+>+$OUTPUT/`date+%2B%25Y%25m%25d`.$db.sql
++++++++gzip+$OUTPUT/`date+%2B%25Y%25m%25d`.$db.sql
++++fi
done|code-block|syntax|javascript|1954204|1954205|1954206|将其设置为可执行文件并运行：|1954207|chmod+700+MySqlBackup.sh
./MySqlBackup.sh|1954208|如果需要，将其添加到crontab中，以便它可以自动运行：|1954209|crontab+-e
00+02+*+*+*+/path/to/MySqlBackup.sh|1954210|在这种情况下，它将在每天凌晨2点运行您可以了解有关crontab+here的更多信息。|1954211|src|1954212|entityMap|0|LINK|mutability|MUTABLE|url|http://unixgeeks.org/security/newbie/unix/cron-1.html|1|http://danieldvork.in/script-for-mysql-backup-to-dropbox/^0|0|0|O|5|U|L|0|0|W|X|Z|X|Y|0|0|0|E|0|0|0|0|0|E|0|0|0|T|A|7|0|0|0|17|X|4|0|0|0|3|0|3|1|0^^$0|@$1|2|3|4|5|6|7|1I|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1K|8|@$F|1L|G|1M|H|I]|$F|1N|G|1O|H|J]]|9|@]|A|$]]|$1|K|3|L|5|E|7|1P|8|@$F|1Q|G|1R|H|J]|$F|1S|G|1T|H|J]|$F|1U|G|1V|H|I]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1W|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1X|8|@$F|1Y|G|1Z|H|J]]|9|@]|A|$]]|$1|P|3|Q|5|R|7|20|8|@]|9|@]|A|$S|T]]|$1|U|3|-4|5|6|7|21|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|22|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|23|8|@$F|24|G|25|H|J]]|9|@]|A|$]]|$1|Y|3|Z|5|R|7|26|8|@]|9|@]|A|$S|T]]|$1|10|3|11|5|6|7|27|8|@$F|28|G|29|H|J]|$F|2A|G|2B|H|I]]|9|@]|A|$]]|$1|12|3|13|5|R|7|2C|8|@]|9|@]|A|$S|T]]|$1|14|3|15|5|6|7|2D|8|@$F|2E|G|2F|H|J]]|9|@$F|2G|G|2H|1|2I]]|A|$]]|$1|16|3|17|5|6|7|2J|8|@$F|2K|G|2L|H|J]]|9|@$F|2M|G|2N|1|2O]]|A|$]]|$1|18|3|-4|5|6|7|2P|8|@]|9|@]|A|$]]]|19|$1A|$5|1B|1C|1D|A|$1E|1F]]|1G|$5|1B|1C|1D|A|$1E|1H]]]]

Late answer, but as simple as it gets:
<ol>
<li>You need to change lines 3, 4 and 5 to reflect your <code>MySQL</code>
user, password and dir where you want to store the dumps.</li>
<li>Every time it runs, it’ll delete all the previous backups (If you don’t
want this, just comment <code>#rm &quot;$OUTPUT/*gz&quot; &gt; /dev/null 2&gt;&amp;1</code>)</li>
</ol>
MySqlBackup.sh
<pre><code>#!/bin/bash
 
USER=&quot;your_user&quot;
PASSWORD=&quot;your_password&quot;
OUTPUT=&quot;/path/to/backup/dir&quot;
 
rm &quot;$OUTPUT/*gz&quot; &gt; /dev/null 2&gt;&amp;1
 
databases=`mysql --user=$USER --password=$PASSWORD -e &quot;SHOW DATABASES;&quot; | tr -d &quot;| &quot; | grep -v Database`
 
for db in $databases; do
 if [[ &quot;$db&quot; != &quot;information_schema&quot; ]] ; then
 echo &quot;Dumping database: $db&quot;
 mysqldump --force --opt --user=$USER --password=$PASSWORD --databases $db &gt; $OUTPUT/`date +%Y%m%d`.$db.sql
 gzip $OUTPUT/`date +%Y%m%d`.$db.sql
 fi
done
</code></pre>
<hr />
<hr />
Make it executable and run it:
<pre><code>chmod 700 MySqlBackup.sh
./MySqlBackup.sh
</code></pre>
If needed, add it to the <code>crontab</code> so it’ll run automagically:
<pre><code>crontab -e
00 02 * * * /path/to/MySqlBackup.sh
</code></pre>
In this case, it’ll run every day at 2 AM. You can learn more about crontab <a href="http://unixgeeks.org/security/newbie/unix/cron-1.html" rel="nofollow noreferrer">here</a>.
<a href="http://danieldvork.in/script-for-mysql-backup-to-dropbox/" rel="nofollow noreferrer">src</a>

blocks|key|770137|text|要忽略错误，请使用--force选项|type|unstyled|depth|inlineStyleRanges|entityRanges|data|770138|mysqldump+-u+root+--force+--all-databases+>+d:\all.sql|code-block|syntax|javascript|770139|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To ignore the errors use --force option

<pre><code>mysqldump -u root --force --all-databases &gt; d:\all.sql
</code></pre>

MySQL Administrator> Backup Project. It is a great tool to allow you to select databases, and schedule it.

However my issue is:

Today got a few new websites (new database created)
Tomorrow got a few more new websites (new databases created)

In this case, I have to always go into Backup Project> Select those remaining schema not in the backup list...

How to make it auto check for any new databases and include in the backup schedule list?

In other word, how to automate the backup of all the mysql databases (so that we don't need to worry when there's new databases created everyday).

Any way to make it happen?

I'm using Windows 2008 server, which is not sh friendly.

How to automatically backup ALL mysql databases into sql statement?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

MySQL Administrator>备份项目。这是一个很棒的工具，可以让你选择数据库，并对其进行调度。然而，我的问题是：今天有几个新网站(创建了新数据库)明天有更多新网站(创建了新数据库)在这种情况下，我必须始终进入Backup Project>选择那些不在备份列表中的剩余架构...如何让它自动检查任何新数据库并将...

问如何将所有mysql数据库自动备份成sql语句？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将所有mysql数据库自动备份成sql语句？EN