在 Windows 服务器上自动备份 MySQL 数据库

MySQL Database backup automatically on a windows server

提问by Coderwannabe

Is there a way to back up MySQL database automatically at certain times of the day for designated servers or send an email with an attachment.. Which ever do you think is the best and safest way to achieve this?

有没有办法在一天中的特定时间为指定的服务器自动备份 MySQL 数据库或发送带有附件的电子邮件.. 你认为实现这一目标的最佳和最安全的方法是什么？

采纳答案by martin

I would use Windows Task Scehduler/cron (depending on your system) and mysqldump. Scroll down in the link, it includes some insights how to achieve what you want.

我会使用 Windows Task Schehduler/cron（取决于你的系统）和mysqldump。在链接中向下滚动，其中包含一些如何实现您想要的目标的见解。

回答by Tarun Gupta

Best way to do this would be

最好的方法是

mysqldump.exe --user=YourUserName --password=YourPassword --host=localhost --port=3306 --result-file="Path\dump.sql" --databases "DatabaseName1" "Database2"


mysqldump.exe --user=root --password=root  --host=localhost --port=3306 --result-file="c:\www\db\backup.%date:~10,4%%date:~7,2%%date:~4,2%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "dbtest1" "dbtest2"

The pattern backup.%date:~10,4%%date:~7,2%%date:~4,2%.sqlwill create a unique name (backup20131010.sql) each time it will run

模式backup.%date:~10,4%%date:~7,2%%date:~4,2%.sql将在backup20131010.sql每次运行时创建一个唯一名称 ( )

Now you just need to call this command in your task scheduler. That's it. :)

现在你只需要在你的任务调度程序中调用这个命令。就是这样。:)

回答by Jess Stone

You can add one of these commands to Windows task scheduler:

您可以将以下命令之一添加到Windows task scheduler：

mysqldump –-user [username] –-password=[password] [database name] > [dump file]

or in a compact way:

或以紧凑的方式：

mysqldump –u[username] –p[password] [database name] > [dump file]

or:

或者：

mysqldump -u[user] -p[password] --result-file="c:\<path>\backup.%DATE:~0,3%.sql" [database]

回答by Mitoxys

databaseW.2016,06,29-22,31,48-15.sql

databaseW.2016,06,29-22,31,48-15.sql

@echo off
rem Backup Database (Daily,via Task Scheduler)
rem databaseW
set filename="c:\xampp\dbk\databaseW.%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2%.sql"
c:\xampp\mysql\bin\mysqldump.exe --user=root --password=dell@root --host=localhost --port=3306 --result-file=%filename% --default-character-set=utf8 --single-transaction=TRUE --databases "databaseW"

To create file whose name is based on the date and time, use %date%and %time%. Note that the 2 variables are based on locale and cmd shell version

要创建名称基于日期和时间的文件，请使用%date%和%time%。请注意，这 2 个变量基于语言环境和 cmd shell 版本

• open the cmd windows
• input echo %time%and echo %date%mine is 22:11:16.80,06/29/2016 Wed
• substr the variable through %variable:~startpos,length%I want the time delimited by comma, so the cmd goes echo %time:~0,2%,%time:~3,2%,%time:~6,2%,%time:~9,2%
• to get a filename like databaseW.2016,06,29-22,31,48-15.sqluse set filename="databaseW.%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2%.sql"
• check the dateand timein advance
• use the --result-fileoption instead of >; According to the Mysql Manuel, the charset of file saved using ">" is UTF-16, while the --result-filefollows the --default-character-set
• save to file BackpDay-databaseW.cmd
• add it to a new task Actionand set a trigger (Windows Task Scheduler)

• 打开 cmd 窗口
• 输入echo %time%和echo %date%我的是22:11:16.80，06/29/2016 Wed
• substr通过%variable:~startpos,length%我想要用逗号分隔的时间变量，所以cmd去 echo %time:~0,2%,%time:~3,2%,%time:~6,2%,%time:~9,2%
• 获取文件名，如databaseW.2016,06,29-22,31,48-15.sql使用set filename="databaseW.%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2%.sql"
• 检查date并time提前
• 使用--result-file选项而不是>; 根据Mysql Manuel，使用“>”保存的文件的字符集是UTF-16，而--result-file以下是--default-character-set
• 保存到文件 BackpDay-databaseW.cmd
• 将其添加到新任务Action并设置触发器（Windows 任务计划程序）

回答by MShNajar

I did the work, similar to what other people explained through... but with little difference and extra work:

我做了这项工作，类似于其他人解释的那样......但几乎没有区别和额外的工作：

1) I made a batch file
2) Ran that batch file through windows scheduler
3) Made appropriate schedule for that task
4) Inside the batch file these steps are executed:

1) 我制作了一个批处理文件
2) 通过 Windows 调度程序运行该批处理文件
3) 为该任务制定适当的计划
4) 在批处理文件中执行以下步骤：

• 4-1) Prepared a file name based on current date
• 4-2) Got a backup by mysqldump.exe in the corresponding directory & file name
• 4-3) Made a compress file through 7-Zip app(install it) & then delete the uncompressed backup
• 4-4) Put a copy on our network File-Server

• 4-1) 根据当前日期准备文件名
• 4-2) mysqldump.exe 备份到对应目录&文件名
• 4-3) 通过 7-Zip 应用程序制作压缩文件（安装它），然后删除未压缩的备份
• 4-4) 在我们的网络文件服务器上放一个副本

Here is a script(.bat) sample:

这是一个脚本（.bat）示例：

@echo off
set current=%date:~10,4%%date:~4,2%%date:~7,2%
set filename="E:\MySQL Backups\DBName-%current%.sql"
set filename2="E:\MySQL Backups\DBName-%current%.zip"
echo %filename%
cd "E:\MySQL Backups"
C:\"Program Files"\MySQL\"MySQL Server 5.5"\bin\mysqldump.exe db_name --user=root --password=rootpass --host="127.0.0.1" --port=instancePort --result-file=%filename% --default-character-set=utf8 --single-transaction=TRUE
echo backup-finished

if exist %filename% (
    "C:\Program Files-Zipz.exe" a %filename2% %filename%
    echo zip-finished
    del %filename%
)
if exist %filename2% (
    copy %filename2% "\192.168.x.x\MySQL Backups"
    echo copy-finished
)