Automatic timed MySQL Backup database method under Windows

The procedure is very simple, it is done in three parts:

1. Create bat file, bat file contents

The code is as follows	Copy Code
@echo off Set Host=localhost Set Username=root Set Passward=ansure Set Database=weihe Set backup_path=d:/db_backup/ echo "Mysql Backup, please waitting" mysqldump-h%host%-u%username%-p%passward%--skip-lock-tables%database% >%backup_path%/%database%_%date:~0,10% . sql echo "OK!"

Here's an explanation.
The first sentence, the General command of the bat file.
The second sentence, set the month format, for the file name does not repeat, and know the current file generation date, the resulting format is: back_up (2013-04-25 Thursday 18_00). This section of SQL Red.
In the third sentence, I will copy Mysqldump.exe from the installation file to this directory, the purpose is to prevent the space (E:/backdata/tool/mysqldump.exe this paragraph can not have spaces, space needs very special processing, here do not write detailed). -U is followed by user name, note that there is no space Oh,-U-root is going to be wrong, same as-P. --database can be followed by multiple databases.

2. Create a scheduled task, execute BAT file

To a new scheduled task in the Windows Task plan, my scheduled task executes 1 months to execute the above bat file, a full backup of the database, in SQL form, normally set at around 3 o'clock in the morning.
Why one months, because we also have incremental backup, if too often, consumption of disk space, also consumes a lot of database performance. So the proposal is a longer time period, do a good incremental backup can be dealt with.

Incremental backup of 3.mysql

Through the above scheme, you can restore the database to the last month's data, but if only the above backup scheme, this month's data can not be retrieved. With the incremental backup solution, you can restore to the past any time, to a more practical degree. So how does an incremental backup back up?
Just add this configuration to the end of the My.ini file:

The code is as follows	Copy Code
#log Log-error=d:/backdata/logs/error.log Log_bin=d:/backdata/logs/logbin.log long_query_time=2 Log-slow-queries=d:/backdata/logs/slowquery.log

What's useful here is the second row, the generated bin file. Several other logs that are not for backup but feel useful, and occupy little space, are good for analyzing MySQL problems.

4. Direct backup of database files

Create a new Db_bak.bat and write the following code

Code start*****************************

The code is as follows	Copy Code
net stop MySQL xcopy C:/mysql/data/bbs/*.* c:/db_bak/bbs/%date:~0,10%//s/i net start MySQL

Code End *****************************

You can then use Windows Scheduled Tasks to execute the batch script at timed intervals. (For example: Perform Back_db.bat 3 o'clock in the morning every day)
Explanation: Backup and restore operations are relatively simple, integrity is relatively high, control backup cycle more flexible, for example, with%date:~0,10%. This method is suitable for users who have a standalone host but have no management experience with MySQL. The disadvantage is to occupy more space, backup period MySQL will be a short time to disconnect (for example: about 30M database time is about 5s), for the use of%date:~0,10% reference