MySQL 如何自动将所有mysql数据库备份到sql语句中？

Question

提问by i need help

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

MySQL 管理员> 备份项目。它是一个很棒的工具，可以让您选择数据库并安排它。

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).

换句话说，如何自动备份所有mysql数据库（这样我们就不必担心每天创建新数据库时）。

Any way to make it happen?

有什么办法让它发生吗？

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

我正在使用 Windows 2008 服务器，这对 sh 不友好。

Answer 1

回答by gahooa

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 (--force), it should be logged (>> ...log), it should be compressed (| gzip), it should keep separate copies of each database, and it should automatically pick up any databases that are added.

理想情况下，您应该每天备份，并有一些历史记录。它应该是防弹的（--force），应该被记录（>> ...log），应该被压缩（| gzip），它应该保留每个数据库的单独副本，并且它应该自动选择添加的任何数据库。

Consider, rather, a shell script like this:

相反，请考虑这样的 shell 脚本：

#!/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 > $file
done

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

假设您有一个文件~/.my.cnf（chmod 600），它具有：

[client]
user = "BACKUP"
password = "SOMEPASS8342783492"

Make sure that whatever user you are using for BACKUPhas this grant statement:

确保您使用的任何用户BACKUP都有此授权声明：

GRANT 
  SELECT, SHOW VIEW ON *.* 
  TO BACKUP@localhost 
  IDENTIFIED BY 'SOMEPASS8342783492';

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

因此，只需将其添加到每晚的 cronjob 中，您就有了一个每周 7 天轮换一次的每日备份。

0 3 * * *   backup-mysql >> backup-mysql.log 2>> backup-mysql.log

The backup directory then contains:

然后备份目录包含：

-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

Answer 2

回答by Sampson

MySQL Administrator

MySQL 管理员

With MySQL Administrator you need to manually add databases to the backup.

使用 MySQL Administrator，您需要手动将数据库添加到备份中。

mysqldump and --all-databases

mysqldump 和 --all-databases

If you approach this through a dump-command, you can use the --all-databasesoption to include all databases into the dump.

如果您通过dump-command处理此问题，则可以使用该--all-databases选项将所有数据库包含到转储中。

Answer 3

回答by Dan Loewenherz

mysqldump -u <username> -p<password> --all-databases > database_backup.sql

Answer 4

回答by bradym

I've been using http://sourceforge.net/projects/automysqlbackup/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:

几年来我一直在使用http://sourceforge.net/projects/automysqlbackup/来备份我的 MySQL 数据库，它对我来说效果很好。以下是 sourceforge 页面的描述：

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..

使用 mysqldump 对 MySQL 数据库进行每日、每周和每月备份的脚本。功能 - 备份多个数据库 - 单个备份文件或每个数据库的单独文件 - 压缩备份文件 - 备份远程服务器 - 电子邮件日志 - 更多..

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.

它有据可查，您可以设置许多选项来满足最基本的数据库备份需求。由于它是一个单独的 bash 脚本，因此如果某些内容与您希望的不同，也很容易修改/调整。

Answer 5

回答by shylent

Try using

尝试使用

mysqldump --all-databases

Answer 6

回答by CONvid19

Late answer, but as simple as it gets:

迟到的答案，但就这么简单：

You need to change lines 3, 4 and 5to reflect your MySQLuser, passwordand dirwhere you want to store the dumps.
Every time it runs, it'll delete all the previous backups(If you don't want this, just comment#rm "$OUTPUT/*gz" > /dev/null 2>&1)

您需要更改第 3、4 和 5 行以反映您要存储转储的MySQL用户、密码和目录。
每次运行时，它会删除所有以前的备份（如果你不想要这个，只需评论#rm "$OUTPUT/*gz" > /dev/null 2>&1）

MySqlBackup.sh

数据库备份文件

#!/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;" | tr -d "| " | 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 +%Y%m%d`.$db.sql
        gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

Now you just need to make it executable and run it:

现在你只需要让它可执行并运行它：

chmod 700 MySqlBackup.sh
./MySqlBackup.sh

If needed, add it to the crontabso it'll run automagically:

如果需要，将它添加到crontab以便它会自动运行：

crontab -e
00 02 * * * /path/to/MySqlBackup.sh

In this case, it'll run every day at 2 AM. You can learn more about crontab here.

在这种情况下，它将在每天凌晨 2 点运行。您可以在此处了解有关 crontab 的更多信息。

src

源文件

Answer 7

回答by Maximus

To ignore the errors use --force option

要忽略错误，请使用 --force 选项

mysqldump -u root --force --all-databases > d:\all.sql

Answer 8

回答by Gonzalo Aguilar Delgado

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

只有我的两分钱。从@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

UDATE：为每个文件添加了 sha1 总和

#!/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 -- 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'@'%';
)"
    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=

if [ ! -z "" ]; then 
    USER=
fi
PASS=
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" ] && [ "$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 | 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'" | $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" | $MYSQL_COMMAND -BN $COMMAND_ARGS | 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}"

The output of the grant file is like this one:

授权文件的输出是这样的：

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 > $OUTPUT/dbbackup-$TIMESTAMP-$db.sql
        gzip $OUTPUT/dbbackup-$TIMESTAMP-$db.sql
done
echo "Finished MySQL Backup";
echo 'date';

Answer 9

回答by Kernelv5

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

我制作了一个脚本，它将采用除 information_schema 和 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

将创建日期和时间 wise 文件夹，将数据库的 db 文件夹名称 wise 和 gunzip 保存起来。我今天创建了它，发现工作 100% ok

##代码##

MySQL 如何自动将所有mysql数据库备份到sql语句中？

提问by i need help

回答by gahooa

回答by Sampson

MySQL Administrator

MySQL 管理员

mysqldump and --all-databases

mysqldump 和 --all-databases

回答by Dan Loewenherz

回答by bradym

回答by shylent

回答by CONvid19

回答by Maximus

回答by Gonzalo Aguilar Delgado

回答by Kernelv5

相关推荐

最近更新

标签

MySQL 如何自动将所有mysql数据库备份到sql语句中？

提问by i need help

回答by gahooa

回答by Sampson

MySQL Administrator

MySQL 管理员

mysqldump and --all-databases

mysqldump 和 --all-databases

回答by Dan Loewenherz

回答by bradym

回答by shylent

回答by CONvid19

回答by Maximus

回答by Gonzalo Aguilar Delgado

回答by Kernelv5

相关推荐

MySQL 选择具有关键字名称的列

是否有为 SQL Server 生成完整数据库 DDL 的工具？Postgres 和 MySQL 呢？

如何在我的 sql 数据库中存储一对多关系？(MySQL)

MySQL Zend_Db：如何从表中获取行数？

相关推荐

最近更新

标签