Summary of Windows MySQL Backup method (automatic backup)

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 "A=%date%%time:~0,2%_%time:~3,2%" e:/backdata/tool/mysqldump.exe-uroot-p111111--database database1 database2> "e:/backdata/back_up (%a%). sql"

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.

my own MySQL backup instance xcopy

First step: New MySQL Database backup script file
Create a new Notepad and rename it to: Mysql_backup.bat then right-click to select Edit, paste the following section in and modify the save, such as I want to back up Activecode This database, the script is as follows:

mysql_backup.bat**************************************** ***************

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

mysql_backup.bat**************************************** ***************

Above the D disk and Db_backup, and other directories can be modified, the script means to copy Taobao this directory to D disk db_backup This directory inside the directory of the name of the day, such as: d:/backup/2010-05-23/, please note that, Many people on the Internet reprinted the said to%date:~4,10% this as the day directory is wrong.

Step Two: Create a new Windows scheduled task　　

Then create a new scheduled task, add the Mysql_backup.bat, set to run the next few hours a day.

Step Three: Database recovery

Replace the Backed-up folder.

Another way is through MySQL mysqldump database back into the. sql file, the disadvantage of this method is that the database is small, but the database is slightly larger, such as more than 1g, especially slow, and super occupy memory, speed is absolutely slower than copying files, And the database is still inaccessible at the time of backup, which is not as good as the way I gave it.

The above method only has the server control right or the VPS effective, the virtual host can only use phpMyAdmin or the imperial backup King to back up.