Several ways to automatically backup Windows MySQL

1, copyDateFolder Backup
============================
Hypothetical environment:
MySQLInstallation Location:C:\MySQL
The Forum database name is:BBS
database backup destination:C:\db_bak\
============================
NewDb_bak.bat, write the following code


Code start*****************************
net stop MySQL
xcopy C:\mysql\data\bbs\*.* c:\db_bak\bbs\%date:~0,10%\/s/i
net start MySQL
Code End *****************************

then useWindowsof the"Scheduled Tasks"execute the batch script at timed intervals. (ex: Every morning3Point ExecutionBack_db.bat)
Explanation: Backup and restore operations are relatively simple, integrity is relatively high, control the backup cycle is more flexible, for example, with%date:~0,10%. This method is suitable for standalone hosts butMySQLusers with no management experience. The disadvantage is that more space is occupied, the backup periodMySQLwill be disconnected for a short time (for example:30Mtime-consuming database5saround),for%date:~0,10%reference to Usage.


2 ,mysqldumpbacked up intoSQLfile
==============
Hypothetical environment:
MySQLInstallation Location:C:\MySQL
The Forum database name is:BBS
MySQL RootPassword:123456
database backup destination:D:\db_backup\

Script:


REM *******************************code start*****************************
@echo off

Set "ymd=%date:~,4%%date:~5,2%%date:~8,2%"
C:\MySQL\bin\mysqldump--opt-u root--password=123456 bbs >d:\db_backup\bbs_%ymd%.sql

@echo on
REM *******************************code end*****************************

Save the above code asBackup_db.bat
then useWindowsof the"Scheduled Tasks"execute the script at timed intervals. (ex: Every morning5Point ExecutionBack_db.bat)

Description: This method does not have to close the database and can back up files by name every day.

through%date:~5,2%to assemble the current date, the combined effect isyyyymmdd,datecommand gets the date format default toYyyy-mm-dd (If this format is not available byPausecommand to pause the command line window to see through%date:~,20%get the current computer date format), so through%date:~5,2%you can get the two characters starting with the fifth character in the date, such as today for2009-02-05,through%date:~5,2%You can get Geneva. (The index of the date string is from0start of)


3 , usingWinRARtheMySQLdatabase for scheduled backups.
forMySQLBackup, the best way is to backup directlyMySQLof the databaseDatadirectory. The following provides a useWinRARto theDataA method for a scheduled backup of the directory.

first of all, of course,WinRARinstalled on the computer.

write the following command to a text file

Code start*****************************
net stop MySQL

C:\progra~1\winrar\winrar a-ag-k-r-s D:\mysql.rar d:\mysql\data

net start MySQL
Code end*****************************

save, and then modify the text file's extension toCMD. Go to Control Panel, open the scheduled task, double-click"Add a scheduled task". In the Scheduled Tasks wizard, locate theCMDfile, and then specify a run time for this task and the password of the account used to run it.
The disadvantage of this method is that it takes more time to compress the backup period ,MySQLdisconnects more time than the first method, but is good for file naming.

The above content comes from the network, is the original part

My own environment is Windows R2 mysql5.6. I use the second method, which is to use mysqldump to do the backup. My MySQL is installed in the C drive Program Files (x86) \mysql5.6 This directory, I created the database is mytest. It's a good idea to authorize root before you create it. Here is the contents of my backup script.

REM ********************code start*******************

@echo off

Set "ymd=%date:~,4%%date:~5,2%%date:~8,2%"
C \ "program Files (x86)" \mysql5.6\bin\mysqldump--opt-u root-p123456 Mytest>c:\mysqldatabck\mysqltest_%ymd%.sql

@echo on

REM ********************code end*******************

Here are some of the problems I encountered in writing scripts

The REM in the 1 code is the key word for the comment, which means that the line is commented, and REM must be followed by an overhead lattice.

2 execution code is best done under DOS, do not write a good script directly. Because the DOS will show the execution process, can see the error message.

3 The Backup Restore command is

Mysql-uroot-p mytest < Mytest20160311.sql, enter the password. Many of the documents on the Web are written using the mysqldump command, which should be wrong.

4 Set up a Windows scheduled task so that you can make regular backups of the database.

Only this article dedicated to those first time to write Windows script, the first time to do database backup of the small white (I am also small white).

Several ways to automatically backup Windows MySQL