Several methods of MySQL daily scheduled backup database under Windows

First: New batch file Backup.dat, which enters the following code:

The code is as follows	Copy Code
net stop MySQL xcopy "C:/Program files/mysql/mysql Server 5.0/data/piaoyi/*.*" d:/db_backup/%date:~0,10%//y net start MySQL

Note: If there are spaces in the path in the batch command, you must enclose the path with double quotes!
You can then use Windows Scheduled Tasks to execute the batch script at timed intervals. (For example: Perform Backup.bat 3 o'clock in the morning every day)
Explanation: Backup and restore operations are relatively simple, integrity is relatively high, control backup cycle is more flexible. This method is suitable for users who have a standalone host but have no management experience with MySQL. The disadvantage is that a lot of space, the backup period MySQL will be a short time to disconnect (for example: about 30M of the database takes about 5s).
Reference to TIME parameters:
%date:~0,10%//Extraction Date
%date:~-3%//Extract Week information
%time:~0,5%//extraction time and minutes
%time:~0,-3%//Extraction time and minute and second information

Second: mysqldump Backup to SQL files
==============
Hypothetical environment:
MySQL Installation Location: C:/mysql
Forum database name: BBS
MySQL Root Password: 123456
Database backup destinations: d:/db_backup/

Script:

The code is as follows	Copy Code
@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

Save the above code as Backup_db.bat
You can then use Windows Scheduled Tasks to execute the script at timed intervals. (For example: Perform Back_db.bat 5 o'clock in the morning every day)
Note: This method can be used to back up files by the name of each day, without shutting down the database.
The current date is grouped by%date:~5,2%, and the combined effect is the date format of the Yyyymmdd,date command, which defaults to YYYY-MM-DD (if this is not the format you can pause the command line window through the pause command. See through%date:~,20% Gets the current computer date format), so you can get the two characters starting with the fifth character in the date by%date:~5,2%, for example, today is 2009-02-05 and 02 is available through%date:~5,2%. (The subscript for the date's string is starting at 0)

The third type: using WinRAR to make a regular backup of MySQL database.
for MySQL backup, a good way is to directly back up the MySQL database data directory. A method of using WinRAR to perform a scheduled backup of the data directory is provided below.

First of all, of course, install the WinRAR on the computer.

Write the following command to a text file, such as Backup.bat

The code is as follows	Copy Code
net stop MySQL "C:/Program Files/winrar/winrar.exe" a-ag-k-r-s d:/db_backup/mysql_.rar "C:/Program Files/MySQL/MySQL Server 5.0/data /" net start MySQL

WinRAR parameter explanation:
A: Adding files to a compressed file
-ag: Use current date to generate compressed file name
-K: Locking Compressed files
-R: Recursive subdirectories
-S: Create solid compressed file

When the above file is executed, a compressed file is generated, such as: Mysql_20130803004138.rar.
Enter the control Panel, open the Scheduled task, and double-click Add Scheduled task. In the Scheduled Task Wizard, locate the Backup.bat file, and then specify a run-time and run-time account password for this task.
The disadvantage of this approach is that it takes a lot of time to compress during backup time, and MySQL disconnects more time than the first method, but it is good for file naming.

1. Create Db_backup folder in D disk and new Backdb.bat.

2. Add code to Backdb.bat:

code is as follows

copy code

Echo Take date, Time variable Value set yy=%date:~0,4% Set mm=%date:~5,2% Set dd=%date:~8,2% if/i%time:~0,2% LSS set Hh=0%time: ~1,1% if/i%time:~0,2% Geq set hh=%time:~0,2% Set mn=%time:~3,2% Set ss=%time:~6,2% Set da te=%yy%%mm%%dd% Set time=%hh%%mn%%ss% Set filename=%date%_%time% "C:/Program Files (x86)/ Mysql/mysql Server 5.0/bin/mysqldump.exe "-uroot-pxxx--opt--default-character-set=utf8-e--triggers-r--hex-blob--f Lush-logs-x dbname > C:/db_backup/dbname%filename%.sql Echo Export is complete #pause

Here to pay attention to your MySQL installation path and the corresponding database username and password, I am using D:/sense/mysql/bin.

3. Double-click Run this script to see if the Dbname20111207_200445.sql file will be generated, if there is no error in the script.

4. Enter the control Panel, add the scheduled task in the task plan, the batch processing to be performed to join the task plan, and set the execution time, it is best to choose daily execution, so that the daily backup of the database automatically.