Several methods of MySQL scheduled backup database under Windows

First: Create a new batch file Backup.dat

The code is as follows:

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: 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. (ex: Daily 3 o'clock in the morning execution of Backup.bat)
Explanation: The operation of backup and recovery is relatively simple, the integrity is relatively high, the control backup period is more flexible. This method is suitable for users with independent hosts but no management experience with MySQL. The disadvantage is that there is more space, and MySQL will be disconnected for a short time during backup (for example, about 5s for a database of about 30M).
References to TIME parameters:
%date:~0,10%//Extract date information
%date:~-3%//Extract Day of the week information
Time and minutes in%time:~0,5%//extraction Time
%time:~0,-3%//extract time and minute and second information

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

The code is as follows:

@echo off

C:/mysql/bin/mysqldump--opt-u root--pass Word @echo on

The third type: The MySQL database is scheduled to be backed up using WinRAR. Save the above code as Backup_db.bat
You can then use the Windows scheduled task to execute the script at timed intervals. (ex: Daily 5 o'clock in the morning execution of Back_db.bat)
Description: This method does not have to close the database and can back up files by name every day.
By%date:~5,2% to combine the current date, the combined effect of the yyyymmdd,date command gets the date format default to YYYY-MM-DD (if not this format can be paused by the Pause Command Line window to see through the%date:~,20% Get 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 by%date:~5,2% you get 02. (The index of the date string is starting from 0)

A good way to backup MySQL is to back up the data directory of the MySQL database directly. The following provides a way to use WinRAR to perform a scheduled backup of the data directory.

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:

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

After executing the above file, a compressed file such as: Mysql_20130803004138.rar is generated. WinRAR parameter explanation:
A: Adding files to Compressed files
-AG: Generate a compressed file name using the current date
-K: Lock Compressed files
-R: Recursive sub-directory
-S: Create a solid compressed file

Go to Control Panel, open the scheduled task, and double-click Add Scheduled Task. Locate the Backup.bat file in the Scheduled Tasks wizard, and then specify a runtime and password for the run time for this task.
The disadvantage of this method is that it takes more time to compress, and it takes time for the backup to be compressed, and MySQL disconnects more time than the first method, but it is good for file naming.

1. Create the Db_backup folder in the D drive and the new Backdb.bat.

2. Add the code to the Backdb.bat:

The code is as follows:

echo Fetch 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 date=%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--flush-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 user name and password, I am using D:/sense/mysql/bin.

3. Double-click to run this script to see if the Dbname20111207_200445.sql file is generated, and if so, the script has no errors.

4. Go to the Control Panel, add a scheduled task to the task plan, the batch to be executed to browse the way to join the task plan, and set the execution time, it is best to choose daily execution, so that the daily automatic backup database.

This article is from the "Tibetan Treasure Court" blog, please be sure to keep this source http://heyhongwu.blog.51cto.com/9921842/1627738

Several methods of MySQL scheduled backup database under Windows